Chapter 14 Using the SQL Shell Interface (1)

Other ways to execute SQL

You can use the $system.sql.execute () method to Execute a line of SQL code from the Terminal command line without calling the SQL Shell. The following example shows how to use this method at terminal prompt:

DHC-APP>SET result=$SYSTEM.SQL.Execute("SELECT TOP 5 name,dob,ssn FROM Sample.Person")
 
DHC-APP>DO result.%Display()
Name    DOB     SSN
yaoxin  54536   111- 11- 1117.
xiaoli          111- 11- 1111.Yao Xin63189   111- 11- 1112.Yao Xin63189   111- 11- 1113.Yao Xin50066   111- 11- 1114.
 
5 Rows(s) Affected
Copy the code

If the SQL statement contains errors, the Execute () method completes successfully; Otherwise, the method is invalid. The % Display () method returns an error message, such as:

USER>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
USER>
Copy the code

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

InterSystems IRIS supports many other methods for writing and executing SQL code

These include:

Embedded SQL: SQL code embedded in ObjectScript code.
Dynamic SQL: use%SQL.

Statement class methods that execute SQL statements from ObjectScript code.

Management portal SQL interface: Use the Execute Query interface to Execute dynamic SQL from the InterSystems IRIS management portal.

Call SQL Shell

SQL Shell can be called at the terminal prompt using the $system.sql.shell () method, as follows:

DO $SYSTEM.SQL.Shell()
Copy the code

Alternatively, the SQL Shell can be called using %SQL as an instance. Shell class, as follows:

  DO ##class(%SQL.Shell%).Go("IRIS")
Copy the code

  SET sqlsh=##class(%SQL.Shell%).New(a)DO sqlsh%.Go("IRIS")
Copy the code

No matter how it is called, SQL Shell returns an SQL Shell prompt like this:

[SQL]termprompt>>
Copy the code

Where [SQL] refers to the SQL Shell, termprompt is the configured terminal prompt, and >> refers to the SQL command line. By default, the SQL Shell prompt is as follows :[SQL] NSP >> where “NSP” is the name of the current namespace.

DHC-APP>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
DHC-APP>>  << entering multiline statement mode >>
        1>>
        1>>SELECT TOP 5 name,dob,ssn FROM Sample.Person
        2>>
        2>>q
DHC-APP>>q
Copy the code

At this prompt, you can use any of the following Shell modes:

Single-line mode: Type a line of SQL code at the prompt.

End the SQL statement and press Enter. By default, this prepares and executes the SQL code (this is called execute now mode). For queries, the result set is displayed on the terminal screen. For other SQL statements, the SQLCODE and line values are displayed on the terminal screen.

Multi-line mode: Press Enter at the prompt. This puts you into multi-line mode. Multiple lines of SQL code can be typed, with each new line prompt indicating the line number. (Blank lines do not increment the line number.) To end a multi-line SQL statement, type GO and press Enter. By default, this both prepares and executes the SQL code. For queries, the result set is displayed on the terminal screen. For other SQL statements, the SQLCODE and line count values are displayed on the terminal screen.

Multi-line mode provides the following command, which you can type at a multi-line prompt and then press Enter: L or LIST to LIST all the SQL code entered so far. C or CLEAR deletes all SQL code entered so far. C n or CLEAR n (where n is a line number integer) to remove a specific SQL line of code. G or GO prepares and executes the SQL code, then returns to single-line mode. Q or QUIT deletes all SQL code entered so far and returns to single-line mode. These commands are case insensitive. Issuing the command does not increment the line number of the next multi-line prompt. To play? These multi-line commands are listed at the multi-line prompt.

To prepare an SQL statement, the SQL Shell first validates the statement, including verifying that the specified table exists in the current namespace and that the specified field exists in the table. If not, it will display the appropriate SQLCODE.

If the statement is valid and has the appropriate privileges, the SQL Shell echoes the SQL statement and assigns it an ordinal number. These numbers are assigned sequentially during the terminal session, whether you change the namespace and/or exit and re-enter the SQL Shell. These assigned statement numbers allow the previous SQL statement to be re-invoked, as described below.

You can also use DO Shell^%apiSQL. At the terminal prompt to call the SQL Shell.

To list all available SQL Shell commands, type? . Under SQL prompt.

To terminate the SQL Shell session and return to the Terminal prompt, enter the Q or QUIT commands or E commands at the SQL prompt. SQL Shell commands are case insensitive.

Here is an example SQL Shell session set with default parameters:

DHC-APP>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
DHC-APP>>SELECT TOP 5 Name,Home_State FROM Sample.Person ORDER BY Home_State
1.      SELECT TOP 5Person ORDER BY Home_State Name Home_State xiaoli5 Rows(s) Affected
statement prepare time(s)Globals/lines/disk: 0.0523 s / 45502/270281/2 ms to executetime(s)/globals/lines/disk: 0.0004 s / 225/2915/0 ms -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- DHC APP > > qCopy the code

Here is a multi-line SQL Shell session set with default parameters:

DHC-APP>DO $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
DHC-APP>>  << entering multiline statement mode >>
        1>>SELECT TOP 5
        2>>Name,Home_State
        3>>FROM Sample.Person
        4>>ORDER BY Home_State
        5>>GO
2.      SELECT TOP 5Person ORDER BY Home_State Name Home_State xiaoli5 Rows(s) Affected
statement prepare time(s)/ globals/lines/disk: 0.0002 s / 18/1168/0 ms to executetime(s)/ globals/lines/disk: 0.0003 s / 225/2886/0 ms -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --Copy the code

The GO command

The SQL Shell GO command executes the latest SQL statement. In single-row mode, GO re-executes the most recently executed SQL statement. In multi-line mode, the GO command is used to execute multi-line SQL statements and exit the multi-line mode. A subsequent GO in single-line mode reexecutes the previous multi-line SQL statement.

