Class Reference
IRIS for UNIX 2019.2
InterSystems: The power behind what matters   
Documentation  Search
  [%SYS] >  [%SQL] >  [Shell]
Private  Storage   

class %SQL.Shell extends %Library.RegisteredObject

%SQL.Shell - Interactive SQL shell

The interactive SQL shell allows the user to execute SQL statements dynamically. There are two execution modes - immediate and deferred. The execution mode can be set by executing a set executemode command with immediate or deferred as the executemode value. If the execution mode is immediate then SQL statements are prepared and executed immediately. If execute mode is deferred then a statement is only prepared and not executed until either a # or go command is entered. The result of executing the statement is displayed on the current device after the statement is executed.

If displaymode is currentdevice and messages is on then the time taken to prepare the current statement and the time taken to execute the current statement are displayed after the result is displayed. Execute time includes the time to instantiate the result object and the time to display the statement.

SQL statements can span multiple lines. To enter a multiline statement, simply press the enter key to enter multiline mode. When the statement is complete enter go to exit multiline mode. If execution mode is immediate the multiline statement will be executed at that point, otherwise and additional # or go will cause the statement to execute.

The user is prompted to supply a value for each parameter defined in the statement each time the statement is executed. If the value entered begins with a colon then the user is prompted to enter if the value is a literal or not. If the value is a literal the user must answer with Y or y. The default is N and if that is the desired response simply press Enter and the value entered is assumed to be a ObjectScript expression or host variable. Host variables are assumed to be public variables. Public variables can be set using the ! set var = value command. Other expressions can be entered, including function calls and class methods that return a value. The statement can be executed again by entering 'go' resulting in prompts for new statement values. The expression used to execute the statement is displayed just prior to executing the statement. If the statement does not contain any parameters then execution will proceed without any prompting.

The Shell supports statement recall. Any statement that is prepared may be recalled from the statement buffer by entering #nbr where nbr is the statement number in the buffer. Just entering # will list the statements currently in the buffer. Entering #clear will clear the statement buffer on confirmation. Entering #0 will recall the most recently prepared statement from the buffer. If executemode is set to immediate then the recalled statement will also be executed.

Statements can also be saved in a statement pool. To save the current statement enter save name. That statement is saved in the pool and can be later recalled by entering open name.

Statements can be deleted from the statement pool by entering clear name. If no name is specified then all statements are removed from the pool on confirmation.

Shell commands are entered in the first column and do not often conflict with executable statements. In some cases, such as set, a command and an SQL statement can be ambiguous. This occurs most often in complex statements such as CREATE PROCEDURE. To eliminate confusion, the SQL Shell supports a command prefix that can be set to any value. The command prefix allows the user to specify that all commands begin with a special character or character sequence. To set the command prefix simply set commandprefix option using the set command:

		USER>>set commandprefix = .

Once the command prefix is set, all commands must be prefixed by the value of the commandprefix setting. There are three exceptions - ?, # and GO do not have to be prefixed.

The commandprefix option is saved by the set save command and, if saved, will be restored automatically the next time the user starts the shell. By default there is no commandprefix. To reset the command prefix back to the default once it has been set to something else, enter:

		USER>>set commandprefix = ""

Shell context settings are set by the set command. Context settings can be retained and used by future instances of the shell by entering set save and cleared by entering set clear. Entering set without any additional qualifiers will display the current shell context settings.

To execute an SQL statement, type it in and press ENTER.

To execute a multiline SQL statement, press ENTER to enter multiline mode, type in the statement, and enter go.

Available commands:

Press ENTER Enter multiline statement mode. While in multiline statement mode, some basic commands are supported.

c[lear] Clear all statement lines entered.
c[lear] line nbr Clear line line nbr.
g[o] Exit multiline statement mode. If EXECUTEMODE is immediate then execute the statement. The result is displayed after execution.
l[ist] List the statement lines entered.
q[uit] Exit multiline statement mode and discard any statement lines.
?
help
Display statement mode help.

