这几天遇到了一个问题就是在insert以及update数据的时候,总是生成一个.db-journal文件,重新启动程序后,该文件消失,前面添加修改的数据没有成功保存。关于如何解决这个问题,网上也没有人给出详细答案,但经过几天的分析,试验,最后成功了。不知道对所有出现的这个问题都能应付的过来,解决的方法很简单,如下:程序中使用了sqlite3_stmt *stmt;然后通过rc = sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail);将sql语句解析到stmt中,然后对数据库操作完成之后,没有释放stmt,当我调用sqlite3_finalize(stmt);将stmt释放之后,上面的问题不在出现了。对此sqlite中还有一对匹配的用法。如下:sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );sqlite3_free_table( azResult ); 下面是一些sql语句的用法。sprintf(sql, "INSERT INTO \"Phonebook\" VALUES((select min(ID)-1 from Phonebook where ID>1 and ID-1 not in (select ID from Phonebook)), \'%s\', \'%s\');", p->list[1], p->list[2]);  char sql1[100];sprintf(sql1, "select * from Phonebook where Username like \'%s%%\'order by Username;", buffer);上面是关于like匹配的用法,Username like '王%'   以及 Username like '王_' ,%用来匹配任意字符,_用来匹配一个字符。而且还要注意的是,c语言中 %的转义方式是%%,而不是\% 。 上面一句话是从csdn上问的结果,插入的数据,是按照ID号从小的开始插入。也就是将中间的空缺补上。#include #include #include "sqlite3.h" #define _DEBUG_int main( void ){sqlite3 *db=NULL;char *zErrMsg = 0;int rc;///open(create) databaserc = sqlite3_open("phonebook.db", &db);if( rc ){fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));sqlite3_close(db);return 0;}else printf("You have opened a sqlite3 database named zieckey.db successfully!\nCongratulations! Have fun ! ^-^ \n");///create tablechar *sql = " CREATE TABLE Call_Record(\      Status CHAR(10),\      Username VARCHAR(20),\      Phonenubmer VARCHAR(20),\      Time timestamp,\      PRIMARY KEY (Status, Username)\      );" ;//上面一句用来定义两个主键sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );///create table2sql = " CREATE TABLE Phonebook(\      Username VARCHAR(20) PRIMARY KEY,\      Phonenubmer VARCHAR(20)\      );" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );///insert databasesql = "INSERT INTO \"Call_Record\" VALUES('missed', 'Tom', '15908079860',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('answered', 'Jack', '15908079861',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('dialed','Merry', '15908079863',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('dialed', 'Zjf', '15908079864',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('dialed', 'Qcx', '15908079865',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('dialed', 'MM', '15908079866',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('answered', 'JJ', '15908079867',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('answered', 'GG', '15908079868',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('answered', 'DD', '15908079869',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('answered', 'Father', '15908079870',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Call_Record\" VALUES('missed', 'Mather', '15908079871',datetime('now','localtime'));" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );///insert database table twosql = "INSERT INTO \"Phonebook\" VALUES('Tom', '15908079860');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('Jack', '15908079861');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('Merry', '15908079863');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('Zjf', '15908079864');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('Qcx', '15908079865');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('MM', '15908079866');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('JJ', '15908079867');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('GG', '15908079868');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('DD', '15908079869');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('Father', '15908079870');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );sql = "INSERT INTO \"Phonebook\" VALUES('Mather', '15908079871');" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );int nrow = 0, ncolumn = 0;char **azResult;///select databasesql = "SELECT * FROM Call_Record ";//sql = "select * from Phonebook order by Username";   //默认为升序asc//sql = "select * from Phonebook order by Username desc";   //降序//sql = "select * from Phonebook order by (case Username when \"\" then \'zzzzz\' else Username end);";//sql = "select * from Phonebook order by (case Username when null then \'zzzzz\' else Username end);";//上面一句是处理空值情况(null 与""),sqlserver默认空值为最小,使用case可以改变它查询的值。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 ; iprintf( "azResult[%d] = %s\n", i , azResult[i] );///select databasesql = "SELECT * FROM Phonebook ";sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );printf( "row:%d column=%d \n" , nrow , ncolumn );printf( "\nThe result of querying is : \n" );for( i=0 ; iprintf( "azResult[%d] = %s\n", i , azResult[i] );/*///delete databasesql = "DELETE FROM Call_Record WHERE Username = \'GG\' ;" ;sqlite3_exec( db , sql , 0 , 0 , &zErrMsg );///update databasechar sql1[100];sprintf(sql1,"UPDATE Call_Record SET Phonenubmer = \'%s\' WHERE Username = \'%s\'", "10111", "MM");sqlite3_exec( db , sql1 , 0 , 0 , &zErrMsg );*////select database desc (jiangxupailie)///if no desc is default(asc)sql = "SELECT * FROM Call_Record order by Username desc";sqlite3_get_table( db , sql , &azResult , &nrow , &ncolumn , &zErrMsg );printf( "\n\n\n\nrow:%d column=%d " , nrow , ncolumn );printf( "\nAfter deleting , the result of querying is : \n" );for( i=0 ; iprintf( "azResult[%d] = %s\n", i , azResult[i] );///free azResultsqlite3_free_table( azResult );///close dbsqlite3_close(db);return 0;}另外一种查询获取列值的方法:            int i, j, rc, ncols;            char *sql;            char *tail;            char buff[100];            sqlite3_stmt *stmt;            ///about dababase select            sql = "select * from Phonebook order by (case Username when \"\" then \'zzzzz\' else Username end);";            rc = sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail);            if (rc !=SQLITE_OK)            {                break;            }            rc = sqlite3_step(stmt);                      //取得数据表中一行的数据            ncols = sqlite3_column_count(stmt);  //获取数据表有多少列            while(rc==SQLITE_ROW) {                for (j = 0; j                     sprintf(buff,"%s",sqlite3_column_text(stmt, j)); //获取一行中每一列的数据                    printf("%s\n",buff);                }                rc= sqlite3_step(stmt);            //获取下一行数据            }            sqlite3_finalize(stmt);
09-06 13:39
查看更多