Code Segment about SQLite3

最新推荐文章于 2025-07-25 19:56:28 发布

qrsforever

最新推荐文章于 2025-07-25 19:56:28 发布

阅读量826

点赞数

CC 4.0 BY-SA版权

分类专栏： Source Reusability 文章标签： sqlite table callback function insert null

本文链接：https://blog.youkuaiyun.com/qrsforever/article/details/6043664

Source Reusability 专栏收录该内容

2 篇文章

订阅专栏

本文详细介绍了一个使用SQLite数据库的应用实例，包括创建表、插入数据、查询数据、更新表、删除表和项等功能。提供了多种实现方法，如使用库函数、SQL语句及API函数等，并涉及图片的存储和检索。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

* =====================================================================================

* Filename: sqlpdu.c

* =====================================================================================

/* 头文件 */

/*{{{*/

#include

#include "sqlpdu.h"

#include "cgic.h"

#include "gmesg.h"

/*}}}*/

/* 头文件 */

* === FUNCTION ======================================================================

* Name: create_table

* Description: 创建一个表

* 1. 函数参数意义与main()函数的参数一样

* 2. 注意字符串的连接的使用

* =====================================================================================

/*{{{*/

int create_table(int argc, char *argv[])

{

sqlite3 *db=NULL;

int rc;

char *dbname = "gprsPdu.db";

char *errmsg = 0;

char *default_table_name = "gprs_pdu";

char sql_create[MSIZE] = { 0 };

memset(sql_create, 0, MSIZE);

/*-----------------------------------------------------------------------------

* 第一步：判断参数

*-----------------------------------------------------------------------------*/

if ( 2 == argc )

{

/* 若参数是两个，则其中第二个是表名，创建给定的表 */

if ( 0 == strcmp(argv[1], "pic") )

{

sprintf( sql_create, "create table pic "

"(ID integer primary key autoincrement, "

"pic blob, "

"remark text)");

}

else

{

sprintf ( sql_create, "create table %s "

"(ID integer primary key autoincrement, "

"sim_attr text, sim_center text, "

"phone_attr text, phone text, "

"fix text, msg text, status text)", argv[1]);

}

else

{

/* 若参数不为两个，则使用默认的表名 */

sprintf ( sql_create, "create table %s "

"(ID integer primary key autoincrement, "

"sim_attr text, sim_center text, "

"phone_attr text, phone text, "

"fix text, msg text, status text)", default_table_name);

}

/*-----------------------------------------------------------------------------

* 第二步：打开数据库

*-----------------------------------------------------------------------------*/

rc = sqlite3_open(dbname, &db);

if ( SQLITE_OK != rc )

{

fprintf(stderr, "Can't open database :%s/n", errmsg);

exit (1);

}

/*-----------------------------------------------------------------------------

* 第三步：执行创建表的操作

*-----------------------------------------------------------------------------*/

rc = sqlite3_exec ( db, sql_create, NULL, 0, &errmsg);

if ( SQLITE_OK != rc )

{

fprintf(stderr, "Can't create table : %s/n", errmsg);

sqlite3_close(db);

return -1;

}

/*-----------------------------------------------------------------------------

* 第四步：关闭数据库

*-----------------------------------------------------------------------------*/

sqlite3_close(db);

return 0;

}

/* ----- end of function create_table ----- */

/*}}}*/

* === FUNCTION ======================================================================

* Name: insert_value

* Description: 使用两种方法完成对表的插入

* 1. 方法一：使用库函数

* 2. 方法二：使用SQLITE API 提供的函数，同时使用了通配符

* =====================================================================================

/*{{{*/

int insert_value(int argc, char *argv[])

{

sqlite3 *db = NULL;

sqlite3_stmt *stmt = NULL;

int rc;

char *dbname = "gprsPdu.db";

char *errmsg = 0;

char sql_insert[MSIZE] = { 0 };

/*-----------------------------------------------------------------------------

* 第一步：判断参数，参数为9个，其中包含命令名、表名和7个字段

*-----------------------------------------------------------------------------*/

if ( 9 !=argc )

{

perror("argc != 9");

exit(0);

}

/*-----------------------------------------------------------------------------

* 第二步：打开数据库

*-----------------------------------------------------------------------------*/

rc = sqlite3_open(dbname, &db);

if ( SQLITE_OK != rc )

{

fprintf(stderr, "Can't open database :%s/n", errmsg);

exit (1);

}

/*-----------------------------------------------------------------------------

* 方法一：使用函数sprintf实现运行时插入数据，特点是简单，但是存在局限性.

*-----------------------------------------------------------------------------*/

#if INSERT_METHOD1

sprintf(sql_insert, "insert into %s (sim_attr, sim_center, phone_attr, phone, "

"fix, msg, status) values ('%s', '%s', '%s', '%s', '%s', '%s', '%s')",

argv[1], argv[2], argv[3], argv[4], argv[5], argv[6], argv[7], argv[8]);

/*-----------------------------------------------------------------------------

* 第三步（方法一）：执行插入操作

*-----------------------------------------------------------------------------*/

rc = sqlite3_exec ( db, sql_insert, NULL, 0, &errmsg);

if ( SQLITE_OK != rc )

{

fprintf(stderr, "Can't insert table : %s/n", errmsg);

sqlite3_close(db);

exit(1);

}

#endif

/*-----------------------------------------------------------------------------

* 方法二：使用sqlite3 API 实现运行时插入数据，相比之下这个麻烦一些，而且插

* 入的表名还是用了sprintf，但是如果插入的数据是二进制（参考其他资料）

* ，那么方法一就无能为力了.

*-----------------------------------------------------------------------------*/

#if INSERT_METHOD2

memset(sql_insert, 0, sizeof(sql_insert));

sprintf(sql_insert, "insert into %s (sim_attr, sim_center, phone_attr,"

"phone, fix, msg, status) values (?, ?, ?, ?, ?, ?, ?)", argv[1]);

/*-----------------------------------------------------------------------------

* 第三步（方法二）1. 将SQL语句封装编译成字节码(声明stmt)，留在后面执行.

*-----------------------------------------------------------------------------*/

sqlite3_prepare( db, sql_insert, -1, &stmt, 0);

/*-----------------------------------------------------------------------------

* 第三步（方法二）2. 将准备好的SQL语句进行绑定.

*-----------------------------------------------------------------------------*/

sqlite3_bind_text( stmt, 1, argv[2], strlen(argv[2]), NULL );

sqlite3_bind_text( stmt, 2, argv[3], strlen(argv[3]), NULL );

sqlite3_bind_text( stmt, 3, argv[4], strlen(argv[4]), NULL );

sqlite3_bind_text( stmt, 4, argv[5], strlen(argv[5]), NULL );

sqlite3_bind_text( stmt, 5, argv[6], strlen(argv[6]), NULL );

sqlite3_bind_text( stmt, 6, argv[7], strlen(argv[7]), NULL );

sqlite3_bind_text( stmt, 7, argv[8], strlen(argv[7]), NULL );

/*-----------------------------------------------------------------------------

* 第三步（方法二）3. 执行前面绑定好的SQL语句，一次返回一项.

*-----------------------------------------------------------------------------*/

rc = sqlite3_step(stmt);

if ( SQLITE_DONE != rc )

{

fprintf(stderr, "Can't insert table :%s/n", sqlite3_errmsg(db));

sqlite3_close(db);

exit(0);

}

/*-----------------------------------------------------------------------------

* 第三步（方法二) 4. 销毁准备好的SQL语句声明.

*-----------------------------------------------------------------------------*/

sqlite3_finalize(stmt);

#endif

/*-----------------------------------------------------------------------------

* 第四步：关闭数据库

*-----------------------------------------------------------------------------*/

sqlite3_close(db);

return 0;

}

/* ----- end of function insert_value ----- */

/*}}}*/

* === FUNCTION ======================================================================

* Name: select_table

* Description: 功能：使用三种方法对sqlite3数据库进行查询.

* 1. 第二个参数和main()函数的第二个参数一样

* 2. 注意一些资源的释放

* 3. 该函数容易发生断错误，主要起因是对查询后的结果访问越界

* =====================================================================================

/*{{{*/

int select_table( int argc, char *argv[] )

{

sqlite3 *db = NULL;

sqlite3_stmt *stmt = NULL;

int rc, i,j;

char *dbname = "gprsPdu.db";

char *errmsg = NULL;

int nrow, ncolumn;

char **dbResult = NULL;

char sql_select[MSIZE] = { 0 };

/*-----------------------------------------------------------------------------

* 第一步：判断参数

*-----------------------------------------------------------------------------*/

if ( argc != 2 )

{

perror("argc > 2 ");

exit(0);

}

sprintf(sql_select, "select * from %s", argv[1]);

/*-----------------------------------------------------------------------------

* 第二步：打开一个数据库

*-----------------------------------------------------------------------------*/

rc = sqlite3_open(dbname, &db);

if ( SQLITE_OK != rc )

{

fprintf(stderr, "open database error : %s/n", sqlite3_errmsg(db));

exit(0);

}

/*-----------------------------------------------------------------------------

* 方法一：通过sqlite3_get_table 函数实现显示table信息

*-----------------------------------------------------------------------------*/

#if SELECT_METHOD1

/*-----------------------------------------------------------------------------

* 第三步（方法一）：1.调用get_table函数返回结果集，nrow行，ncolumn列.

*-----------------------------------------------------------------------------*/

rc = sqlite3_get_table(db, sql_select, &dbResult, &nrow, &ncolumn, &errmsg);

if ( SQLITE_OK != rc )

{

fprintf(stderr, "sqlite3_get_table :%s/n", errmsg);

sqlite3_close(db);

exit(0);

}

/*-----------------------------------------------------------------------------

* 第三步（方法一）：2.逐一显示每一个字段，其中包括了字段的属性名（第一行）

*-----------------------------------------------------------------------------*/

for ( i=0; i<(nrow+1)*ncolumn; )

{

for ( j=1; j<=ncolumn; j++,i++ )

{

printf(" %s ", dbResult[i]);

}

printf("/n");

}

/*-----------------------------------------------------------------------------

* 第三步（方法一）：3.释放调结果集所占空间

*-----------------------------------------------------------------------------*/

sqlite3_free_table( dbResult );

#endif

/*-----------------------------------------------------------------------------

* 方法二：通过sqlite3_prepare sqlite3_step sqlite3_column_name 实现

*-----------------------------------------------------------------------------*/

#if SELECT_METHOD2

/*-----------------------------------------------------------------------------

* 第三步（方法二)：1.封装SQL语句，转变成字节码（声明SQL语句）

*-----------------------------------------------------------------------------*/

rc = sqlite3_prepare( db, sql_select, -1, &stmt, 0 );

if ( SQLITE_OK != rc )

{

fprintf(stderr, "sqlite3_prepare : %s", errmsg);

sqlite3_finalize(stmt);

sqlite3_close(db);

}

/*-----------------------------------------------------------------------------

* 第三步（方法二）：2.输出字段名，column_count可在prepare函数执行后任何时候调用

*-----------------------------------------------------------------------------*/

for ( i=0; i

{

/* 输出字段名(列名) */

printf(" %s |", sqlite3_column_name(stmt, i));

}

printf("/n");

/*-----------------------------------------------------------------------------

* 第三步（方法二）：3.调用step执行一声明的SQL语句，每次一行，注意返回值.

*-----------------------------------------------------------------------------*/

rc = sqlite3_step(stmt); /* 每次读一行 */

if ( SQLITE_ROW != rc )

{

fprintf(stderr, "sqlite3_step :%s", errmsg);

sqlite3_close(db);

exit(0);

}

{

for ( i=0; i

{

/* 输出一行中每一列的值 */

printf(" %s |", sqlite3_column_text(stmt, i));

}

printf("/n");

rc = sqlite3_step(stmt); /* 继续执行SQL语句，直到所有行输出完 */

}while( SQLITE_ROW == rc );

/*-----------------------------------------------------------------------------

* 第三步（方法二）：4.释放statement声明空间

*-----------------------------------------------------------------------------*/

sqlite3_finalize(stmt);

#endif

/*-----------------------------------------------------------------------------

* 方法三：使用回调函数(自己实现callback)，每查到一条记录，就调用一次这个回调

*-----------------------------------------------------------------------------*/

#if SELECT_METHOD3

#if CGI_CALLBACK

/* 这段条件定义中代码，完全可以独立出去，后面也有介绍，这里不多说 */

fprintf(cgiOut, "

");

fprintf(cgiOut, "

Show default pdu ");

fprintf(cgiOut, "

");

fprintf(cgiOut,"

fprintf(cgiOut, "

");

fprintf(cgiOut, "

");

fprintf(cgiOut, "

sim_attr

");

fprintf(cgiOut, "

sim_center

");

fprintf(cgiOut, "

phone_attr

");

fprintf(cgiOut, "

phone

");

fprintf(cgiOut, "

fix

");

fprintf(cgiOut, "

msg

");

fprintf(cgiOut, "

status

");

fprintf(cgiOut, "");

rc = sqlite3_exec( db, sql_select, cgi_callback, NULL, &errmsg );

if ( SQLITE_OK != rc )

{

sqlite3_close(db);

exit(0);

}

fprintf(cgiOut, "");

fprintf(cgiOut, "

");

fprintf(cgiOut, "");

#else

/*-----------------------------------------------------------------------------

* 第三步（方法三）：执行SQL语句，第三个参数是回调函数，详细内容请参考其他

*-----------------------------------------------------------------------------*/

rc = sqlite3_exec( db, sql_select, callback, NULL, &errmsg );

if ( SQLITE_OK != rc )

{

fprintf(stderr, "sqlite3_exec :%s", errmsg);

sqlite3_close(db);

exit(0);

}

#endif

/*-----------------------------------------------------------------------------

* 第四步：关闭数据库

*-----------------------------------------------------------------------------*/

sqlite3_close(db);

return 0;

}

/* ----- end of function table_select ----- */

/*}}}*/

* === FUNCTION ======================================================================

* Name: update_table

* Description: 功能：用户设置默认PDU的那是行与第一行进行更新

* =====================================================================================

/*{{{*/

int update_table(int argc, char *argv[])

{

struct gprs_pdu *gpdu = NULL;

sqlite3 *db = NULL;

sqlite3_stmt *stmt = NULL;

char *dbname = "gprsPdu.db";

char **dbResult = NULL;

int nrow, ncolumn;

char *errmsg = NULL;

char update_table[MSIZE] = { 0 };

char select_table[MSIZE] = { 0 };

char status[SIZE30] = { 0 };

int id, rc, i;

char first_id[SIZE30];

/*-----------------------------------------------------------------------------

* 第一步：判断参数

*-----------------------------------------------------------------------------*/

if ( argc != 3 )

{

fprintf(stderr, "argc != 3/n");

exit(0);

}

/*-----------------------------------------------------------------------------

* 第二步：申请gpdu结构体，用于存放第一行的字段.其实这里当作中间变量

*-----------------------------------------------------------------------------*/

gpdu = (struct gprs_pdu *) malloc (sizeof(struct gprs_pdu));

if ( NULL == gpdu )

{

fprintf(stderr, "gprs malloc fail");

return -1;

}

memset(gpdu, 0, sizeof(struct gprs_pdu));

/*-----------------------------------------------------------------------------

* 第三步：打开数据库

*-----------------------------------------------------------------------------*/

rc =sqlite3_open(dbname, &db);

if ( SQLITE_OK != rc )

{

fprintf(stderr, "Can't open database: %s/n", sqlite3_errmsg(db));

return -1;

}

sprintf(select_table, "select * from %s ", argv[1]);

/*-----------------------------------------------------------------------------

* 第四步：查询表，将结果存放到结果集中

*-----------------------------------------------------------------------------*/

rc = sqlite3_get_table(db, select_table, &dbResult, &nrow, &ncolumn, &errmsg);

if ( SQLITE_OK != rc )

{

fprintf(stderr, "sqlite3_get_table : %s/n", errmsg);

sqlite3_close(db);

exit(0);

}

/*-----------------------------------------------------------------------------

* 第五步：虽然结果集中有nrow行ncolumn列，但是这里只需要第一行，并保存到gpdu中

*-----------------------------------------------------------------------------*/

i=ncolumn;

sprintf( first_id, "%s", dbResult[i]); /* gprs_pdu 结构体中没有这一字段 */

i++;

sprintf( gpdu->sim_attr, "%s", dbResult[i] );

i++;

sprintf( gpdu->sim_center, "%s", dbResult[i] );

i++;

sprintf( gpdu->phone_attr, "%s", dbResult[i] );

i++;

sprintf( gpdu->phone, "%s", dbResult[i] );

i++;

sprintf( gpdu->fix, "%s", dbResult[i] );

i++;

sprintf( gpdu->gb2312_msg, "%s", dbResult[i] );

i++;

sprintf( status, "%s", dbResult[i]); /* 这一字段用来用与安放类型查找关键字段 */

/*-----------------------------------------------------------------------------

* 第六步：释放结果集

*-----------------------------------------------------------------------------*/

sqlite3_free_table( dbResult );

/*-----------------------------------------------------------------------------

* 第七步：查询表，将字段id等于用户传过来值的这行存在结果集中

*-----------------------------------------------------------------------------*/

sprintf(select_table, "select * from %s where id = %d", argv[1],atoi(argv[2]));

rc = sqlite3_get_table(db, select_table, &dbResult, &nrow, &ncolumn, &errmsg);

if ( SQLITE_OK != rc )

{

fprintf(stderr, "sqlite3_get_table : %s/n", errmsg);

sqlite3_close(db);

exit(0);

}

i = ncolumn + 1;

memset(update_table, 0, MSIZE);

/*-----------------------------------------------------------------------------

* 第八步：将结果集中内容通过update函数更新到id=first_id那一行中（实现第一步替换）

*-----------------------------------------------------------------------------*/

sprintf(update_table, "update %s set sim_attr=?1,sim_center=?2, "

"phone_attr=?3, phone=?4, fix=?5, msg=?6, status=?7 where id=?8", argv[1]);

sqlite3_prepare( db, update_table, -1, &stmt, 0 );

sqlite3_bind_text ( stmt, 1, dbResult[i], strlen(dbResult[i]), NULL );

i++;

sqlite3_bind_text ( stmt, 2, dbResult[i], strlen(dbResult[i]), NULL );

i++;

sqlite3_bind_text ( stmt, 3, dbResult[i], strlen(dbResult[i]), NULL );

i++;

sqlite3_bind_text ( stmt, 4, dbResult[i], strlen(dbResult[i]), NULL );

i++;

sqlite3_bind_text ( stmt, 5, dbResult[i], strlen(dbResult[i]), NULL );

i++;

sqlite3_bind_text ( stmt, 6, dbResult[i], strlen(dbResult[i]), NULL );

i++;

sqlite3_bind_text ( stmt, 7, dbResult[i], strlen(dbResult[i]), NULL );

i++;

sqlite3_bind_int ( stmt, 8, atoi(first_id)); /* 整形绑定，后两个参数都是整形 */

rc =sqlite3_step(stmt);

if ( SQLITE_DONE != rc )

{

fprintf(stderr, "Update table fail/n");

sqlite3_close(db);

exit(-1);

}

/*-----------------------------------------------------------------------------

* 第九步：释放调结果集所占空间以及statement空间资源

*-----------------------------------------------------------------------------*/

sqlite3_free_table( dbResult );

sqlite3_finalize(stmt);

/*-----------------------------------------------------------------------------

* 第十步：将原来保存的数据，写到用户参数的位置（实现第二步替换）

*-----------------------------------------------------------------------------*/

memset(update_table, 0, MSIZE);

sprintf(update_table, "update %s set sim_attr=@sim_attr,sim_center=@sim_center, "

"phone_attr=@phone_attr, phone=@phone, fix=@fix, msg=@msg, status=@status where id=@id",

argv[1]);

sqlite3_prepare( db, update_table, -1, &stmt, 0);

/* 这里通过一个新函数，来开阔视野，前面使用?1,?2等不够直观，这里可以用索引 */

int index_sim_attr,index_sim_center, index_phone_attr;

int index_phone, index_fix, index_msg, index_status, index_id;

index_sim_attr = sqlite3_bind_parameter_index(stmt, "@sim_attr");

index_sim_center = sqlite3_bind_parameter_index(stmt, "@sim_center");

index_phone_attr = sqlite3_bind_parameter_index(stmt, "@phone_attr");

index_phone = sqlite3_bind_parameter_index(stmt, "@phone");

index_fix = sqlite3_bind_parameter_index(stmt, "@fix");

index_msg = sqlite3_bind_parameter_index(stmt, "@msg");

index_status = sqlite3_bind_parameter_index(stmt, "@status");

index_id = sqlite3_bind_parameter_index(stmt, "@id");

/* 通过索引进行绑定，有兴趣的可以一一输出这些索引号，看是否是1,2... */

sqlite3_bind_text( stmt, index_sim_attr, gpdu->sim_attr, strlen(gpdu->sim_attr), NULL );

sqlite3_bind_text( stmt, index_sim_center, gpdu->sim_center, strlen(gpdu->sim_center), NULL );

sqlite3_bind_text( stmt, index_phone_attr, gpdu->phone_attr, strlen(gpdu->phone_attr), NULL );

sqlite3_bind_text( stmt, index_phone, gpdu->phone, strlen(gpdu->phone), NULL );

sqlite3_bind_text( stmt, index_fix, gpdu->fix, strlen(gpdu->fix), NULL );

sqlite3_bind_text( stmt, index_msg, gpdu->gb2312_msg, strlen(gpdu->gb2312_msg), NULL );

sqlite3_bind_text( stmt, index_status, status, strlen(status), NULL );

sqlite3_bind_int ( stmt, index_id, atoi(argv[2]));

/* 单步执行 */

rc = sqlite3_step(stmt);

if ( SQLITE_DONE != rc )

{

fprintf(stderr, "slqite3_step fail/n");

sqlite3_close(db);

exit(0);

}

/*-----------------------------------------------------------------------------

* 第十一步：释放资源

*-----------------------------------------------------------------------------*/

sqlite3_finalize(stmt);

free(gpdu);

gpdu = NULL;

/*-----------------------------------------------------------------------------

* 第十二步：关闭数据库

*-----------------------------------------------------------------------------*/

sqlite3_close(db);

return 0;

}

/* ----- end of function update_table ----- */

/*}}}*/

* === FUNCTION ======================================================================

* Name: drop_table

* Description: 删除某一个表

* =====================================================================================

/*{{{*/

int drop_table ( int argc, char *argv[] )

{

sqlite3 *db = NULL;

int rc;

char *dbname = "gprsPdu.db";

char drop_table[MSIZE] = { 0 };

char *errmsg;

/*-----------------------------------------------------------------------------

* 第一步：判断参数

*-----------------------------------------------------------------------------*/

if ( argc != 2 )

{

fprintf(stderr, "argc!=2/n");

return -1;

}

sprintf(drop_table, "drop table %s", argv[1]);

/*-----------------------------------------------------------------------------

* 第二步：打开数据库

*-----------------------------------------------------------------------------*/

rc = sqlite3_open(dbname, &db);

if ( SQLITE_OK != rc )

{

fprintf(stderr, "Cannot open %s table/n", argv[1]);

return -1;

}

/*-----------------------------------------------------------------------------

* 第三步：执行删除表的操作

*-----------------------------------------------------------------------------*/

rc = sqlite3_exec( db, drop_table, NULL, 0, &errmsg );

if ( SQLITE_OK != rc )

{

fprintf(stderr, "Can't drop table : %s/n", errmsg);

sqlite3_close(db);

return -1;

}

/*-----------------------------------------------------------------------------

* 第四步：关闭数据库

*-----------------------------------------------------------------------------*/

sqlite3_close(db);

return 0;

}

/* ----- end of function drop_table ----- */

/*}}}*/

* === FUNCTION ======================================================================

* Name: delete_table

* Description: 删除表中的某一项

* =====================================================================================

/*{{{*/

int delete_table ( int argc, char *argv[] )

{

sqlite3 *db = NULL;

int rc;

char *dbname = "gprsPdu.db";

char del_table[MSIZE] = { 0 };

char *errmsg;

/*-----------------------------------------------------------------------------

* 第一步：判断参数

*-----------------------------------------------------------------------------*/

if ( argc != 3 )

{

fprintf(stderr, "argc!=3/n");

return -1;

}

sprintf(del_table, "delete from %s where id=%d", argv[1], atoi(argv[2]));

/*-----------------------------------------------------------------------------

* 第二步：打开数据库

*-----------------------------------------------------------------------------*/

rc = sqlite3_open(dbname, &db);

if ( SQLITE_OK != rc )

{

fprintf(stderr, "Can not open %s table/n", argv[1]);

return -1;

}

/*-----------------------------------------------------------------------------

* 第三步：执行要删除某一行的操作

*-----------------------------------------------------------------------------*/

rc = sqlite3_exec( db, del_table, NULL, 0, &errmsg );

if ( SQLITE_OK != rc )

{

fprintf(stderr, "Can not delete table :%s/n", errmsg);

sqlite3_close(db);

return -1;

}

/*-----------------------------------------------------------------------------

* 第四步：关闭数据库

*-----------------------------------------------------------------------------*/

sqlite3_close(db);

return 0;

}

/* ----- end of function delete_table ----- */

/*}}}*/

* === FUNCTION ======================================================================

* Name: put_pics

* Description: 将拍出图片存储到sqlite数据库中，存储之后将源照片删除，节省空间

* =====================================================================================

/*{{{*/

int put_pics ( int argc, char *argv[] )

{

/* argv[1] 是照片目录 */

DIR *dp;

struct dirent *entry;

struct stat stat_buff;

struct tm *tm;

char datastring[MSIZE] = { 0 };

char file_path[SIZE30] = { 0 };

sqlite3 *db = NULL;

char *dbname = "gprsPdu.db";

sqlite3_stmt *stmt = NULL;

int rc;

char sql_insert[100] = { 0 };

FILE *fp = NULL;

long filesize = 0;

char *file_buff = NULL;

rc = sqlite3_open(dbname, &db);

if ( SQLITE_OK != rc )

{

fprintf(stderr, "Cannot open %s table/n", argv[1]);

return -1;

}

if ( NULL == (dp = opendir(argv[1])) )

{

fprintf(stderr, "Can not open %s/n", argv[1] );

return -1;

}

while ( NULL != (entry = readdir(dp)) )

{

lstat(entry->d_name, &stat_buff);

if ( strcmp(entry->d_name, ".") == 0 || strcmp(entry->d_name, "..") == 0 )

{

continue;

}

sprintf(file_path, "./%s/%s", argv[1], entry->d_name);

fp = fopen (file_path, "rb");

if ( NULL != fp )

{

fseek( fp, 0, SEEK_END );

filesize = ftell(fp);

fseek( fp, 0, SEEK_SET );

file_buff = (char *) malloc (filesize);

memset(file_buff, 0, filesize);

size_t rcount = fread(file_buff, sizeof(char), filesize, fp);

fclose(fp);

fp = NULL;

}

/* 使用摄像头拍的照片的时间作为pic表中remark 字段，可用来作搜索的关键字段 */

tm = localtime(&stat_buff.st_mtime);

strftime(datastring, sizeof(datastring),"%T", tm);

sqlite3_prepare(db, "insert into pic(pic, remark) values(?,?)", -1, &stmt, 0);

sqlite3_bind_blob(stmt, 1, file_buff, filesize, NULL);

sqlite3_bind_text(stmt, 2, datastring, sizeof(datastring), NULL);

rc = sqlite3_step(stmt);

if ( SQLITE_DONE != rc )

{

fprintf(stderr, "Can't insert table :%s/n", sqlite3_errmsg(db));

sqlite3_close(db);

exit(0);

}

/* 删除已存储到数据库中的照片 */

remove(file_path);

/* 释放资源 */

sqlite3_finalize(stmt);

stmt = NULL;

free(file_buff);

file_buff = NULL;

}

/* 关闭描述符 */

closedir(dp);

sqlite3_close(db);

return 0;

}

/* ----- end of function put_pics ----- */

/*}}}*/

* === FUNCTION ======================================================================

* Name: get_pics

* Description: 根据用户提交的表单，模拟查询用户感兴趣的图片，将其发到客户端

* =====================================================================================

/*{{{*/

int get_pics ( int argc, char *argv[] )

{

/* argv[1] 是 id */

sqlite3 *db = NULL;

char *dbname = "gprsPdu.db";

sqlite3_stmt *stmt = NULL;

char sql_select[MSIZE] = { 0 };

int rc;

FILE *fp = NULL;

pid_t pid;

pid = fork();

if ( -1 == pid )

{

exit(-1);

}

else if ( pid == 0)

{

rc = sqlite3_open(dbname, &db);

if ( SQLITE_OK != rc )

{

fprintf(stderr, "Cannot open pic table/n");

return -1;

}

if ( 0 == strncmp( argv[1], "0", 1) )

{

/* 当用户查询表单为空时，输出全表 */

sprintf(sql_select, "select * from pic");

}

else

{

/* 用户使用精确查询 */

sprintf(sql_select, "select * from pic where id = %d", atoi(argv[1]));

}

sqlite3_prepare(db, sql_select, -1, &stmt, 0);

rc = sqlite3_step(stmt);

while( SQLITE_ROW == rc )

{

char tmp_pic[SIZE30];

/* 把表中的字段对应的值取出来, 用来显示到网页上 */

int id = sqlite3_column_int(stmt, 0);

const void * pic_buff = sqlite3_column_blob(stmt, 1);

int size = sqlite3_column_bytes(stmt,1);

const char *remark = sqlite3_column_text(stmt,2);

/* 产生临时文件，最终输出到王爷后，在删除 */

sprintf(tmp_pic, "temp%d.jpg", id);

fp = fopen(tmp_pic, "wb");

if ( NULL != fp)

{

size_t ret = fwrite(pic_buff, sizeof(char), size, fp);

fclose(fp);

fp = NULL;

}

#if GET_PICS_CGI

fprintf(cgiOut, "

");

fprintf(cgiOut, "

",id );

fprintf(cgiOut, "

", tmp_pic);

fprintf(cgiOut, "

",remark);

fprintf(cgiOut, "");

#endif

/* 再次执行，得到一行 */

rc = sqlite3_step(stmt);

}

/* 释放资源 */

sqlite3_finalize(stmt);

stmt = NULL;

/* 关闭数据库 */

sqlite3_close(db);

}

else

{

close(0);

close(1);

int status;

wait(&status);

/* 删除临时文件 */

system("rm temp*.jpg");

exit(0);

}

return 0;

}

/* ----- end of function get_pics ----- */

/*}}}*/

* === FUNCTION ======================================================================

* Name: callback

* Description: 回调函数，详细请参考其他.

* =====================================================================================

/*{{{*/

int callback( void *para, int n_column, char **column_value, char **column_name)

{

int i;

printf("-----------------------------/n");

for ( i=0; i< n_column; i++ )

{

printf( "- %s : %s /n", column_name[i], column_value[i] );

}

printf("-----------------------------/n");

return 0;

}

/* ----- end of function callback ----- */

/*}}}*/

* === FUNCTION ======================================================================

* Name: del_callback

* Description: 输出到客户端回调函数，详细请参考其他.

* =====================================================================================

/*{{{*/

int del_callback( void *para, int n_column, char **column_value, char **column_name)

{

int i;

char des[MSIZE];

fprintf(cgiOut, "

");

for ( i=0; i< n_column; i++ )

{

fprintf(cgiOut, "

");

fprintf(cgiOut, "%s", column_value[i]);

fprintf(cgiOut, "

");

}

fprintf(cgiOut, "

");

fprintf(cgiOut, "", column_value[0]);

fprintf(cgiOut, "

");

fprintf(cgiOut, "");

return 0;

}

/* ----- end of function callback ----- */

/*}}}*/

* === FUNCTION ======================================================================

* Name: set_callback

* Description: 输出到客户端回调函数，详细请参考其他.

* =====================================================================================

/*{{{*/

int set_callback( void *para, int n_column, char **column_value, char **column_name)

{

int i;

char des[MSIZE];

fprintf(cgiOut, "

");

for ( i=0; i< n_column; i++ )

{

fprintf(cgiOut, "

");

fprintf(cgiOut, "%s", column_value[i]);

fprintf(cgiOut, "

");

}

fprintf(cgiOut, "

");

fprintf(cgiOut, "", column_value[0]);

fprintf(cgiOut, "

");

fprintf(cgiOut, "");

return 0;

}

/* ----- end of function callback ----- */

/*}}}*/

* === FUNCTION ======================================================================

* Name: cgi_callback

* Description: 输出到客户端回调函数，详细请参考其他.

* =====================================================================================

/*{{{*/

int cgi_callback( void *para, int n_column, char **column_value, char **column_name)

{

int i;

char des[MSIZE];

fprintf(cgiOut, "

");

for ( i=0; i< n_column; i++ )

{

fprintf(cgiOut, "

");

fprintf(cgiOut, "%s", column_value[i]);

fprintf(cgiOut, "

");

}

fprintf(cgiOut, "");

return 0;

}

/* ----- end of function callback ----- */

/*}}}*/