The input parameters

SQL Shell supports “?” The input parameters use characters from SQL statements. Each time the SQL statement is executed, the system prompts you to specify the values of these input parameters. Must be preceded by “?” The same order specifies that these value characters appear in SQL statements: the first prompt is the first ‘? Provide a value, and the second prompt is the second “?” Provide a value, and so on.

There is no limit to the number of input parameters. You can use input parameters to supply values to the TOP clause, WHERE clauses, and expressions to SELECT lists. Column names cannot be supplied to the SELECT list using input parameters.

Host variables can be specified as input parameter values. At the input parameter prompt, specify a value that begins with a colon (:). The value can be a public variable, an ObjectScript special variable, a numeric literal, or an expression. The SQL Shell then prompts “Is this text (Y/N)?” . Specifying N (no) at this prompt (or just press Enter) means that the input value is resolved to the host variable. For example, : myval will be resolved to the value of the local variable myval; ^myval will be resolved to the value of the global variable ^myval; : $HOROLOG will be resolved to the value of the $HOROLOG special variable; : 3 will be parsed as the number 3; : 10-3 will be parsed as the number 7. Specifying Y (yes) at this prompt means that the input value (including the colon) is supplied as a literal to the input parameter.

Run ObjectScript

In an SQL Shell, you might want to issue an ObjectScript command. For example, change the InterSystems IRIS NAMESPACE to a NAMESPACE containing the SQL table or stored procedure to be referenced by using the SET $NAMESPACE command. You can use SQL Shell! Command or OBJ command to issue an ObjectScript command line consisting of one or more ObjectScript commands. (OBJ is short for OBJECTSCRIPT.) ! , OBJ and OBJECTSCRIPT commands are synonyms. The following example shows the use of these commands:

%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>>
Copy the code

The rest of the command line after the OBJ command is treated as ObjectScript code. ! No Spaces are required between. And ObjectScript command line. The OBJ command can be specified in SQL Shell single-line mode or SQL Shell multi-line mode. The following example performs 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>>
Copy the code

Note that the OBJ statement does not increase the NUMBER of SQL rows.

In SQL Shell multi-line mode, the OBJ command is executed when the row is returned, but the SQL statement is not issued until GO is specified. Therefore, the following example is functionally the same as 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>>
Copy the code

The following example defines a host variable using the OBJ command:

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
Copy the code

Browsing namespaces

The SQL Shell supports the BROWSE command, which displays schemas, tables, and views defined in or accessible from the current namespace. This display includes multiple levels of prompts. To Return to the previous prompt level, press the Return key when prompted. The name is case sensitive.

Type BROWSE at the SQL Shell prompt to list the schema in the current namespace.
Under the Schema: prompt, select a schema by name or 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 number. This displays the table information, followed by a list of options.
At the option: prompt, select an option by number. You can use this option to list fields or mappings defined for a table.

Specify option 1 (fields by name) or option 2 (fields by number) to display the “Field:” prompt. Specify option 3 (Map) to display the Map: prompt.

in"Field:When prompted, select a field by number or name, or specify * to list all fields. This lists the detailed field information.

At the “Map:” prompt, select a Map by number or name, or specify * to list all maps. This lists the detailed Map information.

CALL command

SQL stored procedures can be invoked by issuing SQL CALL statements using the SQL Shell, as shown in the following example:

DHC-APP>>CALL Sample.PersonSets('G'.'NY')
3.      CALL Sample.PersonSets('G'.'NY')
 
 
 
Dumping result #1
Name    DOB     Spouse
Gallant,Thelma Q.       45767   94
Gibbs,Mark S.   37331   13
Goldman,Will H. 59069   10
Gomez,Mo Q.     55626   55
Gore,Alfred M.  42991   13
Gore,Fred X.    32391   6
Grabscheid,Jocelyn B.   59676   79
 
7 Rows(s) Affected
 
Dumping result #2
Name    Age     Home_City       Home_State
Chadbourne,Danielle G.  34      Pueblo  NY
Eastman,Clint G.        4       Miami   NY
Pape,Linda M.   71      Vail    NY
Peterson,Janice N.      49      Islip   NY
Schaefer,Jocelyn V.     93      Zanesville      NY
 
5 Rows(s) Affected
statement prepare time(s)/ globals/lines/disk: 0.0043 s / 2153/15795/0 ms to executetime(s)/ globals/lines/disk: 0.0015 s / 315/7829/0 ms -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --Copy the code

If the specified stored procedure does not exist in the current namespace, the SQL Shell issues a SQLcode-428 error.

If you specify more input parameters than those defined in the stored procedure, the SQL Shell issues a SQLcode-370 error. You can use literals (” strings “), host variables (: var), and input parameters (?). Specifies parameter values for the stored procedure.

Host variables can be used 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)
Copy the code

Can be found inCALLStatement using input parameters ("?"Character), as shown in the following example:

[SQL]USER>>CALL Sample.PersonSets(? ,?)Copy the code

When the CALL statement is executed, the SQL Shell prompts for a value for each input parameter.

Execute the SQL script file

The SQL Shell RUN command executes an SQL script file. The type of script files is determined by the DIALECT setting. The default value of DIALECT is IRIS (InterSystems SQL).

Chapter 14 Using the SQL Shell Interface (1)

Chapter 14 Using the SQL Shell Interface (1)

Other ways to execute SQL

Call SQL Shell

The GO command

The input parameters

Run ObjectScript

Browsing namespaces

CALL command

Execute the SQL script file

Related Posts

Exploring Es-Objects and Nested Objects (Part 3)

“If I become Zhuge Liang” — Strategic mode

Interviewer: Do you understand the mechanism of reentrant lock?