Skip to main content
Previous sectionNext section

Using the SQL Shell Interface

One way to test SQL statements is to execute them from the Caché Terminal using the SQL Shell. This interactive SQL Shell allows you to execute SQL statements dynamically. The SQL Shell uses Dynamic SQL, which means that queries are prepared and executed at runtime. It accesses resources and performs operations within the current namespace.

Unless otherwise indicated, SQL Shell commands and SQL code are not case-sensitive.

The following topics are documented in this chapter:

Other Ways of Executing SQL

You can execute a single line of SQL code from the Terminal command line without invoking the SQL Shell by using the $SYSTEM.SQL.Execute() method. The following examples show how this method is used from the Terminal prompt:

SAMPLES>SET result=$SYSTEM.SQL.Execute("SELECT TOP 5 name,dob,ssn FROM Sample.Person")

SAMPLES>DO result.%Display()
Copy code to clipboard
SAMPLES>SET result=$SYSTEM.SQL.Execute("CALL Sample.PersonSets('M','MA')")

SAMPLES>DO result.%Display()
Copy code to clipboard

If the SQL statement contains an error, the Execute() method completes successfully; the %Display() method returns the error information, such as the following:

SAMPLES>DO result.%Display()

[SQLCODE: <-29>:<Field not found in the applicable tables>]
[%msg: < Field 'GAME' not found in the applicable tables^ SELECT TOP ? game ,>]
0 Rows Affected
SAMPLES>
Copy code to clipboard

The Execute() method also provides optional SelectMode, Dialect, and ObjectSelectMode parameters.

Caché supports numerous other ways to write and execute SQL code, as described in other chapters of this manual. These include:

Invoking the SQL Shell

You can use the $SYSTEM.SQL.Shell() method to invoke the SQL Shell from the Terminal prompt, as follows:

  DO $SYSTEM.SQL.Shell()
Copy code to clipboard

Alternatively, you can invoke the SQL Shell as an instantiated instance using the %SQL.Shell class, as follows:

  DO ##class(%SQL.Shell).%Go("Cache")
Copy code to clipboard

or

  SET sqlsh=##class(%SQL.Shell).%New()
  DO sqlsh.%Go("Cache")
Copy code to clipboard

Regardless of how invoked, the SQL Shell returns the SQL Shell prompt (nsp>>), where nsp is the name of the current namespace. At this prompt you can use either of the following Shell modes:

  • Single line mode: at the prompt type a line of SQL code. To end the SQL statement, press Enter. By default, this both prepares and executes the SQL code (this is known as Immediate execute mode). For a query, the result set is displayed on the terminal screen. For other SQL statements, the SQLCODE and row count values are displayed on the terminal screen.

  • Multiline mode: at the prompt press Enter. This puts you in multiline mode. You can type multiple lines of SQL code, each new line prompt indicating the line number. (A blank line does not increment the line number.) To conclude a multiline SQL statement, type GO and press Enter. By default, this both prepares and executes the SQL code. For a query, the result set is displayed on the terminal screen. For other SQL statements, the SQLCODE and row count values are displayed on the terminal screen.

    Multiline mode provides the following commands, which you type at the multiline prompt and then press Enter: L or LIST to list all SQL code entered thus far. C or CLEAR to delete all SQL code entered thus far. C n or CLEAR n (where n is a line number integer) to delete a specific line of SQL code. G or GO to prepare and execute the SQL code and return to single line mode. Q or QUIT to delete all SQL code entered thus far and return to single line mode. These commands are not case-sensitive. Issuing a command does not increment the line number of the next multiline prompt. Typing ? at the multiline prompt lists these multiline commands.

To prepare an SQL statement, the SQL Shell first validates the statement, including confirming that the specified tables exist in the current namespace and the specified fields exist in the table. If not, it displays the appropriate SQLCODE.

The SQL Shell performs SQL privilege checking; you must have the appropriate privileges to access or modify a table, field, etc. For further details, refer to the “Users, Roles, and Privileges” chapter of this manual.

If the statement is valid and you have appropriate privileges, the SQL Shell echoes your SQL statement, assigning a sequential number to it. These numbers are assigned sequentially for the duration of the terminal session, regardless of whether you change namespaces and/or exit and re-enter the SQL Shell. These assigned statement numbers permit you to recall prior SQL statements, as described below.

You can also invoke the SQL Shell from the Terminal prompt with DO Shell^%apiSQL.

To list all the available SQL Shell commands, enter ? at the SQL prompt.

To terminate an SQL Shell session and return to the Terminal prompt, enter either the Q or QUIT command or the E or EXIT command at the SQL prompt. SQL Shell commands are not case-sensitive.

The following is a sample SQL Shell session using the default parameter settings:

USER>SET $NAMESPACE="SAMPLES"
SAMPLES>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>SELECT TOP 5 Name,Home_State FROM Sample.Person ORDER BY Home_State
1. SELECT TOP 5 Name,Home_State FROM Sample.Person ORDER BY Home_State

Name               Home_State
Djokovic,Josephine W.  AK
Klingman,Aviel P.      AK
Quine, Sam X.          AK
Xiang,Robert C.        AL
Roentgen,Alexandria Q. AR

