It's easy to connect to the database and print out a table, as shown below.
However, the documentation does not mention the data types of each ptr returned by 'mysql_fetch_row'. They seem to be char* but I'm not sure what the encoding is. It seems to just convert whatever data was actually stored in the database into a string, which is not always the correct way to represent it.
I can get a MYSQL_FIELD structure corresponding to each field which tells me the underlying SQL data type (eg, FIELD_TYPE_TIMESTAMP, FIELD_TYPE_SET) but I don't see any way to actually retreive the data in its originally structured form, which makes this API essentially useless for anything but debug or demonstration purposes.
Clearly, there must be a way to retrieve the fields in some SQL specific data structures corresponding to each SQL datatype, but how?
bool mysql_test()
{
const char *hostname = "";
const char *dbname = "";
const char *username = "";
const char *password = "";
std::cout << "Initializing mysql..\n";
MYSQL *mysql = mysql_init(NULL);
std::cout << "Connecting: " << hostname << "\n"
<< " dbname=" << dbname << "\n"
<< " username=" << username << "\n"
<< " password=" << password <<"\n\n";
MYSQL *connection = mysql_real_connect(mysql,hostname,username,password,dbname,0,NULL,0);
if (connection == NULL)
{
std::cout << mysql_error(mysql) << "\n";
return false;
}
std::cout << "Connected\n";
const char *query = "SELECT * FROM landmark";
std::cout << "querying..\n";
if( mysql_query(connection, query) != 0 )
{
std::cout << mysql_error(mysql) << "\n";
return 1;
}
std::cout << "success\n";
std::cout << "Fetching results from query...\n";
MYSQL_RES *result = mysql_store_result(connection);
int num_rows = mysql_num_rows(result);
int num_cols = mysql_num_fields(result);
std::cout << "Got " << num_rows << " rows with " << num_cols << " cols.\n";
MYSQL_ROW row;
while ( ( row=mysql_fetch_row(result)) != NULL )
{
unsigned long *lengths = mysql_fetch_lengths(result);
for(unsigned i = 0; i < num_cols; i++)
{
printf("[%.*s] ", (int) lengths,row ? row : "NULL");
}
printf("\n");
}
mysql_free_result(result);
mysql_close(connection);
return true;
}