DBQUERY        Execute an SQL Statement on the current database

This script command is used to execute SQL statements on the currently open database the results of which may be processed using the DBGETRESULTS script command.

The built-in SQLite database engine will fail and return an $ERROR_DB_QUERY_FAILED error on queries that return more than 1000 rows. This row limit does not apply to ODBC connections made using a DSN. Any query returning columns with more than 4096 characters will fail with an $ERROR_DB_UNSUPPORTED_RESULT error.

If this command is used to execute a SQL statement that returns multiple result sets only the first result set will be available to Robo-FTP via the DBGETRESULTS command. Not all SQL statements return a result set but, when using an SQLite database, the %dbqueryrows variable contains the number of rows returned in the first set.

SQL Syntax

Basic SQL syntax often differs slightly by database vendor and features considered fundamental on one database platform may be entirely absent on another. For example, with Microsoft SQL Server databases you can write stored procedures that perform complicated calculations before modifying data or returning result sets while SQLite databases have no comparable functionality but are compact, cross-platform and the source code is in the public domain. The script examples below are only intended to explain the use of the DBQUERY script command. It is beyond the scope of Robo-FTP documentation or technical support to offer support or education related to SQL syntax. The development, formatting and testing of SQL statements are exercises left to the script programmer. Consult your database documentation or local database administrator for SQL statements and syntax supported by your particular database engine.

The most common SQL statements for modifying table data are SELECT, UPDATE, INSERT, and DELETE. Below are some examples using the DBQUERY command to execute SQL statements in the format supported by the built-in SQLite database engine. The SQL CREATE TABLE statement below is used to make a new database table named MyTable:

DBQUERY "create table MyTable (animal text primary key, color text);"

These two INSERT statements each add a row of data to our new table:

DBQUERY "insert into MyTable values ( 'frog', 'green' );"

DBQUERY "insert into MyTable values ( 'butterfly', 'blue' );"

This SELECT query returns the row from MyTable that has a value of "frog" in the "animal" column:

DBQUERY "select * from MyTable where animal='frog';"

IFERROR= $ERROR_DB_QUERY_FAILED GOTO done

A good resource for learning more about the SQL syntax supported by the built-in SQLite database engine is available at http://www.sqlite.org.

Queries That Return a Scalar

Some queries return a single scalar value. Common examples include aggregate functions like those that return the count of rows in table.

In those cases, you can use the /result option to have Robo-FTP assign the return value of the query directly to a variable without having to call DBGETRESULTS. For example:

SET my_query = "select count(*) from MyTable where animal='frog';"

DBQUERY my_query /result=num_of_frogs

DISPLAY num_of_frogs ;; Result assigned to variable num_of_frogs

This option allows you to submit queries that do not reset the current result set in memory from a previous query. This is only available when using the SQL Native Client with an ODBC connection. To enable this feature, you must create the following string value in the registry under HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.ini\<dsn>.

MARS_Connection = Yes

Related command(s): DBGETRESULTS, DBUSE, DBCLOSE, DBREWIND

See also: Using the built-in database engine