%SQL.Shell

class %SQL.Shell extends %Library.RegisteredObject

For details on using this class, see Using the 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 = .

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:

Property Inventory

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

Method Inventory

• %Go()
• 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 methods: ColAlignDisplayToLogical(), ColAlignGet(), ColAlignIsValid(), ColAlignLogicalToDisplay(), ColAlignNormalize(), ColAlignSet()

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 methods: CommandPrefixDisplayToLogical(), CommandPrefixGet(), CommandPrefixIsValid(), CommandPrefixLogicalToDisplay(), CommandPrefixLogicalToOdbc(), CommandPrefixNormalize(), CommandPrefixSet()

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 methods: DialectDisplayToLogical(), DialectGet(), DialectIsValid(), DialectLogicalToDisplay(), DialectLogicalToOdbc(), DialectNormalize()

property DisplayDelimiter as %String;

Property methods: DisplayDelimiterDisplayToLogical(), DisplayDelimiterGet(), DisplayDelimiterIsValid(), DisplayDelimiterLogicalToDisplay(), DisplayDelimiterLogicalToOdbc(), DisplayDelimiterNormalize(), DisplayDelimiterSet()

property DisplayFile as %Library.String);

Property methods: DisplayFileDisplayToLogical(), DisplayFileGet(), DisplayFileIsValid(), DisplayFileLogicalToDisplay(), DisplayFileLogicalToOdbc(), DisplayFileNormalize(), DisplayFileSet()

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 methods: DisplayModeDisplayToLogical(), DisplayModeGet(), DisplayModeIsValid(), DisplayModeLogicalToDisplay(), DisplayModeNormalize(), DisplayModeSet()

property DisplayPath as %Library.String);

Property methods: DisplayPathDisplayToLogical(), DisplayPathGet(), DisplayPathIsValid(), DisplayPathLogicalToDisplay(), DisplayPathLogicalToOdbc(), DisplayPathNormalize(), DisplayPathSet()

property DisplayTranslateTable as %Library.String);

Property methods: DisplayTranslateTableDisplayToLogical(), DisplayTranslateTableGet(), DisplayTranslateTableIsValid(), DisplayTranslateTableLogicalToDisplay(), DisplayTranslateTableLogicalToOdbc(), DisplayTranslateTableNormalize(), DisplayTranslateTableSet()

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 methods: EchoModeDisplayToLogical(), EchoModeGet(), EchoModeIsValid(), EchoModeLogicalToDisplay(), EchoModeNormalize(), EchoModeSet()

property EscapeCondition as %String);

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 methods: EscapeConditionDisplayToLogical(), EscapeConditionGet(), EscapeConditionIsValid(), EscapeConditionLogicalToDisplay(), EscapeConditionLogicalToOdbc(), EscapeConditionNormalize(), EscapeConditionSet()

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 methods: ExecuteModeDisplayToLogical(), ExecuteModeGet(), ExecuteModeIsValid(), ExecuteModeLogicalToDisplay(), ExecuteModeNormalize(), ExecuteModeSet()

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 methods: LogGet(), LogGetSwizzled(), LogIsValid(), LogNewObject(), LogSet()

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 methods: LogModeDisplayToLogical(), LogModeGet(), LogModeIsValid(), LogModeLogicalToDisplay(), LogModeNormalize(), LogModeSet()

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 methods: MessageModeDisplayToLogical(), MessageModeGet(), MessageModeIsValid(), MessageModeLogicalToDisplay(), MessageModeNormalize(), MessageModeSet()

property Path as %String);

Path is the schema search path used when resolving unqualified names.

Property methods: PathDisplayToLogical(), PathGet(), PathIsValid(), PathLogicalToDisplay(), PathLogicalToOdbc(), PathNormalize(), PathSet()

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 methods: SelectModeDisplayToLogical(), SelectModeGet(), SelectModeIsValid(), SelectModeLogicalToDisplay(), SelectModeNormalize(), SelectModeSet()

property Statement as %SQL.Statement;

Property methods: StatementGet(), StatementGetSwizzled(), StatementIsValid(), StatementNewObject(), StatementSet()

property StatementNbr as %Integer;

Property methods: StatementNbrDisplayToLogical(), StatementNbrGet(), StatementNbrIsValid(), StatementNbrLogicalToDisplay(), StatementNbrNormalize(), StatementNbrSet()

property StatementText as %Library.String) [ MultiDimensional ];

Property methods: StatementTextDisplayToLogical(), StatementTextGet(), StatementTextIsValid(), StatementTextLogicalToDisplay(), StatementTextLogicalToOdbc(), StatementTextNormalize(), StatementTextSet()

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 methods: executeStartTimeDisplayToLogical(), executeStartTimeGet(), executeStartTimeIsValid(), executeStartTimeLogicalToDisplay(), executeStartTimeLogicalToOdbc(), executeStartTimeNormalize(), executeStartTimeOdbcToLogical(), executeStartTimeSet()

property executeStats as %List;

Used to compute how many lines this query executes, global reads and disk latency

Property methods: executeStatsGet(), executeStatsIsValid(), executeStatsLogicalToOdbc(), executeStatsOdbcToLogical(), executeStatsSet()

property prepareStats as %List;

Used to compute how many lines this query executes, global reads and disk latency

Property methods: prepareStatsGet(), prepareStatsIsValid(), prepareStatsLogicalToOdbc(), prepareStatsOdbcToLogical(), prepareStatsSet()

property prepareTime as %Time;

prepareTime is the time is took to prepare the most recent statement.

Property methods: prepareTimeDisplayToLogical(), prepareTimeGet(), prepareTimeIsValid(), prepareTimeLogicalToDisplay(), prepareTimeLogicalToOdbc(), prepareTimeNormalize(), prepareTimeOdbcToLogical(), prepareTimeSet()

Methods

classmethod %Go(pDialect As %String)

%Go() - instantiate the shell and prompt for commands.

method DialectSet(pValue As %String = "") as %Status

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=""))

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="")="")