SQLite Data manipulation

SQLite, a lightweight database, is an associative database management system that adheres to acid, and the SQLite engine is not a separate process for communicating with it, but rather a major part of the program, and its main communication protocol is the direct API call within the programming language. This has a positive effect on total consumption, delay time, and overall simplicity.
1.Qt Operating SQLite Database
QT provides access to a database interface that is independent of the platform and the type of database, with support types and descriptions:
Driver Type Description
QDB2 IBM DB2
Qibase Borland InterBase Driver
Qmysql MySQL Driver
Qoci Oracle Call Interface Driver
QODBC ODBC Driver (includes Microsoft SQL Server)
Qpsql PostgreSQL Driver
Qsqlite SQLite version 3 or above
QSQLITE2 SQLite Version 2
Qtds Sybase Adaptive Server
Attention points involved in operation:
<1> Create QT project, database placement with project in the same directory, add header file "include <QtSql>", QT project file (Xxx.pro) add involved library "QT +=sql";
<2>. SQLite Database main operation
Qsqldatabase db = Qsqldatabase::adddatabase ("Qsqlite"); Declaring a database type
Db.setdatabasename ("demo.db"); Association database
Db.open (); Connect Open Database "Only physical connections to the database are activated through open, otherwise not available"
Qsqlquery sqlquery (DB); Database operations
Sqlquery.exec ("SELECT * from data table"); Perform SQL-related operations with exec
Db.close (); Run out, close the database
<3> simple example
#include <QtCore/QCoreApplication>
#include <QtSql>
#include <qDebug>

int main (int argc,char *argv[])
{
    qapplication app (ARGC,ARGV);
     Qsqldatabase db = Qsqldatabase::adddatabase ("Qsqlite");
    db.setdatabasename ((char *) "demo.db");
    //db.setusername ("User name");
    //db.setpassword ("password");
    if (!db.open ())
    {
        //connection to database failed
        return-1;
    }

    qsqlquery SQLQuery;
    //Insert Data
    sqlquery.prepare ("INSERT into employee (ID, name, salary) VALUES (: ID,: Name,: Salary) ");
    bool binsert = sqlquery.exec ("INSERT into employee (ID, name, salary) VALUES (1001, ' Zhang San ', 6500)";
    //sqlquery.bindvalue (": id", 1001);
    //sqlquery.bindvalue (": Name", "Zhang San");
    //sqlquery.bindvalue (": Salary", "6500");
    //bool binsert = Sqlquery.exec ();
    if (!binsert)
    {
        //Insert data failed
& nbsp   }

Querying data
Sqlquery.exec ("SELECT * from employee");
while (Sqlquery.next ())
{
int id = sqlquery.value (0). ToInt ();
QString name = Sqlquery.value (1). ToString ();
QString salary = Sqlquery.value (2). toString ();
Get Related data processing
}

Update data
Sqlquery.exec ("update employee set salary= \" 7000\ "where id=\" 1001\ "");

App.exec ();
return 0;
}

2.c/c++ Interface Operation SQLite Database
SQLite itself provides a corresponding API function interface for C + + program invocation, mainly: Sqlite3_open (), Sqlite3_exec () and Sqlite3_close ().
<1> core objects
The main core objects in SQLite are: Database_connection and prepared_statement
The Database_connection object is created and returned by the Sqlite3_open () interface function, which must be called before the application uses any other SQLite interface functions to obtain the Database_connnection object. In other subsequent calls, the object is required as an input parameter to complete the corresponding work;
Prepare_statement can treat it as a compiled SQL statement, as an interface parameter involving operation invocation;

<2> Core interface
Sqlite3_open is the entry function that operates the SQLite database, which returns the Database_connection object as a handle parameter for other interfaces to invoke;

Sqlite3_prepare converts the related SQL statement to the Prepared_statement object and returns the object pointer after the function executes, which is equivalent to an operation initialization preparation;

Sqlite3_step is used to evaluate the Prepared_statement object returned by the Sqlite3_prepare function, executing the data header that points to the result of the object's internal pointer, and for DML statements such as Insert/update/delete. The function only needs to execute once, the other need to access other data rows, need to call repeatedly;

Sqlite3_column gets the data for the specified column of the current row, which is done by the relevant interface:
Sqlite3_column_blob
Sqlite3_column_bytes
Sqlite3_column_bytes16
Sqlite3_column_double
Sqlite3_column_int
Sqlite3_column_int64
Sqlite3_column_text
Sqlite3_column_text16
Sqlite3_column_type
Sqlite3_column_value
Sqlite3_column_count
Where the Sqlite3_column_count function is used to obtain the field data in the current result set;

Sqlite3_finalize is used to destroy prepared statement objects to avoid memory leaks;

Sqlite3_close is used to close previously opened Database_connection objects;

<3> simple example
#include <stdio.h>
#include <sqlite3.h>
int main ()
{
int iRet;
Sqlite3 *db=null;
IRet = Sqlite3_open ("demo.db", &db)
if (SQLITE_OK! = IRet)
{
Failed to open database
return-1;
}

Char *errmsg;
Sqlite3_exec: Parameter 1 (database pointer)/parameter 2 (SQL statement)/Parameter 3 (callback function)/Parameter 4 (callback function parameter)/parameter 5 (return error message)
IRet = sqlite3_exec (db, "INSERT into employee (ID, name, salary) VALUES (1001,\" Zhang San \ ", \" 6500\ ")", NULL, NULL, &ERRMSG);
if (SQLITE_OK! = IRet)
{
Failed to insert data
}

int Sqlite3_prepare (
Sqlite3 *db,/* Database handle */
const char *zsql,/* SQL statement, UTF-8 encoded */
int Nbyte,/* Maximum length of zsql in bytes. */
Sqlite3_stmt **ppstmt,/* out:statement handle */
const char **pztail/* out:pointer to unused portion of Zsql */
// );
Sqlite3_stmt *ppstmt;
IRet = Sqlite3_prepare (db, "Select Id,name,salary from Employee",-1, &ppstmt, NULL);
if (SQLITE_OK! = IRet)
{
Failed to read data
}
Else
{
while (Sqlite3_step (ppstmt) = = Sqlite_row)
{
printf ("ID:%s\t", Sqlite3_column_text (ppstmt, 0));
printf ("Name:%s\t", Sqlite3_column_text (ppstmt, 1));
printf ("Salary:%s\n", Sqlite3_column_text (ppstmt, 2));
}
Sqlite3_finalize (PPSTMT);
}

Sqlite3_close (DB);
return 0;
}

SQLite Data manipulation