MySQL C API dynamic queries

[darkrose0510]

I've been messing about with the MySQL C API lately, and while a basic run query --> fetch results --> write to standard out works fine, I'm having a problems storing the results either as an int or char array.

Basically what I'm trying to do is run a query, then take the resutl from that query and use it in another. Along the lines of this:

#include <stdio.h>
#include <mysql.h>

int main()
{
	MYSQL *conn;
	MYSQL_RES *res;
	MYSQL_ROW row;

	char *server = "localhost";
	char *user = "testuser";
	char *password = "12345"
	char *database = "testdb";

	char query[80];
	int id;

	conn = mysql_init(NULL);
	mysql_real_connect(conn, server, user, password, database, 0, NULL, 0);

	mysql_query(conn, "SELECT id FROM test ORDER BY id DESC LIMIT 1");
	res = mysql_use_result(conn);
	row = mysql_fetch_row(res);
	id = *row[0];
	mysql_free_result(res);

	sprintf(query, "SELECT * FROM test WHERE id > &#37;d ORDER BY id DESC LIMIT 1", id);
	mysql_query(conn, query);
	row = mysql_fetch_row(res);
	printf("%s \n", row[0]);
	mysql_free_result(res);

	mysql_close(conn);
	return(0);
}

Yes I know it's somewhat pointless, just me toying with things.
Makefile looks something like this:

mysqltest:
	gcc -o mysqltest $(shell mysql_config --cflags) mysqltest.c $(shell mysql_config --libs)

This compiles fine, but segfaults when run.
Any ideas?

[Cabhan]

Where exactly does it segfault?

My first guess, without having run the program myself, is that the problem is with one of these lines:

sprintf(query, "SELECT * FROM test WHERE id > %d ORDER BY id DESC LIMIT 1", id);
mysql_query(conn, query);
row = mysql_fetch_row(res);
printf("%s \n", row[0]);
mysql_free_result(res);

The reason for this is that you never reset the value of "res" after freeing it the first time. As a result, when you use res in these two lines, you are referencing the (now-freed) memory from the first use.

[darkrose0510]

err, yeah added in res = mysql_use_result(conn); and it all works fine. A little more fiddling and it does everything I wanted it to.

The original code I wrote still doesn't work though - segfaults at id = *row[0]; - so I guess there's a typo or something silly in there goofing things up.
Oh well, I'll keep playing. Thanks Cabhan.

[darkrose0510]

Nope still problems, take the below example:

#include <stdio.h>
#include <mysql.h>

int main()
{
	MYSQL *conn;
	MYSQL_RES *res;
	MYSQL_ROW row;

	char *server = "localhost";
	char *user = "testuser";
	char *password = "12345"
	char *database = "testdb";

	char query[80];
	int id;

	conn = mysql_init(NULL);
	mysql_real_connect(conn, server, user, password, database, 0, NULL, 0);

	mysql_query(conn, "SELECT id FROM test ORDER BY id DESC LIMIT 1");
	res = mysql_use_result(conn);
	row = mysql_fetch_row(res);
	printf("%s \n",row[0]);
	id = *row[0];
	printf("%d \n",id);
	mysql_free_result(res);

	mysql_close(conn);
	return(0);
}

This prints 2 values which should be the same... but they're not. It seems the second output is always 51, regardless of what the actual value should be or what the first value is. Removing the first printf() still outputs 51, so it's not a case of an error from using row[0] twice.

[darkrose0510]

never mind.

change id to char, and id = *row[0]; to sprintf(id,"%s",row[0]); and it all works perfectly... maybe I need to sleep instead of playing with code.

[lostshell]

other solution, if you need 'id' as int, you can convert it with atoi
id = atoi( row[0] );