Chapter 13 Using Dynamic SQL (7)

SQL metadata

Dynamic SQL provides the following types of metadata:

• After Preparing, describe the metadata for the query type.
• After Prepare, describe the metadata (Columns and Extended Column information) for the selection in the query.
• After preparation, describe the metadata for the query parameters: parameters,: varParameters and constants. (Statement parameters, formal parameters, and objects)
• After execution, describe the metadata of the query result set. After executing the Prepare operation (% Prepare ().% PrepareClassQuery ()or% ExecDirect ()) can be used after% SQL. The StatementMetadataAttribute values.
• Can be directly for the latest% Prepare ()return% the SQL StatementMetadata attributes.
• Can return contain% SQL. The StatementMetadataProperty of oref% % SQL. The Statement of the MetadataProperties. This allows you to return metadata for multiple prepared operations.

A SELECT or CALL statement returns all of this metadata. INSERT, UPDATE, or DELETE returns statement type metadata and formal parameters.

Statement type metadata

After preparing with the % sql. Statement class, you can use the % sqL. StatementMetadata statementType attribute to determine which type of SQL Statement to Prepare, as shown in the following example. This example uses the % sql. Statement % Metadata property to save and compare Metadata for two Prepare operations:

/// d ##class(PHA.TEST.SQL).MetaData()
ClassMethod MetaData(a)
{
	SET tStatement = ##class(%SQL.Statement%).New(a)SET myquery1 = "SELECT TOP ? Name,Age,AVG(Age),CURRENT_DATE FROM Sample.Person"
	SET myquery2 = "CALL Sample.SP_Sample_By_Name(?) "
	SET qStatus = tStatement.%Prepare(myquery1)
	IF qStatus'=1 { WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT } SET meta1 = tStatement.%Metadata SET qStatus = tStatement.%Prepare(myquery2) IF qStatus'=1 {
		WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT
	}
	SET meta2 = tStatement.%Metadata
	WRITE "Statement type query 1:",meta1.statementType,!
	WRITE "Statement type query 2:",meta2.statementType,!
	WRITE "End of metadata"
}

Copy the code

DHC-APP>d ##class(PHA.TEST.SQL).MetaData() statement typequery1: 1 statement typequery2:45End of metadata
Copy the code

The class reference entry for the statementType attribute lists the statementType integer code. The most common codes are 1 (SELECT query) and 45 (call-to-store query).

You can use the % GetImplementationDetails () instance method to return the same information, as described in the results of the successful preparation.

After the query is executed, the statement type name (for example SELECT) can be returned from the result set.

Select item select-item metadata

After preparing a SELECT or CALL Statement with the % SQL.Statement class, you can return metadata about each selection column specified in the query by displaying all metadata or specifying individual metadata items. This column metadata includes ODBC data type information, as well as the origin and class type information of the client type and InterSystems Objects properties.

The following example returns the number of columns specified in the most recently prepared query:

/// d ##class(PHA.TEST.SQL).MetaData1()
ClassMethod MetaData1(a)
{
	SET myquery = "SELECT %ID AS id,Name,DOB,Age,AVG(Age),CURRENT_DATE,Home_State FROM Sample.Person"
	SET tStatement = ##class(%SQL.Statement%).New(a)SET qStatus = tStatement.%Prepare(myquery)
	IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} WRITE "Number of columns=",tStatement.%Metadata.columnCount,! WRITE "End of metadata" }Copy the code

DHC-APP>d ##class(PHA.TEST.SQL).MetaData1(a)Number of columns=7
End of metadata
Copy the code

The following example returns the column name (or column alias), ODBC data type, maximum data length (precision), and the ratio of each SELECT item field:

