Links

SQLite Version 3 home page and links
C/C++ Interface for SQLite Version 3
Good SQL tutorial with on-line, corrected exercises
A session in the sqlite3 utility (link requires password).
Private notes on using SQLite.

SQLite return codes

      SQLITE_OK (0) means that the action completed successfully.
      SQLITE_DONE (101) means that the statement has finished executing successfully.
      SQLITE_BUSY (5) means that the database engine attempted to open a locked database and there is no busy callback registered (so, it can’t perform the action).
      SQLITE_ERROR (1) means that a run-time error, such as a constraint violation, has occurred.
      SQLITE_ROW (100) means, if the SQL statement being executed returns any data, that a new row of data is ready for processing by the caller, i.e.: this is for queries that return multiple times.

SQLite browser details

Use the browser on the path /opt/quest/libexec/vas/sqlite3 because, for some time, it’s older than the latest version generally available and installed on any given host (VAS currently uses version 3.0.8).

A sample session in the browser is worth a thousand words. Some answers are in SQL statement form; SQL statements can be tried directly in the database to determine their worth.

	[email protected]:~> sudo /opt/quest/libexec/vas/sqlite3 /var/opt/quest/vas/vasypd/nismaps/rfc2307_nismaps.vdb
	SQLite version 3.0.8
	Enter ".help" for instructions
	sqlite> .tables
	adnismaps      nis2map     nismap_config    vas2307_xlate    vasypinfo
	sqlite> .schema nismap_config
	CREATE TABLE nismap_config ( typename TEXT UNIQUE, searchbase TEXT, mapname TEXT );
	sqlite> .exit
	[email protected]:~>

Error values in the header...

	#define SQLITE_OK           0   // Successful result
	#define SQLITE_ERROR        1   // SQL error or missing database
	#define SQLITE_INTERNAL     2   // An 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   // (Internal Only) Table or record not found
	#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   // Too much data for one row of a table
	#define SQLITE_CONSTRAINT  19   // Abort due to contraint 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

squlite3_- calls consumed in VAS (crudely compiled)...

	sqlite3_aggregate_context           sqlite3_aggregate_count
	sqlite3_always_code_trigger_setup   sqlite3_bind
	sqlite3_bind_blob                   sqlite3_bind_double
	sqlite3_bind_int                    sqlite3_bind_int64
	sqlite3_bind_null                   sqlite3_bind_parameter_count
	sqlite3_bind_parameter_index        sqlite3_bind_parameter_name
	sqlite3_bind_text                   sqlite3_bind_text16
	sqlite3_btree_trace                 sqlite3_busy_handler
	sqlite3_busy_timeout                sqlite3_changes
	sqlite3_close                       sqlite3_collation_needed
	sqlite3_collation_needed16          sqlite3_column_blob
	sqlite3_column_bytes                sqlite3_column_bytes16
	sqlite3_column_count                sqlite3_column_decltype
	sqlite3_column_decltype16           sqlite3_column_double
	sqlite3_column_int                  sqlite3_column_int64
	sqlite3_column_name                 sqlite3_column_name16
	sqlite3_column_text                 sqlite3_column_type
	sqlite3_commit_hook                 sqlite3_compile
	sqlite3_complete                    sqlite3_complete16
	sqlite3_crashparams                 sqlite3_create_aggregate
	sqlite3_create_collation            sqlite3_create_collation16
	sqlite3_create_function             sqlite3_current_time
	sqlite3_data_count                  sqlite3_diskfull_pending
	sqlite3_errcode                     sqlite3_errmsg
	sqlite3_exec                        sqlite3_exec_printf
	sqlite3_finalize                    sqlite3_free
	sqlite3_free_table                  sqlite3_get_auxdata
	sqlite3_get_table                   sqlite3_get_table_cb
	sqlite3_get_table_printf            sqlite3_iMallocFail
	sqlite3_interrupt                   sqlite3_interrupt_count
	sqlite3_io_error_pending            sqlite3_key
	sqlite3_last_insert_rowid           sqlite3_libversion
	sqlite3_malloc_failed               sqlite3_mprintf
	sqlite3_mprintf_double              sqlite3_mprintf_int
	sqlite3_mprintf_int64               sqlite3_mprintf_scaled
	sqlite3_mprintf_str                 sqlite3_mprintf_stronly
	sqlite3_mprintf_z_test              sqlite3_nFree
	sqlite3_nMalloc                     sqlite3_open
	sqlite3_open16                      sqlite3_open_file_count
	sqlite3_os_trace                    sqlite3_prepare
	sqlite3_prepare16                   sqlite3_progress_handler
	sqlite3_rekey                       sqlite3_reset
	sqlite3_result_blob                 sqlite3_result_double
	sqlite3_result_error                sqlite3_result_int
	sqlite3_result_int64                sqlite3_result_null
	sqlite3_result_text                 sqlite3_result_text16
	sqlite3_result_text16be             sqlite3_result_text16le
	sqlite3_result_value                sqlite3_search_count
	sqlite3_set_authorizer              sqlite3_set_auxdata
	sqlite3_set_result_double           sqlite3_snprintf
	sqlite3_step                        sqlite3_temp_directory
	sqlite3_test_errstr                 sqlite3_total_changes
	sqlite3_trace                       sqlite3_user_data
	sqlite3_value                       sqlite3_value_blob
	sqlite3_value_bytes                 sqlite3_value_bytes16
	sqlite3_value_double                sqlite3_value_double_func
	sqlite3_value_int                   sqlite3_value_int64_func
	sqlite3_value_text                  sqlite3_value_text16
	sqlite3_value_text16be_func         sqlite3_value_text16_func
	sqlite3_value_text16le_func         sqlite3_value_text_func
	sqlite3_value_type                  sqlite3_vdbe_addop_trace
	sqlite3_version                     sqlite3_vmprintf

	get_sqlite3_context

	sqlite3GetFuncCollSeq               sqlite3GetTransientValue
	sqlite3TextToPtr                    sqlite3ValueBytes
	sqlite3ValueFree                    sqlite3ValueNew
	sqlite3ValueText                    sqlite3VdbePrintOp
	sqliteMalloc

Some emerging notes for my own SQL page...

Statement notes...

UNIQUE just means that two rows cannot have the same index value. Putting UNIQUE on a column at table creation obviates creating an index separately when that index is nothing more than a reflection of the table itself:

	CREATE TABLE Employees ( lastname TEXT, firstname TEXT, address TEXT, age INT );
	CERATE UNIQUE INDEX PersonIndex ON Employees ( lastname );

In this case, lastname is the field or column to be indexed. As promised, the following single statement is identical, at least in SQLite3:

	CREATE TABLE Employees ( lastname TEXT UNIQUE, firstname TEXT, address TEXT, age INT );

Of course, in such a table as this table's name implies, enforcing lastname as unique would limit your ability to hire two employees with the same last name. Better would be this:

	CREATE TABLE Employees ( lastname TEXT, firstname TEXT, ssn varchar(9) UNIQUE, address TEXT );

Here's an example of a query:

	SELECT firstname FROM Employees WHERE age < 50;


Some SQLite things to watch out for...

If populating a large-ish table takes a long time, control it with a transaction. This will speed it up. See vastool check conf code.

Balance all successful calls to vassql_prepstmt with a call to vassql_stmt_free in order to avoid leaving a table locked.

If the database is noted (in the last error message of the database handle) as locked, be suspicious of subsequent errors that don’t seem to make sense such as an error back from vassql_prepstmtsqlite3_prepare of 1 with an sqlite3_errmsg to the effect that some column you know is valid doesn’t exist.