sqlite3数据库操作小记

mark一下

#include "sqlite3.h"
#include <windows.h>

#pragma comment(lib,"sqlite3.lib")

int _tmain(int argc, _TCHAR* argv[])
{
	char *zErrMsg = 0;
	int nRet;
	sqlite3 *db=NULL;
	nRet = sqlite3_open( "E:\\Login Data", &db ); 
	if ( 0 != nRet )
	{
		printf("Open db error %s\n", sqlite3_errmsg(db));
		sqlite3_close( db );   

	}
	else    
	{   
		printf( "open db success\n" );   
	}

	//insert data
	char strSql[MAX_PATH];
	strcpy(strSql,"insert into logins (origin_url,username_value,password_element,signon_realm,ssl_valid,preferred,date_created,blacklisted_by_user,scheme) values ('bb','admin','123','fuck',0,0,0,0,0)");
	nRet = sqlite3_exec( db , strSql , 0 , 0 , &zErrMsg );
	if ( 0 != nRet )   
	{    
		printf("Insert Error:%s", zErrMsg); 
	}
	else    
	{    
		printf("Insert OK\n"); 
	}

	//query data
	int nRow = 0, nColumn = 0;
	int index,nResult;

	char **azResult; //二维数组存放結果
	
	strcpy(strSql,"SELECT origin_url, action_url, username_element, username_value, password_element, password_value, signon_realm, date_created from logins");
	nResult = sqlite3_get_table( db , strSql , &azResult , &nRow , &nColumn , &zErrMsg );
	
	printf( "row:%d column=%d \n" , nRow , nColumn );
	printf( "\nThe result of querying is : \n" );

	int i,j;
	if( SQLITE_OK == nResult)
	{
		//查询成功
		index = nColumn; //前面说过 dbResult 前面第一行数据是字段名称，从 nColumn 索引开始才是真正的数据

		printf("查到%d条记录\n", nRow );

		for(i = 0; i < nRow ; i++ )
		{
			printf("第 %d 条记录\n", i+1 );
			for( j = 0 ; j < nColumn; j++ )
			{
				printf("字段名:%s  > 字段值:%s\n",azResult[j],azResult[index]);
				index++; // dbResult 的字段值是连续的，从第0索引到第 nColumn - 1索引都是字段名称，从第 nColumn 索引开始，后面都是字段值，它把一个二维的表（传统的行列表示法）用一个扁平的形式来表示
			}
			printf("-------\n");
		}
	}

	//delete data
	strcpy(strSql,"DELETE FROM logins WHERE username_value='admin'");
	nRet = sqlite3_exec( db , strSql , 0 , 0 , &zErrMsg );
	if ( 0 != nRet )   
	{    
		printf("Delete Error:%s", zErrMsg);
	}
	else
	{
		printf("Delete OK\n");
	}

	//释放azResult 的內存空間
	sqlite3_free_table( azResult );
	//关闭数据库
	sqlite3_close( db );
	
	getchar();
	return 0;
}