SQL StatementPrepare the statement. If EXECUTEMODE is immediate then execute the statement. The result is displayed after execution.
?
help
Display help.
#List statements currently in the history buffer.
#nbr Recall statement nbr from the statement history buffer. If nbr is zero then recall the most recent statement. Recalled statements are executed if EXECUTEMODE is set to 'immediate'. If EXECUTEMODE is set to 'deferred' then it is necessary to enter GO to execute the recalled statement.
#?List statements currently in the history buffer.
#clearClear the statement history buffer.
c[lear] name Clear (delete) the statement identified by name from the statement pool.
! objectscript_commandExecute objectscript_command.
g[o]Run the most recently executed or loaded statement.
l[ist]List statements in the statement pool.
load filenameLoad a statement from filename.
m[etadata]Displays the metadata for the current statement.
obj[ectscript] objectscript_commandExecute objectscript_command.
o[pen] nameOpen the statement name name from the statement pool. The retrieved statement is prepared and executed (depending on the EXECUTEMODE setting).
q[uit]
e[xit]
Exit the shell.
run [filename]Run an SQL script. If is not specified then the user will be prompted for the file name.
saveglobal|sg nameSave the currently prepared statement in the statement global using the given name.
s[ave] filenameSave the currently prepared statement to filename.
set Display the current context settings.
set clear Clear the saved Shell settings for this user.
set colalign [=] delim[iter] | header | data Set column alignment for the SQL Resultset. The context setting colalign can be set to one of the following values:
  • delim[iter] - The SQL Resultset header/data columns will be aligned based on the standard delimiter (tab). [Default]
  • header - The SQL Resultset header/data columns will be aligned based on the length of the column header and the standard delimiter (tab).
  • data - The SQL Resultset header/data columns will be aligned based on the precision/length of the column data property and the standard delimiter (tab).
