[Store & retrieve blob data using MySQL C API] #c #mysql

mysql_blob_example.c

// CREATE TABLE:
// create table images(id int not null primary key, data mediumblob);

#include <stdio.h>

#include "mysql.h"

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

  unsigned long *lengths;

  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, "host", "username", "password", "database_name", 0, NULL, 0);

  fp = fopen("mysql_blob_test.jpg", "rb");
  if (fp == NULL) {
    printf("Can't open 'mysql_blob_test.jpg' for read.\n");
    goto exit;
  }
  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);

  printf("query length=%lu\n", len);
  printf("query=[%s]\n", query);

  if (0 != mysql_real_query(conn, query, len)) {
    printf("%s\n", mysql_error(conn));
  }

  fclose(fp);

  fp = fopen("mysql_blob_test_output.jpg", "wb");
  if (fp == NULL) {
    printf("Can't open 'mysql_blob_test_output.jpg' for write.\n");
    goto exit;
  }

  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);

exit:
  mysql_close(conn);
}