前言:
最近正好在项目中接触到MySQL数据库,无聊时候突然想把照片存储到数据库中,这一想法让我不断的查找资料和编写代码的实践和测试下,今天最终在Linux下实现这一功能.
//dbproc.c
/*************************************************************
FileName : dbproc.c
FileFunc : MySQL数据库操作实现
Version : V0.1
Author : Sunrier
Date : 2012-06-23
Descp : Linux下使用C语言访问MySQL函数
*************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <stdarg.h>
#include <mysql.h>
#define DEBUG __FILE__,__LINE__
static MYSQL s_my_connection;
static int s_iDbconnected = 0; /*数据库连接标志 连接时为1,断开时为0*/
/*程序调试函数*/
void debug(char *pFileName,int iLine,const char *fmt, ...)
{
char szTemp[256];
va_list vap;
/*memset(szTemp,0,sizeof(szTemp));*/
va_start(vap,fmt);
fprintf(stderr,"[%s][%d]:",pFileName,iLine);
memset(szTemp,0,sizeof(szTemp));
vsprintf(szTemp,fmt,vap);
va_end(vap);
fprintf(stderr,"%s\n",szTemp);
fflush(stderr);
}
/*测试MySQL客户端版本*/
void mysql_version( void )
{
printf("MySQL client version : %s ! \n",mysql_get_client_info());
}
/*登陆MySQL*/
int mysql_login(char* pServer,char *pUser,char *pPassword,char *pDataBase)
{
MYSQL *conn_ptr = NULL;
int iRetCode = -1;
unsigned int uiTimeOut = 7;
if( s_iDbconnected )
return 0;
conn_ptr = mysql_init(&s_my_connection);
if( !conn_ptr )
{
fprintf(stderr,"mysql_init failed ! \n");
return EXIT_FAILURE;
}
iRetCode = mysql_options(&s_my_connection,MYSQL_OPT_CONNECT_TIMEOUT,(const char *)&uiTimeOut);
if( iRetCode )
{
fprintf(stderr,"MySQL Connection is timeout! \n");
return EXIT_FAILURE;
}
conn_ptr = NULL;
conn_ptr = mysql_real_connect(&s_my_connection,pServer,pUser,pPassword,pDataBase,0,NULL,0);
if( conn_ptr )
{
printf("MySQL Connection success!\n");
s_iDbconnected = 1;
}
else
{
fprintf(stderr,"MySQL Connection failed!\n");
if( mysql_errno(&s_my_connection) )
{
fprintf(stderr,"Connection error %d: %s!\n",mysql_errno(&s_my_connection),mysql_error(&s_my_connection));
return EXIT_FAILURE;
}
}
return EXIT_SUCCESS;
}
/*退出MySQL*/
void mysql_logout( void )
{
if( s_iDbconnected )
mysql_close(&s_my_connection); /*关闭连接*/
s_iDbconnected = 0 ;
}
int mysql_proc( void )
{
return 0;
}
/*向MySQL数据库中存储图片*/
int mysql_store_image(char *pFileName,char *pImageTableName,unsigned char ucIdFlag,int iId)
{
FILE *fp;
char szImageName[31];
char szImageData[1024*1000];/*图片大小最大不超过1M*/
char szStoreImageData[2*1024*1000+1];
char szSql[2*1024*1000+1];
unsigned long ulReadLength = 0,ulStoreLength = 0;
int iRetCode = -1;
fp = fopen(pFileName,"rb");
if( NULL==fp )
{
fprintf(stderr,"This file: [ %s ] isn't exsit !\n",pFileName);
return EXIT_FAILURE;
}
memset(szImageData,0,sizeof(szImageData));
ulReadLength = fread(szImageData,1,1024*1000,fp);
fclose(fp);
if( !ulReadLength )
{
fprintf(stderr,"Read file found error !\n");
return EXIT_FAILURE;
}
printf("ulReadLength = %ld \n",ulReadLength);
memset(szImageName,0,sizeof(szImageName));
memcpy(szImageName,pFileName,strlen(pFileName));
memset(szStoreImageData,0,sizeof(szStoreImageData));
ulStoreLength = mysql_real_escape_string(&s_my_connection,szStoreImageData,szImageData,ulReadLength);
/*二进制数据可能包含一些特殊字符,这些字符在sql语句中可能会引起一些问题,
所以必须转义,理论上每个字符都可能是特殊字符,所以szStoreImageData数组大小是szImageData数组大小的两倍,
该函数还会在szStoreImageData数组最后加上结尾符
*/
printf("ulStoreLength = %ld \n",ulStoreLength);
memset(szSql,0,sizeof(szSql));
if( 1==ucIdFlag )
{
sprintf(szSql,"insert into %s(id,name,data) values(%d,'%s','%s')",pImageTableName,iId,szImageName,szStoreImageData);
}
else
{
sprintf(szSql,"insert into %s(name,data) values('%s','%s')",pImageTableName,szImageName,szStoreImageData);
}
iRetCode = mysql_query(&s_my_connection,szSql);
if( iRetCode )
{
fprintf(stderr,"insert error ,sqlcode=[%d] : %s !\n",mysql_errno(&s_my_connection),mysql_error(&s_my_connection));
return EXIT_FAILURE;
}
return EXIT_SUCCESS;
}
/*向MySQL数据库中存储图片*/
int mysql_store_image_ex(char *pFileName,char *pImageTableName,unsigned char ucIdFlag,int iId)
{
FILE *fp;
char szImageName[31];
char szImageData[1024*1000];/*图片大小最大不超过1M*/
char szStoreImageData[2*1024*1000+1];
char szSql[2*1024*1000+1];
unsigned long ulReadLength = 0,ulStoreLength = 0,ulStoredLength = 0;
int iRetCode = -1;
fp = fopen(pFileName,"rb");
if( NULL==fp )
{
fprintf(stderr,"This file: [ %s ] isn't exsit !\n",pFileName);
return EXIT_FAILURE;
}
memset(szImageData,0,sizeof(szImageData));
ulReadLength = fread(szImageData,1,1024*1000,fp);
fclose(fp);
if( !ulReadLength )
{
fprintf(stderr,"Read file found error !\n");
return EXIT_FAILURE;
}
printf("ulReadLength = %ld \n",ulReadLength);
memset(szImageName,0,sizeof(szImageName));
memcpy(szImageName,pFileName,strlen(pFileName));
memset(szStoreImageData,0,sizeof(szStoreImageData));
ulStoreLength = mysql_real_escape_string(&s_my_connection,szStoreImageData,szImageData,ulReadLength);
/*二进制数据可能包含一些特殊字符,这些字符在sql语句中可能会引起一些问题,
所以必须转义,理论上每个字符都可能是特殊字符,所以szStoreImageData数组大小是szImageData数组大小的两倍,
该函数还会在szStoreImageData数组最后加上结尾符
*/
printf("ulStoreLength = %ld \n",ulStoreLength);
memset(szSql,0,sizeof(szSql));
if( 1==ucIdFlag )
{
sprintf(szSql,"insert into %s(id,name,data) values(%d,'%s','%s')",pImageTableName,iId,szImageName,szStoreImageData);
}
else
{
sprintf(szSql,"insert into %s(name,data) values('%s','%s')",pImageTableName,szImageName,szStoreImageData);
}
ulStoredLength = strlen(szSql);
iRetCode = mysql_real_query(&s_my_connection,szSql,ulStoredLength);
printf("ulStoredLength = %ld \n",ulStoredLength);
if( iRetCode )
{
fprintf(stderr,"insert error ,sqlcode=[%d] : %s !\n",mysql_errno(&s_my_connection),mysql_error(&s_my_connection));
return EXIT_FAILURE;
}
return EXIT_SUCCESS;
}
/*从MySQL数据库提取存储的图片*/
int mysql_fetch_image(char *pFileName,char *pImageTableName,unsigned char ucFlag,int iId)
{
FILE *fp;
MYSQL_RES *res_ptr = NULL;
MYSQL_ROW sqlrow;
int iRetCode = -1,iTableRow = 0;
char szSql[256];
unsigned long *ulLength = NULL;
unsigned long ulWriteLength = 0;
/*
ucFlag = 1 表示指定文件名
*/
memset(szSql,0,sizeof(szSql));
if( 1==ucFlag )
{
sprintf(szSql,"select data from %s where id = %d",pImageTableName,iId);
}
else
{
fprintf(stderr,"This flag [ %d ] found error for the filename ,and you must specify a filename !\n",ucFlag);
return EXIT_FAILURE;
}
fp = fopen(pFileName,"wb");
if( NULL==fp )
{
fprintf(stderr,"This file: [ %s ] isn't exsit !\n",pFileName);
return EXIT_FAILURE;
}
iRetCode = mysql_query(&s_my_connection,szSql); /*执行SQL语句*/
if( iRetCode )
{
fprintf(stderr,"Select error %d: %s !\n",mysql_errno(&s_my_connection),mysql_error(&s_my_connection)); /*打印错误处理具体信息*/
return EXIT_FAILURE;
}
res_ptr = mysql_store_result(&s_my_connection); /*集合*/
if( res_ptr )
{
iTableRow = mysql_num_rows(res_ptr);/*行*/
if( iTableRow==0 )/*表示查询没有结果*/
{
fclose(fp);
fprintf(stderr,"Select data from %s where id = [ %d ] , result is null !\n",pImageTableName,iId);
return EXIT_FAILURE;
}
sqlrow = mysql_fetch_row(res_ptr);
ulLength = mysql_fetch_lengths(res_ptr);
printf("ulFetchedLength: %ld\n",ulLength[0]);/*从数据库中提取的数据信息长度*/
mysql_free_result(res_ptr); /*完成对数据的所有操作后,调用此函数来让MySQL库清理它分配的对象*/
ulWriteLength = fwrite(sqlrow[0],1,ulLength[0],fp);
if( ulWriteLength==0 )
{
fclose(fp);
fprintf(stderr,"Write file found error !\n");
return EXIT_FAILURE;
}
printf("ulWriteLength = %ld \n",ulWriteLength);/*写入文件的数据信息长度*/
}
else
{
fclose(fp);
fprintf(stderr,"Select result is null !\n");
return EXIT_FAILURE;
}
fclose(fp);
return EXIT_SUCCESS;
}
/*从MySQL数据库提取存储的图片*/
int mysql_fetch_image_ex(char *pFileName,char *pImageTableName,unsigned char ucFlag,int iId)
{
FILE *fp;
MYSQL_RES *res_ptr = NULL;
MYSQL_ROW sqlrow;
int iRetCode = -1,iTableRow = 0;
char szSql[256];
unsigned long *ulLength = NULL;
unsigned long ulWriteLength = 0;
memset(szSql,0,sizeof(szSql));
/*
ucFlag = 2 表示取数据库表中的信息文件名
*/
if( 2==ucFlag )
{
sprintf(szSql,"select data,name from %s where id = %d",pImageTableName,iId);
}
else
{
fprintf(stderr,"This flag [ %d ] found error for the filename ,and you needn't specify a filename !\n",ucFlag);
return EXIT_FAILURE;
}
iRetCode = mysql_query(&s_my_connection,szSql); /*执行SQL语句*/
if( iRetCode )
{
fprintf(stderr,"Select error %d: %s !\n",mysql_errno(&s_my_connection),mysql_error(&s_my_connection)); /*打印错误处理具体信息*/
return EXIT_FAILURE;
}
res_ptr = mysql_store_result(&s_my_connection); /*集合*/
if( res_ptr )
{
iTableRow = mysql_num_rows(res_ptr);/*行*/
if( iTableRow==0 )/*表示查询没有结果*/
{
fclose(fp);
fprintf(stderr,"Select data,name from %s where id = [ %d ] , result is null !\n",pImageTableName,iId);
return EXIT_FAILURE;
}
sqlrow = mysql_fetch_row(res_ptr);
ulLength = mysql_fetch_lengths(res_ptr);
sscanf(sqlrow[1],"%s",pFileName);/*从数据库中取文件名*/
printf("ulFetchedLength: %ld\n",ulLength[0]);
mysql_free_result(res_ptr); /*完成对数据的所有操作后,调用此函数来让MySQL库清理它分配的对象*/
fp = fopen(pFileName,"wb");
if( NULL==fp )
{
fprintf(stderr,"This file: [ %s ] isn't exsit !\n",pFileName);
return EXIT_FAILURE;
}
ulWriteLength = fwrite(sqlrow[0],1,ulLength[0],fp);
if( ulWriteLength==0 )
{
fclose(fp);
fprintf(stderr,"Write file found error !\n");
return EXIT_FAILURE;
}
printf("ulWriteLength = %ld \n",ulWriteLength);
}
else
{
fclose(fp);
fprintf(stderr,"Select result is null !\n");
return EXIT_FAILURE;
}
fclose(fp);
return EXIT_SUCCESS;
}
//store.c
/***********************************************************************
FileName : store.c
FileFunc : 把图片存储到MySQL数据库中操作测试(图片大小最大不超过1M)
Version : V0.1
Author : Sunrier
Date : 2012-06-23
Descp : Linux下使用C语言访问MySQL函数
************************************************************************/
#include <stdio.h>
int main(int argc,char *argv[])
{
char szServer[20] = "localhost"; /*127.0.0.1*/
char szUser[20] = "Sunrier";
char szPassword[20] = "redhat";
char szDatabase[20] = "test";
char szFileName[30],szTableName[30];
unsigned char ucIdFlag = 0;/*手动插入ID标志*/
int iRetCode = -1,iId =0;
iRetCode = mysql_login(szServer,szUser,szPassword,szDatabase);
if( iRetCode )
{
return 1;
}
memset(szFileName,0,sizeof(szFileName));
memset(szTableName,0,sizeof(szTableName));
strcpy(szFileName,"Sunrier.jpg");
strcpy(szTableName,"images");
ucIdFlag = 0;
iId =1;
/*iRetCode = mysql_store_image( szFileName,szTableName,ucIdFlag,iId );*/
iRetCode = mysql_store_image_ex( szFileName,szTableName,ucIdFlag,iId );
if( !iRetCode )
{
fprintf(stderr,"Image has been stored in the database !\n");
}
mysql_logout();
return 0;
}
//fetch.c
/**********************************************************************
FileName : fetch.c
FileFunc : 从MySQL数据库中提取图片操作测试(图片大小最大不超过1M)
Version : V0.1
Author : Sunrier
Date : 2012-06-23
Descp : Linux下使用C语言访问MySQL函数
***********************************************************************/
#include <stdio.h>
int main(int argc,char *argv[])
{
int iRetCode = -1;
char szServer[20] = "localhost";
char szUser[20] = "Sunrier";
char szPassword[20] = "redhat";
char szDatabase[20] = "test";
char szFileName[30],szTableName[30];
unsigned char ucFlag = 0;
int iId = 0;
iRetCode = mysql_login(szServer,szUser,szPassword,szDatabase);
if( iRetCode )
{
return 1;
}
ucFlag = 2;
iId = 9;
memset(szFileName,0,sizeof(szFileName));
memset(szTableName,0,sizeof(szTableName));
strcpy(szFileName,"Sunrier.jpg");
strcpy(szTableName,"images");
/*iRetCode = mysql_fetch_image(szFileName,szTableName,ucFlag,iId);*/
iRetCode = mysql_fetch_image_ex(szFileName,szTableName,ucFlag,iId);
if( !iRetCode )
{
fprintf(stderr,"Image has been fetched from the database !\n");
}
mysql_logout();
return 0;
}
//makefile
#makefile
OBJS = store fetch
all:$(OBJS)
CFLAGS = -O -w -ansi
#CFLAGS = -O -Wall -ansi
CC = gcc $(CFLAGS)
MYSQLINCPATH = -I/usr/include/mysql
MYSQLLIBPATH = -L/usr/lib/mysql
MYSQLLIB = -lmysqlclient -lz -lm
store:store.c dbproc.c
@$(CC) $(MYSQLINCPATH) $(MYSQLLIBPATH) -o $@ $? $(MYSQLLIB)
fetch:fetch.c dbproc.c
@$(CC) $(MYSQLINCPATH) $(MYSQLLIBPATH) -o $@ $? $(MYSQLLIB)
clean:
@ls | grep -v ^makefile$$ | grep -v [.]c$$ | grep -v [.]h$$ | grep -v [.]sql$$ | grep -v [.]jpg$$ | grep -v [.]txt$$ | xargs rm -rf
#makefile
//images.sql
drop database if exists test;
create database test;
use test;
create table images
(
id integer auto_increment not null primary key,
name varchar(30) not null,
data mediumblob not null
);
-- )engine=innodb charset=gb2312; -- 指定引擎和编码方式
-- 图片是二进制数据,mysql有种特殊的数据类型,用来存储二进制数据,叫做BLOB(Binary Large Ojbect)