Data Flask

SQLite

SQLite Client download.

SQLite database files (*.db) may be opened using File/Open Data . SQLite .db files can be opened from the windows shell using Open With and selecting Flask.exe (the installer does not presume to set a default association).

Specifying Column Type

SQLite Column Type Information (Text).

When creating or modifying a table using OleDb Admin, you can specify a specific sqlite column type from a list.

sqlite column types
sqlite provider types

This will be the same text (up to the left paren) that will be provided to the CREATE TABLE command. Additional qualifiers can be added. For example,

varchar(25) DEFAULT 'Value'

will specify that a field defaults to the time the record was appended. The sqlite provider type will be passed directly to the CREATE TABLE command and retained in the .dfg grid file.

Unique Indexes

All unique indexes are get reported by SQLite as Primary Keys . If this causes problems when choosing a key, select a unique column as the update key in the Data Link dialog.

Default Values

Default values are not available upon insert. Please use Data/Reload to obtain newly inserted default values.

SQLite3

Sometimes an unrecognizable name will appear in the Data window. This name comes from the name properties of the SQLite3 provider.

DBPROP_DBMSNAME error

Start with Driver={SQLite3 ODBC Driver} to create an SQLite3 connection string.

Editorial: SQLite2 does not do this, and the problem can go away. There is probably a way to stop this from happening, but I have not found it yet.

SQLite2

Start with Driver={SQLite ODBC Driver} to create an SQLite2 connection string.

For an auto identity key, SQLite 3 would produce PRIMARY KEY AUTOINCREMENT. Use AUTOINCREMENT PRIMARY KEY in SQLite2.

Sample Migration SQL

Load Customers from Nwind.mdb, as in the Crash Course Tutorial. Replace the connection string with a link to a sqlite server database, for example Driver={SQLite3 ODBC Driver};Database=C:\data\flask.db. The .db file will be created upon initialization.

ALTER TABLE cannot add a primary key as an index. To create the primary key with CREATE TABLE, use the Data Link dialog to remove the primary key index. Then use the Grid/Column/Properties dialog to set KEYCOLUMN flag for the CustomerID column.

A Write Data command would create a table with:

CREATE TABLE [Categories] (
   [CategoryID] integer AUTOINCREMENT PRIMARY KEY NOT NULL,
   [CategoryName] varchar(15) NOT NULL,
   [Description] longvarchar(536870910), [Picture] longvarbinary
)

and create keys and indexes using:

CREATE UNIQUE INDEX [CategoryName] ON [Categories] ([CategoryName])

Sample Test Migration: CategoriesSQLite.sql (text).