GSAK (Geocaching Swiss Army Knife)
 

Contents - Index


SQLite (function)

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. SData should be the fully qualified path to the SQLite database file name, or the reserved memory only database of ":memory:". You should open a databases before using any other action. However, a special exception has been added for the "sql" action. That is, if you issue a "sql" action but haven't opened a database, then GSAK will automatically use the current live database. This enables you to immediately run SQL against the current database without having to worry about open semantics. 
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 parameter and only applies to the Create, Sql and Import actions. 

Create:

Active=Yes|no - Yes = Close any current connection and make the created database the active database. This is the is the default. That is, if you don't use this option then GSAK substitutes Active=Yes. This is most likely what macro authors will usually want. However, the problem comes about when all you want to do is attach the newly created database to your existing connection. This is particularly troublesome if your current connection is a memory database, as it is deleted on closing the current connection. No = The current database is not closed and remains the active database. The new database is "created", without any effect on the current connection.

SQL (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. Note: Version 8 sees the introduction of the special delim value of "*csv*" (without the quotes). Using this special delimiter instructs the Sqlite() function to return the results as standard CSV data. You can then use the new Version 8 function CsvGet()  to parse the result. Using this special delimiter means that you would normally have no need for  the delimcvt= and crlfcvt= options. 

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.
crlfcvt=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
error=Halt|Trap - "Halt"  is the current default behaviour and also occurs when the Error= option is not used. Any SQL errors cause the macro to "halt" immediately with an error message. "Trap" - Sqlite errors will not crash the macro but rather return the macro error as the result. When this happens the first 7 characters will always be "*Error*" (without the quotes). All characters after "*Error*" will be the full description of the actual sqlite error message. If you have the option Error=Trap then you should aways test the first 7 characters for "*Error*" and code your macro accordingly.
sqlget=Yes|No - Optimized (and recommended) way to iterate through a SQLite table to read the individual rows and fields. When SqlGet=Yes the first  3 options have no meaning (delim, delimcvt, crlfcvt). For more information on how to use SqlGet=Yes to iterate through the results of a query see the function SqlGet()

The only action that returns a value is "SQL" (unless there is an error and you have used the option error=Trap). When using SQL, each row contains all the fields 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
5. Error = Halt
6. SqlGet = No

Import: (separate each with a space)

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 fields with this string will have it converted to your "delim" string when it arrives in the database
crlfcvt=xxx - Source fields 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 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 huge difference in the timings:
 

$database = "$_AppData\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


Use the system variable $d_CurrentDataPath for the fully qualified path to the current GSAK sqlite database which has the file name "sqlite.db3"

For example, to open the live GSAK sqlite database in a macro the code would be:
  

$data = sqlite("open","$_CurrentDataPath\sqlite.db3")

 
If you already have a database open, but would still like to access the live GSAK tables, then the code would be:
 

$data = sqlite("sql","attach '$_CurrentDataPath\sqlite.db3' as GSAKdbf")

 
Alpha List         Category List
Copyright 2004-2011 CWE Computer Services  
Privacy Policy Contact