Using the Built-in SQL Database Engine

Robo-FTP offers a series of commands and a database engine in order to create SQL database files, and save and recall data within database file(s). Robo-FTP permits there to be one SQL database file to be active at any one time.

Important

Robo-FTP SQL database support does have limitations over standalone databases and has been designed with compromises in mind that still results in a powerful extension to the Robo-FTP script environment. For example, the amount of data that can be returned on any given query is limited. It’s a good rule of thumb to keep any use of the built-in SQL database simple and minimal - this is not intended to be a full relational database implementation but rather a flexible tool to save and retrieve data useful in the management and execution of Robo-FTP scripts.

The commands provide a portal for SQL queries and any responses with a minimum of intervention by the Robo-FTP script environment. This is to say that it is your responsibility, the script programmer, to form valid SQL queries and to interpret the result(s) to these queries within the script environment.

Important

Use of the Robo-FTP SQL database portal commands assumes that you have a working knowledge of SQL databases and queries. It is beyond the scope of Robo-FTP documentation or technical support to offer support or education related to SQL specifically other than how to engage the provided SQL database engine from the Robo-FTP script environment.

The following is a high-level overview of how you might incorporate SQL database access into your Robo-FTP scripts.

Notice that we state “using appropriate SQL statement(s)” above. Once again, it is assumed that you as the script programmer have a working knowledge of SQL commands and queries to pass to the SQL database engine via the DBQUERY script command.

The following is a more specific example of the creation of a SQL database file with a data table named “MyTable” which is then populated with two rows of hard coded data. In production scripts SQL “insert” and “update” queries, for example, would be constructed using

DBUSE "MyDatabase.sql" /new

DBQUERY "create table MyTable (fld1 text primary key, fld2 text);"

DBQUERY "insert into MyTable values ( 'row1', 'data1' );"

DBQUERY "insert into MyTable values ( 'row2', 'data2' );"

DBCLOSE

In production scripts, SQL “insert” queries like the one shown above or others like “update”, for example, would be constructed using string and substring commands to create dynamic commands to build a database with meaningful data. For example, to construct the first “insert” query shown above into a script variable might look like the following.

SET fld1 = "row1"

SET fld2 = "data1"

SET vars =  " ('" + fld1 + "', '" + fld2 + "' );"

SET query = "insert into MyTable values" + vars

DBQUERY query

Once a database is created and populated with data then the following example shows how specific data from a single row may be searched for and retrieved from the database.

DBUSE "MyDatabase.sql"

DBQUERY "select * from MyTable where fld1='row1';"

IFERROR $ERROR_DB_QUERY_FAILED GOTO done

DBGETRESULTS

DISPLAY %db_fld1

DISPLAY %db_fld2

:done

DBCLOSE

The following example shows how specific data from multiple rows may be searched for and retrieved from the database.

DBUSE "MyDatabase.sql"

DBQUERY "select * from MyTable;"

IFERROR $ERROR_DB_QUERY_FAILED GOTO done

DISPLAY %dbqueryrows

:loop

DBGETRESULTS

IFERROR $ERROR_DB_ALL_RESULTS_RTND GOTO done

DISPLAY %db_fld1

DISPLAY %db_fld2

DISPLAY %dbqueryvariables

GOTO loop

:done

DBCLOSE

The number of rows resulting from a query are automatically saved to the %dbqueryrows script variable.

Queries returning up to 1000 rows are permitted and are handled in this manner. Queries resulting in more than 1000 rows will fail with no data returned.

Notice that Robo-FTP automatically creates variables (prefaced with %db_xxxx ) from the column names in the table and stores the values in those columns in the appropriate variable. The number of variables created for a given query is saved to the %dbqueryvariables script variable.

If Robo-FTP cannot parse or otherwise process the query response so that the %db_xxxx variable(s) can be created, the DBQUERY command will fail returning result code $ERROR_DB_RAW_QUERY_RESULTS and the raw SQL query result is saved in its entirety in the %dbrawqueryresult script variable. Your script may parse this value directly if the response is expected or may be used for troubleshooting purposes.