SQL

GSAK (Geocaching Swiss Army Knife)

SQL (function)

Overview

Warning: The SQL command was the first introduction of SQL to the macro language. It has many limitations and quirks and is now deprecated. It is provided for here for backwards compatibility only. It is very much recommended that you use the more complete and robust SQLite database engine. See the SQLite() function.

The SQL() function is a tool for manipulating large amounts of data as database tables. While SQL tables can be created with SQL commands, GSAK also provides a CSV export and the DBtoSQL() function for creating SQL tables from data in GSAK databases..

This implementation of SQLis a single user relational Database engine implemented using plain text files with semi-colon separated data for data storage. This implementation of SQL is a subset of the full standard, and includes a few non standard commands. While it is a minimal set it has been implemented to do very fast database calculations in databases you create in your macro

Supported SQL: SELECT (with table joins, field aliases and calculated), UPDATE, INSERT (values and sub-select), DELETE, CREATE TABLE, DROP TABLE, ALTER TABLE, CONNECT TO, COMMIT, WHERE (rich bracketed expression), IN (list or sub query), GROUP BY, HAVING, ORDER BY ( ASC, DESC), nested sub queries, statistics (COUNT, SUM, AVG, MAX, MIN), operators (+,-,*,/, and, or,>,>=,<,<=,=,<>,Like), functions (UPPER, LOWER, TRIM, LEFT, MID, RIGHT, LEN, FIX, SOUNDEX, SQR, SQRT). High performance: complete in-memory handling of tables and recordsets), field aliases, table aliases, join "unlimited" tables, stdDev aggregate function, ASSIGN TO for named temporary tables, SAVE TABLE for persisting recordsets, INSERT INTO, ISO 8601 dates.

SQL tables are generally in memory but may also be plain text files that have the .txt extension (to allow for quick opening in e.g. Notepad) and where data is separated by semi-colons. The first row of the file contains the field names. No quotes are used around text fields. You can not use the semi-colon character in data fields as it is used as the field separator.

Tables are automatically loaded into memory (opened) when needed by a query. Any changes to tables are performed in memory. Tables are saved to disk when you use the COMMIT statement (saves all changed tables) or the SAVE TABLE statement (to store an individual table). Also the CREATE TABLE statement stores a new table to disk immediately and the DROP TABLE statement immediately deletes a table from both memory and disk.

The SQL Function syntax

SQL(sData,sOptions) : string

sData = The SQL command(s) to process. Multiple commands can be executed by separating each command with a ; (semi colon).
sOptions = Options. Currently the only option supported is "H". This forces the return string to include the column headings.

The return string is the result of the SQL query and represents the only mechansism for bringing data from SQL data tables into GSAK variables. The format of the resulting text consist of a columnwise text table where the width of each column is the maximum width needed by the particular field. If it begins with "*ERROR*" there was a problem when trying to run the SQL statements. When the last statement is a "SELECT" then the return string is the full result set of that query. It should be noted that not all errors are trapped and some return from the SQL as "invariant errors" - such errors are usually due to otherwise valid command syntax, but errors in the resulting calculations. Note that it is entirely possible for a query to return no text; while this may be unintended, it is typically a valid.

When the SQL() function is used, the tables stay resident in memory for the duration of your GSAK session (until GSAK is closed). Once GSAK closes, the memory-resident SQL tables are lost, but the versions that have been committed (Saved) to disk remain.

Please also see the SQL engine quirks and known issues.

Unlike most other database engines that have strong data typing, there are no data types, everything is stored as a string and in calculations converted ad-hoc to a number when applicable in the context. String data is surrounded by single quotes, and the decimal point is used for numeric data (not a comma)

To handle dates you must store them as a string in the ISO 8601 format:

YYYY-MM-DD

A 4-digit year, followed by the 2-digit month number followed by the 2-digit day number. In your WHERE clauses you can then compare these string dates with each other. E.g. 1953-11-16 will be less than 2002-03-26.

SELECT * FROM caches WHERE PlaceDate > '2007-01-01'

Also the ORDER BY clause will produce correct results.

SQL Commnds:

ALTER TABLE
ASSIGN TO
CONNECT TO
COMMIT
CREATE TABLE
DELETE FROM
DROP TABLE
INSERT INTO
RELEASE TABLE
SAVE TABLE
SELECT FROM
UPDATE

Options:

GROUP BY
HAVING
ORDER BY
WHERE

Functions:

You can use functions wherever you can use an expression to be calculated (Calculated output fields, WHERE clause, HAVING clause). Use extra brackets around function parameters when you have a function with more than one parameter:

SELECT trunc((userid/7),2) as foo FROM users

and not:

SELECT trunc(userid/7,2) as foo FROM users

Date functions
String functions
Numeric functions
Conversion functions

Related Topics and macro functions:

DateToSQL Convert a date to SQL date format
DBToSQL Database data to SQL conversion
SQLToDate Convert a SQL date string to a macro date
SQLToHTMLView SQL data in browser

Also see "File=>Export=>csv/txt" and the GSAK SQL type

The macro library also contains a macro that you can use as an environment to test and run SQL statements. You can get the macro here