#include "sqlite3.h"
// DATABASE NAME
#define DB_SQLITE3_TEST_FULLNAME "test.db"
// TABLE NAME
#define DB_TestTableName "test"
static sqlite3* td_db=NULL;
static char* pTempOutputMsg=NULL;
// 1. open/creat database
bool OpenDatabase(char *pInputDatabase)
{
if(SQLITE_OK != sqlite3_open(pInputDatabase,&td_db))
{
printf("%s\n",sqlite3_errmsg(td_db));
return false;
}
sqlite3_exec(td_db,"PRAGMA synchronous = OFF",0,0,&pTempOutputMsg);//如果有定期备份的机制,而且少量数据丢失可接受,用OFF
sqlite3_exec(td_db,"PRAGMA page_size = 4096",0,0,&pTempOutputMsg);//只有在未创建数据库时才能设置
sqlite3_exec(td_db,"PRAGMA cache_size = 8000",0,0,&pTempOutputMsg); //建议改为8000
sqlite3_exec(td_db,"PRAGMA case_sensitive_like=1",0,0,&pTempOutputMsg);//搜索中文字串
return true;
}
// 2. create table.
bool CreateTable(char tInputTableNo)
{
char pTempCmd[256];
switch( tInputTableNo )
{
case 0:
sprintf(pTempCmd, "create table %s %s",DB_TestTableName,
" ("
"idx integer, "
"lang integer"
");");
break;
default:
break;
}
//JPRINTF(("pTempCmd = %s \n",pTempCmd));
if(SQLITE_OK!=sqlite3_exec(td_db,pTempCmd,NULL,NULL,&pTempOutputMsg))
{
printf("%s\n",sqlite3_errmsg(td_db));
return false;
}
else
return true;
}
// 3. insert records into table.
bool InsertRecords(char* pInputTableName,int pInputInsertCnt)
{
int i;
char pTempCmd[256];
for(i=0;i<pInputInsertCnt;++i)
{
sprintf(pTempCmd,"insert into %s values(7351,%d,0,'WWW',30,3,0,0,0,0,1,2,3,4);",pInputTableName,i);
//JPRINTF(("SQL: %s \n",pTempCmd));
if( SQLITE_OK!=sqlite3_exec(td_db,pTempCmd,0,0,&pTempOutputMsg) )
return false;
}
return true;
}
// 4. show records in the table.
bool ShowRecords(char *pInputTableName)
{
sqlite3_stmt* stmt=NULL;
char* szMsg=NULL;
int one=0,two=0;
char *pTempName;
char pTempCmd[256];
sprintf(pTempCmd,"select * from %s;",pInputTableName);
if(SQLITE_OK!=sqlite3_prepare(td_db,pTempCmd,strlen(pTempCmd),&stmt,NULL))
return false;
printf("\n\tone\t\ttwo\t\tname\n");
printf("\t--------------------\n");
while(1)
{
if(SQLITE_ROW!=sqlite3_step(stmt))
break;
//sqlite3_column_text(stmt,0);
one=sqlite3_column_int(stmt,0);
two=sqlite3_column_int(stmt,1);
pTempName=(char *)sqlite3_column_blob(stmt,2);
printf("\t%d\t\t%d\t\t%s\n",one,two,pTempName);
}
sqlite3_finalize(stmt);
printf("\n");
return true;
}
// 5. delete the records from table.
bool DeleteRecords(char *pInputTableName, char *pInputIndexName, int pInputIndexValue)
{
char pTempCmd[256];
sprintf(pTempCmd,"delete from %s where %s=%d;",pInputTableName,pInputIndexName,pInputIndexValue);
if(SQLITE_OK!=sqlite3_exec(td_db,pTempCmd,NULL,NULL,&pTempOutputMsg))
{
printf("%s\n",sqlite3_errmsg(td_db));
return false;
}
else
return true;
}
// 6. drop the point table.
bool DropTable(char *pInputTableName)
{
char pTempCmd[256];
sprintf(pTempCmd,"drop table %s;",pInputTableName);
if(SQLITE_OK!=sqlite3_exec(td_db,pTempCmd,NULL,NULL,&pTempOutputMsg))
{
printf("%s\n",sqlite3_errmsg(td_db));
return false;
}
else
return true;
}
// 7 .Search database
bool SearchDatabase(char *pInputTableName, char *pInputIndexName, int pInputIndexValue)
{
int nret;
int one=0,two=0;
char *pTempName;
char pTempCmd[256];
sqlite3_stmt* stmt=NULL;
sprintf(pTempCmd,"select * from %s where %s=%d;",pInputTableName,pInputIndexName,pInputIndexValue);
if(SQLITE_OK!=sqlite3_exec(td_db,pTempCmd,NULL,NULL,&pTempOutputMsg))
{
printf("%s\n",sqlite3_errmsg(td_db));
return false;
}
printf("\n\tone\t\ttwo\t\tname\n");
printf("\t--------------------\n");
while(1)
{
if(SQLITE_ROW!=sqlite3_step(stmt))
break;
one=sqlite3_column_int(stmt,0);
two=sqlite3_column_int(stmt,1);
pTempName=(char *)sqlite3_column_blob(stmt,2);
printf("\t%d\t\t%d\t\t%s\n",one,two,pTempName);
}
sqlite3_finalize(stmt);
printf("\n");
return true;
}
// 8. creat index on pointer table.
bool CreatIndexOnDatabase(char *pInputIndexName,char *pInputTableName,char *pInputRawName)
{
char pTempCmd[256];
sprintf(pTempCmd,"create index %s on %s(%s)",pInputIndexName,pInputTableName,pInputRawName);
if(SQLITE_OK!=sqlite3_exec(td_db,pTempCmd,NULL,NULL,&pTempOutputMsg))
{
printf("%s\n",sqlite3_errmsg(td_db));
return false;
}
else
return true;
}
// 9. close database
bool CloseDatabase()
{
sqlite3_close(td_db);
td_db=NULL;
return true;
}
int Db_Sqlite3_init(void)
{
char pTempString[256];
if(!OpenDatabase(DB_SQLITE3_TEST_FULLNAME))
{
JPRINTF(("\nStep 1. Open database failed.\n"));
return -1;
}else
JPRINTF(("\nStep 1. Open database succeeded.\n"));
if( !CreateTable(0) )
{
JPRINTF(("Step 2. Create table failed.\n"));
}else
JPRINTF(("Step 2. Create table succeeded.\n"));
if( !InsertRecords(DB_TestTableName,5) )
{
JPRINTF(("Step 3. Insert data to point table Failed.\n"));
}else
JPRINTF(("Step 3. Insert data to point table succeeded.\n"));
if( !ShowRecords(DB_TestTableName))
{
JPRINTF(("Step 4. Read data from point table Failed.\n"));
}else
JPRINTF(("Step 4. Read data from point table succeeded.\n"));
if( !DeleteRecords(DB_TestTableName,"lang",2) )
{
JPRINTF(("Step 5. Delete data from point table Failed.\n"));
}else
JPRINTF(("Step 5. Delete data from point table succeeded.\n"));
// show again.
if( !ShowRecords(DB_TestTableName))
{
JPRINTF(("Step 4. Read data from point table Failed.\n"));
}else
JPRINTF(("Step 4. Read data from point table succeeded.\n"));
#if 0
if( !DropTable(DB_TestTableName))
{
JPRINTF(("Step 6. delete point table Failed.\n"));
}else
JPRINTF(("Step 6. delete point table succeeded.\n"));
#endif
if( !SearchDatabase(DB_TestTableName,"lang",3) )
{
JPRINTF(("Step 7. Serch data from point table Failed.\n"));
}else
JPRINTF(("Step 7. Serch data from point table succeeded.\n"));
if( !CreatIndexOnDatabase("lang_",DB_TestTableName,"lang") )
{
JPRINTF(("Step 8. create index on point table Failed.\n"));
}else
JPRINTF(("Step 8. create index on point table succeeded.\n"));
CloseDatabase();
}
分享到:
相关推荐
SQLite3 API编程手册
sqlite3,SQLite3 C语言API入门.pdf
sqlite3编程实例,VS编写,下载可以直接运行测试。内部包含sqlite3源码
本文件给出了sqlite编程实例源码及其执行结果。其中,源码实例中包括了最常用的一些API,非常适合对于初学者学习使用。 有关SQLite安装与编程请参考下面的帖子 Linux下SQLite数据库安装操作与编程 . ...
Wince SQLite 数据库编程 工程文件CPP
SQLite3API编程手册,C++,需要的快下载哦
包含常用的API函数讲解及例程,方便快速入门
本资源给出了linux+ARM 环境下SQLite数据库嵌入式编程实例。 详细内容请参考 Linux下SQLite数据库移植与编程 . http://blog.csdn.net/lanyang123456/article/details/7680670
使用sqlite3所需要用到的库文件等
gcc使用的sqlite3数据库,sqlite3.a ,可以用于Dev-C++,也可以用于Windows下使用gcc编译的程序,sqlite版本是:3270200,还包含了sqlite3.lib库文件,可以用于visual studio。
SQlite3中级篇(C/C++编程接口) 源代码
sqlite3.h sqlite3头文件~~~~~~~
SQLite适用的范围
基于sqlite-amalgamation-3280000和wxsqlite3-3.3.1重编译版本,支持Sqlite3_key和Sqlite3_rekey方法
基于sqlite3的加密版本的数据库动态链接库,附lib和.h头文件,新测试可用。可设置密码和修改密码,加密后的数据库文件是无法被打开的。官方提供的免费版本sqlite3都是没有加密的,官方收费的很贵哦,有需要的朋友...
Qt(由挪威TrollTech公司开发)是一个功能全面、高性 能、多平台的C++图形用户界面...境下以一个编程实例讲解Qt中SQLite数据库的应用编程过 程,向读者展示利用Qt与SQLite数据库开发嵌入式数据库应 用程序的一般方法。
linux下的sqlite3 C语言编程实例
包括sqlite3.lib sqlite3.h sqlite3.dll sqlite3.exe
sqlite3 svn cleanup sqlite3 svn cleanup sqlite3 svn cleanup
文件列表:1)sqlite3.h;2)sqlite3.lib;3)sqlite3.dll; 学习VS2010/MFC下数据库编程已经有一段时间了,大多是在网站上浏览一些资源,这些资源大多以VC6.0位开发环境,而在VS2010下往往出现问题,所幸看到了...