Preliminary understanding and use of SQLite3 database Native C ++ encapsulation class (Unicode) CppSQLite3U

SQLite3 database Native
C ++ encapsulation class (Unicode) CppSQLite3U preliminary understanding and use by oblique drizzle QQ: 253786989 2012-02-12

(1)

Http://www.sqlite.org/cvstrac/wiki? P = SqliteWrappers

From the above URL, we can find the encapsulation of the c api of the SQLite database in various languages. Including c, c ++, vb, and c #. net, delphi, Lisp, D, Java, Javascript, Objective-C, Perl, PHP, Python, Ruby, Lua, and Fortran. From this we can see that the SQLite database is widely used as a local data storage tool. C ++
There are also dozens of Wrappers, such as easySQLite, SQLite ++, CppSQLite, CppSQLiteU, and so on. Because I usually develop apps on Windows CE embedded operating systems, while Windows
The CE operating system only supports the Unicode Character Set. Therefore, we need to learn and test the Unicode version "C ++ Wrappers": CppSQLiteU.

(2)

Because SQLite is used on the Windows CE operating system, before using CppSQLiteU, you must compile
SQLite3 DLL of CE platform. This step is saved, and I directly borrowed the one compiled by someone else.

(3)

The following is a simple test example. It mainly uses class cppsqlitedb to create a database, create a table, insert and delete update records, use transactions, and query records.

# Define SQLITE3_FILE_NAME TEXT ("sqlite. db3 ") // obtain the current program path void GetCurrentDirectory (CString & szPath) {TCHAR buf [256] = {0}; GetModuleFileName (NULL, buf, sizeof (buf) /sizeof (TCHAR); szPath = buf; szPath = szPath. left (szPath. reverseFind ('\') + 1);} CString strDbPath; GetCurrentDirectory (strDbPath); strDbPath + = SQLITE3_FILE_NAME; cppsqlitedb; try {// open or create a database db. open (strDbPath); // determines whether the table name already exists if (! Db. tableExists (TEXT ("MERs") {// does NOT exist. CREATE the Customers db.exe cDML (TEXT ("create table Customers (cust_name varchar (50) not null primary key, cust_address varchar (50); ");} // INSERT 1 record db.exe cDML (TEXT (" insert into MERs VALUES ('village Toys ', '1970 Maple Lane '); "); // INSERT 1 record db.exe cDML (TEXT (" insert into MERs VALUES ('kids place ', '2014 South Lake Drive '); "); // DELETE 1 record db.exe cDML (TEXT (" DELETE FROM MERs WHERE cust_name = 'village Toys '; "); // use the display transaction to insert 10 records TCHAR buf [256] = {0 }; db.exe cDML (TEXT ("begin transaction;"); for (int I = 0; I <10; ++ I) {memset (buf, 0, sizeof (buf); wsprintf (buf, TEXT ("insert into MERs VALUES ('fun % dall', '% d Sunny Place');"), I, I ); db.exe cDML (buf);} db.exe cDML (TEXT ("commit transaction;"); // UPDATE db.exe cDML (TEXT ("UPDATE Customers SET cust_add Ress = '2014 53rd Street 'WHERE cust_name = 'fun0all'; "); // get the total number of records int nCount = db.exe cScalar (TEXT (" select count (*) FROM MERs MERS; "); TCHAR szCount [50] = {0}; memset (szCount, 0, sizeof (szCount); wsprintf (szCount, TEXT (" Record count: % d. "), nCount); AfxMessageBox (szCount); // obtain each record CppSQLite3Query q = db.exe cQuery (TEXT (" SELECT * FROM MERs MERS; "); while (! Q. eof () {AfxMessageBox (q. fieldValue (0); q. nextRow ();} // destroy statement q. finalize (); // closes the database. close (); AfxMessageBox (TEXT ("test completed! ");} Catch (CppSQLite3Exception ex) {AfxMessageBox (ex. errorMessage ());}

(4)

CppSQLite3U encapsulates four classes: CppSQLite3Exception, cppsqlitedb, CppSQLite3Statement, and CppSQLite3Query.

A)CppSQLite3Exception is used to catch exceptions. errorCode Returns Error Codes in integer form, and errorMessage Returns Error Codes in Unicode string type.

class CppSQLite3Exception{public:    … …    const int errorCode() { return mnErrCode; }    LPCTSTR errorMessage() { return mpszErrMess; }    static LPCTSTR  errorCodeAsString(int nErrCode);    … …};

