MYSQL C 언어로 BLOB 타입에 파일을 입출력 시키기

정말 많은 시간을 찾은듯... 이렇게 잘나와 있을 줄은 몰랐음.

---------------------------------------------------------------------------

[출처 http://zetcode.com/tutorials/mysqlcapitutorial/] 

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 called BLOB (Binary Large Object).

mysql> describe images;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI |         |       |
| data  | mediumblob | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

This is the table, that we will use in our example. It can be created by the following SQL statement.

create table images(id int not null primary key, data mediumblob);

#include <my_global.h>
#include <mysql.h>

int main(int argc, char **argv)
{
  MYSQL *conn;

  int len, size;
  char data[1000*1024];
  char chunk[2*1000*1024+1];
  char query[1024*5000];

  FILE *fp;

  conn = mysql_init(NULL);
  mysql_real_connect(conn, "localhost", "zetcode", "passwd", "testdb", 0, NULL, 0);

  fp = fopen("image.png", "rb");
  size = fread(data, 1, 1024*1000, fp);

  mysql_real_escape_string(conn, chunk, data, size);

  char *stat = "INSERT INTO images(id, data) VALUES('1', '%s')";
  len = snprintf(query, sizeof(stat)+sizeof(chunk) , stat, chunk);

  mysql_real_query(conn, query, len);

  fclose(fp);
  mysql_close(conn);
}

In this example, we will insert one image into the images table. The image can be max 1 MB.

 fp = fopen("image.png", "rb");
 size = fread(data, 1, 1024*1000, fp);

Here we open the image and read it into the data array.

 mysql_real_escape_string(conn, chunk, data, size);

Binary data can obtain special characters, that might cause troubles in the statements. We must escape them. The mysql_real_escape_string() puts the encoded data into the chunk array. In theory, every character might be a special character. That's why the chunk array two times as big as the data array. The function also adds a terminating null character.

 
 char *stat = "INSERT INTO images(id, data) VALUES('1', '%s')";
 len = snprintf(query, sizeof(stat)+sizeof(chunk) , stat, chunk);

These two code lines prepare the MySQL query.

 mysql_real_query(conn, query, len);

Finally, we execute the query.

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.

#include <my_global.h>
#include <mysql.h>

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

  unsigned long *lengths;
  FILE *fp;

  conn = mysql_init(NULL);
  mysql_real_connect(conn, "localhost", "zetcode", "passwd", "testdb", 0, NULL, 0);

  fp = fopen("image.png", "wb");

  mysql_query(conn, "SELECT data FROM images WHERE id=1");
  result = mysql_store_result(conn);

  row = mysql_fetch_row(result);
  lengths = mysql_fetch_lengths(result);

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

  fclose(fp);
  mysql_close(conn);
}

In this example, we will create an image file from the database.

 fp = fopen("image.png", "wb");

We open a file for writing.

 mysql_query(conn, "SELECT data FROM images WHERE id=1");

We select an image with id 1.

 row = mysql_fetch_row(result);

The row contains raw data.

 lengths = mysql_fetch_lengths(result);

We get the length of the image.

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

We create the image file using the fwrite() standard function call.