/// d ##class(PHA.TEST.SQL).MetaData2()
ClassMethod MetaData2(a)
{
	SET $NAMESPACE="SAMPLES"
	SET myquery=2
	SET myquery(1)="SELECT Name AS VendorName,LastPayDate,MinPayment,NetDays,"
	SET myquery(2)="AVG(MinPayment),$HOROLOG,%TABLENAME FROM Sample.Vendor"
	SET rset = ##class(%SQL.Statement%).New(a)SET qStatus = rset.%Prepare(.myquery)
	IF qStatus'=1 { WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT } SET x=rset.%Metadata.columns.Count() SET x=1 WHILE rset.%Metadata.columns.GetAt(x) { SET column=rset.%Metadata.columns.GetAt(x) WRITE ! ColName," is the data type ",column.ODBCType WRITE "Size is ",column.precision," scale = ",column.scale SET x=x+1} WRITE! ,"End of metadata" }Copy the code

DHC-APP>d ##class(PHA.TEST.SQL).MetaData2(1)VendorNameYes Data type 12 size 50 scale= 0
2LastPayDate is a data type9Size of the10Size =0
3MinPayment is a data type8Size of the6Size =0
4NetDays is a data type4Size of the3Size =0
5Aggregate_5 is the data type8Size of the20Size =0
6Expression_6 is a data type12Size of the255Size =0
7Literal_7 is a data type12Size of the13Size =0
End of metadata
Copy the code

The following example uses the % sqL.statementMetadata % Display () instance method to Display all column metadata:

/// d ##class(PHA.TEST.SQL).MetaData3()
ClassMethod MetaData3(a)
{
	SET tStatement = ##class(%SQL.Statement%).New(a)SET qStatus = tStatement.%Prepare("SELECT %ID AS id,Name,DOB,Age,AVG(Age),CURRENT_DATE,Home_State FROM Sample.Person")
	IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} DO tStatement.%Metadata.%Display() WRITE ! ,"End of metadata" }Copy the code

DHC-APP>d ##class(PHA.TEST.SQL).MetaData3(a)Columns (SQLRESULTCOL.property 'columns') :Column Name  Type Prec Scale Null Label        Table        Schema       CType
-----------  ---- ---- ----- ---- ------------ ------------ ------------ -----
id4 to 10 0 0id           Person       Sample          5
Name           12   50     0    0 Name         Person       Sample         10
DOB9 10 0 to 1DOB          Person       Sample          2
Age4 to 10 0 to 1Age          Person       Sample          5
Aggregate_520 August 1 2Aggregate_5                              14
Expression_6    9   11     0    2 Expression_6                              2
Home_State12 2 0 to 1Home_State   Person       Sample         10
 
 
Extended Column Info (SQLRESULTCOL)
     Flags: 1:AutoIncrement, 2:CaseSensitive, 3:Currency, 4:ReadOnly, 5:RowVersion,
            6:Unique, 7:Aliased, 8:Expression, 9:Hidden, 10:Identity, 11:KeyColumn,
            12:RowId
 
Column Name  Linked Prop           Type Class            Flags
------------ --------------------- --------------------- -----------------------
id                                 Sample.Person         Y.N.N.Y.N.Y.Y.N.N.Y.Y.Y
Name         Sample.Person.Name    %Library.String       N.N.N.N.N.N.N.N.N.N.N.N
DOB          Sample.Person.DOB     %Library.Date         N.N.N.N.N.N.N.N.N.N.N.N
Age          Sample.Person.Age     %Library.Integer      N.N.N.N.N.N.N.N.N.N.N.N
Aggregate_5                        %Library.Numeric      N.N.N.Y.N.N.Y.N.N.N.N.N
Expression_6                       %Library.Date         N.N.N.Y.N.N.Y.Y.N.N.N.N
Home_State   Sample.Address.State
                                   %Library.String       N.N.N.N.N.N.N.N.N.N.N.N
 
 
Statement Parameters (property 'parameters') :Nbr. Type precision scale nullable colName      columntype
---- ---- --------- ----- -------- ------------ ----------
 
 
Formal Parameters (property 'formalParameters') :Nbr. Type precision scale nullable colName      columntype
---- ---- --------- ----- -------- ------------ ----------
 
 
Objects:
 
