isql

isql is an interactive ODBC test utility that you can use to test your DSNs for their connectivity to databases and to execute SQL statements once you are connected to a database. The 32-bit utility is named isql and is installed with PSQL Server 32-bit, PSQL Client 32-bit, and PSQL 64-bit. The 64-bit utility is named isql64 and is installed with PSQL Server 64-bit and with PSQL Client 64-bit.

The only difference between the two utilities is the type of DSN to which they can connect. By default, dsnadd creates DSNs that are accessible to both 32-bit and 64-bit applications (the DSNs specify the driver description “Pervasive ODBC Interface”). You can test connectivity of such DSNs with isql or isql64. See PSQL ODBC Driver Descriptions for Linux or OS X.

Deprecated, legacy-style DSNs are accessible only to 32-bit applications. Therefore, you can test connectivity of such DSNs only with isql. See FAQs About ODBC and DSN Support for Linux or OS X.

For example, to connect to the DEMODATA sample database included with PSQL, run isql (or isql64) with the DSN as the first parameter: isql DEMODATA or isql64 DEMODATA. (The DSN for DEMODATA specifies the driver description “Pervasive ODBC Interface” so either utility can test its DSN connectivity.)

The utility puts you in an interactive state with the database. From that state, you can query the database (such as SELECT * FROM Department).

To enable security on a database using isql, first connect to the database as the Master user, then use the SET SECURITY statement in SQL to set the Master user password. For example:

isql DEMODATA Master

SET SECURITY = password

See SET SECURITY in SQL Engine Reference.

To connect to a secured database, pass the user name and password as the second and third parameters respectively to the isql utility. For example, to connect to DEMODATA as user Master using password vforge, enter isql64 DEMODATA Master vforge or isql DEMODATA Master vforge.

<isql | isql64> DSN [UID [PWD]] [options]

DSN is the data source name for the database to which you want to connect. Always required.

UID is the user name to connect to the PSQL SQL database engine for a secured database. Required only for a secured database.

PWD is the password for UID. Required only if UID used.

options is one or more of the options as defined below.


-b	Suppress prompts for batch processing. See Notes.
-c	Display column names on first row (use with -d)
-dx	Delimit columns with character x.
-llocnname	Set locale to locname.
-mn	Limit column display width to n characters.
-v	Display verbose explanations for errors and warnings.
--version	Display version of unixODBC in use.
-w	Wrap results in an HTML table.
-x0xHH	Delimit columns with HH, where x is in hex. For example, 0x09 is the tab character.

Once you are in interactive mode, the following commands may be used.


sql-statement	A valid SQL statement to execute against the database to which isql or isql64 is connected.
help [tablename]	Display column information. The output is the same as for the dbo.fSQLColumns catalog function. See dbo.fSQLColumns in SQL Engine Reference. If tablename is omitted, the output is for all tables in the database, including system tables.
quit	Exits interactive mode.

By default, isql and isql64 display prompt information when in interactive mode (such as Connected!, sql-statement, help [tablename], and quit). For redirection and piping of output to a file, you may not want the prompt information. The -b option suppresses the output of the prompt information.

Isql and isql64 support redirection and piping for input and output. In addition, both utilities can process a file containing multiple SQL statements. Each statement must end with a carriage return/line feed. The last line of the file must be a blank line. See Examples.

The following example connects to an unsecured database named acctspay that will be accessed by a 64-bit ODBC application running on a 64-bit client:

isql64 acctspay

The DSN is also named acctspay and specifies the ODBC driver description “Pervasive ODBC Interface.”

The following example connects to a secured database named payroll as user Master with password j77b99:

isql payrollsecdb Master j77b99

The DSN is named payrollsecdb”and specifies the deprecated, legacy-style ODBC driver description “Pervasive ODBC Engine Interface.” You could also use isql to test the DSN if the DSN had specified the ODBC driver description “Pervasive ODBC Interface.”

The following example shows how to process multiple SQL statements. Suppose that you want to run the following two queries against the DEMODATA sample database:

select count(*) from billing
select count(*) from person

Create a file (named two-queries.sql for discussion purposes) with the two lines. Include a blank line as the last line in the file.

Run the following command:

cat two-queries.sql | isql demodata -b

The result is as follows:

SQLRowCount returns 1

SQLRowCount returns 1

Note the use of the -b option to suppress the prompting information from the output. Without the -b option, the result is as follows:

+---------------------------------------+

| sql-statement |

| help [tablename] |

+---------------------------------------+

SQL> +------------+

SQLRowCount returns 1

SQL> +------------+

SQLRowCount returns 1