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 resultsets only the first resultset will be available to Robo-FTP via the DBGETRESULTS command.

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 resultsets 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.

Not all SQL statements return data rows.  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.

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

See also: Using the built-in database engine