Col Column Name Extent            ExportCall-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 1id          Sample.Person# #class(Sample.Person%).SQLQuickLoad
Copy the code

This returns a list of two tables for the selected field. The first metadata table lists the column definition information:

The second column metadata table lists the extended column information. The extended column information table lists each column with twelve Boolean flags (SQLRESULTCOL), which are specified as Y (yes) or N (no) :

The extended column information metadata table lists the column name (SQL name or column alias), link property (persistence class property of the link), and type class (data type class) for each selected field. Note that the link property lists the persistence class name (not the SQL table name) and the property name (not the column alias).

• For plain table fields (SELECT Name FROM Sample.Person) :Linked Prop=Sample.Person.Name, Type Class=%Library.String.
• For table RowID (SELECT %ID FROM Sample.Person) :Linked Prop= [none], Type Class=Sample.Person.
• For expressions, aggregations, text,HostVarOr subquery (SELECT COUNT(Name) FROM Sample.Person) :Linked Prop= [none], Type Class=%Library.BigInt.
• For your reference% Serial ObjectEmbedded object properties (SELECT Home_State FROM Sample.Person). Linked Prop=Sample.Address.State, Type Class=%Library.String.
• For reference% SerialObjectFields of embedded objects (SELECT Home FROM Sample.Person). Linked Prop=Sample.Person.Home, Type Class=Sample.Address.

In this example, the Home_State field in Sample.Person references the State property of the % SerialObject class sample.address.

The following example returns the metadata of the called stored procedure with one formal argument (that is, statement argument) :

/// d ##class(PHA.TEST.SQL).MetaData4()
ClassMethod MetaData4(a)
{
	SET $NAMESPACE="SAMPLES"
	SET mysql = "CALL Sample.SP_Sample_By_Name(?) "
	SET tStatement = ##class(%SQL.Statement%).New(a)SET qStatus = tStatement.%Prepare(.mysql)
	IF qStatus'=1 { WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT } DO tStatement.%Metadata.%Display() WRITE ! ,"End of metadata" }Copy the code

It returns not only column (field) information, but also statement parameters, formal parameters, and object values.

The following example returns metadata with three formal parameters. The question mark (?) is used for one of these three arguments. Specifies to make it a statement parameter:

/// d ##class(PHA.TEST.SQL).MetaData5()
ClassMethod MetaData5(a)
{
	SET $NAMESPACE="SAMPLES"
	SET mycall = "CALL personsets(? ,'MA')"
	SET tStatement = ##class(%SQL.Statement%).New(0,"sample")
	SET qStatus = tStatement.%Prepare(mycall)
	IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT} DO tStatement.%Metadata.%Display() WRITE ! ,"End of metadata" }Copy the code

DHC-APP>d ##class(PHA.TEST.SQL).MetaData5(a)Columns (SQLRESULTCOL.property 'columns') :Column Name  Type Prec Scale Null Label        Table        Schema       CType
-----------  ---- ---- ----- ---- ------------ ------------ ------------ -----
 
 
Extended Column Info (SQLRESULTCOL)
     Flags: 1:AutoIncrement, 2:CaseSensitive, 3:Currency, 4:ReadOnly, 5:RowVersion,
            6:Unique, 7:Aliased, 8:Expression, 9:Hidden, 10:Identity, 11:KeyColumn,
            12:RowId
 
Column Name  Linked Prop           Type Class            Flags
------------ --------------------- --------------------- -----------------------
 
 
Statement Parameters (property 'parameters') :Nbr. Type precision scale nullable colName      columntype-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 1 12 50 0 2name            1
 
 
Formal Parameters (property 'formalParameters') :Nbr. Type precision scale nullable colName      columntype-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 1 4 4 0 2_isc_sp_ret_val5, 2, 12, 50, 0, 2name1, 3, 12, 50, 0, 2state           1
 
 
Objects:
 