5 Row(s) Affected
----------------------------------------------
SAMPLES>>SELECT GETDATE()
2. SELECT GETDATE()

Expression_1
2009-09-29 11:41:42

1 Row(s) Affected
----------------------------------------------
SAMPLES>>QUIT

SAMPLES>
Copy code to clipboard

The following is a multiline SQL Shell session using the default parameter settings:

USER>SET $NAMESPACE="SAMPLES"
SAMPLES>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>   << entering multiline statement mode >>
        1>>SELECT TOP 5
        2>>Name,Home_State
        3>>FROM Sample.Person
        4>>ORDER BY Home_State
        5>>GO

1. SELECT TOP 5
   Name,Home_State
   FROM Sample.Person
   ORDER BY Home_State

Name               Home_State
Djokovic,Josephine W.  AK
Klingman,Aviel P.      AK
Quine, Sam X.          AK
Xiang,Robert C.        AL
Roentgen,Alexandria Q. AR

5 Row(s) Affected
----------------------------------------------
SAMPLES>>
Copy code to clipboard

GO Command

The SQL Shell GO command executes the most recent SQL statement. In single line mode, GO re-executes the SQL statement most recently executed. When in multiline mode, the GO command is used to execute the multiline SQL statement and exit multiline mode. A subsequent GO in single line mode re-executes the SQL statement.

Input Parameters

The SQL Shell supports the use of input parameters using the “?” character in the SQL statement. Each time you execute the SQL statement, you are prompted to specify values for these input parameters. You must specified these values in the same sequence that the “?” characters appear in the SQL statement: the first prompt supplies a value to the first “?”, the second prompt supplies a value to the second “?”, and so on.

There is no limit on the number of input parameters. You can use input parameters to supply values to the TOP clause, the WHERE clause, and to supply expressions to the SELECT list; you cannot use input parameters to supply column names to the SELECT list.

You can specify a host variable as an input parameter value. At the input parameter prompt, specify a value prefaced by a colon (:). This value may be a public variable, an ObjectScript special variable, a numeric literal, or an expression. The SQL Shell then prompts you with “is this a literal (Y/N)?”. Specifying N (No) at this prompt (or just pressing Enter) means that the input value is parsed as a host variable. For example, :myval would be parsed as the value of the local variable myval; :^myval would be parsed as the value of the global variable ^myval; :$HOROLOG would be parsed as the value of the $HOROLOG special variable; :3 would be parsed as the number 3; :10-3 would be parsed as the number 7. Specifying Y (Yes) at this prompt means that the input value, including the colon preface, is supplied to the input parameter as a literal.

Executing ObjectScript Commands

Within the SQL Shell, you may wish to issue an ObjectScript command. For example, to change the Caché namespace by using the SET $NAMESPACE command to the namespace containing the SQL table or stored procedure you wish to reference. You can use the SQL Shell COS command to issue an ObjectScript command line, consisting of one or more ObjectScript commands, as shown in the following example:

%SYS>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]%SYS>>COS SET oldns=$NAMESPACE SET $NAMESPACE="USER" WRITE "changed the namespace"
changed the namespace
[SQL]USER>>COS SET $NAMESPACE=oldns WRITE "reverted to old namespace"
reverted to old namespace
[SQL]%SYS>>
Copy code to clipboard

The rest of the command line following the COS command is treated as ObjectScript code. You can specify a COS command while in SQL Shell single-line mode or in SQL Shell multiline mode. The following example executes a SELECT query on a table defined in the USER namespace:

%SYS>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]%SYS>>  << entering multiline statement mode >>
        1>>COS SET $NAMESPACE="USER"

        1>>SELECT TOP 5 Name,Home_State
        2>>FROM Sample.Person
        3>>GO
   /* SQL query results */
[SQL]USER>>
Copy code to clipboard

Note that the COS statement does not advance the line count.

In SQL Shell multiline mode, a COS command is executed upon line return, but an SQL statement is not issued until you specify GO. Thus, the following example is functionally identical to the previous example:

%SYS>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]%SYS>>  << entering multiline statement mode >>
        1>>SELECT TOP 5 Name,Home_State
        2>>FROM Sample.Person
        3>>COS SET $NAMESPACE="USER" WRITE "changed namespace"
changed namespace
        3>>GO
   /* SQL query results */
[SQL]USER>>
Copy code to clipboard

The following example uses a COS command to define a host variable:

USER>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
[SQL]USER>>  << entering multiline statement mode >>
        1>>SELECT TOP :n Name,Home_State
        2>>FROM Sample.Person
        3>>COS SET n=5

        3>>GO
Copy code to clipboard

CALL Command

You can use the SQL Shell to issue the SQL CALL statement to call an SQL stored procedure, as shown in the following example:

SAMPLES>>CALL Sample.PersonSets('G','NY')
Copy code to clipboard

The SQL Shell issues an SQLCODE -428 error if the specified stored procedure does not exist in the current namespace.

