Connecting to SQLite
Description
Alpha Anywhere AlphaDAO includes a SQLite native driver built from the open source SQLite code base. SQLite connections in Alpha Anywhere do not require any configuration. Alpha Anywhere includes all software necessary.
How SQLite Works
SQLite is, as its name implies, a lightweight database engine. It is considered "serverless" because it runs entirely within the process of the caller. SQLite can store data in memory, in a temporary file, or in a single permanent file on disk. For more information on SQLite see https://www.sqlite.org
Databases in SQLite
A SQLite database may be in any one of three locations:
- SQLite File Locations
- Description
- Permanent File
If you provide a valid path and file name, SQLite will open the file and store table data in that file. This is the most common usage
- In Memory
If you open a database named ':memory:', SQLite stores all data in memory; deleting it when the connection is closed. This can be useful when you want to manipulate data using SQL but do so in memory.
- Temporary File
If you completely omit the database name in the connection string (or set it to an empty string) SQLite automatically creates a temporary file, stores all data in that file and then deletes the file when the connection is closed.
SQLite Connection String Properties
- Connection Property
- Description
- A5API
Setting the connection property A5API to 'SQLite' will load the SQLite driver when you open a connection.
- Database
If you provide a valid path and file name as the value to the Database connection property, SQLite will open the file and store table data in that file.
- A5BusyTimeout
When sharing a SQLite database and there are multiple concurrent operations being performed, you may encounter a "busy timeout" error. If this should occur, you can set the timeout by assigning A5BusyTimeout to a set period of time (in milliseconds), or to 'Infinite' (not recommended) to wait forever. The default behavior (wait for 100 milliseconds) is generally sufficient.
- A5CreateOnOpen
You can choose to create the file if it is not found when the database connection is opened. Setting the A5CreateOnOpen connection property to 'Y' will create the file when the connection is opened if it does not exist.
- A5ReadOnly
If you set the A5ReadOnly connection property to 'Y', the file will be opened for read only and updates will not be allowed.
- A5TraceSQL
Setting the A5TraceSQL connection property to 'Y', causes the connection to write an entry to the SQL trace tab each time a SQL statement is processed. This tab accessible from the View and then Trace Window menu options in Alpha Anywhere.
- A5TraceSQLLevel
Set this value to determine how much detail will be written to the trace log. When set to "Errors", only errors will be reported. When set to "SQL" a summary of each statement and the results will be reported, including errors. When set to "Complete" all errors and SQL will be reported as well as key function calls and translated queries with argument markers as well as any caller context provided. When set to "Diagnostic", all of the previous output will be included along with the user thread name, the argument values and bind mappings. All other values are treated as if "SQL" was set.
- A5TraceSQLFormat
Set this value to indicate the output format desired. When set to "JSON", information will be written in JSON format with a trailing comma. Parsing a file in JSON format can be done by stripping the last trailing comma and wrapping the text in square brackets "[]". When set to "Text", the output is written as formatted text. This is the default format.
- A5DBTimeZoneIsUTC
Defaults to Y, indicating that all datetime values inserted into the database will be converted from local time to UTC time. All datetime values read from the database are converted from UTC to local time. Setting this value to N will result in datetime values being passed into and read from the database without conversion. Note: Setting this value is not recommended if you use a database in multiple time zones. This can result in corrupt values.
- A5InitialCommand
A string of commands (separated by semicolons ';') to be executed each time the connection is opened.
Using the SQLite Connection String Dialog
When you select SQLite as your connection type in the connection string dialog, the dialog below will be displayed. You can select the database file and set any other options you need to. The values you select will be generated into the connection string at the top of the dialog for you as you make changes.
The SQLite connection string dialog has a tab page for general properties and a tab to set one or more initial commands. Each of the properties are described above.
Each time your connection opens, commands that you set into the A5InitialCommand connection property are executed. This is especially important for commands that must be executed everytime a new connection is opened. Sometimes a new connection is opened implicitly (such as when you hold onto a result set from an open connection). You can use these commands to invoke SQLite pragmas or to attach external databases (discussed below).
Creating a New Database in the SQLite Connection String Dialog
You can create a database from the connection string dialog by clicking on the "Create Database" button. The dialog below will be displayed. Type or browse for a path and enter a database name (by convention, SQLite databases generally have an extension of '.db'. Click 'Save' in the browse dialog and then 'OK' in the Create SQLite Database dialog to create a new database file.
The name of your new file is automatically set into the "File Name" field on the "General" tab of the connection string dialog.
Attaching Databases to a SQLite Connection
A SQLite connection has a primary database known as 'main' and may also attach other databases using the "attach database" command. This can be useful when you have a set of data that is shared with multiple databases (such as a price or cost database), or you want to work with tables but not fragment your permanent database file.
attach database 'c:\temp\mydatabase.db' as mydatabase
When you attach a database, that database is addressed using the alias you assign in the 'as' clause. Alpha Anywhere treats this connection as a schema and you must include the alias (mydatabase in this case) when referring to the tables in the attached database.
Using Portable SQL with SQLite
A SQLite connection automatically selects SQLite as it's syntax. The SQLite syntax handler reads table information, and generates DDL (Data Definition Language) such as CREATE TABLE, DROP TABLE and DML (Data Manipulation Language) such as SELECT, INSERT, UPDATE, DELETE. It is also responsible for translating AlphaDAO Portable SQL functions into native implementations. Although geography functions are not currently supported, SQLite has been extended to support all other portable SQL functions.
- The SQLite syntax handler may be used with ODBC or ADO.Net drivers for some purposes, but will not support all of the portable SQL functions that using the SQLite driver will; as the functions are implemented in the Alpha Anywhere SQLite driver.