The common usage is as follows:

try{    … …    }catch(CppSQLite3Exception ex){    AfxMessageBox(ex.errorMessage());}

B)Cppsqlitedb is used to create a database, open and close the database connection, execute DML, DDL, and retrieve data. For example, open the database connection, close the database connection, tableExists checks whether a table exists, execDML executes the SQL command, execQuery retrieves records, and setBusyTimeout sets busy in SQLite.
The timeout time of handler. SQLiteVersion returns the SQLite version.

class CppSQLite3DB {public:        … …    void open(LPCTSTR szFile);    void close();    bool tableExists(LPCTSTR szTable);    int execDML(LPCTSTR szSQL);    CppSQLite3Query execQuery(LPCTSTR szSQL);    int execScalar(LPCTSTR szSQL);    CString execScalarStr(LPCTSTR szSQL);    CppSQLite3Statement compileStatement(LPCTSTR szSQL);    sqlite_int64 lastRowId();    void interrupt() { sqlite3_interrupt(mpDB); }        void setBusyTimeout(int nMillisecs);    static const char* SQLiteVersion() { return SQLITE_VERSION; }    … …};

C)CppSQLite3Statement can also execute SQL commands. The biggest feature of CppSQLite3Statement is that it supports parameter binding. For the usefulness of parameter binding, refer to "C Programming Interface of SQlite database (IV)
Bound Parameters: learning about Using SQlite Reading Notes. In the interface functions exported by this class, bind is used to bind the specified value to the parameters in the SQL statement. The reset function is used to reset the SQL statement, and finalize is used to destroy the statement.

class CppSQLite3Statement{public:    … …    CppSQLite3Statement& operator=(const CppSQLite3Statement& rStatement);    int execDML();    CppSQLite3Query execQuery();    void bind(int nParam, LPCTSTR szValue);    void bind(int nParam, const int nValue);    void bind(int nParam, const double dwValue);    void bind(int nParam, const unsigned char* blobValue, int nLen);    void bindNull(int nParam);    void reset();    void finalize();    … …};

Usage example:

try{    CppSQLite3DB db;    db.execDML("CREATE TABLE emp(empno int, empname char(20));");    db.execDML("BEGIN TRANSACTION;");    CppSQLite3Statement stmt = db.compileStatement("INSERT INTO emp VALUES (:empno, : empname);");    for (i = 0; i < nRowsToCreate; ++i)    {        char buf[16];        sprintf(buf, "EmpName%06d", i);        stmt.bind(":empno", i);        stmt.bind(":empname ", buf);        stmt.execDML();        stmt.reset();    }    db.execDML("COMMIT TRANSACTION; ");}catch (CppSQLite3Exception& e){    cerr << e.errorCode() << ":" << e.errorMessage() << endl;}

D)CppSQLite3Query is used to retrieve records and read the results. Most of the public interface functions it exports are SQLite3 C
Encapsulation of the API _ sqlite3_column_xxx function, which reads a column in a row in the result set. The nextRow function is used to retrieve the next row. The eof function is used to determine whether the result set ends.

class CppSQLite3Query{public:    … ….    int numFields();    int fieldIndex(LPCTSTR szField);    LPCTSTR fieldName(int nCol);    LPCTSTR fieldDeclType(int nCol);    int fieldDataType(int nCol);    LPCTSTR fieldValue(int nField);    LPCTSTR fieldValue(LPCTSTR szField);    int getIntField(int nField, int nNullValue=0);    int getIntField(LPCTSTR szField, int nNullValue=0);    double getFloatField(int nField, double fNullValue=0.0);    double getFloatField(LPCTSTR szField, double fNullValue=0.0);    LPCTSTR getStringField(int nField, LPCTSTR szNullValue=_T(""));    LPCTSTR getStringField(LPCTSTR szField, LPCTSTR szNullValue=_T(""));    const unsigned char* getBlobField(int nField, int& nLen);    const unsigned char* getBlobField(LPCTSTR szField, int& nLen);    bool fieldIsNull(int nField);    bool fieldIsNull(LPCTSTR szField);    bool eof();    void nextRow();    void finalize();    … … };

SQLite3 database Native
C ++ encapsulation class (Unicode) CppSQLite3U preliminary understanding and use by oblique drizzle QQ: 253786989 2012-02-12