Example c prog
- gcc -o simplesqlite3 simplesqlite3.c -Wall -W -O2 -Wl,-R/usr/local/lib -lsqlite3
- could only get source to compile with given compiler flags on linux.
command line usage
- sqlite3 mydata.db
- or use SQL command attach interactively
- -line = execute one-off SQL statement
- sqlite3 -line mydata.db ’select * from memos where priority > 20;’
- -html = Query results will be output as simple HTML tables.
- -init file = Read and execute commands from file , which can contain a mix of SQL statements and meta-commands.
interactive usage
- .quit = .exit = GTFO
- .database = show list of databases
- .table = show list of tables
- .dump = show a dump of the whole db
- .schema <table_name> = show the table create statement
Trigger Behavior
-- ********************************************************************
-- Creating a trigger for timeEnter
-- Run as follows:
-- $ sqlite3 test.db < trigger1
-- ********************************************************************
CREATE TRIGGER functionname AFTER INSERT ON table_name
BEGIN
UPDATE table_name SET certain_row = DATETIME('NOW') WHERE rowid = new.rowid;
END;
-- ********************************************************************
C++ API Calls
int sqlite3_open_v2(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb, /* OUT: SQLite db handle */
int flags, /* Flags */
const char *zVfs /* Name of VFS module (OS Interface Object) to use */
);
- prepare statement - To execute an SQL query, it must first be compiled into a byte-code program
- only use the _v2 call; the non v2 is retained for backward compatibility.
int sqlite3_prepare_v2(
sqlite3 *db, /* Database handle */
const char *zSql, /* SQL statement, UTF-8 encoded */
int nByte, /* Maximum length of zSql in bytes. Just use -1 */
sqlite3_stmt **ppStmt, /* OUT: Statement handle - what gets passed to sqlite3_step()*/
const char **pzTail /* OUT: Pointer to unused portion of zSql, Just use NULL*/
);
int sqlite3_busy_handler(
sqlite3*, // Pointer to db connect
int(*)(void*,int), // Function pointer to callback function
void* // Some kind of string
);
- This routine sets a callback function that might be invoked whenever an attempt is made to open a database table that another thread or process has locked.
- This function is good for defining what happens when a busy connection is returned, like outputting some busy message. If you don't care, and all you want to do is just have the connection wait, better to use sqlite3_busy_timeout(..)
- sqlite3_mprintf - enhanced sprintf function with added SQL formatting options.
- sqlite_exec
int sqlite3_exec(
sqlite3*, /* An open database */
const char *sql, /* SQL to be evaluated */
int (*callback)(void*,int,char**,char**), /* Callback function */
void *, /* 1st argument to callback */
char **errmsg /* Error msg written here */
);
Sequence of Events
- The first argument, "db", is a database connection obtained from a prior successful call to sqlite3_open(), sqlite3_open_v2(), etc.
- Tell SQLITE what to do if a connection is busy using sqlite_busy_handler(), or more simply sqlite_busy_timeout()
- Create the object using sqlite3_prepare_v2() or a related function. Here you set up the syntax of the SQL statement, and put tokens into the string that will substitute or "bind" data into that string
- Bind values to host parameters using the sqlite3_bind_*() interfaces.
- Run the SQL by calling sqlite3_step() one or more times.
- Here's where the result codes are returned, like SQLITE_BUSY, SQLITE_DONE
- Use the column access functions to the values returned by statement executed.
- Reset the statement using sqlite3_reset() then go back to before the bind step. Do this zero or more times.
- Destroy the object using sqlite3_finalize().
File Locking and Concurrency
- Handled by the "pager" module, which handles multiple threads, processes, or both.
- Wrap the SQL statements with BEGIN TRANSACTION; ... END TRANSACTION;