Win32Forth

SQLite -- Database Class for SQLite

Tom Dixon

This class provides an interface to SQLite databases.

SQLite Behavior

SQLite operates on database files. Files are opened as read-only, if no update occurs, and can be shared through several applications. Writes cause a block until the file can be opened as read/write. This makes the locking scheme very efficient and easy to work with.

SQLite is very fast, flexible, and simple. One very nice feature is that SQLite will convert types as best as it can for you if you want a type different from the native database type.

For more information about SQLite, please see www.sqlite.org

:class SQLiteDB               <SUPER Object

SQLiteDB is an interface to SQLite.

:M ERR: ( -- ) \ thows an error on any problem

Displays any error that might have occured.

:M Open: ( str len -- )

Opens a database file so we can execute operations on it.
If the string is ":memory:" the database is actually created in memory instead of on disk, and should be faster.

:M Close: ( -- flag )

Closes the database. You can still open another database with the same object after closing, if desired.

:M Version: ( -- str len )

Returns the version of SQLite being used

:M Execute: ( str len -- )

Execute a SQL query on the cursor. Any returned data will be in the cursor.

:M Requery: ( -- )

Rerun the last query.

:M FieldCnt: ( -- n )

Returns the number of columns in the current record.

:M FieldType: ( field -- DataTypeEnum )

Returns the data type constant of the given column. Possible data types are:

SQLITE_INTEGER

SQLITE_FLOAT

SQLITE_TEXT

SQLITE_BLOB

SQLITE_NULL

:M FieldName: ( field -- str len )

Returns the column name of the given column number.

:M GetInt: ( field -- int )

Returns an integer value of the given column on the current row.

:M GetDouble: ( field -- d )

Returns the double of the given column on the current row.

:M GetFloat: ( field -- float )

Returns the floating point value of the given column on the current row.

:M GetStr: ( field -- str len )

Returns the string of the given column on the current row. May be much longer than 255

:M GetBLOB: ( field -- addr len )

Returns the Binary Buffer of the given column on the current row. This binary data may be anything.

:M isNull?: ( field -- flag )

Returns true if the given field for the given flag is null

:M NextRow: ( -- flag )

Goes to the next row of the query result. If there are no more rows, it will return true

:M BindInt: ( n i -- )

Binds a '?' in the query string to a integer. If there are no more question marks in the query string, the query will execute.

:M BindDouble: ( d i -- )

Binds a '?' in the query string to a double int. If there are no more question marks in the query string, the query will execute.

:M BindFloat: ( f i -- )

Binds a '?' in the query string to a floating point number. If there are no more question marks in the query string, the query will execute.

:M BindStr: ( str len i -- )

Binds a '?' in the query string to a string. If there are no more question marks in the query string, the query will execute.

:M BindBlob: ( str len i -- )

Binds a '?' in the query string to a blob (binary buffer object, or in simpler terms, a bunch of bytes). If there are no more question marks in the query string, the query will execute.

Examples of Usage:

Creating/Opening a Database:

SQLiteDB sqlite
s" c:\test.db" open: sqlite

Creating a Database in RAM:

SQLiteDB sqlite
s" :memory:" open: sqlite

Closing a Database:

close: sqlite

Creating a table:

s" CREATE TABLE idtoname (id int, name varchar)"
execute: sqlite

Inserting into a table:

s" INSERT INTO idtoname(id, name) VALUES(?,?)"
execute: sqlite
1                  0 bindint: sqlite
s" Jim Hawkins"    1 bindstr: sqlite

s" INSERT INTO idtoname(id, name) VALUES(?,?)"
execute: sqlite
2                  0 bindint: sqlite
s" Billy Bones"    1 bindstr: sqlite

s" INSERT INTO idtoname(id, name) VALUES(?,?)"
execute: sqlite
3                  0 bindint: sqlite
s" Long John Silver" 1 bindstr: sqlite

Executing SQL:

: qdump ( -- )
 fieldcnt: sqlite
 0 ?do
   i fieldname: sqlite type tab
 loop cr cr
 begin
   fieldcnt: sqlite
   0 ?do
       i getstr: sqlite type tab
   loop cr
   nextrow: sqlite
 until ;

s" SELECT * FROM idtoname WHERE id < 1000 ORDER BY name DESC"
execute: sqlite
cr qdump

Deleting a table:

s" DROP TABLE idtoname"
execute: sqlite