Retrieving structured data with libmysqlclient?
Posted by: Stu H
Date: June 05, 2012 05:32PM

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;
}

Options: ReplyQuote


Subject
Views
Written By
Posted
Retrieving structured data with libmysqlclient?
1493
June 05, 2012 05:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.