The SQL Shell issues an SQLCODE -370 error if you specify more input parameters than are defined in the stored procedure. You can specify parameter values to the stored procedure using any combination of literals ('string'), host variables (:var), and input parameters (?).

  • You can use host variables in a CALL statement, as shown in the following example:

    SAMPLES>>COS SET a="G",b="NY"
    SAMPLES>>CALL Sample.PersonSets(:a,:b)
    Copy code to clipboard
  • You can use input parameters (“?” characters) in a CALL statement, as shown in the following example:

    SAMPLES>>CALL Sample.PersonSets(?,?)
    Copy code to clipboard

    The SQL Shell prompts you for a value for each of these input parameters when the CALL statement is executed.

Executing an SQL Script File

The SQL Shell RUN command executes an SQL script file. The type of script file is determined by the DIALECT setting. The DIALECT default is CACHE (Caché SQL). For further details, see RUN Command later in this chapter.

Storing and Recalling SQL Statements

Recall by Number

The SQL Shell automatically stores each successful SQL statement issued during the terminal session in a local cache and assigns it a sequential number. These numbers are used for recalling prior SQL statements during the current Terminal process. SQL Shell only assigns numbers to SQL statements that are successful; if an error occurs during preparation of an SQL statement, no number is assigned. These number assignments are not namespace-specific. The following are the available recall by number commands:

  • #: You can use # to list all of the prior cached SQL statements with their assigned numbers.

  • #n: You can recall and execute a prior SQL statement by specifying #n at the SQL Shell prompt, where n is an integer that SQL Shell assigned to that statement.

  • #0: You can recall and execute the most recently prepared SQL statement by specifying #0 at the SQL Shell prompt. #0 recalls the most recently prepared SQL statement, not necessarily the most recently executed SQL statement. Therefore, recalling and executing SQL statements has no effect on which SQL statement is recalled by #0.

Recalling an SQL statement by number does not assign a new number to the statement. SQL Shell assigns numbers sequentially for the duration of the Terminal session; exiting and re-entering the SQL Shell or changing namespaces have no effect on number assignment or the validity of prior assigned numbers.

To delete all number assignments, use #CLEAR and confirm this action at the displayed prompt. This deletes all prior number assignments and restarts number assignment with 1.

Recall by Name

You can optionally assign a name to an SQL statement, then recall the statement by name. These names are used for recalling prior SQL statements issued from any of the current user's Terminal processes. There are two ways to save and recall an SQL statement by name:

  • Save to a global using SAVEGLOBAL; recall from a global using OPEN.

  • Save to a file using SAVE; recall from a file using LOAD.

Saving to a Global

To assign a global name to the most recent SQL statement, use the SQL Shell command SAVEGLOBAL name, which can be abbreviated as SG name. You can then use the SQL Shell command OPEN name to recall the SQL statement from the global. If EXECUTEMODE is IMMEDIATE, the SQL Shell both recalls and executes the statement. If EXECUTEMODE is DEFERRED, the statement will be prepared but will not be executed until you specify the GO command.

Each time you use OPEN name to recall an SQL statement by global name, the SQL Shell assigns a new number to the statement. Both the old and new numbers remain valid for recall by number.

A name can contain any printable characters except the blank space character. Letters in a name are case-sensitive. A name can be of any length. A name is specific to the current namespace. You can save the same SQL statement multiple times with different names; all of the saved names remain valid. If you attempt to save an SQL statement using a name already assigned, SQL Shell prompts you whether you wish to overwrite the existing name, reassigning it to the new SQL statement.

Global names are assigned for the current namespace. You can list all assigned global names for the current namespace using the SQL Shell L (or LIST) command. Once assigned, a name is available to all of the current user's Terminal processes. An assigned name persists after the Terminal process that created it has ended. If there are no name assignments, LIST returns a “No statements saved” message.

To delete a global name assignment, use CLEAR name. To delete all global name assignments for the current namespace, use CLEAR and confirm this action at the displayed prompt.

Saving to a File

To assign a file name to the most recent SQL statement, use the SQL Shell command SAVE name. You can then use the SQL Shell command LOAD name to recall the SQL statement. If EXECUTEMODE is IMMEDIATE, the SQL Shell both recalls and executes the statement. Each time you use LOAD name to recall an SQL statement by file name, the SQL Shell assigns a new number to the statement. Both the old and new numbers remain valid for recall by number.

A name can contain any printable characters except the blank space character. Letters in a name are case-sensitive. A name can be of any length. A name is specific to the current namespace. You can save the same SQL statement multiple times with different names; all of the saved names remain valid. If you attempt to save an SQL statement using a name already assigned, SQL Shell prompts you whether you wish to overwrite the existing name, reassigning it to the new SQL statement.

Names are assigned for the current namespace. Once assigned, a name is available to all of the current user's Terminal processes. An assigned name persists after the Terminal process that created it has ended.

SQL Shell Parameters

