SQLite database usage Summary

SQLite is a small open-source database mainly used on embedded mobile devices.

1. Download The sqlite3 package

You can go to the official website of SQLite to download, use the SQLite version of a variety of systems, for: http://www.sqlite.org/

Ii. compile and generate lib files

Start a command line and enter the installation directory of VC, for example, C:/Microsoft Visual Studio/vc98/bin. There is a lib.exe file under this directory, we are using this file to generate the required sqlite3.lib file, put the sqlite3.def file in the sqlite-source-3_3_7.zip package officially downloaded in SQLite to the same directory, or absolute path, and then enter the following command in the command line: d:/Microsoft Visual Studio/vc98/bin> lib/machine: ix86/DEF: SQLite. def. If the command is successfully executed, we can see that the lib.exe file has helped us generate the sqlite3.lib file. Of course I have compiled a, You can go here to download: http://download.csdn.net/source/2480804

III. C ++ encapsulation class

Since SQLite is written in C language, in order to make it more in line with object-oriented thinking, I encapsulated C ++ on the C API provided by SQLite, make the operation more convenient, this class advantage is: concise and clear, simple operation, disadvantage is: does not support UTF-8. if there is any problem with the code, or have any good suggestions, you can raise it, you can also mail me: sgc0710@gmail.com. the source code is as follows:

 

SQLite. h

# Ifndef sqlite_h <br/> # define sqlite_h <br/> // standard constants <br/> # UNDEF null <br/> # define null 0 <br/> # include" sqlite3.h "<br/> # include <stdio. h> <br/> # include <iostream> <br/> # include <string> <br/> # include <windows. h> <br/> # include <tchar. h> <br/> using namespace STD; // defines the namespace <br/> # pragma comment (Lib, "sqlite3.lib ") // load sqlite3.dll <br/> //////////////////////////////// ///////////////////// /// // <Br/> // class function: error handling when operating the SQLite database <br/> // Author: Sun gaochao <br/> // Date: 2010.06.14 <br/> /////////////////////////////////// /// // <br/> class cmyerror: public STD: runtime_error // inheritance and experience class <br/>{< br/> Public: <br/> cmyerror (const char * strerrmsg); <br/> }; <br/> //////////////////////////////////// /// // <br //> // class functions: database record set class <br/> // Author: Sun gaochao <br/> // Date: 2010.06.14 <br/> /////////////////////////////////// /// // <br/> class csqliterecordset <br/>{< br/> public: <br/> void finalize (); // release record set function <br/> void nextrow (); // record pointer moving function <br/> lpctstr getfieldvalue (lpctstr strfield ); // obtain the field value <br/> bool rseof (); // function at the end of the record set pointer <br/> csqliterecordset (sqlite3 * PDB, sqlite3_stmt * PVM, bool beof, bool bfinalize = true); <br/> csqli Terecordset (); <br/> ~ Csqliterecordset (); <br/> Public: <br/> void movefrist (); <br/> PRIVATE: <br/> int icols; // Number of columns <br/> sqlite3 * m_pdb; // define the database object <br/> sqlite3_stmt * m_pvm; // record set pointer an instance of the following opaque structure is used to represent a compiled SQL statment. <br/> bool iseof; // end flag <br/> bool isfinalize; // release flag <br/> }; <br/> //////////////////////////////////// /// // <br //> // Class Functions: Operate SQLite database Open Database execute Operation database read database table <br/> // Author: Sun gaochao <br/> // Date: 2010.06.14 <br/> /////////////////////////////////// /// // <br/> class csqlite <br/>{< br/> public: <br/> void closedb (); <br/> bool executesql (const char * strsql ); // execute the SQL statement function <br/> bool opensqlitedb (const char * strnamedb); // connect to the database <br/> bool funchecktable (string strtablename ); // check whether the data table exists. True does not exist. False <br/> C Sqliterecordset execquery (lpctstr strsql); // read the value of a specific field <br/> csqlite (); <br/> virtual ~ Csqlite (); <br/> PRIVATE: <br/> sqlite3 * sqlitedb; // define the database object <br/>}; <br/> # endif

SQLite. cpp

