wiki:Docs/Prog/Manual/ApplicationLibraries/Database/MySQLclient

MySQL Client

Add the appropriate references to the MySQL client library to your project properties.

Example for command line queries:

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

int main(int argc, char* argv[])
{
	MYSQL* conn;
	MYSQL_RES* res;
	MYSQL_ROW row;

	const char* server = "192.168.1.106";
	const char* user = "pi";
	const char* password = "raspberry";
	const char* database = "test";
	const char* query = "SELECT * FROM abc;";

	if(argc > 1)
		server = argv[1];
	if(argc > 2)
		user = argv[2];
	if(argc > 3)
		password = argv[3];
	if(argc > 4)
		database = argv[4];
	if(argc > 5)
		query = argv[5];

	printf("**srv [%s] user [%s] pass [%s] db [%s] q [%s]\n", server, user, password, database, query);

	conn = mysql_init(NULL);

	if(!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0))
	{
		printf("Error connecting to DB: %s\n", mysql_error(conn));
		return 0;
	}

	printf("***\n");

	if(mysql_query(conn, query))
	{
		printf("Error query: %s\n", mysql_error(conn));
		return 0;
	}

	int rows_affected = (int) mysql_affected_rows(conn);

	printf("affected %d\n", rows_affected);

	if(rows_affected < 1) // SELECT or SHOW will not have any rows affected, so display query results
	{
		res = mysql_use_result(conn);
		int cnt = 0, i;

		while((row = mysql_fetch_row(res)) != NULL)
		{
			cnt++;
			if(cnt == 1)
			{
				for(i = 0; i < (int)res->field_count; i++)
				{

				    if(i > 50)
				    	break;

				    printf("%s,", res->fields[i].name);
				}
				printf("\n");

			}
			if(cnt > 10000)
				break;

			for(i = 0; i < (int)res->field_count; i++)
			{

			    if(i > 50)
			    	break;
			    printf("%s,", row[i]);
			}
			printf("\n");
		}
		mysql_free_result(res);
	}
	mysql_close(conn);

	return 0;
}

This example runs from the command line and if no arguments are specified assumes server at 192.168.1.106 user name "pi" password "raspberry" database named "test" and query to show all records of the database table "abc".

$ ./mysqltest 192.168.1.106 pi raspberry test "select * from abc;"
**srv [192.168.1.106] user [pi] pass [raspberry] db [test] q [select * from abc;]
***
affected -1
P_Id,LastName,FirstName,Address,City,
2,Hancock,(null),(null),Chicago,
$
$ ./mysqltest 192.168.1.106 pi raspberry test "insert into abc (P_Id,LastName,FirstName,Address,City) values (5,'ZZZZZ','ABCD','123 Oak st','Somewhere');"
**srv [192.168.1.106] user [pi] pass [raspberry] db [test] q [insert into abc (P_Id,LastName,FirstName,Address,City) values (5,'ZZZZZ','ABCD','123 Oak st','Somewhere');]
***
affected 1
$ ./mysqltest 192.168.1.106 pi raspberry test "select * from abc;"
**srv [192.168.1.106] user [pi] pass [raspberry] db [test] q [select * from abc;]
***
affected -1
P_Id,LastName,FirstName,Address,City,
2,Hancock,(null),(null),Chicago,
5,ZZZZZ,ABCD,123 Oak st,Somewhere,
$
$ ./mysqltest 192.168.1.106 pi raspberry test "delete from abc where Lastname='Hancock';"
**srv [192.168.1.106] user [pi] pass [raspberry] db [test] q [delete from abc where Lastname='Hancock';]
***
affected 1
$ ./mysqltest 192.168.1.106 pi raspberry test "select * from abc;"
**srv [192.168.1.106] user [pi] pass [raspberry] db [test] q [select * from abc;]
***
affected -1
P_Id,LastName,FirstName,Address,City,
5,ZZZZZ,ABCD,123 Oak st,Somewhere,
$
Last modified 12 years ago Last modified on 02/05/13 22:01:10

Attachments (2)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.