set commandprefix [=] string value Sets the command prefix to string value. To set the command prefix to nothing, enter "" as the string value.
set dialect [=] sybase | mssql | IRIS Sets the dialect to the desired SQL dialect. If schemalist is empty then IRIS SQL is assumed. The dialect setting determines which dialect mode is used when preparing a statement.
set displayfile [=] file Set the name of the file to be used for displaying the results of executing SQL statements. If no file name is specified then a random file name will be generated for each statement execution. Additionally, a messages file may be produced. The name of the file containing messages is either the file concatenated with "Messages" or it is a random file name if file is set to nothing. To set the file setting to nothing after it was previously set, enter "".
set displaymode [=] displaymode Set the DISPLAYMODE. If displaymode is not specified, the current DISPLAYMODE setting is displayed on the current device. displaymode values can be cur[rentdevice],xml,html,pdf, or txt. When DISPLAYMODE is set to CURRENTDEVICE, statement results are displayed on the current device. When XML, HTML, PDF or TXT are specified then statement results are written to a temporary file using a ZEN Report that is generated from the statement metadata.
set displaypath [=] path Set the path where files used to save statement results will be placed.
set displaytranslate[table] [=] translatetable Set the translate table used for displayfile when writing output in 'csv' and 'txt' formats. For example, "UTF8". Values are case sensitive.
set executemode [=] executemode Set EXECUTEMODE to executemode. Valid values are immediate and deferred. If immediate then SQL statements are executed immediately. If deferred then the current statement is executed by entering a 'go' command. If executemode is not specified then the current EXECUTEMODE setting is displayed.
set log [=] on | off | file Turn logging on or off. If a file is specified, logging is turned on and the log is written to the file specified. The default file for logging is used if logging is simply turned 'on'.
set messages [=] on | off Turn messages on or off. If messages is off then results are displayed without any additional information. Times are not displayed.
set path [=] schemalist Sets the current schema search path. If schemalist is empty then the current path is displayed. To set PATH to the default schema then specify schemalist as """".
set save Save the current Shell settings for this user. The settings are restored when the current user starts another Shell session.
set selectmode [=] selectmode Sets the current SELECTMODE to odbc, logical, or display. If selectmode is NULL the current setting is displayed.
show [pl[an]] [v[erbose]] Show the execution plan for the current statement.
If the verbose qualifier is used, show all the module details for the current statement's execution plan; Otherwise, display only the top-level module details by default.
show planalt [v[erbose]] Run the ShowPlanAlt() method on the the current statement which allows user to browse alternate execution plans.
If the verbose qualifier is used, show all the module details for the current statement's execution plan and all alternate plans; Otherwise, display only the top-level module details by default.
show st[atement] Show the current statement text and other details such as the implementation class.

Inventory

Parameters Properties Methods Queries Indices ForeignKeys Triggers
23 25


Summary

Properties
ColAlign CommandPrefix Dialect DisplayFile
DisplayMode DisplayPath DisplayTranslateTable EchoMode
EscapeCondition ExecuteMode Log LogMode
MessageMode Path SelectMode Statement
StatementNbr StatementText executeStartTime executeStats
prepareStats prepareTime

Methods
%AddToSaveSet %ClassIsLatestVersion %ClassName %ConstructClone
%DispatchClassMethod %DispatchGetModified %DispatchGetProperty %DispatchMethod
%DispatchSetModified %DispatchSetMultidimProperty %DispatchSetProperty %Extends
%GetParameter %Go %IsA %IsModified
%New %NormalizeObject %ObjectModified %OriginalNamespace
%PackageName %RemoveFromSaveSet %SerializeObject %SetModified
%ValidateObject DialectSet EnterStatement Help
HelpStatementMode cmdGo cmdLoad cmdMetadata
cmdObjectScript cmdOpen cmdProcessStatement cmdRun
cmdSave cmdSaveGlobal cmdSet cmdShow
dispatcher display displayResult displayStatement
execute getPrompt processCache


Properties

• property ColAlign as %Integer(DISPLAYLIST=",delimiter,header,data",VALUELIST=",0,1,2") [ InitialExpression = 0 ];

ColAlign represents the column alignment for the SQL Resultset, and can be set to one of the following values:

  • delim[iter] - The SQL Resultset header/data columns will be aligned based on the standard delimiter (tab). [Default]
  • header - The SQL Resultset header/data columns will be aligned based on the length of the column header and the standard delimiter (tab).
  • data - The SQL Resultset header/data columns will be aligned based on the precision/length of the column data property and the standard delimiter (tab).
USER>>set colalign [=] delim[iter]
USER>>set colalign [=] header
USER>>set colalign [=] data
• property CommandPrefix as %String(MAXLEN=10);

CommandPrefix is the string value used to indicate that a command is being entered. The default value is null, meaning that no command prefix is in use. If CommandPrefix is set to a non-empty value then that prefix must be entered as a prefix to most commands. There are three commands that do not require a prefix: ?, # and GO.

USER>>set commandprefix = /
USER>>/set save
USER>>/quit
• property Dialect as %Library.String;
Dialect is the dialect of SQL to be used when preparing a statement. Supported dialects are IRIS (default), Sybase and MS SQL Server.
• property DisplayFile as %Library.String(MAXLEN="");
• property DisplayMode as %Integer(DISPLAYLIST=",currentdevice,xml,html,pdf,txt,csv",VALUELIST=",-1,0,1,2,99,100") [ InitialExpression = -1 ];
DisplayMode is the format in which results are displayed.
• property DisplayPath as %Library.String(MAXLEN="");
• property DisplayTranslateTable as %Library.String(MAXLEN="");
• property EchoMode as %Integer(DISPLAYLIST=",off,on",VALUELIST=",0,1") [ InitialExpression = 1 ];
EchoMode is either on (not zero) or off (zero). If on, statements and results are echoed to the current device. This setting does not impact logging.
• property EscapeCondition as %String(MAXLEN="");
EscapeCondition is the argument to an ObjectScript IF condition which must evaluate to TRUE in order to execute ObjectScript commands from the shell The process running the SQL Shell must also be in programmer mode, not application mode. When IF evaluation results in an error, executing ObjectScript commands will not be allowed.
• property ExecuteMode as %Integer(DISPLAYLIST=",deferred,immediate",VALUELIST=",0,1") [ InitialExpression = 1 ];
ExecuteMode defines when the current statement will be executed. If ExecuteMode is 'immediate' then each statement is executed as so as it is prepared. If ExecuteMode is 'deferred' then the statement is prepared but not executed until a 'GO' command is entered.
• property Log as %XSQL.Log;
Log instance. This is an oref that references a log object to be used when logging statements, messages, and results.
• property LogMode as %Integer(DISPLAYLIST=",off,on",VALUELIST=",0,1") [ InitialExpression = 0 ];
Either on or off. If on then statements, messages and results will be logged using the current Log value.
• property MessageMode as %Integer(DISPLAYLIST=",off,on",VALUELIST=",0,1") [ InitialExpression = 1 ];
MessageMode is either on (not zero) or off (zero). If on, messages will be displayed on the current device. This does not impact the LogMode setting.
• property Path as %String(MAXLEN="");
Path is the schema search path used when resolving unqualified names.
• property SelectMode as %Integer(DISPLAYLIST=",logical,odbc,display",VALUELIST=",0,1,2") [ InitialExpression = 0 ];
SelectMode defines the format of literals and column values when executing an SQL statement.
• property Statement as %SQL.Statement;
• property StatementNbr as %Integer;
• property StatementText as %Library.String(MAXLEN="") [ MultiDimensional ];
• property executeStartTime as %Time;
executeStartTime is the time the statement execute started. This is reset during execute() after parameter values are entered. The execute time is computed after %Display() is complete and displayed after the rowcount and SQLCODE.
• property executeStats as %List;
Used to compute how many lines this query executes, global reads and disk latency
• property prepareStats as %List;
Used to compute how many lines this query executes, global reads and disk latency
• property prepareTime as %Time;
prepareTime is the time is took to prepare the most recent statement.

Methods

• classmethod %Go(pDialect As %String)
%Go() - instantiate the shell and prompt for commands.
• method DialectSet(pValue As %String = "") as %Status
This is a Set accessor method for the Dialect property.
• method EnterStatement(ByRef pInput As %Library.String(MAXLEN="")="")
EnterStatement - multi-line statement entry
• classmethod Help()
• classmethod HelpStatementMode()
• method cmdGo()
• classmethod cmdLoad(pInput As %String(MAXLEN=""), ByRef pStatement As %String(MAXLEN=""))
• method cmdMetadata(ByRef pInputLC As %Library.String(MAXLEN="")="")
Display the current statement's metadata.
• method cmdObjectScript(ByRef pInput As %Library.String(MAXLEN="")="")
cmdObjectScript will execute a object script command
• method cmdOpen(ByRef pInput As %Library.String(MAXLEN="")="")
Open (retrieve) a statement from the SQL statement pool.
• method cmdProcessStatement(ByRef pInput As %Library.String(MAXLEN="")="")
• method cmdRun(ByRef pInput As %Library.String(MAXLEN="")="")
cmdRun will run a SQL script file - Currently only Sybase is supported.
• method cmdSave(pInput As %String(MAXLEN=""))
• method cmdSaveGlobal(ByRef pInput As %Library.String(MAXLEN="")="")
• method cmdSet(ByRef pInputLC As %Library.String(MAXLEN="")="", pInput As %Library.String(MAXLEN=""))
cmd
• method cmdShow(ByRef pInputLC As %Library.String(MAXLEN="")="", pCommand As %Library.String = "")
• method dispatcher(ByRef pInput As %Library.String = "")
• classmethod display(str As %String, lmargin As %Integer = 25, rmargin As %Integer = 80)
Given a string, display the string on this, and as many lines needed, between the given margins.
• method displayResult(pResult As %SQL.StatementResult = "")
This method displays the result produced by executing the current statement. DISPLAYMODE determines how the statement is displayed and ECHOMODE suppresses the results display on the current device.
• classmethod displayStatement(ByRef pStatement As %Library.String(MAXLEN=""), pIndentFirst As %Library.String = $Char(9), pIndent As %Library.String = $Char(9))
• method execute(pStatement As %SQL.Statement) as %SQL.StatementResult
• classmethod getPrompt(lasttime As %Library.String = "0.0")
getPrompt() returs the string to use for the prompt based on the configured Terminal Prompt settings
• method processCache(ByRef pInput As %Library.String = "", pCommand As %Library.String(MAXLEN="")="")


Copyright (c) 2019 by InterSystems Corporation. Cambridge, Massachusetts, U.S.A. All rights reserved. Confidential property of InterSystems Corporation.