catalogue

Actions to be implemented:  

mysql database image storage read_image or write_image

mysql database image storage mysql_write

mysql database image storage mysql_read

  Completed code demonstration and results

reflection

Actions to be implemented:  

1. Prepare a picture and read it.
2. xxx,xxx， mysql_write_image
3. mysql_read_image
4. Write to disk

mysql database image storage read_image or write_image

Start with simple 1 and 4

  Relevant codes:

// 1
int read_image(char *filename, char *buffer){

	if(filename == NULL || buffer == NULL)  return -1;
	
	FILE *fp = fopen(filename, "rb");   //Standard c
	if(fp == NULL){
		printf("fopen failed\n");
		return -2;	
	}

	//file size
	fseek(fp, 0, SEEK_END);
	int length = ftell(fp);     //tell us file size
	fseek(fp, 0, SEEK_SET);     //fp  -->begin

	//Starting from the pointer fp position, read one byte at a time, read the length for 4 times, and only buffer
	int size = fread(buffer, 1, length, fp);
	if(size != length){
		printf("fread failed: %d\n", size);
		return -3;
	}
	
	fclose(fp);

	return size;
	
}

// 4 
int write_image(char *filename, char *buffer, int length){
	if(filename == NULL || buffer == NULL || length <=0) return -1;
	FILE *fp = fopen(filename, "wb+");   // +Indicates that no file is created
	if(fp == NULL){
		printf("fopen failed\n");
		return -2;	
	}

	int size = fwrite(buffer, 1, length, fp);
	if(size != length){
		printf("fwrite failed: %d\n", size);
		return -3;
	}

	fclose(fp);

	return size;

}

mysql database image storage mysql_write

Enter the following command in MySQL workbench, and click TBL_USER adds another column of U_IMG:

ALTER TABLE TBL_USER ADD U_IMG BLOB

  Focus on understanding the relationship between MYSQL *handle and state!!

Compared with the storage room between the satellite launch center and the space station, the MYSQL *handle is equivalent to the middle part from the launch pad to the space station.

Relevant codes:

#define SQL_INSERT_IMG_USER 	      "INSERT TBL_USER(U_NAME, U_GENGDER, U_IMG) VALUES('GQ', 'man', ?);" //? As placeholder


// 2
int mysql_write(MYSQL *handle, char *buffer, int length){

	if(handle == NULL || buffer == NULL || length <=0) return -1;
	
	MYSQL_STMT *stmt = mysql_stmt_init(handle);
	int ret = mysql_stmt_prepare(stmt, SQL_INSERT_IMG_USER, strlen(SQL_INSERT_IMG_USER));
	if(ret){
		printf("mysql_stmt_prepare: %s\n",mysql_error(handle));
		return -2;
	}

	MYSQL_BIND param = {0};
	param.buffer_type = MYSQL_TYPE_LONG_BLOB;
	param.buffer = NULL;
	param.is_null = 0;
	param.length = NULL;

	ret = mysql_stmt_bind_param(stmt, &param);  //stmt and param binding
	if(ret){
		printf("mysql_stmt_bind_param: %s\n",mysql_error(handle));
		return -3;
	}

	ret = mysql_stmt_send_long_data(stmt, 0, buffer, length);
	if(ret){
		printf("mysql_stmt_send_long_data: %s\n",mysql_error(handle));
		return -4;
	}

	ret = mysql_stmt_execute(stmt);
	if(ret){
		printf("mysql_stmt_execue: %s\n",mysql_error(handle));
		return -5;
	}

	ret = mysql_stmt_close(stmt);
	if(ret){
		printf("mysql_stmt_close: %s\n",mysql_error(handle));
		return -6;
	}
	
	return ret;
	
}

