MySQL C API programming tutorial 2nd

Multiple statements

It is possible to execute multiple SQL statements in one query. We must set the CLIENT_MULTI_STATEMENTS flag in the connect method.

if (mysql_real_connect(con, "localhost", "user12", "34klq*", 
        "testdb", 0, NULL, CLIENT_MULTI_STATEMENTS) == NULL) 
{
    finish_with_error(con);
}

The last option of the mysql_real_connect() method is the client flag. It is used to enable certain features. The CLIENT_MULTI_STATEMENTS enables the execution of multiple statements. This is disabled by default.

if (mysql_query(con, "SELECT Name FROM Cars WHERE Id=2;\
    SELECT Name FROM Cars WHERE Id=3;SELECT Name FROM Cars WHERE Id=6")) 
{
    finish_with_error(con);
}

The query consists of three SELECT statements. They are separated by the semicolon (;) character. The backslash character (\) is used to separate the string into two lines. It has nothing to do with multiple statements.

do {  
...    
} while(status == 0);

The code is placed between the do/while statements. The data retrieval is to be done in multiple cycles. We will retrieve data for each SELECT statement separately.

status = mysql_next_result(con); 

We expect multiple result sets. Therefore, we call the mysql_next_result() function. It reads the next statement result and returns a status to indicate whether more results exist. The function returns 0, if the execution went OK and there are more results. It returns -1, when it is executed OK and there are no more results. Finally, it returns value greater than zero, if an error occured.

if (status > 0) {
    finish_with_error(con);
}

We check for error.

$ ./multiple_statements 
Mercedes
Skoda
Citroen

Example output.

Inserting images into MySQL database

Some people prefer to put their images into the database, some prefer to keep them on the file system for their applications. Technical difficulties arise when we work with millions of images. Images are binary data. MySQL database has a special data type to store binary data calledBLOB (Binary Large Object).

mysql> CREATE TABLE Images(Id INT PRIMARY KEY, Data MEDIUMBLOB);

For our examples, we create a new Images table. The image size can be up to 16 MB. It is determined by the MEDIUMBLOB data type.

char chunk[2*size+1];
mysql_real_escape_string(con, chunk, data, size);

The mysql_real_escape_string() function adds an escape character, the backslash, \, before certain potentially dangerous characters in a string passed in to the function. This can help prevent SQL injection attacks. The new buffer must be at least 2*size+1 long.

char *st = "INSERT INTO Images(Id, Data) VALUES(1, '%s')";
size_t st_len = strlen(st);

Here we start building the SQL statement. We determine the size of the SQL string using thestrlen() function.

char query[st_len + 2*size+1]; 
int len = snprintf(query, st_len + 2*size+1, st, chunk);

The query must take be long enough to contain the size of the SQL string statement and the size of the image file. Using the snprintf() function, we write the formatted output to query buffer.

if (mysql_real_query(con, query, len))
{
    finish_with_error(con);
};

We execute the query using the mysql_real_query() function. The mysql_query() cannot be used for statements that contain binary data; we must use the mysql_real_query() instead.

---

It should support the size of the picture less than 16M.

there's an error when I am using a 5M picture.

Why I got the <Address out of bounds> error???????????

101 char chunk[2*size+1];

(gdb) p size

$1 = 5791109

(gdb) s

102 mysql_real_escape_string(con, chunk, data, size);

(gdb) p chunk

$2 = 0x7ffffef6c840 <Address 0x7ffffef6c840 out of bounds>

(gdb) p data

$3 = 0x7fffffa78360 "\377\330\377\341)\376Exif"

(gdb) p size

$4 = 5791109

(gdb) s

Program received signal SIGSEGV, Segmentation fault.

0x0000000000400f40 in main (argc=1, argv=0x7fffffffe288) at insert_images.c:102

102 mysql_real_escape_string(con, chunk, data, size);

---

Selecting images from MySQL database

In the previous example, we have inserted an image into the database. In the following example, we will select the inserted image back from the database.

if (mysql_query(con, "SELECT Data FROM Images WHERE Id=1"))
{
    finish_with_error(con);
}

We select the Data column from the Image table with Id 1.

MYSQL_ROW row = mysql_fetch_row(result);

The row contains raw data.

unsigned long *lengths = mysql_fetch_lengths(result);

We get the length of the image.

fwrite(row[0], lengths[0], 1, fp);

if (ferror(fp)) 
{            
    fprintf(stderr, "fwrite() failed\n");
    mysql_free_result(result);
    mysql_close(con);

    exit(1);      
}  

We write the retrieved data to the disk using the fwrite() function call. We check for the error indicator with the ferror() function.

int r = fclose(fp);

if (r == EOF) {
    fprintf(stderr, "cannot close file handler\n");
}

After we have written the image data, we close the file handler using the fclose() function.