C + + packaged SQLite instance < three >

The previous blog describes how to get the contents of a table based on the sqlite3_get_table () function, which is to store all the contents of the table in a one-dimensional array. The rules have been introduced. What follows is how to select records that meet the selection criteria based on a SQL query statement. The selected records are not known in advance. You do n’t know how many rows are in the result of this query, and you do n’t know what the detailed record value in each row is, so there is no way to get the data with the given row and column values. It just doesn't work. If you want to flexibly manage the results returned by a query, this will be much more complicated, and there are many SQLite native functions used.

There are four fields in this CppSQLite3Query class, each is


sqlite3 * mpDB;
sqlite3_stmt * mpStmt;
bool mbEof; // Because the result of the query requires line by line access, set a bool value to indicate whether the end of the last line
int mnCols; // The number of columns in this query result, the number of rows is not known
The first parameter: query the database to be connected
The second parameter:


** CAPI3REF: SQL Statement Object
** KEYWORDS: {prepared statement} {prepared statements}
**
** An instance of this object represents a single SQL statement.
** This object is variously known as a "prepared statement" or a
** "compiled SQL statement" or simply as a "statement".
**
** The life of a statement object goes something like this:
**
** <ol>
** <li> Create the object using [sqlite3_prepare_v2 ()] or a related
** function.
** <li> Bind values to [host parameters] using the sqlite3_bind _ * ()
** interfaces.
** <li> Run the SQL by calling [sqlite3_step ()] one or more times.
** <li> Reset the statement using [sqlite3_reset ()] then go back
** to step 2. Do this zero or more times.
** <li> Destroy the object using [sqlite3_finalize ()].
** </ ol>
Is an SQL statement object, or a native SQL query statement select * from XXX; after SQLite processing generates a sqlite3_stmt object. In the future, you can do queries without using native statements. You can use this sqlite3_stmt * directly to query. When the query ends, you must release the sqlite3_stmt * pointer object.
The release function sqlite3_finalize () and sqlite3_free (); functions are similar, except that the latter releases the sqlite3 * object, while the former releases the sqlite3_stmt * object.

Function prototype SQLITE_API int sqlite3_finalize (sqlite3_stmt * pStmt);
Function description:

/ *
** CAPI3REF: Destroy A Prepared Statement Object
**
** ^ The sqlite3_finalize () function is called to delete a [prepared statement].
** ^ If the most recent evaluation of the statement encountered no errors
** or if the statement is never been evaluated, then sqlite3_finalize () returns
** SQLITE_OK. ^ If the most recent evaluation of statement S failed, then
** sqlite3_finalize (S) returns the appropriate [error code] or
** [extended error code].
**
** ^ The sqlite3_finalize (S) routine can be called at any point during
** the life cycle of [prepared statement] S:
** before statement S is ever evaluated, after
** one or more calls to [sqlite3_reset ()], or after any call
** to [sqlite3_step ()] regardless of whether or not the statement has
** completed execution.
**
** ^ Invoking sqlite3_finalize () on a NULL pointer is a harmless no-op.
**
** The application must finalize every [prepared statement] in order to avoid
** resource leaks. It is a grievous error for the application to try to use
** a prepared statement after it has been finalized. Any use of a prepared statement
** statement after it has been finalized can result in undefined and
** undesirable behavior such as segfaults and heap corruption.
* /

The function used to initialize the mnCols field is:

SQLITE_API int sqlite3_column_count (sqlite3_stmt * pStmt);
Function description:
/ *
** CAPI3REF: Number Of Columns In A Result Set
**
** ^ Return the number of columns in the result set returned by the
** [prepared statement]. ^ This routine returns 0 if pStmt is an SQL
** statement that does not return data (for example an [UPDATE]).
**
** See also: [sqlite3_data_count ()]
* /

The function used to obtain the column name of the current column based on the column index value:

Function prototype: SQLITE_API const char * sqlite3_column_name (sqlite3_stmt *, int N);


When inferring whether the data is empty, a function for detecting the data type is used:

Function prototype: SQLITE_API int sqlite3_column_type (sqlite3_stmt *, int iCol);
This function returns an integer type, where different values represent different defined macros, the following are various types:
#define SQLITE_INTEGER 1
#define SQLITE_FLOAT 2
#define SQLITE_BLOB 4
#define SQLITE_NULL 5
#ifdef SQLITE_TEXT
# undef SQLITE_TEXT
#else
# define SQLITE_TEXT 3
#endif
#define SQLITE3_TEXT 3
Another function to query data types