The SQL Shell provides the following configurable parameters:

  • Commandprefix: (TSQL) specifies a prefix for SQL Shell commands.

  • Dialect: (TSQL) specifies the version of SQL to use.

  • Displayfile

  • Displaymode: specifies the format for query output.

  • Displaypath

  • Displaytranslate[table]

  • Echo: specifies whether result set data should be echoed to the Terminal.

  • Executemode: specifies whether or not to defer SQL execution.

  • Log: specifies that Shell activity should be logged to a file.

  • Messages: specifies whether error messages or query metrics and the cached query name should be displayed to the Terminal.

  • Path: sets the schema search path for an unqualified table name.

  • Selectmode: specifies whether data should be displayed in Logical, ODBC, or Display mode. Also determines if input data is converted from a display format to logical storage format.

The parameters labelled (TSQL) are principally used for executing Sybase or MSSQL Transact-SQL code from the SQL Shell. They are described in the “Transact-SQL Support” section at the end of this chapter.

Displaying, Setting, and Saving SQL Shell Parameters

SQL Shell configuration parameters are specific to the current SQL Shell invocation on the current Terminal process. Settings apply across namespaces. However, if you exit the SQL Shell, all SQL Shell parameters reset to default values. Caché provides system default values; you can establish different default values using SET SAVE, as described below.

The SQL Shell SET command (with no arguments) displays the current shell configuration parameters, as shown in the following example. In this example, the SET shows the system default values, which are the values established when you invoke the SQL Shell:

USER>>SET
 
commandprefix = ""
dialect = CACHE
displayfile =
displaymode = currentdevice
displaypath =
displaytranslatetable =
echo = on
executemode = immediate
log = off
messages = on
path = SQLUser
selectmode = logical
USER>>
Copy code to clipboard

To display the current setting for a single configuration parameter, specify SET param. For example, SET SELECTMODE returns the current selectmode setting.

You can use the SQL Shell SET command to set a shell configuration parameter. A set value persists for the duration of the SQL Shell invocation; each time you invoke the SQL Shell, the parameters reset to default values. SET can use either of the following syntax forms:

SET param value
SET param = value

Both param and value are not case-sensitive. Spaces are permitted, but not required, before and after the equal sign.

The SQL Shell SET SAVE command saves the current shell configuration parameter settings as the user defaults. These defaults are applied to all subsequent SQL Shell invocations from the current process. They are also applied as SQL shell defaults to any subsequently invoked SQL Shell on any user Terminal process. They remain in effect until specifically reset. Using SET SAVE does not affect currently running SQL Shell invocations.

The SQL Shell SET CLEAR command clears (resets to system defaults) the current shell configuration parameter settings for the current process. Caché applies this reset to defaults to subsequent SQL Shell invocations by the current process, or any new Terminal process invoked by the current user. SET CLEAR does not affect currently running SQL Shell invocations.

Setting DISPLAYMODE and DISPLAYTRANSLATE

You can use SET DISPLAYMODE to specify the format used to display query data, as shown in the following example:

SAMPLES>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>SET DISPLAYMODE XML

displaymode = xml
SAMPLES>>
Copy code to clipboard

The DISPLAYMODE default is CURRENTDEVICE, which displays the query data on the Terminal in TXT format. You can specify SET DISPLAYMODE = CUR to restore the CURRENTDEVICE default.

The other available options are TXT, HTML, PDF, XML, and CSV. The selection of a format determines the file type. Caché creates a file of this type, writes the query data to the file, and, when possible, launches the appropriate program to display this query data file. For all options except TXT, a second file is created to record result set messages. By default, SQL Shell creates these files in the Caché mgr\Temp\ directory and assigns a randomly generated file name with the appropriate file type suffix. The generated Message file name is the same as the data file name, except for the appended string “Messages”. For the HTML, PDF, and XML options, the Messages file has the same file type suffix as the query data file. For the CSV option, the Messages file has the TXT file type suffix.

The following is an example of the files created when DISPLAYMODE = TXT:

C:\InterSystems\Cache\mgr\Temp\sGm7qLdVZn5VbA.txt
C:\InterSystems\Cache\mgr\Temp\sGm7qLdVZn5VbAMessages.txt
Copy code to clipboard

Each time you run a query, the SQL Shell creates a new pair of files with randomly generated file names.

If DISPLAYMODE is TXT or CSV, you can optionally specify the name of a translate table to apply when performing format conversion. You can specify either SET DISPLAYTRANSLATE or SET DISPLAYTRANSLATETABLE. Translate table name values are case-sensitive.

If DISPLAYMODE is set to a value other than CURRENTDEVICE, any query result set data containing a control character results in a generated Warning message. Generally, control characters only appear in query result set data when it is in Logical mode. For example, data in a List structure contains control characters when displayed in Logical mode. For this reason, it is recommended that when you set DISPLAYMODE to a value other than CURRENTDEVICE that you also set SELECTMODE to either DISPLAY or ODBC.

Setting DISPLAYFILE and DISPLAYPATH

