SQLite C/C++ function interfaces
Summary
Exploring SQLite's C/C++ function interfaces begins with understanding its comprehensive set of error codes, such as SQLITE_OK and SQLITE_ERROR, which are crucial for robust application development. Key functions like sqlite3_open, sqlite3_close, and sqlite3_exec facilitate fundamental database operations, including opening or creating databases, executing SQL commands, and managing connections. A practical example demonstrates the workflow for initializing a database, creating a table, inserting records, and querying data. This includes using sqlite3_get_table to retrieve results and sqlite3_free_table for proper memory management after data retrieval.
#define SQLITE_OK 0 /* Successful result */
/* beginning-of-error-codes */
#define SQLITE_ERROR 1 /* SQL error or missing database */
#define SQLITE_INTERNAL 2 /* Internal logic error in SQLite */
#define SQLITE_PERM 3 /* Access permission denied */
#define SQLITE_ABORT 4 /* Callback routine requested an abort */
#define SQLITE_BUSY 5 /* The database file is locked */
#define SQLITE_LOCKED 6 /* A table in the database is locked */
#define SQLITE_NOMEM 7 /* A malloc() failed */
#define SQLITE_READONLY 8 /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT 9 /* Operation terminated by sqlite3_interrupt()*/
#define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT 11 /* The database disk image is malformed */
#define SQLITE_NOTFOUND 12 /* Unknown opcode in sqlite3_file_control() */
#define SQLITE_FULL 13 /* Insertion failed because database is full */
#define SQLITE_CANTOPEN 14 /* Unable to open the database file */
#define SQLITE_PROTOCOL 15 /* Database lock protocol error */
#define SQLITE_EMPTY 16 /* Database is empty */
#define SQLITE_SCHEMA 17 /* The database schema changed */
#define SQLITE_TOOBIG 18 /* String or BLOB exceeds size limit */
#define SQLITE_CONSTRAINT 19 /* Abort due to constraint violation */
#define SQLITE_MISMATCH 20 /* Data type mismatch */
#define SQLITE_MISUSE 21 /* Library used incorrectly */
#define SQLITE_NOLFS 22 /* Uses OS features not supported on host */
#define SQLITE_AUTH 23 /* Authorization denied */
#define SQLITE_FORMAT 24 /* Auxiliary database format error */
#define SQLITE_RANGE 25 /* 2nd parameter to sqlite3_bind out of range */
#define SQLITE_NOTADB 26 /* File opened that is not a database file */
#define SQLITE_ROW 100 /* sqlite3_step() has another row ready */
#define SQLITE_DONE 101 /* sqlite3_step() has finished executing */
Some frequently used functions:
1. int sqlite3_open(const char *filename, sqlite3 **ppDb);
This function is to open the database if it exists or create a new database if it doesn't exist.
2. int sqlite3_open16(const void*, sqlite3**);
Use UTF-16 encoding to process the database file name, otherwise it will process the database file name with UTF-8 encoding.
3. int sqlite3_close(sqlite3*);
Close database
4. int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);
Database query operation, the second parameter is the query to be executed.
One simple example:
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
int main (int argc, char ** argv)
{
int result = 0;
sqlite3* db = NULL;
char * errMsg = NULL;
char sql_cmd[200];
memset(sql_cmd, 0x00, sizeof(sql_cmd));
result = sqlite3_open("lester.db", &db);
printf("Hello Lesterk, result = %d\n", result);
char* sqlCreate = "create table LesterData2 (LesterId INTEGER, LesterName Integer);";
result = sqlite3_exec(db, sqlCreate, 0, 0, &errMsg);
printf("Create_Result = %d , Msg = %s \n", result, errMsg);
char* sqlInsert = "INSERT INTO \"LesterData2\" VALUES( 1, 2);";
result = sqlite3_exec(db, sqlInsert, 0, 0, &errMsg);
printf("Insert_Result = %d , Msg = %s \n", result, errMsg);
char* sql = "SELECT * FROM LesterData2";
int nrow = 1, ncolumn = 1;
char **azResult;
result = sqlite3_get_table( db, sql, &azResult, &nrow, &ncolumn, &errMsg);
printf("Select_Result = %d , Msg = %s \n", result, errMsg);
int i = 0;
printf( "row = %d, column=%d \n" , nrow , ncolumn );
printf( "\nThe result of querying is : \n" );
for( i = 0; i < ( nrow + 1 ) * ncolumn ; i++){
printf( "azResult[%d] = %s\n", i , azResult[i] );
}
sqlite3_free_table( azResult );
printf("Hello World\n, %d", result);
sqlite3_close(db);
return 0;
}
Reference : http://linger-yan.iteye.com/blog/1576254
No comment for this article.