Linux下通过c操作mysql(向数据库的表中存储图片并进行查询)
一、通过c连接mysql
MYSQL mysql;
if ( NULL == mysql_init ( & mysql) )
{
printf ( "mysql_init: %s\n" , mysql_error ( & mysql) ) ;
return - 1 ;
}
# define KING_DB_SERVER_IP "192.168.112.111"
# define KING_DB_SERVER_PORT 3306
# define KING_DB_USERNAME "admin"
# define KING_DB_PASSWORD "123456"
# define KING_DB_DATABASE "DB_King"
if ( ! mysql_real_connect ( & mysql, KING_DB_SERVER_IP, KING_DB_USERNAME, KING_DB_PASSWORD, KING_DB_DATABASE, KING_DB_SERVER_PORT, NULL , 0 ) )
{
printf ( "mysql_real_connect: %s\n" , mysql_error ( & mysql) ) ;
}
二、向mysql的表中插入数据
# define SQL "insert into t_1(id,name) values(1,'as');"
if ( mysql_real_query ( & mysql, SQL, strlen ( SQL) ) )
{
printf ( "mysql_real_connect: %s\n" , mysql_error ( & mysql) ) ;
}
mysql_close ( & mysql) ;
三、 查询mysql的表中的数据
int King_mysql_select ( MYSQL * mysql)
{
# define SQL_Select "select * from t_1;"
if ( mysql_real_query ( mysql, SQL_Select, strlen ( SQL_Select) ) )
{
printf ( "mysql_real_query: %s\n" , mysql_error ( mysql) ) ;
return - 1 ;
}
MYSQL_RES * res= mysql_store_result ( mysql) ;
if ( res== NULL )
{
printf ( "mysql_store_result: %s\n" , mysql_error ( mysql) ) ;
return - 2 ;
}
int rows= mysql_num_rows ( res) ;
printf ( "rows:%d\n" , rows) ;
int fields= mysql_num_fields ( res) ;
printf ( "fields:%d\n" , fields) ;
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 ;
}
三、向mysql的表中添加图片
int read_image ( char * filename, char * buffer)
{
if ( filename == NULL || buffer== NULL )
return - 1 ;
FILE * fp= fopen ( filename, "rb" ) ;
if ( fp== NULL )
{
printf ( "fopen failed\n" ) ;
return - 2 ;
}
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\n" , size) ;
return - 3 ;
}
fclose ( fp) ;
return size;
}
int write_image ( char * filename, char * buffer, int length)
{
if ( filename== NULL || buffer== NULL || length<= 0 )
return - 1 ;
FILE * fp= fopen ( filename, "wb+" ) ;
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;
}
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) ;
# define SQL_Insert "insert into t_1(id,name,u_img) values(2,'bs',?);"
int ret= mysql_stmt_prepare ( stmt, SQL_Insert, strlen ( SQL_Insert) ) ;
if ( ret)
{
printf ( "mysql_stmt_prepare: %s\n" , mysql_stmt_error ( stmt) ) ;
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) ;
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_execute: %s\n" , mysql_error ( handle) ) ;
return - 5 ;
}
mysql_stmt_close ( stmt) ;
return 0 ;
}
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, strlen ( SQL_Select_Img) ) ;
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_bind_result: %s\n" , mysql_error ( handle) ) ;
return - 3 ;
}
mysql_stmt_execute ( stmt) ;
mysql_stmt_store_result ( stmt) ;
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;
}
int main ( )
{
# define FILE_IMAGE_LENGTH ( 64 * 1024 )
printf ( "case:mysql-->read_image and write mysql\n" ) ;
char buffer[ FILE_IMAGE_LENGTH] = { 0 } ;
int length= read_image ( "01.jpg" , buffer) ;
if ( length< 0 ) goto Exit;
mysql_write ( & mysql, buffer, length) ;
printf ( "case:mysql-->read_image and write mysql\n" ) ;
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 ;
}