SQLITE_API const char * sqlite3_column_decltype (sqlite3_stmt *, int);
What he returns directly is the type name.

Various functions are used when obtaining various types of data, and they return values of different data types:

SQLITE_API const void * sqlite3_column_blob (sqlite3_stmt *, int iCol);
SQLITE_API int sqlite3_column_bytes (sqlite3_stmt *, int iCol);
SQLITE_API int sqlite3_column_bytes16 (sqlite3_stmt *, int iCol);
SQLITE_API double sqlite3_column_double (sqlite3_stmt *, int iCol);
SQLITE_API int sqlite3_column_int (sqlite3_stmt *, int iCol);
SQLITE_API sqlite3_int64 sqlite3_column_int64 (sqlite3_stmt *, int iCol);
SQLITE_API const unsigned char * sqlite3_column_text (sqlite3_stmt *, int iCol);
SQLITE_API const void * sqlite3_column_text16 (sqlite3_stmt *, int iCol);
SQLITE_API int sqlite3_column_type (sqlite3_stmt *, int iCol);
SQLITE_API sqlite3_value * sqlite3_column_value (sqlite3_stmt *, int iCol);
Some of the above functions may not appear in this encapsulated C ++ class.

Directly paste the following code:

class CppSQLite3Query
{
private:
sqlite3 * mpDB;
sqlite3_stmt * mpStmt;
bool mbEof;
int mnCols;

void CheckStmt ();
public:
CppSQLite3Query ();
CppSQLite3Query (sqlite3 * pdb, sqlite3_stmt * pStmt, bool bEof);
CppSQLite3Query (const CppSQLite3Query & rQuery);
CppSQLite3Query & operator = (const CppSQLite3Query & rQuery);
~ CppSQLite3Query ();

int FieldNums ();

int FieldIndex (const char * szField);
const char * FieldName (int nField);

int FieldDataType (int nField);
const char * FieldDeclType (int nField);

const char * FieldValue (int nField);
const char * FieldValue (const char * szField);

bool FieldIsNull (int nField);
bool FieldIsNull (const char * szField);

bool GetIntValue (int nField, int & rDest);
bool GetIntValue (const char * szField, int & rDest);

bool GetFloatValue (int nField, double & rDest);
bool GetFloatValue (const char * szField, double & rDest);

bool GetStringValue (int nField, char * & rDest);
bool GetStringValue (const char * szField, char * & rDest);

bool Eof ();

void NextRow ();

void Finalize ();
};
CppSQLite3Query :: CppSQLite3Query ()
{
mpDB = 0;
mpStmt = 0;
mnCols = 0;
mbEof = true;
}

CppSQLite3Query :: CppSQLite3Query (sqlite3 * pdb, sqlite3_stmt * pStmt, bool bEof)
{
mpDB = pdb;
mpStmt = pStmt;
mbEof = bEof;
mnCols = sqlite3_column_count (pStmt);
}

CppSQLite3Query :: CppSQLite3Query (const CppSQLite3Query & rQuery)
{
mpStmt = rQuery.mpStmt;
const_cast <CppSQLite3Query &> (rQuery) .mpStmt = 0;
mnCols = rQuery.mnCols;
mbEof = rQuery.mbEof;
}

CppSQLite3Query & CppSQLite3Query :: operator = (const CppSQLite3Query & rQuery)
{
mpStmt = rQuery.mpStmt;
const_cast <CppSQLite3Query &> (rQuery) .mpStmt = 0;
mnCols = rQuery.mnCols;
mbEof = rQuery.mbEof;

return * this;
}

CppSQLite3Query :: ~ CppSQLite3Query ()
{
Finalize ();
}

void CppSQLite3Query :: CheckStmt ()
{
if (mpStmt == 0)
{
throw CppSQLite3Exception (CPPSQLITE_ERROR,
"Invalid Stmt Pointer",
DONT_DELETE_MSG);
}
}

int CppSQLite3Query :: FieldNums ()
{
CheckStmt ();

return mnCols;
}

