Previous | Table of Contents | Next
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