Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » UltraLite - Database Management and Developer Guide » UltraLite database reference » UltraLite utilities

Interactive SQL for UltraLite utility (dbisql)

Executes SQL statements and runs script files against a database.

Syntax
dbisql -c "connection-string" [ options ] [ dbisql-statement | dbisql-script-file ]
dbisql -c "connection-string" -ul [ options ] [ dbisql-statement | dbisql-script-file ]
dbisql-statement: A SQL statement  or a series of sql statements separated by a command-delimiter.
Option Description
@data

Reads options from the specified environment variable or configuration file.

If both the environment variable and configuration file exist with the same name, the environment variable is used.

To protect information in the configuration file, you can use the File Hiding utility (dbfhide) to encode the contents of the configuration file. Interactive SQL does not support configuration files that are encrypted.

-c "keyword=value; ..."

Specifies connection parameters. If Interactive SQL cannot connect, you are presented with a window where you can enter the connection parameters. If you do not specify both a user ID and a password, the default UID of DBA and PWD of sql are assumed.

-d delimiter

Specifies a command delimiter. Quotation marks around the delimiter are optional, but are required when the command shell itself interprets the delimiter in some special way.

This option overrides the setting of the Interactive SQL command_delimiter option.

-d1 Echoes all statements explicitly executed by the user to the command window (STDOUT). This can provide useful feedback for debugging SQL scripts, or when Interactive SQL is processing a long SQL script. (The final character is a number 1, not a lowercase L). This option is only available when you run Interactive SQL as a command line program.
-datasource DSN-name Specifies an ODBC data source to connect to.
-f filename

Opens (but does not run) the file called filename in the SQL Statements pane.

If the -f option is given, the -c option is ignored; that is, no connection is made to the database.

The file name can be enclosed in quotation marks, and must be enclosed in quotation marks if the file name contains a space.

If the file does not exist, or if it is really a directory instead of a file, Interactive SQL prints an error message and then quits.

If the file name does not include a full drive and path specification, it is assumed to be relative to the current directory.

This option is only supported when Interactive SQL is run as a windowed application.

-host hostname Specifies the hostname or IP address of the computer on which the database server is running. You can use the name localhost to represent the current computer.
-nogui

Runs Interactive SQL as a console application, with no windowed user interface. This is useful for batch operations.

If you specify either dbisql-statement or dbisql-script-file, then -nogui is assumed.

In this mode, Interactive SQL sets the program exit code to indicate success or failure. On Windows operating systems, the environment variable ERRORLEVEL is set to the program exit code.

-onerror { continue | exit }

Controls what happens if an error is encountered while reading statements from a script file. It is useful when using Interactive SQL in batch operations. This option overrides the Interactive SQL on_error option setting.

Define one of the following supported behavior values:

  • Continue

    The error is ignored and Interactive SQL continues executing statements.

  • Exit

    Interactive SQL terminates.

-q

Suppresses output messages. Sets the utility to run in quiet mode. This is useful only if you start Interactive SQL with a statement or script file. Specifying this option does not suppress error messages, but it does suppress the following:

  • warnings and other non-fatal messages

  • the printing of result sets

-ul

Specifies that UltraLite databases are the default. Interactive SQL customizes the options available to you depending on the type of database you are connected to.

By default, Interactive SQL assumes that you are connecting to SQL Anywhere databases. When you specify the -ul option, the default changes to UltraLite databases. Regardless of the type of database set as the default, you can connect to either SQL Anywhere or UltraLite databases by choosing the database type from the Change Database Type dropdown list on the Connect window.

-version

Displays the version number of Interactive SQL. You can also view the version number from within Interactive SQL; from the Help menu, click About Interactive SQL.

-x

Scans statements but does not execute them. This is useful for checking long script files for syntax errors.

dbisql-statement | dbisql-script-file

Execute the SQL statement or execute the specified dbisql-script-file.

If you do not specify a dbisql-statement or dbisql-script-file, Interactive SQL enters interactive mode, where you can type a statement into a command window.

Remarks

Interactive SQL allows you to browse the database, execute SQL statements, and run script files. It also provides feedback about:

  • the number of rows affected
  • the time required for each statement
  • the execution plan of queries
  • any error messages

You can use Interactive SQL to connect to a SQL Anywhere database, an UltraLite database, an SAP IQ database, an SAP HANA database, or a generic ODBC database.

For Windows, there are two executables:

  1. Batch scripts should call dbisql or dbisql.com, not dbisql.exe. The dbisql.com executable is linked as a console application.

  2. The dbisql.exe executable is linked as a windowed application and does not block the command shell from which it was started. If dbisql.exe is run from a batch file, you won't see any output sent to the standard output or standard error files.

The default encoding for Interactive SQL can also be temporarily set using the Interactive SQL default_isql_encoding option.

You can specify the encoding to use when reading or writing files using the ENCODING clause of the INPUT, OUTPUT, or READ statement.

  • INPUT statement
  • OUTPUT statement
  • READ statement

Exit codes are 0 (success) or non-zero (failure). Non-zero exit codes are set only when you run Interactive SQL in batch mode (with a command line that contains a SQL statement or the name of a script file).

In command-prompt mode, Interactive SQL sets the program exit code to indicate success or failure. On Windows operating systems, the environment variable ERRORLEVEL is set to the program exit code.

When executing a reload.sql file with Interactive SQL and the database is encrypted, you must specify the encryption key as a parameter. If you do not provide the key in the READ statement, Interactive SQL prompts for the key.

You can start Interactive SQL in the following ways:

  • From SQL Central, by clicking Start of the navigation path File Next navigation step Open Interactive SQL End of the navigation path.
  • From the Start menu by clicking Start of the navigation path Start Next navigation step Programs Next navigation step SQL Anywhere 17 Next navigation step Administration Tools Next navigation step Interactive SQL End of the navigation path.
  • Using the dbisql command at a command prompt.

Example

The following command runs the script file mycom.sql against the CustDB.udb database for UltraLite. Because a user ID and password are not defined, the default user ID DBA and password sql are assumed. The -onerror option is defined as Exit; so, if there is an error in the script file, the process terminates.

dbisql -ul -c DBF=CustDB.udb -onerror exit mycom.sql