This article combines Linux C/C ++ to realize MySQL image insertion and image reading, especially the specific process of database reading and writing
1. File reading related functions
- fseek() can move the file pointer to any position in the file. Its basic form is as follows:
int fseek(FILE *stream, long offset, int whence);
Among them, stream is a pointer to the file stream that has been opened; offset is the offset relative to the position indicated by the whence parameter, which can be positive, negative or zero; and whence specifies the relative offset indicated by the offset location sign. Specifically, it can take one of three values:
– SEEK_SET: Indicates that the offset is calculated from the beginning of the file;
– SEEK_CUR: Indicates to calculate the offset from the current position;
– SEEK_END: Indicates that the offset is calculated from the end of the file.
When the call is successful, the return value is 0; otherwise, a non-zero value is returned.
It should be noted that after using the fseek() function, we need to obtain the position of the current file pointer by calling the ftell() function, and ensure that the position is consistent with the position we want to set.
- ftell() is a function used to obtain the current position of the file pointer, and its prototype is as follows:
long ftell(FILE *stream);
Among them, stream means to get the file stream pointer of the current position. After the call is successful, the return value is a long type, indicating the offset (in bytes) of the current file pointer relative to the beginning of the file.
- fread() is used to read binary data from a file and store it in a memory buffer. Its prototype is as follows:
size_t fread(void *ptr, size_t size, size_t count, FILE *stream);
Among them, ptr: the address of the buffer where the data is stored after reading. size: The number of bytes to read elements at a time. count: The number of read elements. stream: file pointer. When the call is successful, the return value is the number of elements actually read, which is usually equal to the count parameter. If an end-of-file character is encountered or an error occurs during the read operation, the return value will be less than count.
- fwrite() is used to write the binary data in the memory to the file according to the specified number and size. Its prototype is as follows:
size_t fwrite(void *ptr, size_t size, size_t count, FILE *stream);
Among them, ptr: pointer to the data to be written. size: The size (in bytes) of each data block. count: The number of data blocks to write. stream: FILE structure pointer pointing to the target file.
2. stmt related functions
Using stmt (or called statement) can precompile SQL statements, which can improve code execution efficiency and security. When we use stmt, we can bind the parameter placeholder (such as “?”) that needs to be passed in the SQL query with the actual parameter value, and then the database will precompile the SQL statement and generate an execution plan. Afterwards, each time the SQL statement is executed, the plan can be executed by simply filling the actual parameter values into the placeholder positions without recompiling the entire SQL statement. This can greatly reduce the overhead of the database system and improve the response speed of the application.
1. The specific process of writing local (picture) data to the database is as follows
1. Create a MYSQL_STMT *stmt to represent the prepared statement
2. Use the mysql_stmt_init() function to initialize the MYSQL_STMT structure object stmt
3. Use the mysql_stmt_prepare() function to bind the SQL statement to be executed to the object stmt
4. Create a structure MYSQL_BIND *param to bind the variable of the parameter in the SQL statement
5. Use the mysql_stmt_bind_param() function to set the parameters required in the SQL statement
6. Use the mysql_stmt_send_long_data() function to bind binary data to the placeholder of the preprocessed SQL statement
7. Use the mysql_stmt_execute() function to execute SQL statements
8. Use the mysql_stmt_fetch() function to get the data in the result set
9. After use, use the mysql_stmt_close() function to release the MYSQL_STMT object
2. The specific process of reading (picture) data from the database is as follows
1. Create a MYSQL_STMT *stmt to represent the prepared statement
2. Use the mysql_stmt_init() function to initialize the MYSQL_STMT structure object stmt
3. Use the mysql_stmt_prepare() function to bind the SQL statement to be executed to the object stmt
4. Create a structure MYSQL_BIND *result to bind the result set of parameters in the SQL statement
5. Use the mysql_stmt_bind_result(stmt, & amp;result) function to bind the query result to the result structure
6. Use the mysql_stmt_execute() function to execute SQL statements
7. Use the mysql_stmt_store_result() function to store all result rows in a local buffer
8. Use the mysql_stmt_fetch() function to get the row data in the result set and store it in result.buffer
9. Use the mysql_stmt_fetch_column() function to obtain the result set column data and store it in the corresponding position of the buffer
9. After use, use the mysql_stmt_close() function to release the MYSQL_STMT object
#include<stdio.h>
#include <string.h>
#include <mysql.h>
#define MING_DB_IP "192.168.42.128"
#define MING_DB_PORT 3306
#define MING_DB_USENAME "admin"
#define MING_DB_PASSWORD "123456"
#define MING_DB_DEFAULTDB "MING_DB"
#define FILE_IMAGE_LENGTH (64*1024)
#define SQL_INSERT_IMG_USER "insert TBL_USER(U_NAME,U_GENDER,U_IMG) values('zxm','women',?);"
#define SQL_SELECT_IMG_USER "select U_IMG from TBL_USER where U_NAME='zxm';"
//Read the picture from the client and store it in the buffer of the node server
//filename: image path; buffer: store image
int read_image(char *filename,char *buffer){<!-- -->
if (filename == NULL || buffer == NULL ) return -1;
FILE *fp=fopen(filename,"rb"); //Read the file in binary mode
if (fp == NULL){<!-- -->
printf("fopen failed\
");
return -2;
}
//Check file size file
fseek(fp,0,SEEK_END);
int length=ftell(fp);
fseek(fp,0,SEEK_SET);
int size=fread(buffer,1,length,fp);
if (size != length){<!-- -->
printf("fread failed:%d\
", size);
return -3;
}
fclose(fp);
return size;
}
//write the picture from the buffer of the node server to the client
int write_image(char *filename,char *buffer,int length){<!-- -->
if (filename == NULL || buffer == NULL || length <=1) return -1;
FILE *fp=fopen(filename,"wb + ");
if (fp == NULL){<!-- -->
printf("fopen failed\
");
return -2;
}
int size=fwrite(buffer,1,length,fp);
if (size != length){<!-- -->
printf("fwrite failed:%d\
", size);
return -3;
}
fclose(fp);
return size;
}
//Read data from the buffer of the node server to the database
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\
", 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, ¶m);
if (ret){<!-- -->
printf("mysql_stmt_bind_param:%s\
", mysql_error(handle));
return -3;
}
ret=mysql_stmt_send_long_data(stmt,0,buffer,length);
if (ret){<!-- -->
printf("mysql_stmt_send_long_data:%s\
", mysql_error(handle));
return -4;
}
ret=mysql_stmt_execute(stmt);
if (ret){<!-- -->
printf("mysql_stmt_execute:%s\
", mysql_error(handle));
return -5;
}
ret=mysql_stmt_close(stmt);
if (ret){<!-- -->
printf("mysql_stmt_close:%s\
",mysql_error(handle));
return -6;
}
return ret;
}
//Write data from the database to the buffer of the node server
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_stat_prepare:%s\
",mysql_error(handle));
return -2;
}
MYSQL_BIND result={<!-- -->0};
result.buffer_type=MYSQL_TYPE_LONG_BLOB;
unsigned long total_length=0;
result.length= & amp; total_length;
ret=mysql_stmt_bind_result(stmt, &result);
if (ret){<!-- -->
printf("mysql_stmt_bind_result:%s\
", mysql_error(handle));
return -3;
}
ret=mysql_stmt_execute(stmt);
if (ret){<!-- -->
printf("mysql_stmt_execute:%s\
", mysql_error(handle));
return -4;
}
ret=mysql_stmt_store_result(stmt);
if (ret){<!-- -->
printf("mysql_stmt_store_result:%s\
", mysql_error(handle));
return -5;
}
while (1)
{<!-- -->
// Get the next line of data and store it in result.buffer
ret=mysql_stmt_fetch(stmt);
if (ret !=0 & amp; & amp; ret != MYSQL_DATA_TRUNCATED ) break;
int start=0;
while(start <(int)total_length ){<!-- -->
/*buffer is a pointer to the start address of the buffer, and start is an integer representing the offset.
Thus, the result of buffer + start is a pointer to a specific location in the buffer.
result.buffer points to buffer + start*/
result.buffer=buffer + start;
//Store the actual read data length in bytes
result.buffer_length=1;
/*When using the mysql_stmt_fetch_column() function to read data from the result set, only a part of the data will be read each time and stored in the
In the buffer pointed to by result.buffer. If you want to combine multiple data segments into a complete data block,
You need to use the offset to adjust the storage location. Specifically, it is possible to control where data is written by continuously modifying the value of start. */
mysql_stmt_fetch_column(stmt, & result, 0, start);
start + =result.buffer_length;
}
}
mysql_stmt_close(stmt);
return total_length;
}
int main(){<!-- -->
//Define a structure variable of type MYSQL and initialize it by calling the mysql_init() function
//Call the mysql_error() function to get information about the error
MYSQL mysql;
if (mysql_init( &mysql)==NULL){<!-- -->
printf("mysql_init:%s\
",mysql_error( & amp;mysql));
return -1;
}
//mysql_real_connect() is used to connect to the MySQL server. It requires the following parameters:
/*1. The structure pointer of MYSQL type, which has been initialized by mysql_init().
2. The host name or IP address of the MySQL server.
3. The user name used when logging in to the MySQL server.
4. The password used when logging in to the MySQL server.
5. The name of the database to be connected.
6. Port number (3306 by default).
7. Specify socket 8. Specify different connection options
*/
if(!mysql_real_connect( &mysql,MING_DB_IP,MING_DB_USENAME,MING_DB_PASSWORD,
MING_DB_DEFAULTDB,MING_DB_PORT,NULL,0)){<!-- --> //Equal to 0, unsuccessful
printf("mysql_real_connect:%s\
",mysql_error( & amp;mysql));
return -2;
}
printf("case: mysql read image and write image \
");
char buffer[FILE_IMAGE_LENGTH]={<!-- -->0};
int length=read_image("/home/zxm/share/06mysqlPicture/0voice.jpg", buffer);
if (length < 0) goto Exit;
mysql_write( & mysql, buffer, length);
printf("case:mysql read mysql and write image \
");
memset(buffer,0,FILE_IMAGE_LENGTH);
length=mysql_read( &mysql,buffer,FILE_IMAGE_LENGTH);
write_image("a.jpg", buffer, length);
Exit:
mysql_close( & mysql);
return 0;
}