You specify the path (absolute or relative) to the SQLite database here. If the database is protected with a password, you can optionally enter it here. If you want to store the password it is necessary to check the "Save Password" option here. Note: The password is stored in the Definition data in encrypted form.
Create a database if it does not exist: If this option is checked, when the SQLite database in the specified folder does not already exist, a new (empty) SQLite database will be created.
Create tables if they do not exist: This option informs the SQLite Adapter to generate new tables in the SQLite database if they are not already available. Note: If this option is checked, you must manually enter a table name into the "Fields / Data types" tab.
Insert missing fields automatically into tables: When this option is checked, the SQLite table schema is adjusted to the actual fields listed in the SQLite Adapter. Note: No fields are deleted or existing fields amended (e.g. data types), only missing table fields are inserted.
When the Adapter is being used on the WRITE side (right-hand side), in the lower half of this tab you can specify what actions the SQLite Adapter should take during an import (insert/update) into the SQLite database.
Insert data: When this option is checked, SQL Insert statements are generated.
Ignore existing records: During an import and when this option is checked, records that already exist in the table are ignored.
Empty the table before importing: When this is checked you tell the SQLite Adapter to empty the contents of the table prior to running the import, effectively deleting all existing rows.
Update data: When this option is checked, SQL Update statements are generated. Note: If both the Insert and Update options are checked, the SQLite Adapter checks whether an SQL Update or Insert should be generated in each instance, by reference to the PrimaryKey. Tip: If you are certain there is only data to insert then avoid checking the Update option, as this will make the process significantly faster.
No updates or inserts to data, only generate an SQL script: When this option is checked, it signals the SQLite Adapter to make no inline changes to the database, but instead to store an SQL script with Insert and/or Update statements. This is useful for testing during development and for subsequent application to the database. If this option is checked you should also check the option below and enter the filename that the SQL statements are to be stored in.
Generate SQL script: This option instructs the SQLite Adapter to store the change statements (Insert, Update) as an SQL script file with the specified name and path.
Fields / Data types tab
SQL: data available varies according to the side of the Adapter in use:
On the READ side: You can choose tables and views here. You also have the possibility to record complex SQL statements in the text field. You have to define relations between tables (joins) yourself. Existing tables or views that are available in the SQLite database are automatically listed in the second ComboBox.
On the WRITE side: Only the tables available appear here. The second ComboBox automatically lists the existing tables of the SQLite database. If you chose the option "Create tables if they do not exist" you must manually enter a table name in the text field "Table name if it does not exist" that will be used to create the new table.
Fields: When you click the Load Schema button, information is retrieved from the database schema (field names, field sizes, data types, primary key, etc.) for the SQL statement above. The information about the fields is then loaded into the field list to the left of this button.
Note: The fields in the field list can be ordered in any sequence required. Fields that are not required can either be temporarily disabled here (tick removed) or simply deleted.
Field properties: How the properties of Primary Key and Auto Increment for the currently highlighted field are to be interpreted are adjusted. This information is only required on the WRITE side. No changes are needed here generally, since the correct information is usually obtained directly from the schema.
A PrimaryKey field is used in an Update to identify record that possibly exists.
Auto Increment fields are neither assigned nor amended in Insert/Update statements. (Not used by SQLite Adapter)
Warning: If you make changes here it can result in more than one record being updated with an Update!
Adapter settings in the Format tab
- Merging CSV and Excel data using an SQLite databaseMerging CSV and Excel data using an SQLite database