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.

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.

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.

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.