If DISPLAYMODE is set to a value other than CURRENTDEVICE, you can specify the target file location using the DISPLAYFILE and DISPLAYPATH parameters:

  • DISPLAYFILE: set this parameter to a simple file name with no suffix; for example, SET DISPLAYFILE = myfile. You can also set this parameter to a partially-qualified path, which Caché appends to the DISPLAYPATH value or the default directory, creating subdirectories as needed; for example, SET DISPLAYFILE = mydir\myfile. If DISPLAYPATH is set, the system creates a file with this file name in the specified directory; if DISPLAYPATH is not set, the system creates a file with this file name in the Caché mgr\Temp\ directory.

  • DISPLAYPATH: set this parameter to an existing fully-qualified directory path structure ending in a slash (“/”) or backslash (“\”), depending on operating system platform. If DISPLAYFILE is set, the system creates a file with the DISPLAYFILE name in this directory; if DISPLAYFILE is not set, the system creates a file with a randomly-generated name in this directory. If the DISPLAYPATH directory does not exist, Caché ignores DISPLAYPATH and DISPLAYFILE settings and instead uses the default directory and default randomly-generated file name.

When necessary, the system automatically adds a slash (or backslash) to the end of your DISPLAYPATH value and/or removes a slash (or backslash) from the beginning of your DISPLAYFILE value to create a valid fully-qualified directory path.

The following example sets DISPLAYMODE, DISPLAYFILE, and DISPLAYPATH:

SAMPLES>>SET DISPLAYMODE XML

displaymode = xml
SAMPLES>>SET DISPLAYFILE = myfile

displayfile = myfile
SAMPLES>>SET DISPLAYPATH = C:\temp\mydir\

displaypath = C:\temp\mydir\
SAMPLES>> 
Copy code to clipboard

When you execute a query the SQL Shell will generate the following files. The first contains the query data. The second contains any messages resulting from the query execution:

C:\temp\mydir\myfile.xml
C:\temp\mydir\myfileMessages.xml
Copy code to clipboard

If you specify neither DISPLAYFILE or DISPLAYPATH, the system creates files in the Mgr\Temp\ directory for your Caché installation (for example, C:\InterSystems\Cache\Mgr\Temp\) with a randomly generated file name.

If DISPLAYMODE is not set to CURRENTDEVICE, each time you run a query with DISPLAYFILE set, any existing data in the named file and the corresponding Messages file is replaced by the new query data. Each time you run a query with DISPLAYFILE not set, the SQL Shell creates a new file with a randomly generated file name and a new corresponding Messages file.

If DISPLAYMODE is set to CURRENTDEVICE, the DISPLAYFILE and DISPLAYPATH parameters have no effect.

Setting EXECUTEMODE

The SQL Shell supports immediate and deferred SQL statement execution. Immediate execution prepares and executes the specified SQL statement when you press Enter. Deferred execution prepares the statement when you press Enter, but does not execute it until you specify GO at the SQL prompt.

The available options are SET EXECUTEMODE IMMEDIATE (the default), SET EXECUTEMODE DEFERRED, and SET EXECUTEMODE to display the current mode setting. The following example sets the execute mode:

SAMPLES>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>SET EXECUTEMODE DEFERRED

Executemode = deferred
SAMPLES>>
Copy code to clipboard

Deferred execution allows you to prepare multiple SQL queries, then recall them by name or number for execution. To execute a prepared SQL statement, recall the desired statement (from the appropriate namespace) then specify GO.

The following example shows the preparation of three queries in Deferred mode. The first two are saved and assigned a recall name; the third is not assigned a name, but can be recalled by number:

SAMPLES>>SELECT TOP 5 Name,Home_State FROM Sample.Person
1. SELECT TOP 5 Name,Home_State FROM Sample.Person
SAMPLES>>SAVE 5sample
Query saved as: 5sample
SAMPLES>>SELECT TOP 5 Name,Home_State FROM Sample.Person ORDER BY Home_State
2. SELECT TOP 5 Name,Home_State FROM Sample.Person ORDER BY Home_State
SAMPLES>>SAVE 5ordered
Query saved as: 5ordered
SAMPLES>>SELECT Name,Home_State FROM Sample.Person ORDER BY Home_State
3. SELECT Name,Home_State FROM Sample.Person ORDER BY Home_State
SAMPLES>>
Copy code to clipboard

The following example shows the deferred mode execution of two of the queries defined in the previous example. Note that this example recalls one query by name (upon recall the SQL Shell gives it a new number), and one query by number:

SAMPLES>>OPEN 5ordered
SELECT TOP 5 Name,Home_State FROM Sample.Person ORDER BY Home_State
4. SELECT TOP 5 Name,Home_State FROM Sample.Person ORDER BY Home_State
----------------------------------------------
SAMPLES>>GO

Name               Home_State
Djokovic,Josephine W.  AK
Klingman,Aviel P.      AK
Quine, Sam X.          AK
Xiang,Robert C.        AL
Roentgen,Alexandria Q. AR

5 Row(s) Affected
----------------------------------------------
SAMPLES>>#3
SELECT Name,Home_State FROM Sample.Person ORDER BY Home_State
3. SELECT Name,Home_State FROM Sample.Person ORDER BY Home_State
----------------------------------------------
SAMPLES>>GO
.
.
.
Copy code to clipboard

Setting ECHO