Code analysis and interpretation:

  STATEMENT this storage space has been created by the following command

	MYSQL_STMT *stmt = mysql_stmt_init(handle);
	int ret = mysql_stmt_prepare(stmt, SQL_INSERT_IMG_USER, strlen(SQL_INSERT_IMG_USER));
	if(ret){
		printf("mysql_stmt_prepare: %s\n",mysql_error(handle));
		return -2;
	}

         Now we need to bind what data and what type of data are stored in the question mark? We need to declare!! The following code implements this problem. Put the data to be transmitted in the warehouse of the satellite launch center, prepare to launch to the warehouse of the space station, and bind a parameter param.

MYSQL_BIND param = {0};
param.buffer_type = MYSQL_TYPE_LONG_BLOB;
param.buffer = NULL;
param.is_null = 0;
param.length = NULL;

ret = mysql_stmt_bind_param(stmt, &param);  //stmt and param binding
if(ret){
	printf("mysql_stmt_bind_param: %s\n",mysql_error(handle));
	return -3;
}

Note: for the explanation of all the following function statements, please refer to the MySQL 5.1 reference manual. There will be differences in different versions

 MYSQL_BIND 

This structure is used for statement input (data value sent to the server) and output (result value returned from the server). For input, it is similar to mysql_stmt_bind_param() is used to bind parameter data values to a buffer for mysql_stmt_execute() is used. For output, it is similar to MySQL_ stmt_ bind_ Used with result() to bind the result buffer for use with mysql_stmt_fetch() to get the row.

          Now we send the data to the space station:

ret = mysql_stmt_send_long_data(stmt, 0, buffer, length);
if(ret){
	printf("mysql_stmt_send_long_data: %s\n",mysql_error(handle));
	return -4;
}

 mysql_stmt_send_long_data()

my_bool mysql_stmt_send_long_data(MYSQL_STMT *stmt, unsigned int parameter_number, const char *data, unsigned long length)

Allows applications to send parameter data to the server in segments (chunks). This function can be called multiple times to send different parts of character or binary data about a column. The column must be one of the TEXT or BLOB data types

         Now the data has been sent to the space station warehouse, but not to our database server database table. Now send to the database server.

ret = mysql_stmt_execute(stmt);
if(ret){
	printf("mysql_stmt_execue: %s\n",mysql_error(handle));
	return -5;
}

 mysql_stmt_execute()

mysql_stmt_execute() executes a preprocessed query related to the statement handle. During this call, the value of the currently bound parameter marker is sent to the server, which replaces the marker with the newly provided data.

            Finally, remember to close!       

mysql database image storage mysql_read

Relevant codes:

#define SQL_SELECT_IMG_USER		 "SELECT U_IMG FROM TBL_USER WHERE U_NAME='GQ';"

int mysql_read(MYSQL *handle, char *buffer, int length){
	if(handle == NULL || buffer == NULL || length <=0) return -1;
	
	MYSQL_STMT *stmt = mysql_stmt_init(handle);
	int ret = mysql_stmt_prepare(stmt, SQL_SELECT_IMG_USER, strlen(SQL_SELECT_IMG_USER));
	if(ret){
		printf("mysql_stmt_prepare: %s\n",mysql_error(handle));
		return -2;
	}

	
	MYSQL_BIND result = {0};
	result.buffer_type = MYSQL_TYPE_LONG_BLOB;
	unsigned long total_length = 0;
	result.length = &total_length;

	ret = mysql_stmt_bind_result(stmt, &result);
	if(ret){
		printf("mysql_stmt_prepare: %s\n",mysql_error(handle));
		return -3;
	}

	ret = mysql_stmt_execute(stmt);
	if(ret){
		printf("mysql_stmt_execute: %s\n",mysql_error(handle));
		return -4;
	}

	ret = mysql_stmt_store_result(stmt);
	if(ret){
		printf("mysql_stmt_store_result: %s\n",mysql_error(handle));
		return -5;
	}

	while(1){
		
		ret= mysql_stmt_fetch(stmt);
		if(ret !=0 && ret!= MYSQL_DATA_TRUNCATED) break;

		int start = 0;
		while(start < (int)total_length){
			result.buffer = buffer + start;  //?
			result.buffer_length = 1;
			mysql_stmt_fetch_column(stmt, &result, 0, start);
			start += result.buffer_length;
		}	
	}

	mysql_stmt_close(stmt);


	return total_length;
}