Col Column Name Extent            ExportCall
--- ----------- ----------------- -----------------------------
 
End of metadata
Copy the code

Note that this metadata does not return any column information, but the Statement Parameters, formal Parameters list contains column names and data types.

Query Parameter metadata

After preparing with the % sql.statement class, you can return metadata about the query parameters: the input parameter (specified as question mark (?)) ), enter the host variable (specified as: varname) and constant (literal value). The following metadata can be returned:

• ?parameter: parameterCountattribute
• The ODBC data type is?parameter: % sql.statementMetadata % Display ()Example method Statement Parameters list.
• ? , v (: var) and c (constant) arguments list:% GetImplementationDetails ()Instance methods, as described in the results of successful preparation.
• ? , ODBC data types for v (: var) and c (constant) arguments:formalParametersProperties.

% sql.statementMetadata % Display () instance method “formal parameters” list.

• Query text, where the following parameters are displayed:% GetImplementationDetails ()Instance methods, as described in successful preparation results.

The statement metadata % Display () method lists “statement parameters” and “formal parameters”. For each parameter, it lists the order parameter number, ODBC data type, precision, decimal number, whether the parameter can be null (2 means a value is always provided) and its corresponding attribute name (colName) and column type.

Note that some ODBC data types are returned as negative integers.

The following example returns each query parameter (? , : var and constant) of ODBC data types. Note that the TOP parameter is returned as data type 12 (VARCHAR) instead of data type 4 (INTEGER), because TOP ALL can be specified:

