Chapter 6. Advanced Queries

Advanced Query

You may replace mysql_query with the better version int mysql_real_query(MYSQL *mysql, const char *query, uint length). This is the same as mysql_query but does not call strlen on the query to determine the length of the query. This is a performance improvement and is also necessary if you are using binary data with NUL characters.

If you are working with a large return set, you may not want to transfer the entire thing to the client all at once. In this case, you may replace mysql_store_result with MYSQL_RES *mysql_use_result(MYSQL *mysql). This will cause mysql_fetch_row to retrieve each individual row from the server. There are several implications of using this function:

Only one active result per connection to the database.
Ties up the server and should not be used for long-running or user-interactive sessions.
May not use mysql_data_seek

Encoding Values

When you send queries to the MySQL server, you must ensure that you encode the values properly. Such characters as ' \ newline and linefeed must be encoded. There is a utility function to help you with this task. unsigned int mysql_escape_string(char *to,const char *from,unsigned int from_length) will take a string "from" of "from_length" size and place it into "to" in its encoded form. Note that you must malloc "to" to have at least from_length*2+1 bytes in size.

An example of how to encode a simple column:

int add_binary_row(char * my_binary_object, unsigned int length)
{
int rc;
char * encoded_binary_object;
char * query;

encoded_binary_object = (char *) malloc(length*2+1);
query = (char *) malloc(length*2+100);

mysql_escape_string(encoded_binary_object,my_binary_object,length);

sprintf(query,"INSERT INTO my_table
  VALUES ('%s')",encoded_binary_object);
rc = mysql_query(&mysql,query);
free(query);
free(encoded_binary_object);
return rc;
}

You must encode all data you pass to the database, or otherwise you will get errors.

Previous | Table of Contents | Next