Code analysis and interpretation:

        Like sending to the space station, the data returned from the space station also needs to be stored in the warehouse of the launch center. So similar to the former, the implementation of the code is the same.

MYSQL_STMT *stmt = mysql_stmt_init(handle);
int ret = mysql_stmt_prepare(stmt, SQL_SELECT_IMG_USER, strlen(SQL_SELECT_IMG_USER));
if(ret){
	printf("mysql_stmt_prepare: %s\n",mysql_error(handle));
	return -2;
}

        The returned result also needs to be bound. When it is returned, it returns a length value.

MYSQL_BIND result = {0};
result.buffer_type = MYSQL_TYPE_LONG_BLOB;
unsigned long total_length = 0;
result.length = &total_length;

ret = mysql_stmt_bind_result(stmt, &result);
if(ret){
	printf("mysql_stmt_prepare: %s\n",mysql_error(handle));
	return -3;
}

        After execution, the data is in the pipeline.

ret = mysql_stmt_execute(stmt);
if(ret){
	printf("mysql_stmt_execute: %s\n",mysql_error(handle));
	return -4;
}

        Now take out the data in the pipeline for storage, and Project practice I The store process in is similar to that in the upgrade version.

ret = mysql_stmt_store_result(stmt);
if(ret){
	printf("mysql_stmt_store_result: %s\n",mysql_error(handle));
	return -5;
}

          Next, use the while (1) loop to read and display the data, mysql_stmt_fetch and Project practice I The fetech process in is similar and equivalent to an upgraded version.

while(1){
		
	ret= mysql_stmt_fetch(stmt);
	if(ret !=0 && ret!= MYSQL_DATA_TRUNCATED) break;

	int start = 0;
	while(start < (int)total_length){
		result.buffer = buffer + start;  //?
		result.buffer_length = 1;
		mysql_stmt_fetch_column(stmt, &result, 0, start);
		start += result.buffer_length;
	}	
}    

  Key understanding:

result.buffer = buffer + start;  //?

The first buffer is the result set, and the second buffer is passed from the parameters. The two buffers share the same space and do not need to allocate another space for copy. As an image metaphor, we use trucks to pick up the data sent by the space station one by one. This truck is the buffer, and the warehouse space of the satellite launch center is the buffer.

result.buffer_length = 1;        // 1 byte

This means the length of a truck, the data of how long a truck can receive.

  Completed code demonstration and results

Before running the code:

#include<stdio.h>

#include<mysql.h>

#include<string.h>


#define GQ_DB_SERVER_IP              "192.168.80.128"
#define GQ_DB_SERVER_PORT            3306

#define GQ_DB_SERVER_USERNAME     	 "admin"
#define GQ_DB_SERVER_PASSWORD        "521125"

#define GQ_DB_SERVER_DEFAULTBD  	 "GQ_DB" 

#define SQL_INSERT_TBL_USER          "INSERT TBL_USER(U_NAME, U_GENGDER) VALUES('GQ', 'man'); "
#define SQL_SELECT_TBL_USER			 "SELECT *FROM TBL_USER;"

#define SQL_DELETE_TBL_USER			 "CALL PROC_DELETE_USER('GQ')"
#define SQL_INSERT_IMG_USER 			  "INSERT TBL_USER(U_NAME, U_GENGDER, U_IMG) VALUES('GQ', 'man', ?);" //? placeholder 

#define SQL_SELECT_IMG_USER			 "SELECT U_IMG FROM TBL_USER WHERE U_NAME='GQ';"
#define FILE_IMAGE_LENGTH			 (64*1024)



