Mac - DbToSQL

GSAK (Geocaching Swiss Army Knife)

DBToSQL (function)

Warning: The DBToSQL 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.

DBToSQL(sTable,sFields,sFile,sReplace) : string

This SQL related function allows quick and easy integration with the GSAK database and the SQL engine.

This function creates a text file that is fully compatible with the SQL feature and makes it available as a table so that other SQL() commands can be used to process it. The resulting file contains a header row of field names, each subsequent line is a record terminated with CRLF, and the field data on each line are separated with semicolons. The return value is one of two values: "OK" if the file was created without problems or if the file was not created then the value is "*ERROR* xxxxx" where xxxxx is the error message.

The benefits of this function over using the "File=>Export=>Text" with the SQL option:

1. Speed - it has been fully optimised and is very fast!
2. All tables are supported
3. All database fields are supported
4. You have the option to replace "special" characters to preserve the fidelity of the data
5. Generic - no need for user settings and is ideal when writing macros that will run on another users computer
6. Just one macro line of code needed per table

Parameters:

sTable = The SQL table to generate. Valid values are "Caches", "Logs", and "Waypoints"

sFields = A comma delimited list of database fields. The names are the same as for database variables. However to avoid confusion when using variable substitution, you must remove the "$d_" part. So if you want the field $d_code in your sql table, it should be entered as "code" only.

sFile = This is the fully qualified SQL file name (table) that you want to generate. As I considered you would always want to generated a file with this function I added this parameter. Consider it the same as using a Putfile() function after internal generation of the SQL data.

sReplace = For general purpose use, leave this last parameter empty (""). This will instruct GSAK to delete any "naughty" characters from the raw data that would make your SQL file invalid (currently these characters are ";" and CRLF).

However (geek alert), I have provided this extra functionality for the user that needs to preserve the full fidelity of the data. You would do this by replacing the problem characters with another character or set of characters that are valid. The moment you enter something in the last parameter, this overrides the default delete behaviour and is used for replacing and/or deletion of characters.

Description: Each Group replacement should be separated by a ; (semi colon) and the format for a group replacement is:

nn,nn,..=nn,nn,..

Where nn represents the ASCII value of the individual character (same as using the chr() function. For example to replace a ; (semi colon) in the raw data with the ASCII character 255 the syntax would be:
59=255

To replace it with the combination of characters chr(255) + chr(254) :

59=255,254

To delete, just leave the part after the = blank

59=

To replace a CRLF with chr(255):

13,10=255

To replace CRLF with chr(255) and ; with chr(254)

13,10=255;59=254

To delete the ; but replace a CRLF with chr(255)

59=;13,10=255

By adding this replacement parameter and having it work this way, means you can delete/replace any single character or any group of characters.

Sample code to Generate a few fields from your cache table:

$file = $_Install + "\macros\caches.txt"
$status = dbtosql("caches","Code,Bearing,Found,Name,PlacedBy,LastGpxDate,Distance",$file,"")

The return value of this function is the same as the PutFile() function. That is, if the file is successfully generated it is "OK". However, if the file can not be written (open in another application for example) it will be "*Error* Description of error"

Note1: Generating "Memo" type fields (short and long description for example) will noticeably slow down the SQL file creation

Note2: This generation respects you current filter for all tables. That is, the "logs" and "waypoints" tables will only generate records that have "parents" in your current filter.

Summary