You can use SET ECHO to specify whether to echo the query results to the SQL Shell. If you specify SET ECHO=OFF, the query is prepared, a cached query is defined, and the query is executed. No query results are displayed to the Terminal. This is shown in the following example:

[SQL]USER>>set echo=off
 
echo = off
[SQL]USER>>SELECT Name,Age FROM Sample.MyTest
4.      SELECT Name,Age FROM Sample.MyTest
 
 
statement prepare time(s)/globals/lines/disk: 0.0002s/5/155/0ms
          execute time(s)/globals/lines/disk: 0.0001s/0/105/0ms
                          cached query class: %sqlcq.USER.cls3
---------------------------------------------------------------------------
[SQL]USER>>
Copy code to clipboard

If you specify SET ECHO=ON (the default) the query results are displayed to the Terminal. This is shown in the following example:

[SQL]USER>>set echo=on
 
echo = on
[SQL]USER>>SELECT Name,Age FROM Sample.MyTest
5.      SELECT Name,Age FROM Sample.MyTest
 
Name    Age
Fred Flintstone 41
Wilma Flintstone        38
Barney Rubble   40
Betty Rubble    42
 
4 Rows(s) Affected
statement prepare time(s)/globals/lines/disk: 0.0002s/5/155/0ms
          execute time(s)/globals/lines/disk: 0.0002s/5/719/0ms
                          cached query class: %sqlcq.USER.cls3
---------------------------------------------------------------------------
[SQL]USER>>
Copy code to clipboard

SET ECHO is only meaningful if DISPLAYMODE=CURRENTDEVICE (the default).

SET ECHO and SET MESSAGES specify what is displayed on the Terminal; they do not affect the prepare or execution of the query. If both SET MESSAGES=OFF and SET ECHO=OFF, the query is prepared, a cached query is created, and query execution creates a query result set, but nothing is returned to the Terminal.

Setting MESSAGES

You can use SET MESSAGES to specify whether to display the query error message (if unsuccessful), or query execution information (if successful):

  • If query execution is unsuccessful: If you specify SET MESSAGES=OFF, nothing is displayed to the Terminal. If you specify SET MESSAGES=ON (the default) the query error message is displayed, such as the following: ERROR #5540: SQLCODE: -30 Message: Table 'SAMPLE.NOTABLE' not found.

  • If query execution is successful: If you specify SET MESSAGES=OFF, only the query results and the line n Rows(s) Affected are displayed to the Terminal. If you specify SET MESSAGES=ON (the default) the query results and the line n Rows(s) Affected are followed by the statement prepare metrics, the statement execution metrics, and the name of the generated cached query.

    Prepare and Execute metrics are measured in elapsed time (in fractional seconds), total number of global references, total number of lines executed, and disk read latency (in milliseconds).

The information displayed when SET MESSAGES=ON is not changed by setting DISPLAYMODE. Some DISPLAYMODE options create both a query result set file and a messages file. This messages file contains result set messages, not the query prepare and execute messages displayed to the Terminal when SET MESSAGES=ON.

SET MESSAGES and SET ECHO specify what is displayed on the Terminal; they do not affect the prepare or execution of the query. If both SET MESSAGES=OFF and SET ECHO=OFF, a successful query is prepared, a cached query is created, and query execution creates a query result set, but nothing is returned to the Terminal.

Setting LOG

You can use SET LOG to specify whether to log SQL Shell activity to a file. The available options are:

  • SET LOG OFF: The default. Caché does not log activity for the current SQL Shell.

  • SET LOG ON: Caché logs SQL Shell activity to the default log file.

  • SET LOG pathname: Caché logs SQL Shell activity to the file specified by pathname.

SET LOG ON creates a log file in Cache\mgr\namespace, where namespace is the name of the current namespace for the process. This default log file is named xsqlnnnn.log, where nnnn is the process ID (pid) number for the current process.

By default, a log file is specific to the current process and the current namespace. To log SQL Shell activity from multiple processes and/or from multiple namespaces in the same log, specify SET LOG pathname for each process and/or namespace using the same pathname.

A log file can be suspended and resumed. Once a log file has been created, SET LOG OFF suspends writing to that log file. SET LOG ON resumes writing to the default log file. Log restarted: date time is written to the log file when logging resumes. SET LOG ON always activates the default log file. Thus, if you suspend writing to a specified pathname log file, you must specify SET LOG pathname when resuming.

Activating a log file creates a copy of SQL Shell activity displayed on the terminal; it does not redirect SQL Shell terminal output. The SQL Shell log records SQL errors for failed SQL execution and the SQL code and resulting row count for successful SQL execution. The SQL Shell log does not record result set data.

If a log is already active, specifying SET LOG ON has no effect. If a log is already active, specifying SET LOG pathname suspends the current log and activates the log specified by pathname.

Setting PATH

You can use SET PATH schema to set the schema search path, which SQL uses to supply the correct schema name for an unqualified table name. schema can be a single schema name, or a comma-separated list of schema names, as shown in the following example:

[SQL]USER>>SET PATH cinema,sample,user
Copy code to clipboard

SET PATH with no argument deletes the current schema search path, reverting to the system-wide default schema name.