// SQLite. CPP: Implementation of the csqlite class. <br/> ////////////////////////////// //////////////////////////////////////// <br/> # include "SQLite. H "<br/> extern" C "<br/>{< br/> # include" sqlite3.h "<br/> }; <br/> //////////////////////////////////// /// // <br/> // csqlite construction/destruction <br/> //////////////////////////////// ///////////////////////////// //// // <Br/> csqlite: csqlite () <br/>{< br/> sqlitedb = 0; // initialization <br/>}< br/> csqlite ::~ Csqlite () <br/>{< br/> closedb (); // close the database <br/>}< br/> /////////////////////////// //////////////////////////////////////// /// <br/> // csqliterecordest construction/destruction <br/> /////////////////////// //////////////////////////////////////// ////// <br/> csqliterecordset:: csqliterecordset () <br/>{< br/> // initialize the member variable <br/> m_pvm = 0; <br/> iseof = true; <br/> icols = 0; <br/> m_pdb = 0; <br/>}< br/> csqlite Recordset: csqliterecordset (sqlite3 * PDB, sqlite3_stmt * PVM, bool beof, bool bfinalize/* = true */) <br/>{< br/> // member variable assignment <br/> m_pdb = PDB; <br/> m_pvm = PVM; <br/> iseof = beof; <br/> icols = sqlite3_column_count (m_pvm); <br/>}< br/> csqliterecordset ::~ Csqliterecordset () <br/>{< br/> // release record set pointer <br/> finalize (); <br/>}< br/> /////////////////////////////// /// // <br/> // cmyerror construction/destruction <br/> /////////////////////////// //////////////////////////////////////// /// <br/> cmyerror:: cmyerror (const char * strerrmsg): runtime_error (strerrmsg) {}< br/> //******************************** * ************ <br/> // <br/> // Function: connect to the SQLite database <br/> // parameter: char * string <br/> // return value: true: Connection successful; false: connection Failed <br/> // Author: sun gaochao 2010.06.13 <br/> //************************ * ******************* <br/> bool csqlite:: opensqlitedb (const char * strnamedb) <br/>{< br/> int iopenresult; <br/> iopenresult = 0; <br/> // open the SQLite database <br/> iopenresult = sqlite3_open (strnamedb, & sqlitedb); </P> <p> If (iopenresult! = Sqlite_ OK) <br/>{< br/> throw cmyerror ("the database cannot be opened! "); <Br/>}< br/> return true; <br/>}< br/> //***************************** * *************** <br/> // function functions: perform Database Operations <br/> // parameter: true: Connection succeeded; false: Connection Failed <br/> // return value: number of returned records <br/> // Author: sun gaochao 2010.06.13 <br/> //************************ * ******************* <br/> bool csqlite:: executesql (const char * strsql) <br/>{</P> <p> int iopenresult; // Save the database connection result <br/> char * strerrmsg; // error message <br/> iopenresult = 0; <br/> // When operating the SQLite database, you need to execute begin first and then commit; to take effect <br/> If (sqlite3_exec (sqlitedb, "begin;", 0, 0, & strerrmsg) // start the operation library <br/>{< br/> throw cmyerror (strerrmsg ); <br/>}< br/> iopenresult = sqlite3_exec (sqlitedb, strsql, 0, 0, & strerrmsg); // execute an SQL statement <br/> If (iopenresult! = Sqlite_ OK) <br/>{< br/> throw cmyerror (strerrmsg); // capture error information <br/>}< br/> If (sqlite3_exec (sqlitedb, "commit;", 0, 0, & strerrmsg) // database operation effective <br/>{< br/> throw cmyerror (strerrmsg ); <br/>}< br/> return true; <br/>}< br/> //***************************** * *************** <br/> // function functions: read the specified field from the SQLite database table <br/> // parameter: const char * strtablename table name <br/> // const char * strfactor query condition <br/> // cconst char * STRF Ieldname query field name <br/> // return value: return record set <br/> // Author: sun gaochao 2010.06.13 <br/> //************************ * ******************* <br/> csqliterecordset csqlite:: execquery (lpctstr strsql) <br/>{< br/> int iresult; <br/> sqlite3_stmt * PVM; </P> <p> DO <br/> {<br/> iresult = sqlite3_prepare (sqlitedb, strsql,-1, & PVM, null ); // Execute SQL statement to query execute an SQL query <br/> If (iresult! = Sqlite_ OK) // sqlite_ OK successful result <br/>{< br/> PVM = NULL; <br/> throw cmyerror (sqlite3_errmsg (sqlitedb )); // error message returned when execution fails <br/>}< br/> iresult = sqlite3_step (PVM ); // This function must be called one or more times to execute the statement. <br/> If (sqlite_done = iresult) // query sqlite_done sqlite3_step () has finished executing <br/> {// the last row has been reached <br/> return csqliterecordset (sqlitedb, PVM, True/* EOF */); <br/>}< br/> else if (sqlite_row = iresult) // sqlite_row sqlite3_step () has another row ready <br/> {// at least one row <br/> return csqliterecordset (sqlitedb, PVM, false/* EOF */); <br/>}< br/> iresult = sqlite3_finalize (PVM); </P> <p >}while (sqlite_schema = iresult ); // sqlite_schema the database schema changed <br/> throw cmyerror (sqlite3_errmsg (sqlitedb )); // throw an exception <br/>}< br/> //***************** ************************** <Br/> // <br/> // function: query whether the SQLite database table exists <br/> // parameter: const char * strtablename table name <br/> // return value: true: false: does not exist <br/> // Author: sun gaochao 2010.06.17 <br/> //************************ * ******************* <br/> bool csqlite:: funchecktable (string strtablename) <br/>{< br/> string strsql; <br/> const char * strtail; <br/> string strfieldvalue; <br/> sqlite3_stmt * pstmt; <br/> int iresult; <Br/> // sqlite_master system table type name table name can only be lowercase <br/> strsql = "select * From sqlite_master where type = 'table' and name = '" + strtablename + "'; "; <br/> If (sqlite3_prepare (sqlitedb, strsql. c_str (),-1, & pstmt, & strtail) <br/>{< br/> throw cmyerror (sqlite3_errmsg (sqlitedb )); // error message returned when execution fails <br/>}< br/> iresult = sqlite3_step (pstmt); // Execute SQL </P> <p> sqlite3_finalize (pstmt ); // release stmt <br/> If (iresult! = Sqlite_row) // No record <br/>{< br/> return false; <br/>}< br/> return true; <br/>}< br/> //***************************** * *************** <br/> // function functions: whether the end of the record set is found <br/> // parameter: <br/> // return value: true: false: Not found <br/> // Author: sun gaochao 2010.06.17 <br/> //************************ * ******************* <br/> bool csqliterecordset:: rseof () <br/>{< br/> If (m_pvm = 0) // indicates whether a record pointer exists. <br/>{< br/> throw cmyerror (_ T ("null vi Rtual machine Pointer "); <br/>}< br/> return iseof; <br/>}< br/> //***************************** * *************** <br/> // function functions: obtain the field value <br/> // parameter: field name <br/> // return value: specified field value <br/> // Author: sun gaochao 2010.06.17 <br/> //************************ * ******************* <br/> lpctstr csqliterecordset:: getfieldvalue (lpctstr strfield) <br/>{< br/> int nfield; // obtain the number of columns of this field <br/> If (strfield) // field name is not blank <br/>{< br/> (Nfield = 0; nfield <icols; nfield ++) <br/>{< br/> lpctstr sztemp = (lpctstr) sqlite3_column_name (m_pvm, nfield ); // obtain the field name of the corresponding column <br/> If (_ tcscmp (strfield, sztemp) = 0) // obtain the field value if the names of the connected fields are the same <br/>{< br/> return (lpctstr) sqlite3_column_text (m_pvm, nfield ); <br/>}< br/> throw cmyerror (_ T ("invalid field name requested ")); // if the field is null, an exception is thrown. <br/>}< br/> //********************** *********************** <br // <Br/> // function: query the next row of the record set <br/> // parameter: <br/> // return value: true: false: not yet <br/> // Author: sun gaochao 2010.06.17 <br/> //************************ * ******************* <br/> void csqliterecordset:: nextrow () <br/>{< br/> int nret = sqlite3_step (m_pvm); <br/> If (nret = sqlite_done) <br/>{< br/> // The flag position at the end of the last line is 1 <br/> iseof = true; <br/>}< br/> else if (nret = sqlite_row) <br/>{< br/> // The Position of the flag at the end of a row is 0 <br/> iseof = Fal Se; <br/>}< br/> else <br/>{< br/> nret = sqlite3_finalize (m_pvm ); // execute an error to release the record set pointer <br/> m_pvm = 0; <br/> throw cmyerror (sqlite3_errmsg (m_pdb )); // throw an exception <br/>}< br/> //******************** * *********************** <br/> // function functions: release record set pointer <br/> // parameter: <br/> // return value: <br/> // Author: sun gaochao 2010.06.17 <br/> //************************ * ******************* <br/> void csqliterecordset:: Finalize () <br/>{< Br/> If (m_pvm) // records set pointer <br/>{< br/> int nret = sqlite3_finalize (m_pvm ); // release <br/> m_pvm = 0; <br/> If (nret! = Sqlite_ OK) // If execution fails, an exception is thrown. <br/>{< br/> throw cmyerror (sqlite3_errmsg (m_pdb )); <br/>}< br/> //******************* * *********************** <br/> // function Function: close the database <br/> // parameter: <br/> // return value: <br/> // Author: sun gaochao 2010.06.17 <br/> //************************ * ******************* <br/> void csqlite:: closedb () <br/>{< br/> If (sqlitedb) // The data is on <br/>{< br/> int nret = sqlite3_close (sqlitedb ); // Close the database </P> <p> If (nret! = Sqlite_ OK) <br/>{< br/> throw cmyerror (sqlite3_errmsg (sqlitedb); <br/>}</P> <p> sqlitedb = 0; // set 0 <br/>}< br/> //******************** * *********************** <br/> // function functions: return to the first line of the record set <br/> // parameter: <br/> // return value: <br/> // Author: sun gaochao 2010.06.17 <br/> //************************ * ******************* <br/> void csqliterecordset:: movefrist () <br/>{< br/> If (m_pvm) <br/>{< br/> int nret = sqlite3 _ Reset (m_pvm); // reset record set <br/> nextrow (); // call nextrow to execute sqlite3_step to generate a record <br/> iseof = false; // The tail flag is cleared. <br/> If (nret! = Sqlite_ OK) <br/>{< br/> throw cmyerror (sqlite3_errmsg (m_pdb); <br/>}< br/> <br/>