Robo-FTP User's Guide > Script Programming > Script Commands > All Script Commands > DBQUERY

Syntax:	DBQUERY	[ query ]
Arguments:	[ query ]	Variable or string defining a SQL statement to execute.
Options:	/result=xx	Variable to store results for queries that return a scalar
	/csvout=xx	Variable or string specifying the path to which a CSV file will be written
	/csvdelim=xx	Variable or string determining the character used between fields (columns). Must have a length of exactly 1 character.
	/csvrecsep=xx	Variable or string determining the character used between records (rows)
	/csvheaders=xx	Variable or string indicating if the csv header (initial row at beginning of file giving the names of each field) should be included in an outputted CSV file, or not. Permitted values are true and false.

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

Outputting to a CSV (Comma-separated value) file

By specifying the /csvout, /csvdelim, and /csvrecsep options together, you can write the results of a query directly to a file. Consider the following example:

DBUSE "C:\data\employee_db.sqlite"

DBQUERY "SELECT id, name, occupation FROM employees" /csvout="C:\data\employees.csv" /csvdelim="," /csvrecsep=%lf

Unsupported Data Types

Queries involving SQL Server binary types (binary, varbinary), are not supported by the DBQUERY command.

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