SQLite3学习笔记

#include <iostream>
using namespace std;

#include "sqlite3.h"

#include <ctime>

void GetNow(char *buf)
{
	time_t now_time;
	now_time = time(NULL);
	struct tm *localTime;
	localTime = localtime(&now_time);

	int year = 1900 + localTime->tm_year;
	int month = 1 + localTime->tm_mon;
	sprintf(buf,"%04d-%02d-%02d %02d:%02d:%02d",year,month,localTime->tm_mday,localTime->tm_hour,localTime->tm_min,localTime->tm_sec);
}

int main()
{
	sqlite3 *pDB = NULL;
	char * pErr;

	//for sql insert statement prepare
	string address;
	string isEnable;
	string lastAccessTime;

	//Open the database file,if not exist create it.
	int rc = sqlite3_open("MySQLite.db",&pDB);

	if (rc)
	{
		cout << " Open the database failed" << endl;
	}


	//Create table if not exist.
	string sql = "create table if not exists AvailableServer"
		"(AS_ID INTEGER PRIMARY KEY autoincrement,Address VARCHAR(64),IsEnable bool,AccessTime DATETIME);";

	rc = sqlite3_exec(pDB,sql.c_str(),NULL,&pErr);
	if (rc != SQLITE_OK)
	{
		cout << "Create table failed!" << endl;
	}

	//Add new line
	//insert into test(date_time) values(date('2012-12-21 00:00'))
	sql = "insert into AvailableServer(Address,IsEnable,AccessTime) values(?,?,?)";
	sqlite3_stmt  *stmtInsert = NULL;
	rc = sqlite3_prepare_v2(pDB,sql.size(),&stmtInsert,NULL);
	if (rc != SQLITE_OK)
	{
		if (stmtInsert)
			//delete a prepared statement
			sqlite3_finalize(stmtInsert);

		cout << "SQL syntax error!" << endl;
		goto _END;
	}

	address = "www.baidu.com/recognizevoice";
	isEnable = "true";
	lastAccessTime = "2016-03-02 14:13:12";
	sqlite3_bind_text(stmtInsert,1,address.c_str(),address.size(),NULL);
	sqlite3_bind_text(stmtInsert,2,isEnable.c_str(),isEnable.size(),3,lastAccessTime.c_str(),lastAccessTime.size(),NULL);
	if (sqlite3_step(stmtInsert) != SQLITE_DONE)
	{
		//delete a prepared statement
		sqlite3_finalize(stmtInsert);
		cout << "Add new line to the AvailableServer table failed!" << endl;
		goto _END;
	}
	//reset stmt variable's data,but not change sql content.
	//sqlite3_reset(stmt);

	//Erase the specified line.
	sql = "delete from AvailableServer where AS_ID=1";

	rc = sqlite3_exec(pDB,&pErr);
	if (rc != SQLITE_OK)
	{
		cout << "Erase the specified line in the table failed!" << endl;
	}

	//Modify the specified line.
	sqlite3_stmt  *stmtUpdate = NULL;
	//sql = "UPDATE AvailableServer SET Address=?,AccessTime=?,IsEnable=? where AS_ID=?";
	sql = "UPDATE AvailableServer SET IsEnable=?,AccessTime=? where Address=?";
	sqlite3_prepare_v2(pDB,&stmtUpdate,NULL);
	address = "www.google2.com";
	char bufLastAccessTime[64] = { 0 };
	GetNow(bufLastAccessTime);
	sqlite3_bind_int(stmtUpdate,0);
	sqlite3_bind_text(stmtUpdate,bufLastAccessTime,strlen(bufLastAccessTime),NULL);
	sqlite3_bind_text(stmtUpdate,NULL);
	rc = sqlite3_step(stmtUpdate);

	//Query info from the table.
	sqlite3_stmt  *stmtSelect = NULL;
	sql = "select AS_ID,Address,AccessTime from AvailableServer";
	//true value of bool type//sql = "select * from AvailableServer where IsEnable=1";
	//false value of bool type//sql = "select * from AvailableServer where IsEnable=0";
	sqlite3_prepare_v2(pDB,&stmtSelect,NULL); 
	while (sqlite3_step(stmtSelect) == SQLITE_ROW)
	{
		cout << "ID:" << sqlite3_column_int(stmtSelect,0) <<
			" Address:" << sqlite3_column_text(stmtSelect,1) << 
			" IsEnable:" << sqlite3_column_text(stmtSelect,2) <<
			" AccessTime:" << sqlite3_column_text(stmtSelect,3) << endl;		

		//when bool type value want to append to a string,u must using below snippet.
		//content.append((char *)(sqlite3_column_text(g_stmtList,2)==NULL?"false":"true"));
	}
	//Query again!
	sqlite3_reset(stmtSelect); 
	cout << "Query again!" << endl;
	while (sqlite3_step(stmtSelect) == SQLITE_ROW)
	{
		cout << "ID:" << sqlite3_column_int(stmtSelect,1) <<
			" IsEnable:" << sqlite3_column_text(stmtSelect,3) << endl;
	}

	//delete a prepared statement
	sqlite3_finalize(stmtSelect);

	//Release the database instance.
_END:
	sqlite3_close(pDB);

	cin.get();

	return 0;
}

Compile the program dependencies ASIO and SQLite3 in CentOS7

#project name
project(AvailableServers)  
cmake_minimum_required(VERSION 2.8)
  
#set verbose on
SET( CMAKE_VERBOSE_MAKEFILE ON )    

#boost asio library dependencies below boost libraries
find_package(Boost 1.57 COMPONENTS system thread chrono date_time regex serialization coroutine REQUIRED)

include_directories(${Boost_INCLUDE_DIRS})  
 
#files are in current path need compile
aux_source_directory(. DIR_SRCS)  

#define executable executable file name
add_executable(AvailableServers ${DIR_SRCS})

#Default search library path is /usr/local/lib and /usr/local/include.
#Add dl pthread two libraries int tail is for compile the sqlite3.c file.
TARGET_LINK_LIBRARIES(AvailableServers ${Boost_LIBRARIES} dl pthread)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!