If SET PATH schema is not specified, or the table is not found in the specified schemas, SQL Shell uses the system-wide default schema name. For further details on schema search paths, refer to the #SQLCompile Path macro in the “ObjectScript Macros and the Macro Preprocessor” chapter of Using Caché ObjectScript.

Setting SELECTMODE

You can use SET SELECTMODE to specify the mode used to display query data.

SAMPLES>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>SET SELECTMODE DISPLAY

selectmode = display
SAMPLES>>
Copy code to clipboard

The available options are DISPLAY, LOGICAL, and ODBC. LOGICAL is the default. To determine the current mode, specify SET SELECTMODE without a value:

SAMPLES>>SET SELECTMODE

selectmode = logical
SAMPLES>>
Copy code to clipboard

%List data is encoded using non-printing characters. Therefore, when selectmode=logical, SQL Shell displays a %List data value as a $LISTBUILD statement, such as the following: $lb("White","Green"). Time data type data supports fractional seconds. Therefore, when selectmode=odbc, SQL Shell displays fractional seconds, which does not correspond to the ODBC standard. The actual ODBC TIME data type truncates fractional seconds.

For further details on SelectMode options, refer to “Data Display Options” in the “Caché SQL Basics” chapter of this book.

You can also use SET SELECTMODE to specify whether input data will be converted from display format to logical storage format. For this data conversion to occur, the SQL code must have been compiled with a select mode of RUNTIME. At execution time, SET SELECTMODE must be set to LOGICAL (the default). For further details, refer to the INSERT or UPDATE statement in the Caché SQL Reference.

SQL Metadata and Performance Metrics

Displaying Metadata, Show Plan, and Show Statement

The SQL Shell supports the following additional commands:

  • M or METADATA to display metadata information about the current query. For details, refer to Select-item Metadata in the “Using Dynamic SQL” chapter.

  • SHOW PLAN, SHOW PL (or simply SHOW) to display show plan information about the current query. The show plan can be used for debugging and optimizing the performance of a query. It specifies how the query executes, including the use of indexes and a cost value for the query. A show plan can be returned for the following statements: SELECT, DECLARE, non-cursor UPDATE or DELETE, and INSERT...SELECT.

  • SHOW STATEMENT or SHOW ST to display the prepared SQL statement. This information consists of the Implementation Class, the Arguments (a comma-separated list of the actual arguments, such as the TOP clause and WHERE clause argument values), and the Statement Text.

For further details on Caché SQL Shell commands, enter ? at the SQL prompt, or refer to %SYSTEM.SQL.Shell() in the InterSystems Class Reference.

For further details on interpreting a query plan, see “Interpreting an SQL Query Plan” in the Caché SQL Optimization Guide.

SQL Shell Performance

Following the successful execution of an SQL statement, the SQL Shell displays four statement prepare values (times(s)/globals/lines/disk) and four statement execute values (times(s)/globals/lines/disk):

  • The statement prepare time is the time it took to prepare the dynamic statement. This includes the time it took to generate and compile the statement. It includes the time it took to find the statement in the statement cache. Thus, if a statement is executed, then recalled by number or recalled by name, the prepare time on the recalled statement is near zero. If a statement is prepared and executed, then re-executed by issuing the GO command, the prepare time on the re-execution is zero.

  • The elapsed execute time is the elapsed time from the call to %Execute() until the return from %Display(). It does not include wait time for input parameter values.

The statement globals is the count of global references, lines is the count of lines of code executed, and disk is the disk latency time in milliseconds. The SQL Shell keeps separate counts for the Prepare operation and the Execute operation.

These performance values are only displayed when DISPLAYMODE is set to currentdevice, and MESSAGES is set to ON. These are the SQL Shell default settings.

Transact-SQL Support

By default, the SQL Shell executes Caché SQL code. However, the SQL Shell can be used to execute Sybase or MSSQL code.

Setting DIALECT

By default, the SQL Shell parses code as Caché SQL. You can use SET DIALECT to configure the SQL Shell to execute Sybase or MSSQL code. To change the current dialect, SET DIALECT to Sybase, MSSQL, or Cache. The default is Dialect=Cache.

he following is an example of the executing a MSSQL program from the SQL Shell:

SAMPLES>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>SET DIALECT MSSQL

dialect = MSSQL
SAMPLES>>SELECT TOP 5 name + '-' + ssn FROM Sample.Person
1.      SELECT TOP 5 name + '-' + ssn FROM Sample.Person
 
Expression_1
Zweifelhofer,Maria H.-559-20-7648
Vonnegut,Bill A.-552-41-2071
Clinton,Terry E.-757-30-8013
Bachman,Peter U.-775-59-3756
Avery,Emily N.-833-18-9563
 
5 Rows(s) Affected
statement prepare time: 0.2894s, elapsed execute time: 0.0467s.
---------------------------------------------------------------------------
SAMPLES>>
Copy code to clipboard

