I am trying to create a windows form application that is connected to a MySQL da
ID: 3769883 • Letter: I
Question
I am trying to create a windows form application that is connected to a MySQL database. I need to run a query against the database and post the results into a multiline textbox.
How can I modify the following code to display each record on a new line. When I use " " it almost displays exactly as I would like it too. However, it does not go to a new line after each record. and if I use " " instead of " " each column displays on a new row.
MySqlConnection conn = new MySqlConnection("server=localhost;user id=testID;persistsecurityinfo=True;database=TestDB;pwd=testPassword;persistsecurityinfo=True"");
conn.Open();
DataSet ds = new DataSet();
MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * from table", conn);
adapter.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
{
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
txtData.Text += row[i].ToString() + " "; // " "
}
Explanation / Answer
#include <iostream>
#include <sstream>
#include <memory>
#include <string>
#include <stdexcept>
/* MySQL Connector/C++ specific headers */
#include <driver.h>
#include <connection.h>
#include <statement.h>
#include <prepared_statement.h>
#include <resultset.h>
#include <metadata.h>
#include <resultset_metadata.h>
#include <exception.h>
#include <warning.h>
#define DBHOST "tcp://127.0.0.1:3306"
#define USER "root"
#define PASSWORD "admin"
#define DATABASE "test"
#define NUMOFFSET 100
#define COLNAME 200
using namespace std;
using namespace sql;
static void retrieve_data_and_print (ResultSet *rs, int type, int colidx, string colname) {
/* retrieve the row count in the result set */
cout << " Retrieved " << rs -> rowsCount() << " row(s)." << endl;
cout << " CityName" << endl;
cout << "--------" << endl;
/* fetch the data : retrieve all the rows in the result set */
while (rs->next()) {
if (type == NUMOFFSET) {
cout << rs -> getString(colidx) << endl;
} else if (type == COLNAME) {
cout << rs -> getString(colname) << endl;
} // if-else
} // while
cout << endl;
} // retrieve_data_and_print()
static void retrieve_dbmetadata_and_print (Connection *dbcon) {
if (dbcon -> isClosed()) {
throw runtime_error("DatabaseMetaData FAILURE - database connection closed");
}
cout << " Database Metadata" << endl;
cout << "-----------------" << endl;
cout << boolalpha;
/* The following commented statement won't work with Connector/C++ 1.0.5 and later */
//auto_ptr < DatabaseMetaData > dbcon_meta (dbcon -> getMetaData());
DatabaseMetaData *dbcon_meta = dbcon -> getMetaData();
cout << "Database Product Name: " << dbcon_meta -> getDatabaseProductName() << endl;
cout << "Database Product Version: " << dbcon_meta -> getDatabaseProductVersion() << endl;
cout << "Database User Name: " << dbcon_meta -> getUserName() << endl << endl;
cout << "Driver name: " << dbcon_meta -> getDriverName() << endl;
cout << "Driver version: " << dbcon_meta -> getDriverVersion() << endl << endl;
cout << "Database in Read-Only Mode?: " << dbcon_meta -> isReadOnly() << endl;
cout << "Supports Transactions?: " << dbcon_meta -> supportsTransactions() << endl;
cout << "Supports DML Transactions only?: " << dbcon_meta -> supportsDataManipulationTransactionsOnly() << endl;
cout << "Supports Batch Updates?: " << dbcon_meta -> supportsBatchUpdates() << endl;
cout << "Supports Outer Joins?: " << dbcon_meta -> supportsOuterJoins() << endl;
cout << "Supports Multiple Transactions?: " << dbcon_meta -> supportsMultipleTransactions() << endl;
cout << "Supports Named Parameters?: " << dbcon_meta -> supportsNamedParameters() << endl;
cout << "Supports Statement Pooling?: " << dbcon_meta -> supportsStatementPooling() << endl;
cout << "Supports Stored Procedures?: " << dbcon_meta -> supportsStoredProcedures() << endl;
cout << "Supports Union?: " << dbcon_meta -> supportsUnion() << endl << endl;
cout << "Maximum Connections: " << dbcon_meta -> getMaxConnections() << endl;
cout << "Maximum Columns per Table: " << dbcon_meta -> getMaxColumnsInTable() << endl;
cout << "Maximum Columns per Index: " << dbcon_meta -> getMaxColumnsInIndex() << endl;
cout << "Maximum Row Size per Table: " << dbcon_meta -> getMaxRowSize() << " bytes" << endl;
cout << " Database schemas: " << endl;
auto_ptr < ResultSet > rs ( dbcon_meta -> getSchemas());
cout << " Total number of schemas = " << rs -> rowsCount() << endl;
cout << endl;
int row = 1;
while (rs -> next()) {
cout << " " << row << ". " << rs -> getString("TABLE_SCHEM") << endl;
++row;
} // while
cout << endl << endl;
} // retrieve_dbmetadata_and_print()
static void retrieve_rsmetadata_and_print (ResultSet *rs) {
if (rs -> rowsCount() == 0) {
throw runtime_error("ResultSetMetaData FAILURE - no records in the result set");
}
cout << "ResultSet Metadata" << endl;
cout << "------------------" << endl;
/* The following commented statement won't work with Connector/C++ 1.0.5 and later */
//auto_ptr < ResultSetMetaData > res_meta ( rs -> getMetaData() );
ResultSetMetaData *res_meta = rs -> getMetaData();
int numcols = res_meta -> getColumnCount();
cout << " Number of columns in the result set = " << numcols << endl << endl;
cout.width(20);
cout << "Column Name/Label";
cout.width(20);
cout << "Column Type";
cout.width(20);
cout << "Column Size" << endl;
for (int i = 0; i < numcols; ++i) {
cout.width(20);
cout << res_meta -> getColumnLabel (i+1);
cout.width(20);
cout << res_meta -> getColumnTypeName (i+1);
cout.width(20);
cout << res_meta -> getColumnDisplaySize (i+1) << endl << endl;
}
cout << " Column "" << res_meta -> getColumnLabel(1);
cout << "" belongs to the Table: "" << res_meta -> getTableName(1);
cout << "" which belongs to the Schema: "" << res_meta -> getSchemaName(1) << """ << endl << endl;
} // retrieve_rsmetadata_and_print()
int main(int argc, const char *argv[]) {
Driver *driver;
Connection *con;
Statement *stmt;
ResultSet *res;
PreparedStatement *prep_stmt;
Savepoint *savept;
int updatecount = 0;
/* initiate url, user, password and database variables */
string url(argc >= 2 ? argv[1] : DBHOST);
const string user(argc >= 3 ? argv[2] : USER);
const string password(argc >= 4 ? argv[3] : PASSWORD);
const string database(argc >= 5 ? argv[4] : DATABASE);
try {
driver = get_driver_instance();
/* create a database connection using the Driver */
con = driver -> connect(url, user, password);
/* alternate syntax using auto_ptr to create the db connection */
//auto_ptr <Connection> con (driver -> connect(url, user, password));
/* turn off the autocommit */
con -> setAutoCommit(0);
cout << " Database connection's autocommit mode = " << con -> getAutoCommit() << endl;
/* select appropriate database schema */
con -> setSchema(database);
/* retrieve and display the database metadata */
retrieve_dbmetadata_and_print (con);
/* create a statement object */
stmt = con -> createStatement();
cout << "Executing the Query: "SELECT * FROM City" .." << endl;
/* run a query which returns exactly one result set */
res = stmt -> executeQuery ("SELECT * FROM City");
cout << "Retrieving the result set .." << endl;
/* retrieve the data from the result set and display on stdout */
retrieve_data_and_print (res, NUMOFFSET, 1, string("CityName"));
/* retrieve and display the result set metadata */
retrieve_rsmetadata_and_print (res);
cout << "Demonstrating Prepared Statements .. " << endl << endl;
/* insert couple of rows of data into City table using Prepared Statements */
prep_stmt = con -> prepareStatement ("INSERT INTO City (CityName) VALUES (?)");
cout << " Inserting "London, UK" into the table, City .." << endl;
prep_stmt -> setString (1, "London, UK");
updatecount = prep_stmt -> executeUpdate();
cout << " Creating a save point "SAVEPT1" .." << endl;
savept = con -> setSavepoint ("SAVEPT1");
cout << " Inserting "Paris, France" into the table, City .." << endl;
prep_stmt -> setString (1, "Paris, France");
updatecount = prep_stmt -> executeUpdate();
cout << " Rolling back until the last save point "SAVEPT1" .." << endl;
con -> rollback (savept);
con -> releaseSavepoint (savept);
cout << " Committing outstanding updates to the database .." << endl;
con -> commit();
cout << " Querying the City table again .." << endl;
/* re-use result set object */
res = NULL;
res = stmt -> executeQuery ("SELECT * FROM City");
/* retrieve the data from the result set and display on stdout */
retrieve_data_and_print (res, COLNAME, 1, string ("CityName"));
cout << "Cleaning up the resources .." << endl;
/* Clean up */
delete res;
delete stmt;
delete prep_stmt;
con -> close();
delete con;
} catch (SQLException &e) {
cout << "ERROR: SQLException in " << __FILE__;
cout << " (" << __func__<< ") on line " << __LINE__ << endl;
cout << "ERROR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << ")" << endl;
if (e.getErrorCode() == 1047) {
/*
Error: 1047 SQLSTATE: 08S01 (ER_UNKNOWN_COM_ERROR)
Message: Unknown command
*/
cout << " Your server does not seem to support Prepared Statements at all. ";
cout << "Perhaps MYSQL < 4.1?" << endl;
}
return EXIT_FAILURE;
} catch (std::runtime_error &e) {
cout << "ERROR: runtime_error in " << __FILE__;
cout << " (" << __func__ << ") on line " << __LINE__ << endl;
cout << "ERROR: " << e.what() << endl;
return EXIT_FAILURE;
}
return EXIT_SUCCESS;
} // main()
# CC -V
CC: Sun C++ 5.9 SunOS_i386 Patch 124864-09 2008/12/16
# CC -o mysqlconnectorc++client -g0 -xO4 -features=extensions -I/opt/coolstack/mysql_32bit/include/mysql
-I/export/expts/MySQLConnectorC++/include/cppconn -L/opt/coolstack/mysql_32bit/lib/mysql
-L/export/expts/MySQLConnectorC++/lib -lmysqlclient_r -lmysqlcppconn MySQLConnectorC++Client.cpp
# export LD_LIBRARY_PATH=/opt/coolstack/mysql_32bit/lib/mysql:/export/expts/ConnectorC++/lib/:$LD_LIBRARY_PATH
# ./mysqlconnectorc++client localhost root admin test
Database connection's autocommit mode = 0
Database Metadata
-----------------
Database Product Name: MySQL
Database Product Version: 5.1.24-rc-standard
Database User Name: root@localhost
Driver name: MySQL Connector/C++
Driver version: 1.0.5
Database in Read-Only Mode?: false
Supports Transactions?: true
Supports DML Transactions only?: false
Supports Batch Updates?: true
Supports Outer Joins?: true
Supports Multiple Transactions?: true
Supports Named Parameters?: false
Supports Statement Pooling?: false
Supports Stored Procedures?: true
Supports Union?: true
Maximum Connections: 151
Maximum Columns per Table: 512
Maximum Columns per Index: 16
Maximum Row Size per Table: 2147483639 bytes
Database schemas:
Total number of schemas = 4
1. information_schema
2. ISVe
3. mysql
4. test
Executing the Query: "SELECT * FROM City" ..
Retrieving the result set ..
Retrieved 3 row(s).
CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia
ResultSet Metadata
------------------
Number of columns in the result set = 1
Column Name/Label Column Type Column Size
CityName VARCHAR 30
Column "CityName" belongs to the Table: "City" which belongs to the Schema: "test"
Demonstrating Prepared Statements ..
Inserting "London, UK" into the table, City ..
Creating a save point "SAVEPT1" ..
Inserting "Paris, France" into the table, City ..
Rolling back until the last save point "SAVEPT1" ..
Committing outstanding updates to the database ..
Querying the City table again ..
Retrieved 4 row(s).
CityName
--------
Hyderabad, India
San Francisco, USA
Sydney, Australia
London, UK
Cleaning up the resources ..
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.