int Gq_mysql_select(MYSQL *handle){

	//mysql_real_query --> sql
	if(mysql_real_query(handle, SQL_SELECT_TBL_USER, strlen(SQL_SELECT_TBL_USER))){
		printf("mysql_real_query: %s\n",mysql_error(handle));
		return -1;
	}
	

	//sotre -->
	MYSQL_RES *res = mysql_store_result(handle);
	if(res == NULL){
		printf("mysql_store_result: %s\n", mysql_error(handle));
		return -2;
	}

	
	//rows / fields
	int rows = mysql_num_rows(res);
	printf("rows: %d\n", rows);
	
	int fields = mysql_num_fields(res);
	printf("fields: %d\n", fields);
	

	//fetch
	MYSQL_ROW row;
	while((row = mysql_fetch_row(res))){

		int i = 0;
		for(i = 0; i < fields; i++){
			printf("%s\t", row[i]);
		}
		printf("\n");
		
	}


	mysql_free_result(res);

	return 0;

}


//filename: path + filename
//buffer: store image data


// 1
int read_image(char *filename, char *buffer){

	if(filename == NULL || buffer == NULL)  return -1;
	
	FILE *fp = fopen(filename, "rb");   //Standard c
	if(fp == NULL){
		printf("fopen failed\n");
		return -2;	
	}

	//file size
	fseek(fp, 0, SEEK_END);
	int length = ftell(fp);     //tell us file size
	fseek(fp, 0, SEEK_SET);     //fp  -->begin

	//Starting from the pointer fp position, read one byte at a time, read the length for 4 times, and only buffer
	int size = fread(buffer, 1, length, fp);
	if(size != length){
		printf("fread failed: %d\n", size);
		return -3;
	}
	
	fclose(fp);

	return size;
	
}

// 4 
int write_image(char *filename, char *buffer, int length){
	if(filename == NULL || buffer == NULL || length <=0) return -1;
	FILE *fp = fopen(filename, "wb+");   // +Indicates that no file is created
	if(fp == NULL){
		printf("fopen failed\n");
		return -2;	
	}

	int size = fwrite(buffer, 1, length, fp);
	if(size != length){
		printf("fwrite failed: %d\n", size);
		return -3;
	}

	fclose(fp);

	return size;

}

// 2
int mysql_write(MYSQL *handle, char *buffer, int length){

	if(handle == NULL || buffer == NULL || length <=0) return -1;
	
	MYSQL_STMT *stmt = mysql_stmt_init(handle);
	int ret = mysql_stmt_prepare(stmt, SQL_INSERT_IMG_USER, strlen(SQL_INSERT_IMG_USER));
	if(ret){
		printf("mysql_stmt_prepare: %s\n",mysql_error(handle));
		return -2;
	}

	MYSQL_BIND param = {0};
	param.buffer_type = MYSQL_TYPE_LONG_BLOB;
	param.buffer = NULL;
	param.is_null = 0;
	param.length = NULL;

	ret = mysql_stmt_bind_param(stmt, &param);  //stmt and param binding
	if(ret){
		printf("mysql_stmt_bind_param: %s\n",mysql_error(handle));
		return -3;
	}

	ret = mysql_stmt_send_long_data(stmt, 0, buffer, length);
	if(ret){
		printf("mysql_stmt_send_long_data: %s\n",mysql_error(handle));
		return -4;
	}

	ret = mysql_stmt_execute(stmt);
	if(ret){
		printf("mysql_stmt_execue: %s\n",mysql_error(handle));
		return -5;
	}

	ret = mysql_stmt_close(stmt);
	if(ret){
		printf("mysql_stmt_close: %s\n",mysql_error(handle));
		return -6;
	}
	
	return ret;
	
}