/// d ##class(PHA.TEST.SQL).MetaData6()
ClassMethod MetaData6(a)
{
	SET myquery = 4
	SET myquery(1) = "SELECT TOP ? Name,DOB,Age+10 "
	SET myquery(2) = "FROM Sample.Person"
	SET myquery(3) = "WHERE %ID BETWEEN :startid :endid AND DOB=?"
	SET myquery(4) = "ORDER BY $PIECE(Name,',',?) "
	SET tStatement = ##class(%SQL.Statement%).New(a)SET qStatus = tStatement.%Prepare(.myquery)
	IF qStatus'=1 { WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT } SET prepmeta = tStatement.%Metadata WRITE "Number of ? parameters=",prepmeta.parameterCount,! SET formalobj = prepmeta.formalParameters SET i=1 WHILE formalobj.GetAt(i) { SET prop=formalobj.GetAt(i) WRITE prop.colName," type= ",prop.ODBCType,! SET i=i+1 } WRITE "End of metadata" }Copy the code

After Execute is executed, parameter metadata cannot be obtained from query result set metadata. In the result set, all parameters are resolved. Therefore parameterCount = 0 and formalParameters does not contain any data.

Query Result set metadata

After executing with the % sql. Statement class, result set metadata can be returned with a call:

• % SQL. The StatementResultClass properties.
• % sql.statementResult % GetMetadata ()Method, access% SQL. The StatementMetadataClass attribute.

% SQL. The StatementResult properties

After executing the query, % sql.statementResult returns:

• % StatementTypeProperty returns the integer code corresponding to the most recently executed SQL statement. Here is a partial list of these integer codes:1 = SELECT; 2 = INSERT; 3 = UPDATE; 4 = DELETE or TRUNCATE TABLE; 9 = CREATE TABLE; 15 = CREATE INDEX; 45 = CALL.
• % StatementTypeNameThe calculated properties are based on% StatementTypeReturns the command name of the most recently executed SQL statement. The name is returned in uppercase letters. Please note that,TRUNCATE TABLEThe action will act asDELETETo return. Even if the update operation is performed,INSERT OR UPDATEAlso will be used asINSERTTo return.
• % ResultColumnCountProperty returns the number of columns in the result set row.

The following example shows these properties:

/// d ##class(PHA.TEST.SQL).MetaData7()
ClassMethod MetaData7(a)
{
	SET myquery = "SELECT TOP ? Name,DOB,Age FROM Sample.Person WHERE Age > ?"
	SET tStatement = ##class(%SQL.Statement%).New(a)SET qStatus = tStatement.%Prepare(myquery)
	IF qStatus'= 1 {WRITE "% Prepare failed:" DO $System. The Status. The DisplayError (qStatus) QUIT} SET rset = tStatement. % the Execute IF zhongguo kuangye daxue (10) rset.%SQLCODE=0 { WRITE "Statement type=",rset.%StatementType,! WRITE "Statement name=",rset.%StatementTypeName,! WRITE "Column count=",rset.%ResultColumnCount,! WRITE "End of metadata" } ELSE { WRITE ! ,"SQLCODE=",rset.%SQLCODE," ",rset.%Message } }Copy the code

% sql.statementResult % GetMetadata ()

After execution, the % sqL.statementMetadata class attribute can be accessed using the % sqL.statementResult % GetMetadata () method. These are the same properties accessed by the % sqL. Statement % Metadata property after Prepare.

The following example shows the properties:

/// d ##class(PHA.TEST.SQL).MetaData8()
ClassMethod MetaData8(a)
{
	SET myquery=2
	SET myquery(1)="SELECT Name AS VendorName,LastPayDate,MinPayment,NetDays,"
	SET myquery(2)="AVG(MinPayment),$HOROLOG,%TABLENAME FROM Sample.Vendor"
	SET tStatement = ##class(%SQL.Statement%).New(a)SET qStatus = tStatement.%Prepare(.myquery)
	IF qStatus'=1 { WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT } SET rset = tStatement.%Execute() IF rset.%SQLCODE=0 { SET rsmeta=rset.%GetMetadata() SET x=rsmeta.columns.Count() SET x=1 WHILE rsmeta.columns.GetAt(x) { SET column=rsmeta.columns.GetAt(x) WRITE ! ,x," ",column.colName," is data type ",column.ODBCType WRITE " with a size of ",column.precision," and scale = ",column.scale SET x=x+1 } } ELSE { WRITE ! ,"SQLCODE=",rset.%SQLCODE," ",rset.%Message } WRITE ! ,"End of metadata" }Copy the code

Note that the result set metadata does not provide parameter metadata. This is because the Execute operation parses all the parameters. Therefore, parameterCount = 0 in the result set, and formalParameters does not contain any data.

Auditing dynamic SQL

InterSystems IRIS supports optional auditing of dynamic SQL statements. Dynamic SQL auditing is performed when % System / % SQL/DynamicStatement is enabled for System auditing events. By default, this system audit event is not enabled.

If % System / % SQL/DynamicStatement is enabled, the System automatically reviews every % SQL.Statement that is executed system-wide. Audits record information in an audit database.

To view the audit database, go to the administrative Portal, System Administration, select Security, Audit, and then view the audit database. You can set the Event Name filter to DynamicStatement to limit the View Audit Database to Dynamic SQL statements. The audit database lists the time (local timestamp), user, PID (process ID), and event description. Note Specify the type of the dynamic SQL statement. For example, SQL SELECT Statement (% sql.statement) or SQL CREATE VIEW Statement (% SQL.statement).

Additional information, including event data, can be listed by selecting the event details link. Event data includes the values of the SQL statement executed and any parameters of that statement. Such as:

SELECT TOP ? Name , Age FROM Sample . MyTest WHERE Name %STARTSWITH ?
/*#OPTIONS {"DynamicSQLTypeList":",1"} */ 
Parameter values:
%CallArgs(1) =5 
%CallArgs(2) ="Fred"
Copy the code

The total length of event data (including statements and parameters) is 3,632,952 characters. If the statement and argument are longer than 3632952, the event data will be truncated.

InterSystems IRIS also supports auditing of ODBC and JDBC statements (event name = XDBCStatement), and auditing of embedded SQL statements (event name = EmbeddedStatement).