|
Using the Built-in SQLite Database Engine |
Top Previous Next |
|
Robo-FTP includes a built-in SQLite database engine that allows you to create database files and manipulate them using the same scripts commands used to interact with other ODBC data sources. Robo-FTP permits there to be one SQLite database file to be active at any one time. The DIFF and FTPDIFF family of script commands use the SQLite database engine to identify files that are new or have changed since between script executions.
Important Robo-FTP SQLite 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 SQLite 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 SQLite 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 SQLite queries and to interpret the result(s) to these queries within the script environment.
Important Use of the Robo-FTP SQLite 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 SQLite database engine from the Robo-FTP script environment. See: http://www.sqlite.org
The following is a high-level overview of how you might incorporate SQLite 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 SQLite database engine via the DBQUERY script command.
The following is a more specific example of the creation of a SQLite 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' );"
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 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 (in the format %db_ColumnName ) 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_ColumnName 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 debugging purposes.
Related command(s): DBUSE, DBQUERY, DBGETRESULTS, DBCLOSE, DBREWIND
|