One way to test SQL statements is to execute them from the 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.
Invoking the SQL Shell
You can use the $SYSTEM.SQL.Shell()Opens in a new tab method to invoke the SQL Shell from the Terminal prompt, as follows:
DO $SYSTEM.SQL.Shell()
Alternatively, you can invoke the SQL Shell as an instantiated instance using the %SQL.ShellOpens in a new tab class, as follows:
DO ##class(%SQL.Shell).%Go("IRIS")
or
SET sqlsh=##class(%SQL.Shell).%New()
DO sqlsh.%Go("IRIS")
Regardless of how invoked, the SQL Shell returns the SQL Shell prompt, which displays as follows:
Where [SQL] is a literal indicating that you are in the SQL Shell, termprompt is the configured terminal prompt, and >> is a literal indicating the SQL command line. By default, the SQL Shell prompt appears as follows [SQL]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 SQL Users, Roles, and Privileges.
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.
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 Ctrl-C command is disabled while in the SQL Shell.
The following is a sample SQL Shell session using the default parameter settings:
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>>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
----------------------------------------------
[SQL]USER>>SELECT GETDATE()
2. SELECT GETDATE()
Expression_1
2009-09-29 11:41:42
1 Row(s) Affected
----------------------------------------------
[SQL]USER>>QUIT
USER>
The following is a multiline SQL Shell session using the default parameter settings:
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 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
----------------------------------------------
[SQL]USER>>
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 prior multiline SQL statement.
Executing ObjectScript Commands
Within the SQL Shell, you may wish to issue an ObjectScript command. For example, to change the InterSystems IRIS 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 ! command or OBJ command to issue an ObjectScript command line, consisting of one or more ObjectScript commands. (OBJ is an abbreviation for OBJECTSCRIPT.) The !, OBJ, and OBJECTSCRIPT commands are synonyms. Use of these commands is 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>>! SET oldns=$NAMESPACE SET $NAMESPACE="USER" WRITE "changed the namespace"
changed the namespace
[SQL]USER>>OBJ SET $NAMESPACE=oldns WRITE "reverted to old namespace"
reverted to old namespace
[SQL]%SYS>>
The rest of the command line following the OBJ command is treated as ObjectScript code. A space is not required between the ! and the ObjectScript command line. You can specify an OBJ 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>>OBJ SET $NAMESPACE="USER"
1>>SELECT TOP 5 Name,Home_State
2>>FROM Sample.Person
3>>GO
/* SQL query results */
[SQL]USER>>
Note that the OBJ statement does not advance the SQL line count.
In SQL Shell multiline mode, an OBJ 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>>OBJ SET $NAMESPACE="USER" WRITE "changed namespace"
changed namespace
3>>GO
/* SQL query results */
[SQL]USER>>
The following example uses an OBJ 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>>OBJ SET n=5
3>>GO
Browsing the Namespace
The SQL Shell supports a BROWSE command that displays the schemas, tables, and views defined in or accessible from the current namespace. The display consists of several levels of prompts. To return to the previous prompt level, press the Return key at a prompt. Names are case-sensitive.
-
Type BROWSE at the SQL Shell prompt to list the schemas in the current namespace.
-
At the Schema: prompt, select a schema by name or by number. This lists the tables and views in the schema.
-
At the Table/View: prompt, select a table (T) or view (V) by name or by number. This displays table information then presents a list of options.
-
At the Option: prompt, select an option by number. You can use this option to list the fields or maps defined for the table.
Specify option 1 (Fields by name) or option 2 (fields by number) to display the Field: prompt. Specify option 3 (maps) to display the Map: prompt.
-
At the Field: prompt, select a field by number or by name, or specify * to list all fields. This lists detailed field information.
At the Map: prompt, select a map by number or by name, or specify * to list all maps. This lists detailed map information.
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:
[SQL]USER>>CALL Sample.PersonSets('G','NY')
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:
[SQL]USER>>OBJ SET a="G",b="NY"
[SQL]USER>>CALL Sample.PersonSets(:a,:b)
-
You can use input parameters (“?” characters) in a CALL statement, as shown in the following example:
[SQL]USER>>CALL Sample.PersonSets(?,?)
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 IRIS (InterSystems SQL). For further details, see RUN Command.
Configuring the SQL Shell
-
You can configure the SQL Shell defaults system-wide using the Management Portal.
-
You can configure an individual SQL Shell using SQL Shell Parameters. Changing an SQL Shell parameter overrides the system-wide default for the current invocation of the SQL Shell; it does not change the system-wide SQL Shell default value.
The following are the available SQL Shell configuration options, the corresponding shell parameters, and the default settings:
The parameters labelled (TSQL) are principally used for executing Sybase or MSSQL Transact-SQL code from the SQL Shell. They are described in Transact-SQL Support.
Configuring SQL Shell System-wide Defaults
Go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, SQL. Select the SQL Shell tab. View and set the current default settings for SQL Shell system-wide.
If you change one or more configuration settings, this is indicated by an asterisk (*) in the upper left-hand corner of the screen immediately following the Management Portal path. For example, System > Configuration > SQL *. Press the Save button to accept the changes. The changes are activated and the asterisk disappears.
Configuring Parameters for an SQL Shell
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 system-wide default values. InterSystems IRIS provides system default values; you can establish different default values for the current process 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:
[SQL]USER>>SET
commandprefix = ""
dialect = IRIS
displayfile =
displaymode = currentdevice
displaypath =
displaytranslatetable =
echo = on
executemode = immediate
log = off
messages = on
path = SQLUser
selectmode = logical
[SQL]USER>>
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 a Terminal process invoked by that user. 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. InterSystems IRIS 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.
Neither SET SAVE nor SET CLEAR change the system-wide SQL Shell default settings configured and displayed using the Management Portal.
Setting COLALIGN
You can use SET COLALIGN to specify the whitespace format used to display query resultset data and column headers. The available options are:
-
delimiter: Resultset header/data columns will be aligned based on the standard delimiter (tab). This is the default.
-
header: Resultset header/data columns will be aligned based on the length of the column header and the standard delimiter (tab).
-
data: Resultset header/data columns will be aligned based on the precision/length of the column data property and the standard delimiter (tab).
For further details, see %Display() method.
Setting DISPLAYMODE and DISPLAYTRANSLATETABLE
You can use SET DISPLAYMODE to specify the format used to display query data, as shown in the following example:
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>>SET DISPLAYMODE XML
displaymode = xml
[SQL]USER>>
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. InterSystems IRIS 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 InterSystems IRIS 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\IRIS\mgr\Temp\sGm7qLdVZn5VbA.txt
C:\InterSystems\IRIS\mgr\Temp\sGm7qLdVZn5VbAMessages.txt
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 InterSystems IRIS 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 InterSystems IRIS 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, InterSystems IRIS 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:
[SQL]USER>>SET DISPLAYMODE XML
displaymode = xml
[SQL]USER>>SET DISPLAYFILE myfile
displayfile = myfile
[SQL]USER>>SET DISPLAYPATH C:\temp\mydir\
displaypath = C:\temp\mydir\
[SQL]USER>>
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
If you specify neither DISPLAYFILE or DISPLAYPATH, the system creates files in the Mgr\Temp\ directory for your InterSystems IRIS installation (for example, C:\InterSystems\IRIS\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:
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>>SET EXECUTEMODE DEFERRED
Executemode = deferred
[SQL]USER>>
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:
[SQL]USER>>SELECT TOP 5 Name,Home_State FROM Sample.Person
1. SELECT TOP 5 Name,Home_State FROM Sample.Person
[SQL]USER>>SAVE 5sample
Query saved as: 5sample
[SQL]USER>>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
[SQL]USER>>SAVE 5ordered
Query saved as: 5ordered
[SQL]USER>>SELECT Name,Home_State FROM Sample.Person ORDER BY Home_State
3. SELECT Name,Home_State FROM Sample.Person ORDER BY Home_State
[SQL]USER>>
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:
[SQL]USER>>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
----------------------------------------------
[SQL]USER>>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
----------------------------------------------
[SQL]USER>>#3
SELECT Name,Home_State FROM Sample.Person ORDER BY Home_State
3. SELECT Name,Home_State FROM Sample.Person ORDER BY Home_State
----------------------------------------------
[SQL]USER>>GO
.
.
.
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/cmds/disk: 0.0002s/5/155/0ms
execute time(s)/globals/cmds/disk: 0.0001s/0/105/0ms
cached query class: %sqlcq.USER.cls3
---------------------------------------------------------------------------
[SQL]USER>>
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/cmds/disk: 0.0002s/5/155/0ms
execute time(s)/globals/cmds/disk: 0.0002s/5/719/0ms
cached query class: %sqlcq.USER.cls3
---------------------------------------------------------------------------
[SQL]USER>>
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 commands 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. InterSystems IRIS does not log activity for the current SQL Shell.
-
SET LOG ON: InterSystems IRIS logs SQL Shell activity to the default log file.
-
SET LOG pathname: InterSystems IRIS logs SQL Shell activity to the file specified by pathname.
SET LOG ON creates a log file in IRIS\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
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, see the #sqlcompile path macro.
Setting SELECTMODE
You can use SET SELECTMODE to specify the mode used to display query data.
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>>SET SELECTMODE DISPLAY
selectmode = display
[SQL]USER>>
The available options are DISPLAY, LOGICAL, and ODBC. LOGICAL is the default. To determine the current mode, specify SET SELECTMODE without a value:
[SQL]USER>>SET SELECTMODE
selectmode = logical
[SQL]USER>>
%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, see Data Display Options.
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.
Transact-SQL Support
By default, the SQL Shell executes InterSystems 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 InterSystems 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 IRIS. The default is Dialect=IRIS. These SET DIALECT options are not case-sensitive.
he following is an example of the executing a MSSQL program from the SQL Shell:
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>>SET DIALECT MSSQL
dialect = MSSQL
[SQL]USER>>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.
---------------------------------------------------------------------------
[SQL]USER>>
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:
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>>SET COMMANDPREFIX=/
commandprefix = /
[SQL]USER>>/SET LOG=ON
log = xsql4148.log
[SQL]USER>> << 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.
---------------------------------------------------------------------------
[SQL]USER>>/SET COMMANDPREFIX
commandprefix = /
[SQL]USER>>/SET COMMANDPREFIX=""
commandprefix = ""
[SQL]USER>>SET COMMANDPREFIX
commandprefix =
[SQL]USER>>
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 IRIS (InterSystems SQL), Sybase (Sybase TSQL), or MSSQL (Microsoft SQL); the default dialect is IRIS. 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:
[SQL]USER>>SET DIALECT=Sybase
dialect = Sybase
[SQL]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 IRIS 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
.
.
.
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 InterSystems IRIS and executes the same procedure using the InterSystems SQL CALL command.
[SQL]USER>>SET DIALECT Sybase
dialect = Sybase
[SQL]USER>> << 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.
---------------------------------------------------------------------------
[SQL]USER>>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.
---------------------------------------------------------------------------
[SQL]USER>>SET DIALECT=IRIS
dialect = IRIS
[SQL]USER>>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.
---------------------------------------------------------------------------
[SQL]USER>>