Invoking isql

From InterBase

Go Up to Command-line isql Tool

To start the isql utility, type the following at a UNIX shell prompt or Windows console prompt:

isql [options] [database_name]

where options are command-line options and <database_name> is the name of the database to connect to, including disk and directory path.

If no options are specified, isql starts an interactive session. If no database is specified, you must connect to an existing database or create a new one. If a database was specified, isql starts the interactive session by connecting to the named database.

If options are specified, isql starts interactively or noninteractively, depending on the options. For example, reading an input file and writing to an output file are noninteractive tasks, so the -input or -output options do not start an interactive session. Additional noninteractive options include -a, -database, -extract, and -x, which are used when extracting DDL statements.

When you start an interactive isql session, the following prompt appears:

SQL>

You must then end each command with a terminator character. The default terminator is a semicolon (;). You can change the terminator to any character or group of characters with the SET TERMINATOR command or with the -terminator command-line option. If you omit the terminator, a continuation prompt appears (CON>).

Note:
For clarity, all of the commands and examples in this chapter end with the default semicolon terminator.

Command-line Options

Only the initial characters in an option are required. You can also type any portion of the text enclosed in brackets, including the full option name. For example, specifying -n, -no, or -noauto has the same effect.

The table below lists the availabel isql command-line options:

Option Description

-a

Extracts all DDL for the named database.

-c[ache]

Set number of cache buffers for this connection to the database; see Default Cache Size Per isql.

-d[atabase] <name>

Used with -x; changes the CREATE DATABASE statement that is extracted to a file.

  • Without -d, CREATE DATABASE appears as a C-style comment and uses the database name specified on the isql command line.
  • With -d, isql extracts an uncommented CREATE DATABASE and substitutes <name> as its database argument.

-e[cho]

Displays (echoes) each statement before executing it.

-ex[tract]

Same as -x

-i[nput] <file>

Reads commands from an input file such as a SQL script file instead of from standard input.

  • input files can contain -input commands that call other files, enabling execution to branch and then return.
  • isql exits (with a commit) when it reaches the end of the first file.
  • In interactive sessions, use -input to read commands from a file.

-m[erge_stderr]

  • Merges stderr output with stdout.
  • Useful for capturing output and errors to a single file when running isql in a shell script or batch file.

-names <character set name>

Specifies the character set to use for current database attachment. Default is NONE.

Note: Any SET NAMES call in isql or inside an SQL script overrides the character set that you provide in the command-line.

-n[oauto]

Turns off automatic commit of DDL statements; by default, DDL statements are committed automatically in a separate transaction.

-nowarnings

Displays warning messages if, and only if, an error occurs (be default, isql displays any message returned in a status vector, even if no error occurred).

-o[utput] file

Writes results to an output file instead of to standard output; in interactive sessions, use -output to write results to a file.

-pas[sword] password

Used with -user

  • Specifies a password when connecting to a remote server.
  • For access, both <password> and <user> must represent a valid entry in the security database.

-page[length] <n>

Prints column headers every <n> lines instead of the default 20.

-q[uiet]

-r[ole] <rolename>

Grants privileges of role <rolename> to <user> on connection to the database.

-s[qldialect] <n>

Interprets subsequent commands as dialect <n> until end of session or until dialect is changed by a SET SQL DIALECT statement.

  • For <n> = 1, commands are processed as in InterBase 5 or earlier.
  • For <n> = 2, elements that have different interpretations in dialect 1 and 3 are all flagged with warnings or errors to assist in migrating databases to dialect 3.
  • For <n> = 3, all statements are parsed as current InterBase SQL semantics: double quotes are delimited identifiers, DATE data type is SQL DATE, and exact numerics with precision greater than 9 are stored as INT64.

-t[erminator] <x>

Changes the end-of-statement symbol from the default semicolon (;) to <x>, where <x> is a single character or any sequence of characters; deprecated in InterBase 7.

-u[ser] <user>

Used with -password; specifies a user name when connecting to a remote server.

  • For access, both <password> and <user> must represent a valid entry in the security database.

-x

Extracts DDL for the named database; displays DDL to the screen unless redirected to a file.

-z

Displays the software version of isql.

Using Warnings

Warnings can be issued for the following conditions:

  • SQL statements with no effect
  • SQL expressions that produce different results in InterBase 5 versus InterBase 6 or later
  • API calls that will be replaced in future versions of the product
  • Pending database shutdown


Examples of Invoking isql

  • Suppose createdb.sql contains DDL statements to create a database. To execute the statements, enter:
isql -input createdb.sql
  • The following example starts an interactive connection to a remote database. The remote server, jupiter, accepts the specified user and password combination with the privileges assigned to the STAFF role:
isql -user sales -password mycode -role 'staff''jupiter:/usr/customer.ib'
  • The next example starts an interactive session but does not attach to a database. isql commands are displayed, and query results print column headers every 30 lines:
isql -echo -page 30

Exiting isql after invoking isql

To exit isql and roll back all uncommitted work, enter:

QUIT;

To exit isql and commit all work, enter:

EXIT;

Connecting to a Database Using isql

If you do not specify a database on the command-line when invoking isql, you must either connect to an existing database or create a new one. Use the CONNECT command to connect to a database and CREATE DATABASE to create a database. For the full syntax of CONNECT and CREATE DATABASE, see the Language Reference.

You can connect to either local or remote databases. The syntax is slightly different for the two:

To connect to a local database on a Windows platform, use the CONNECT command with the full path of the database as the argument. For example:

SQL> CONNECT 'C:/Embarcadero/InterBase/Database/examples/employee.ib' role 'staff';

To connect to a remote database on a Windows or UNIX server using TCP/IP, use the CONNECT command with the full node name and path of the database as the argument. Separate the node name from the database path with a colon.

Examples of connecting to remote databases:

To connect to a database on a UNIX platform named jupiter:

SQL> CONNECT 'jupiter:/usr/InterBase/examples/employee.ib';

To connect to a database on a Windows platform named venus:

SQL> CONNECT 'venus:c:/Embarcadero/InterBase/examples/database/employee.ib';
Note:
Be careful not to confuse node names and shared disks, since both are specified with a colon separator. If you specify a single letter that maps to a disk drive, it is assumed to be a drive, not a node name.
Tip:
You can use either forward slashes ( / ) or backslashes ( \ ) as directory separators. InterBase automatically converts either type of slash to the appropriate type for the server operating system.

Advance To: