SQLite Verbs

• sqlite.clearQuery: Clear an SQLite query. New verb.
• sqlite.close: Close an SQLite database. New verb.
• sqlite.compileQuery: Compile an SQLite query, preparing it for execution. New verb.
• sqlite.easyQuery: Perform a query and retrieve the entire results table. New verb.
• sqlite.escapeQuotes: Substitute in-line single quotes with two single-quotes. New verb.
• sqlite.getColumn: Retrieve the value of the specified column element. New verb.
• sqlite.getColumnCount: Retrieve the number of columns returned from an SQLite query. New verb.
• sqlite.getColumnDouble: Retrieve the column value for a double field. New verb.
• sqlite.getColumnInt: Retrieve the column value for an integer field. New verb.
• sqlite.getColumnName: Retrieve the name of a column. New verb.
• sqlite.getColumnText: Retrieve the column value for a text field. New verb.
• sqlite.getColumnType: Retrieve the type of a column. New verb.
• sqlite.getErrorMessage: Returns the error message for the most recent SQLite API call. New verb.
• sqlite.getLastInsertRowId: Gets the row id of the last INSERT statement for database connection ID.

. New verb.

• sqlite.getRow: Retrieve an entire row from an SQLite query. New verb.
• sqlite.getRowColumnNames: Retrieve the column names from a row returned from an SQLite query. New verb.
• sqlite.getTableNames: Retrieve a list of table names in an SQLite database. New verb.
• sqlite.getTableSchema: Retrieve the schema for a named table in SQL format. New verb.
• sqlite.open: Open (or create) an SQLite database file. New verb.
• sqlite.resetQuery: Reset an SQLite query. New verb.
• sqlite.setColumnBlob: Sets the binary data for a column of a row. New verb.
• sqlite.stepQuery: Move forward a row in an SQLite query. New verb.
• sqlite.tableExists: Determine if the named table exists in the database. New verb.

As of 10/10/2006, SQLite is implemented within Frontier’s main branch, in Frontier version 10.1a11. – David Gewirtz

This section describes the Frontier-specific wrapper to the SQLite database engine. The following are some notes you should be aware of as you prepare to use SQLite within Frontier:

• SQLite is embedded within the Frontier kernel. There are no external libraries you need to use.
• The version of SQLite embedded within the Frontier Kernel is 3.3.7
• Frontier implements a “Frontierified” version of SQLite, designed initially to provide large data storage capabilities natively within Frontier. Not all of the SQLite API has been implemented.
• Function names have been “Frontierified” as well, to make them clearer for a Frontier developer. For example, the sqlite3_prepare function has been named sqlite.compileQuery in Frontier. The sqlite3_finalize function has been named sqlite.clearQuery in Frontier, and so forth. This has also been done to indicate that Frontier calls to SQLite are not direct one-to-one analogs to the SQLite API. Some adjustment to the data handling and operation has been done within Frontier.
• Frontier supports a data interface for text, integer, and floating point numbers. Blob support has not been implemented.
• SQLite operates on a base-0 structure, meaning that the first item in a given sequence is always considered 0. By contrast, Frontier operates on a base-1 structure, meaning that the first element in a Frontier sequence is always a 1. The Frontier wrapper to SQLite implements access as a base-1 structure. If you want the first column, you’ll specify column 1, not 0. Internally, Frontier converts from its own base-1 to base-0. This can be confusing if you’re reading SQLite documentation, so simply be aware that whenever SQLite talks about an offset, SQLite begins with 0 and Frontier with 1.
• SQLite supports multiple SQL statements in SQL queries. The current Frontier implementation does not. For each query, Frontier’s wrapper to SQL will simply query based on the first statement in the SQL provided.
• SQLite provides three different ways to perform queries. The Frontier wrapper implements only one of them. In the Frontier implementation, you’ll need to first compile a query, then step through your query results row-by-row, and then, finally, clear the query from memory. Obviously for SQL calls that only return one line of data or no data at all, you’ll only need to step through one row of query results to be sure the query executes. Frontier does implement sqlite.easyQuery, a script-based interface to the SQLite query engine that does everything in one call.
• SQLite (and all SQL, for that matter) is very fussy about characters and character formatting. One utility function, sqlite.escapeQuotes has been provided to help with this, but you’ll need to be very careful about how you send strings into the SQLite interface and how you process them when they come back out. Pay particular attention to single and double quotes, and to the Frontier escape character, the backslash (\).
• Although SQLite has a series of Unicode-smart functions, the Frontier SQLite wrapper does not. Frontier’s SQLite wrapper implements simple, seven-bit character values. Anything more may screw things up, so, again, use care.
• While SQLite is reasonably thread-aware, it’s generally not good to pass database IDs across threads. See multi-threading for details.
• The Frontier implementation of the SQLite wrapper requires you to do more management than is typical in Frontier. In particular, you’ll need to keep track of database IDs and be sure to close them when you exit Frontier and you’ll need to keep track of query IDs and be sure to clear them when you’re done or exit Frontier. Frontier will not implicitly do any SQLite garbage collection.
• The Frontier implementation of the SQLite wrapper works on OSX as well as Windows, but the Mac implementation is a bit convoluted. SQLite requires POSIX-based filenames passed to sqlite.open, so you’re likely to have to do some string conversion to make it work.

• SQLite home page
• The Definitive Guide to SQLite: Excellent book about SQLite that was instrumental in helping build the Frontier SQLite wrapper
• There are a bunch of great GUI programs for manipulating SQLite databases. I’ve used SQLiteSpy and SQLite Expert extensively in the development of the SQLite Frontier wrappers.