SQLite(sAction,sData,[sOptions]) : string
This function is the main portal to the very powerful SQLite database engine. For an overview of SQLite please see this topic
sAction:
Open - open the database provided by sData. SDate should be the fully qualified path to the SQLite database file name, or the reserved memory only database of ":Memory:"
Create - create a new database provided by sData
Close - close the current database (sdata and sOptions should be "")
Sql - Execute any SQL statement(s). If using multiple statements, then each statement must end with a ";" (semi colon).
Import - Import data to a table.
sData
sData contains the string associated with the action. The most common being the actual SQL query you are performing with the "sql" action.
sOptions is an optional paramater and only applies to the Sql and Import actions. For select it can contain any of the following (separate each with a space)
delim=xxx - The delimiter to use when returning the result of the Select. If not entered the default is ";" (without the quotes) you can uses any character or number of characters here.
delimcvt=xxx - The string to convert any fields that have delim inside them. This allows you to preserve the fidelity of the source date by doing a replace later.
delimclf=xxx - The string to convert any fields that have a line feed inside them. This allows you to preserve the fidelity of the source data by doing a replace later.
headings=Yes|No - Includes the field names as the first line in the result set
The return value is only "not empty" for the SQL action. When using SQL, each row contains all the fileds delimited with the "delimiter" and each row separated by a crlf
If you don't include any of these options the defaults are:
1. Remove any delimiters found in the data fields
2. Remove any line feeds found in the data fields
3. Delimiter is ";" (without the quotes)
4. Headings = No.
For the import action available options are:
table=xxxx - The table name to import the data in to.
delim=xxx - The delimiter to use for the data. If not entered the default is ";" (without the quotes) you can use any character or number of characters here.
delimcvt=xxx - Source fileds with this string will have it converted to your "delim" string when it arrives in the database
delimclf=xxx - Source fileds with this string will have a crlf inserted when it arrives in the database
For example, if you had a csv file on disk you wanted to get into your database table "fruit", it should be as simple as:
$data = GetFile("c:\temp\fruit.csv") $status = sqlite("open","c:\temp\test.db3") $status = sqlite("import",$data,"table=fruit delim=,") |
In addition to the standard SQLite shipped functions, GSAK also includes some extra functions. For more information see Sqlite Functions
For a list of examples see Sqlite examples
Note: if you add add many records using Insert, be sure to wrap them inside "begin" and "commit" - it makes a HUGE difference to the time to add many records. When using the SQL action you would usually optimize commands by wrapping them with "begin" and finally "commit"
For example, the following code will generate a sqlite database in your macros folder with the name of TestExample1.db3. It will generate a small table and then insert 100 records without a transaction, then another 100 records within a transaction. Notice the hugh difference in the timings:
$database = "$_install\macros\TestExample1.db3" If not(FileExists($database)) $data = sqlite("create",$database) EndIf $data = sqlite("open",$database) $data = sqlite("sql","create table if not exists test1 (field1 text)") $data = sqlite("sql","delete from test1") # first insert without a transaction $x = 0 Timer status=on while $x < 100 $data = sqlite("sql","insert into test1 values('" + "$x" + "')") $x = $x + 1 endwhile timer status=off # now with a transaction $x = 0 Timer status=on $data = sqlite("sql","begin") while $x < 100 $data = sqlite("sql","insert into test1 values('" + "$x" + "')") $x = $x + 1 endwhile $data = sqlite("sql","commit") timer status=off |
First insert:
Second insert
Summary