The Sybase and MSSQL dialects support a limited subset of SQL statements in these dialects. They support the SELECT, INSERT, UPDATE, and DELETE statements. They support the CREATE TABLE statement for permanent tables, but not for temporary tables. CREATE VIEW is supported. CREATE TRIGGER and DROP TRIGGER are supported. However, this implementation does not support transaction rollback should the CREATE TRIGGER statement partially succeed but then fail on class compile. CREATE PROCEDURE and CREATE FUNCTION are supported.

Setting COMMANDPREFIX

You can use SET COMMANDPREFIX to specify a prefix (usually a single character) that must be appended to subsequent SQL Shell commands. This prefix is not used on SQL statements issued from the SQL Shell prompt. The purpose of this prefix is to prevent ambiguity between SQL Shell commands and SQL code statements. For example, SET is an SQL Shell command; SET is also an SQL code statement in Sybase and MSSQL.

By default, there is no command prefix. To establish a command prefix, SET COMMANDPREFIX=prefix, with prefix specified without quotation marks. To revert to having no command prefix, SET COMMANDPREFIX="". The following example shows the command prefix / (the slash character) being set, used, and reverted:

SAMPLES>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>SET COMMANDPREFIX=/
 
commandprefix = /
SAMPLES>>/SET LOG=ON
 
log = xsql4148.log
SAMPLES>>  << entering multiline statement mode >>
        1>>SELECT TOP 3 Name,Age
        2>>FROM Sample.Person
        3>>/GO
9.      SELECT TOP 3 Name,Age
        FROM Sample.Person
 
Name    Age
Frith,Jose M.   13
Finn,William D. 15
Ximines,Uma Y.  44
 
3 Rows(s) Affected
statement prepare time: 0.0010s, elapsed execute time: 0.0014s.
---------------------------------------------------------------------------
SAMPLES>>/SET COMMANDPREFIX
 
commandprefix = /
SAMPLES>>/SET COMMANDPREFIX=""
 
commandprefix = ""
SAMPLES>>SET COMMANDPREFIX
 
commandprefix =
SAMPLES>>
Copy code to clipboard

When a command prefix is set, the command prefix is required for all SQL Shell commands, except ?, #, and GO; these three SQL Shell commands can be issued with or without the command prefix.

The SQL Shell displays the current command prefix as part of the SQL Shell initialization, when you issue a SET or a SET COMMANDPREFIX command, and at the end of the ? commands option display.

RUN Command

The SQL Shell RUN command executes an SQL script file. You must SET DIALECT before issuing a RUN command to specify either Caché SQL, Sybase TSQL, or Microsoft SQL (MSSQL); the default dialect is Caché SQL. You can either invoke RUN scriptname or just invoke RUN and be prompted for the script file name.

RUN loads the script file, then prepares and executes each statement contained in the file. Statements in the script file must be delimited, usually either with a GO line, or with a semicolon (;). The RUN command prompts you to specify the delimiter.

The SQL script file results are displayed on the current device and, optionally, in a log file. Optionally, a file containing statements that failed to prepare can be produced.

The RUN command returns prompts to specify these options, as shown in the following example:

USER>>SET DIALECT=Sybase

dialect = Sybase
USER>>RUN
 
Enter the name of the SQL script file to run: SybaseTest
 
Enter the file name that will contain a log of statements, results and errors (.log): SyTest.log
     SyTest.log
 
Many script files contain statements not supported by Cache' SQL.
Would you like to log the statements not supported to a file so they
can be dealt with manually, if applicable?   Y=> y
Enter the file name in which to record non-supported statements (_Unsupported.log): SyTest_Unsupported.log
 
Please enter the end-of-statement delimiter (Default is 'GO'):  GO=>
 
Pause how many seconds after error?   5 => 3
 
Sybase Conversion Utility (v3)
Reading source from file:
Statements, results and messages will be logged to: SyTest.log
.
.
.
Copy code to clipboard

TSQL Examples

The following SQL Shell example creates a Sybase procedure AvgAge. It executes this procedure using the Sybase EXEC command. It then changes the dialect to Caché and executes the same procedure using the Caché SQL CALL command.

SAMPLES>>SET DIALECT Sybase
 
dialect = Sybase
SAMPLES>>  << entering multiline statement mode >>
        1>>CREATE PROCEDURE AvgAge
        2>>AS SELECT AVG(Age) FROM Sample.Person
        3>>GO
12.     CREATE PROCEDURE AvgAge
        AS SELECT AVG(Age) FROM Sample.Person
 
 
statement prepare time: 0.1114s, elapsed execute time: 0.4364s.
---------------------------------------------------------------------------
SAMPLES>>EXEC AvgAge
13.     EXEC AvgAge
 
 
 
Dumping result #1
Aggregate_1
44.35
 
1 Rows(s) Affected
statement prepare time: 0.0956s, elapsed execute time: 1.1761s.
---------------------------------------------------------------------------

SAMPLES>>SET DIALECT=Cache
 
dialect = CACHE
SAMPLES>>CALL AvgAge()
14.     CALL AvgAge()
 
 
 
Dumping result #1
Aggregate_1
44.35
 
1 Rows(s) Affected
statement prepare time: 0.0418s, elapsed execute time: 0.0040s.
---------------------------------------------------------------------------
SAMPLES>>
Copy code to clipboard