int mysql_read(MYSQL *handle, char *buffer, int length){
	if(handle == NULL || buffer == NULL || length <=0) return -1;
	
	MYSQL_STMT *stmt = mysql_stmt_init(handle);
	int ret = mysql_stmt_prepare(stmt, SQL_SELECT_IMG_USER, strlen(SQL_SELECT_IMG_USER));
	if(ret){
		printf("mysql_stmt_prepare: %s\n",mysql_error(handle));
		return -2;
	}

	
	MYSQL_BIND result = {0};
	result.buffer_type = MYSQL_TYPE_LONG_BLOB;
	unsigned long total_length = 0;
	result.length = &total_length;

	ret = mysql_stmt_bind_result(stmt, &result);
	if(ret){
		printf("mysql_stmt_prepare: %s\n",mysql_error(handle));
		return -3;
	}

	ret = mysql_stmt_execute(stmt);
	if(ret){
		printf("mysql_stmt_execute: %s\n",mysql_error(handle));
		return -4;
	}

	ret = mysql_stmt_store_result(stmt);
	if(ret){
		printf("mysql_stmt_store_result: %s\n",mysql_error(handle));
		return -5;
	}

	while(1){
		
		ret= mysql_stmt_fetch(stmt);
		if(ret !=0 && ret!= MYSQL_DATA_TRUNCATED) break;

		int start = 0;
		while(start < (int)total_length){
			result.buffer = buffer + start;  //?
			result.buffer_length = 1;
			mysql_stmt_fetch_column(stmt, &result, 0, start);
			start += result.buffer_length;
		}	
	}

	mysql_stmt_close(stmt);


	return total_length;
}



//C U R D  

int main(){
	
	MYSQL mysql;   //Operation handle mysql | (pipeline)

	if(NULL == mysql_init(&mysql)){
		printf("mysql_init: %s\n", mysql_error(&mysql));
		return -1;
	}

	if(!mysql_real_connect(&mysql, GQ_DB_SERVER_IP, GQ_DB_SERVER_USERNAME,
		GQ_DB_SERVER_PASSWORD, GQ_DB_SERVER_DEFAULTBD, GQ_DB_SERVER_PORT, NULL, 0)){

		printf("mysql_real_connect: %s\n", mysql_error(&mysql));
		goto Exit;
	}

	//mysql --> insert
	printf("case: mysql --> insert\n");
#if 1
	if(mysql_real_query(&mysql, SQL_INSERT_TBL_USER, strlen(SQL_INSERT_TBL_USER))){
		printf("mysql_real_query: %s\n",mysql_error(&mysql));
		goto Exit;
	}

#endif

	Gq_mysql_select(&mysql);

// mysql -->delete
	printf("case: mysql -->delete\n");
#if 1
	if(mysql_real_query(&mysql, SQL_DELETE_TBL_USER, strlen(SQL_DELETE_TBL_USER))){
		printf("mysql_real_query: %s\n",mysql_error(&mysql));
		goto Exit;
	}
		
#endif
	
	Gq_mysql_select(&mysql);

	printf("case: mysql -->read image and write mysql\n");
	
	char buffer[FILE_IMAGE_LENGTH] = {0};
	int length = read_image("/home/guoqiang/share/Mysql/0voice.jpg",buffer); //Picture path
	if(length < 0) goto Exit;

	mysql_write(&mysql, buffer, length);  //

	printf("case: mysql -->read mysql and write image\n");
	
	memset(buffer, 0, FILE_IMAGE_LENGTH);       // Set to 0 to prevent dirty data in the past, so that there is no data in the buffer
	length = mysql_read(&mysql, buffer, FILE_IMAGE_LENGTH);

	write_image("b.jpg", buffer, length);      // The name of the written picture is saved as b.jpg

Exit:
	mysql_close(&mysql);

	return 0;
	
}

After operation:

  Check whether the inserted picture is the same as the returned picture, that is, whether 0voice.jpg and b.jpg are the same. If they are the same, it means success.

reflection

        How to implement the connection pool of MySQL database? You don't need to handle every time. The connection pool should support SQL statement reading and writing stored procedures, including file reading and file insertion.