DBUSE        Open a SQL database file or connection

<< Click to Display Table of Contents >>

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

DBUSE        Open a SQL database file or connection

Syntax:

DBUSE

[ dbfile ] [ /options ]

Arguments:

[ file name ]

Variable or string defining a database file name; if no path is defined the alternate default path is used.

Options:

/new

Create the database file if it does not exist or empty it if it does exist.

 

/odbc

Use an ODBC Data Source Name (DSN) instead of a filename.

 

/pw=xx

Optional password to use with the /odbc option.

 

/sqlce

Use a Microsoft SQL Server Compact edition sdf file instead of a SQLite database file. This should not be combined with the /odbc option.

 

/type=xx

Database connection type. This may be used instead of the /odbc or /sqlce options. Possible values are: sqlite, odbc and sqlce.

 

/user=xx

Optional username to use with the /odbc option.

 

 

This script command is used to open a database file or connection. Only one database may be open at any time. Once the database file or connection is open you can use the DBQUERY script command to execute SQL statements and the DBGETRESULTS command to process the results of those statements. Execute the DBCLOSE command to stop using a database file or connection.

 

Database Files

Call the DBUSE command with no options to open an existing SQLite or SQL CE database file. Add the /new option if the database file does not exist and you want a new file to be created. When a new file is created it is just an empty database - you must create one or more tables in the database file via the DBQUERY script command before data rows may be inserted. If a database file does exist then the /new option empties it.

 

Note: The built-in SQLite database engine is unable to process queries that return more than 1000 rows of results.

 

ODBC Database Connections

Use the /odbc option to specify that the [ file name ] argument refers to the name of a ODBC User or System Data Source Name (DSN.) File DSNs are not supported. You can create DSN records using the Data Sources tool in the Administrative Tools folder under the Control Panel. This tool is named ODBC Data Source Administrator in some versions of Windows. The exact steps for creating a DSN depends on the features of your database's ODBC driver and is beyond the scope of this help topic. Please contact your local database administrator to resolve DSN configuration issues.

 

Note: Robo-FTP is a 32 bit application.  If you want to use a User or System DSN on a 64 bit version of Windows you must use the 32-bit version of the ODBC Data Source Administrator by running %systemroot%\sysWOW64\odbcad32.exe.  Be sure to use the full path because the 64-bit version of the file is named %systemroot%\system32\odbcad32.exe so it is quite easy to run the wrong version by mistake.  The name of your 32-bit DSN must be unique; your queries may fail if your computer also has a 64-bit DSN defined that shares the same name.

 

The /user and /pw options can optionally be combined with the /odbc option if you don't save database access credentials in your DSN. Creating a single shared System DSN without stored credentials may be preferable to creating separate User DSN records for each user account on a shared computer.

 

This is an example of connecting to a pre-configured DSN:

 

 DBUSE "mydsn" /odbc /user=robo /pw=robo

 

Here we connect to an ODBC database directly through a driver connect string, instead of using a DSN:

 

 DBUSE "Driver={SQL Server Native Client 11.0};Server=localhost;Database=test;Trusted_Connection=Yes;MARS_Connection=No;" /odbc

 

The following example illustrates connecting to a (file-based) SQLITE database. If the mydb.sqlite file below does not exist, it will be created.

 

 DBUSE "C:\databases\mydb.sqlite" /type=sqlite

 

Example of Using Database-related Commands to Run a Query

 

DBUSE "mydsn" /odbc /user=robo /pw=robo

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

 

 

Related command(s): DBQUERY, DBGETRESULTS, DBCLOSE, DBREWIND, BROWSE  

See also: Using the built-in database engine, Alternate Default Path