Using Caché SQL
Using the SQL Shell Interface
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

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.

The following topics are documented in this chapter:
Other Ways of Executing SQL
You can execute a single line of SQL code from the Caché 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()
SAMPLES>SET result=$SYSTEM.SQL.Execute("CALL Sample.PersonSets('M','MA')")

SAMPLES>DO result.%Display()
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>
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 use the $SYSTEM.SQL.Shell() method to invoke the SQL Shell from the Terminal prompt, as follows:
  DO $SYSTEM.SQL.Shell()
This 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:
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>ZNSPACE "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>
The following is a multiline SQL Shell session using the default parameter settings:
USER>ZNSPACE "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>>
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 specfy 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, a Caché 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 Caché ObjectScript Commands
Within the SQL Shell, you may wish to issue a Caché ObjectScript command. For example, to change the Caché namespace by using the ZNSPACE 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 a Caché ObjectScript command, as shown in the following example:
>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
USER>>COS ZNSPACE "SAMPLES"
 
SAMPLES>>
The rest of the command line following the COS command is treated as Caché ObjectScript code. You can specify a COS command while in SQL Shell single-line mode or in SQL Shell multiline mode, 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.
USER>>  << entering multiline statement mode >>
        1>>COS ZNSPACE "SAMPLES"

        1>>SELECT TOP 5 Name,Home_State
        2>>FROM Sample.Person
        3>>GO
Note that the COS statement does not advance the line count. A COS command is always issued before the SQL statement. Thus, the following example is functionally identical to the previous example:
USER>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
USER>>  << entering multiline statement mode >>
        1>>SELECT TOP 5 Name,Home_State
        2>>FROM Sample.Person
        3>>COS ZNSPACE "SAMPLES"

        3>>GO
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')
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 (?).
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:
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 Caché 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:
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 Caché Terminal processes. An assigned name persists after the Caché 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 Caché Terminal processes. An assigned name persists after the Caché Terminal process that created it has ended.
SQL Shell Parameters
The SQL Shell provides the following configurable parameters:
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 Caché 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 =
echo = on
executemode = immediate
log = off
messages = on
path = SQLUser
selectmode = logical
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 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>>
The DISPLAYMODE default is CURRENTDEVICE, which displays the query data on the Caché 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. A second file of the same format is created to record 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”
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
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:
When necessary, Caché 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>> 
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, Caché 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>>
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>>
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
.
.
.
Setting LOG
You can use SET LOG to specify whether to log SQL Shell activity to a file. The available options are:
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 a default 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:
SAMPLES>>SET PATH cinema,sample,user
SET PATH with no argument deletes the current schema search path, reverting to the system default. If SET PATH schema is not specified, or the table is not found in the specified schemas, SQL Shell uses the system default schema for the current namespace. 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>>
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>>
%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:
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 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>>
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>>
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
.
.
.
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>>