SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL relational database management system (RDBMS). This tutorial covers the command line interface and the SQLite C/C++ database programming API.
SQLite comes with Linux: (example of package list on RHEL)
- sqlite
- sqlite-devel
- python-sqlite
Basic SQLite: Create database, create a database table and insert a database record.
[prompt]$ sqlite3 /tmp/bedrock.db
sqlite> .help
sqlite> CREATE TABLE employee (Name varchar(20),Dept varchar(20),jobTitle varchar(20));
sqlite> .schema employee
CREATE TABLE employee (Name varchar(20),Dept varchar(20),jobTitle varchar(20));
sqlite> INSERT INTO employee VALUES ('Fred Flinstone','Quarry Worker','Rock Digger');
sqlite> .exit
Populate a database from a SQL command file:
File: employeeFile.sql
INSERT INTO employee VALUES ('Wilma Flinstone','Finance','Analyst');
INSERT into employee values ('Barney Rubble','Sales','Neighbor');
INSERT INTO employee VALUES ('Betty Rubble','IT','Neighbor');
Load SQL file, execute a select and delete a record:
[prompt]$ sqlite3 /tmp/bedrock.db sqlite> .tables employee sqlite> .read employeeFile.sql sqlite> SELECT Name FROM employee WHERE dept='Sales'; Barney Rubble sqlite> SELECT * FROM employee; Fred Flinstone|Quarry Worker|Rock Digger Wilma Flinstone|Finance|Analyst Barney Rubble|Sales|Neighbor Betty Rubble|IT|Neighbor sqlite> DELETE FROM employee WHERE dept='Sales'; sqlite> .exit
Generate an ASCII file dump of the database:
[prompt]$ sqlite3 /tmp/bedrock.db
sqlite> .show
echo: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
width:
sqlite> .output /tmp/bedrock.sql
sqlite> .dump
sqlite> .exit
This generates the following file: /tmp/bedrock.sql
BEGIN TRANSACTION;
CREATE TABLE employee (Name varchar(20),Dept varchar(20),jobTitle varchar(20));
INSERT INTO "employee" VALUES('Fred Flinstone','Quarry Worker','Rock Digger');
INSERT INTO "employee" VALUES('Wilma Flinstone','Finance','Analyst');
INSERT INTO "employee" VALUES('Barney Rubble','Sales','Neighbor');
INSERT INTO "employee" VALUES('Betty Rubble','IT','Neighbor');
COMMIT;
Upgrading the SQLite database:
sqlite test.db .dump | sqlite3 testV3.db
SQLite Information:
Accessing SQLite with C/C++ API:
| Function | Description |
|---|---|
| sqlite3_open() | Opens specified database file. If the database file does not already exist, it is created. |
| sqlite3_close() | Closes a previously opened database file. |
| sqlite3_prepare_v2() | Prepares a SQL statement ready for execution. |
| sqlite3_step() | Executes a SQL statement previously prepared by the sqlite3_prepare_v2() function. |
| sqlite3_column_<type>() | Returns a data field from the results of a SQL retrieval operation where <type> is replaced by the data type of the data to be extracted (text, blob, bytes, int, int16 etc). |
| sqlite3_finalize() | Deletes a previously prepared SQL statement from memory. |
| sqlite3_exec() | Combines the functionality of sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() into a single function call. |
Database access example:
File: accessTableEmployee.cpp
#include <iostream>
#include <sqlite3.h>
#include <stdlib.h>
// g++ AccessTableEmployee.cpp -lsqlite3
using namespace std;
static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
int i;
cout << "Number of args= " << argc << endl;
for(i=0; i<argc; i++)
{
cout << azColName[i] << " = " << (argv[i] ? argv[i] : "NULL") << endl;
}
cout << endl;
return 0;
}
int main(int argc, char **argv)
{
sqlite3 *db; // Declare pointer to sqlite database structure
char *zErrMsg = 0;
// Open Database
int rc = sqlite3_open("/tmp/bedrock.db", &db);
if( rc )
{
cerr << "Can't open database: " << sqlite3_errmsg(db) << endl;
sqlite3_close(db);
exit(1);
}
// Insert data into database
const char *zSql = "INSERT INTO employee(Name, Dept, jobTitle) VALUES('Barney Rubble','Sales','Neighbor')";
sqlite3_stmt *ppStmt;
const char **pzTail;
if( sqlite3_prepare_v2(db, zSql, strlen(zSql)+1, &ppStmt, pzTail) != SQLITE_OK )
{
cerr << "db error: " << sqlite3_errmsg(db) << endl;
}
if(ppStmt)
{
sqlite3_step(ppStmt);
sqlite3_finalize(ppStmt);
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
}
else
{
cerr << "Error: ppStmt is NULL" << endl;
}
// Select from database
rc = sqlite3_exec(db,"select * from employee", callback, 0, &zErrMsg);
if( rc!=SQLITE_OK )
{
cerr << "SQL error: " << zErrMsg << endl;
sqlite3_free(zErrMsg);
}
// Close
sqlite3_close(db);
return 0;
}
Run: a.out
Number of args= 3 Name = Wilma Flinstone Dept = Finance jobTitle = Analyst Number of args= 3 Name = Betty Rubble Dept = IT jobTitle = Neighbor Number of args= 3 Name = Barney Rubble Dept = Sales jobTitle = Neighbor
Blobs: (binary objects eg images or C data structures)
Create a database table "btest" with one BLOB collumn called "MyData":[prompt]$ sqlite3 /tmp/bedrock.db sqlite> create table btest(ID INTEGER, MyData BLOB);
Insert a blob into the database. Select the blob using the sqlite3_exec() API call.
File: sqliteBlobExampleExec.cpp
#include <iostream>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include <time.h>
using namespace std;
// Store gmtime data structure as a blob
// See man pages for information on gmtime data structure: man gmtime
static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
struct tm blob;
for(int i=0; i<argc; i++)
{
if(!strcmp(azColName[i],"MyData"))
{ // Handle Blob data
memcpy(&blob, argv[i], sizeof(struct tm));
cout << "Year retrieved from blob: " << blob.tm_year+1900 << endl;
}
else
{ // All other database collumns
cout << azColName[i] << " = " << (argv[i] ? argv[i] : "NULL") << endl;
}
}
cout << endl;
return 0;
}
int main(int argc, char **argv)
{
sqlite3 *db;
char *zErrMsg = 0;
time_t tt = 0;
time_t now = time(&tt); // seconds since the Epoch
struct tm *blob = gmtime(&now); // Create the blob to store in the database
cout << "Year stored: " << blob->tm_year+1900 << endl;
cout << endl;
int rc = sqlite3_open("/tmp/bedrock.db", &db);
if( rc )
{
cerr << "Can't open database: " << sqlite3_errmsg(db) << endl;
exit(1);
}
// Insert blob data into database
const char *zSql = "INSERT INTO btest(ID, MyData) VALUES('1',?)";
sqlite3_stmt *ppStmt;
const char **pzTail;
if( sqlite3_prepare_v2(db, zSql, strlen(zSql)+1, &ppStmt, pzTail) != SQLITE_OK )
{
cerr << "db error: " << sqlite3_errmsg(db) << endl;
sqlite3_close(db);
exit(1);
}
if(ppStmt)
{
// For Blob collumn bind 1
sqlite3_bind_blob(ppStmt, 1, blob, sizeof(struct tm), SQLITE_TRANSIENT);
sqlite3_step(ppStmt);
sqlite3_finalize(ppStmt);
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
}
else
{
cerr << "Error: ppStmt is NULL" << endl;
sqlite3_close(db);
exit(1);
}
// Select rows from database
// Note: sqlite3_exec() does sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() into a single function call
rc = sqlite3_exec(db,"SELECT * FROM btest", callback, 0, &zErrMsg);
if( rc!=SQLITE_OK )
{
cerr << "SQL error: " << zErrMsg << endl;
sqlite3_free(zErrMsg);
sqlite3_close(db);
exit(1);
}
sqlite3_exec(db, "END", NULL, NULL, NULL);
// Close database
sqlite3_close(db);
return 0;
}
Run: ./a.out
Result:
Year stored: 2010 ID = 1 Year retrieved from blob: 2010
Insert a blob into the database. Select the blob using the sqlite3_step() API call.
File: sqliteBlobExample.cpp
#include <iostream>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include <time.h>
using namespace std;
// Store gmtime data structure as a blob
// See man pages for information on gmtime data structure: man gmtime
int main(int argc, char **argv)
{
sqlite3 *db;
sqlite3_stmt *ppStmt;
char *zErrMsg = 0;
const char *zSql = "INSERT INTO btest(ID, MyData) VALUES('1',?)";
time_t tt = 0;
time_t now = time(&tt); // seconds since the Epoch
struct tm *blob = gmtime(&now); // Create the blob to store in the database
cout << "Year stored: " << blob->tm_year+1900 << endl;
cout << endl;
int rc = sqlite3_open("/tmp/bedrock.db", &db);
if( rc )
{
cerr << "Can't open database: " << sqlite3_errmsg(db) << endl;
exit(1);
}
// Insert blob data into database
if( sqlite3_prepare_v2(db, zSql, -1, &ppStmt, NULL) != SQLITE_OK )
{
cerr << "db error: " << sqlite3_errmsg(db) << endl;
sqlite3_close(db);
exit(1);
}
if(ppStmt)
{
// For Blob collumn bind 1
sqlite3_bind_blob(ppStmt, 1, blob, sizeof(struct tm), SQLITE_TRANSIENT);
sqlite3_step(ppStmt);
sqlite3_finalize(ppStmt);
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
}
else
{
cerr << "Error: ppStmt is NULL" << endl;
sqlite3_close(db);
exit(1);
}
// Select rows from database
const char *zSqlSelect = "select * from btest";
if( sqlite3_prepare_v2(db, zSqlSelect, -1, &ppStmt, NULL) != SQLITE_OK )
{
cerr << "db error: " << sqlite3_errmsg(db) << endl;
sqlite3_close(db);
exit(1);
}
// Name of DB table
cout << sqlite3_column_table_name(ppStmt,0) << endl;
// For each row returned
while (sqlite3_step(ppStmt) == SQLITE_ROW)
{
// For each collumn
for(int jj=0; jj < sqlite3_column_count(ppStmt); jj++)
{
// Print collumn name
cout << sqlite3_column_name(ppStmt,jj) << " = ";
// Print collumn data
switch(sqlite3_column_type(ppStmt, jj))
{
case SQLITE_INTEGER: cout << sqlite3_column_int(ppStmt, jj) << endl;
break;
case SQLITE_FLOAT: cout << sqlite3_column_double(ppStmt, jj) << endl;
break;
case SQLITE_TEXT: cout << sqlite3_column_text(ppStmt, jj) << endl;
break;
case SQLITE_BLOB: cout << "BLOB " << endl;
cout << "Size of blob: " << sqlite3_column_bytes(ppStmt, jj) << endl;
struct tm *blobRetreived;
blobRetreived = (struct tm *) sqlite3_column_blob(ppStmt, jj);
cout << "Year retrieved from blob: " << blobRetreived->tm_year+1900 << endl;
break;
case SQLITE_NULL: cout << "NULL " << endl;
break;
default: cout << "default " << endl;
break;
}
}
}
sqlite3_finalize(ppStmt);
sqlite3_exec(db, "END", NULL, NULL, NULL);
// Close database
sqlite3_close(db);
return 0;
}
Run: ./a.out
Result:
Year stored: 2010 btest ID = 1 MyData = BLOB Size of blob: 44 Year retrieved from blob: 2010Notes:
- Calls to sqlite3_step() and sqlite3_finalize() clear out allocated memory returned by sqlite3_column_<type>().
Thus do not free memory returned by sqlite3_column_blob() or for any of the calls to sqlite3_column_<type>().
SQLite Database Admin Tools:
- SQLiteman - Qt, Cross platform
- SQLiteGui - SQLite browser GUI (C++/Qt)
- sqlite-manager - UIL Firefox extension
- SQLite Studio - cross platform, multi-language support, Tcl/Tk
- TkSQLite - Tcl/Tk
- SQL Db Manager - developed in Delphi-7
- wxSQLite - C+/wxWidgets (fr)
- SQLite Db Browser
- Web Admin Tools:
- SQLite Manager - PHP4/5
- Bazdig - PHP
- SQLiteWeb.sh - shell script CGI [cache]
- SQLite web
SQLite Software Development Links:
- sqlite-sdbc-driver - Open Office
- DB_Sqlite_Tools - PHP Pear package
- SQLiteDB - PHP class
- Perl Parse-Dia-SQL - Perl CPAN module
- Javascript API for SQLite
- Ruby API for SQLite
- knoda KDE driver library
Related SQLite Links:
Books:
 |
Using SQLite
by Jay A. Kreibich ISBN #0596521189, O'Reilly First edition. |
|
 |
The Definitive Guide to SQLite
by Mike Owens ISBN #1590596730, Apress First edition. |
|
 |
C++ How to Program
by Harvey M. Deitel, Paul J. Deitel ISBN #0131857576, Prentice Hall Fifth edition. The first edition of this book (and Professor Sheely at UTA) taught me to program C++. It is complete and covers all the nuances of the C++ language. It also has good code examples. Good for both learning and reference. |
|
