#include <Stdio.h>
//包含SQLite3头文件
#include "sqlite3.h"
//连接sqlite3库文件
#pragma comment(lib,"sqlite3.lib")
int main( int argc, char* argv[] )
{
int nRet;
sqlite3 *db=NULL;
nRet = sqlite3_open( "xx.db", &db );
if ( 0 != nRet )
{
printf("Open db error %s/n", sqlite3_errmsg(db));
sqlite3_close( db );
}else
{
printf( "open db success/n" );
}
//create a table
char *zErrMsg = 0;
char *sql = " CREATE TABLE SensorData( ID INTEGER PRIMARY KEY,SensorID INTEGER,SiteNum INTEGER,Time VARCHAR(12),SensorParameter REAL);";
printf( "%s/n", sql );
nRet = sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
if ( 0 != nRet )
{
printf("Create table falure, MSG:%s/n", zErrMsg);
}else
{
printf( "Create table success/n" );
}
//insert data
sql = "INSERT INTO /"SensorData/" VALUES(NULL , 1 , 1 , '200605011206', 18.9 );" ;
nRet = sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
if ( 0 != nRet )
{
printf("Insert Error:%s", zErrMsg);
}else
{
printf("Insert OK/n");
}
//insert data
sql = "INSERT INTO /"SensorData/" VALUES(NULL , 1 , 1 , '200605011306', 16.4 );" ;
nRet = sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
if ( 0 != nRet )
{
printf("Insert Error:%s", zErrMsg);
}else
{
printf("Insert OK/n");
}
//update data
sql = "UPDATE SensorData SET SensorParameter=1000.01,SensorID=123 WHERE SensorParameter=100.01";
nRet = sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
if ( 0 != nRet )
{
printf("Update Error:%s", zErrMsg);
}else
{
printf("Update OK/n");
}
//delete data
sql = "DELETE FROM SensorData WHERE SensorID=123";
nRet = sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );
if ( 0 != nRet )
{
printf("Delete Error:%s", zErrMsg);
}else
{
printf("Delete OK/n");
}
//query data
int nrow = 0, ncolumn = 0;
char **azResult; //二维数组存放結果
sql = "SELECT * FROM SensorData ";
sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );
int i = 0 ;
printf( "row:%d column=%d /n" , nrow , ncolumn );
printf( "/nThe result of querying is : /n" );
for( i=0 ; i<( nrow + 1 ) * ncolumn ; i++ )
printf( "azResult[%d] = %s/n", i , azResult[i] );
//释放azResult 的內存空間
sqlite3_free_table( azResult );
//关闭数据库
sqlite3_close( db );
return 0;
}