// Return the column index based on the field name
int CppSQLite3Query :: FieldIndex (const char * szField)
{
CheckStmt ();

if (szField)
{
for (int nField = 0; nField <mnCols; nField ++)
{
// There are a lot of similar functions behind, the parameters are almost the same, you need a sqlite3_stmt * and the column index value, this should be the result returned after the internal query, rather than saved
const char * szTemp = sqlite3_column_name (mpStmt, nField);
if (strcmp (szTemp, szField) == 0)
{
return nField;
}
}
}

throw CppSQLite3Exception (CPPSQLITE_ERROR,
"Invalid field name requested",
DONT_DELETE_MSG);
}
const char * CppSQLite3Query :: FieldName (int nField)
{
CheckStmt ();

if (nField <0 || nField> mnCols-1)
{
throw CppSQLite3Exception (CPPSQLITE_ERROR,
"Invalid field index requested",
DONT_DELETE_MSG);
}

return sqlite3_column_name (mpStmt, nField);
}

int CppSQLite3Query :: FieldDataType (int nField)
{
CheckStmt ();

if (nField <0 || nField> mnCols-1)
{
throw CppSQLite3Exception (CPPSQLITE_ERROR,
"Invalid field index requested",
DONT_DELETE_MSG);
}

return sqlite3_column_type (mpStmt, nField);
}
const char * CppSQLite3Query :: FieldDeclType (int nField)
{
CheckStmt ();

if (nField <0 || nField> mnCols-1)
{
throw CppSQLite3Exception (CPPSQLITE_ERROR,
"Invalid field index requested",
DONT_DELETE_MSG);
}

return sqlite3_column_decltype (mpStmt, nField);
}

const char * CppSQLite3Query :: FieldValue (int nField)
{
CheckStmt ();

if (nField <0 || nField> mnCols-1)
{
throw CppSQLite3Exception (CPPSQLITE_ERROR,
"Invalid field index requested",
DONT_DELETE_MSG);
}

return (const char *) sqlite3_column_text (mpStmt, nField);

}

const char * CppSQLite3Query :: FieldValue (const char * szField)
{
int nField = FieldIndex (szField);
return FieldValue (nField);
}

bool CppSQLite3Query :: FieldIsNull (int nField)
{
return (FieldDataType (nField) == SQLITE_NULL);
}

bool CppSQLite3Query :: FieldIsNull (const char * szField)
{
int nField = FieldIndex (szField);
return (FieldDataType (nField) == SQLITE_NULL);
}

bool CppSQLite3Query :: GetIntValue (int nField, int & rDest)
{
if (FieldDataType (nField) == SQLITE_NULL)
{
return false;
}
else
{
rDest = sqlite3_column_int (mpStmt, nField);
return true;
}
}

bool CppSQLite3Query :: GetIntValue (const char * szField, int & rDest)
{
int nField = FieldIndex (szField);
return GetIntValue (nField, rDest);
}

bool CppSQLite3Query :: GetFloatValue (int nField, double & rDest)
{
if (FieldDataType (nField) == SQLITE_NULL)
{
return false;
}
else
{
rDest = sqlite3_column_double (mpStmt, nField);
return true;
}
}
bool CppSQLite3Query :: GetFloatValue (const char * szField, double & rDest)
{
int nField = FieldIndex (szField);
return GetFloatValue (nField, rDest);
}

bool CppSQLite3Query :: GetStringValue (int nField, char * & rDest)
{
if (FieldDataType (nField) == SQLITE_NULL)
{
return false;
}
else
{
rDest = const_cast <char *> ((const char *) sqlite3_column_text (mpStmt, nField));
return true;
}
}
bool CppSQLite3Query :: GetStringValue (const char * szField, char * & rDest)
{
int nField = FieldIndex (szField);
return GetStringValue (nField, rDest);
}

bool CppSQLite3Query :: Eof ()
{
CheckStmt ();

return mbEof;
}

void CppSQLite3Query :: NextRow ()
{
CheckStmt ();

int nRet = sqlite3_step (mpStmt);

if (nRet == SQLITE_DONE)
{
// no rows
mbEof = true;
}
else if (nRet == SQLITE_ROW)
{
// more rows, nothing to do
}
else
{
nRet = sqlite3_finalize (mpStmt);
mpStmt = 0;
const char * szError = sqlite3_errmsg (mpDB);
throw CppSQLite3Exception (nRet, szError, DONT_DELETE_MSG);
}
}

void CppSQLite3Query :: Finalize ()
{
if (mpStmt)
{
int nRet = sqlite3_finalize (mpStmt);
mpStmt = 0;
if (nRet! = SQLITE_OK)
{
const char * szError = sqlite3_errmsg (mpDB);
throw CppSQLite3Exception(nRet, szError, DONT_DELETE_MSG);
}
}
}
C ++ package SQLite instance