SQL Metadata

Dynamic SQL provides the following types of metadata:

After a Prepare, metadata describing the type of query.
After a Prepare, metadata describing the select-items in the query (Columns and Extended Column Info).
After a Prepare, metadata describing the query arguments: ? parameters, :var parameters, and constants. (Statement Parameters, Formal Parameters, and Objects)
After an Execute, metadata describing the query result set.

%SQL.StatementMetadataOpens in a new tab property values are available following a Prepare operation (%Prepare(), %PrepareClassQuery(), or %ExecDirect()).

You can return %SQL.StatementMetadataOpens in a new tab properties directly for the most recent %Prepare().
You can return the %SQL.StatementOpens in a new tab %Metadata property containing the oref for the %SQL.StatementMetadataOpens in a new tab properties. This enables you to return metadata for multiple Prepare operations.

A SELECT or CALL statement returns all of this metadata. An INSERT, UPDATE, or DELETE returns Statement Type Metadata and the Formal Parameters.

Statement Type Metadata

Following a Prepare using the %SQL.StatementOpens in a new tab class, you can use the %SQL.StatementMetadataOpens in a new tab statementType property to determine what type of SQL statement was prepared, as shown in the following example. This example uses the %SQL.StatementOpens in a new tab %Metadata property to preserve and compare the metadata for two Prepare operations:

   set tStatement = ##class(%SQL.Statement).%New()
   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"

The Class Reference entry for the statementTypeOpens in a new tab property lists the statement type integer codes. The most common codes are 1 (a SELECT query) and 45 (a CALL to a stored query).

You can return the same information using the %GetImplementationDetails()Opens in a new tab instance method, as described in Results of a Successful Prepare.

After executing a query, you can return the statement type name (for example, SELECT) from the result set.

Select-item Metadata

Following a Prepare of a SELECT or CALL statement using the %SQL.StatementOpens in a new tab class, you can return metadata about each select-item column specified in the query, either by displaying all of the metadata or by specifying individual metadata items. This column metadata includes ODBC data type information, as well as client type and InterSystems Objects property origins and class type information.

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

  set myquery = "SELECT %ID AS id,Name,DOB,Age,AVG(Age),CURRENT_DATE,Home_State FROM Sample.Person"
  set tStatement = ##class(%SQL.Statement).%New()
  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"

The following example returns the column name (or column alias), ODBC data type, maximum data length (precision), and scale for each select-item field:

  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()
  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 !,x," ",column.colName," is data type ",column.ODBCType
    write " with a size of ",column.precision," and scale = ",column.scale
    set x=x+1 }
  write !,"End of metadata"

The following example displays all of the column metadata using the %SQL.StatementMetadataOpens in a new tab %Display()Opens in a new tab instance method:

  set query = "SELECT %ID AS id,Name,DOB,Age,AVG(Age),CURRENT_DATE,Home_State FROM Sample.Person"
  set tStatement = ##class(%SQL.Statement).%New()
  set qStatus = tStatement.%Prepare(query)
  if qStatus'=1 {write "%Prepare failed:" do $System.Status.DisplayError(qStatus) quit}
  do tStatement.%Metadata.%Display()
  write !,"End of metadata"

This returns two table listings of the selected fields. The first columns metadata table lists column definition information:

Display Header	%SQL.StatementColumnOpens in a new tab Property	Description
Column Name	colName	The SQL name of the column. If the column is given an alias, the column alias, not the field name, is listed here. Names and aliases are truncated to 12 characters. For an expression, aggregate, literal, host variable, or subquery, the assigned “Expression_n”, “Aggregate_n”, “Literal_n”, “HostVar_n”, or “Subquery_n” label is listed (with n being the SELECT item sequence number). If you have assigned an alias to an expression, aggregate, literal, host variable, or subquery, the alias is listed here.
Type	ODBCType	The integer code for the ODBC data type. These codes are listed in the Integer Codes for Data Types section of the Data Types reference page in the InterSystems SQL Reference. Note that these ODBC data type codes are not the same as the CType data type codes.
Prec	precision	The precision or maximum length, in characters. Precision and scale metadata for TIME data types are described in Date, Time, PosixTime, and TimeStamp Data Types.
Scale	scale	The maximum number of fractional decimal digits. Returns 0 for integer or non-numeric values. Precision and scale metadata for TIME data types are described in Date, Time, PosixTime, and TimeStamp Data Types.
Null	isNullable	An integer value that indicates whether the column is defined as Non-NULL (0), or if NULL is permitted (1). The RowID returns 0. If the SELECT item is an aggregate or subquery that could result in NULL, or if it specifies the NULL literal, this item is set to 1. If the SELECT item is an expression or host variable, this item is set to 2 (cannot be determined).
Label	label	The column name or column alias (same as Column Name).
Table	tableName	The SQL table name. The actual table name is always listed here, even if you have given the table an alias. If the SELECT item is an expression or an aggregate no table name is listed. If the SELECT item is a subquery, the subquery table name is listed.
Schema	schemaName	The table’s schema name. If no schema name was specified, returns the system-wide default schema. If the SELECT item is an expression or an aggregate no schema name is listed. If the SELECT item is a subquery no schema name is listed.
CType	clientType	The integer code for the client data type. See the %SQL.StatementColumnOpens in a new tab clientTypeOpens in a new tab property for a list of values.

The second columns metadata table lists extended column information. The Extended Column Info table lists each column with twelve boolean flags (SQLRESULTCOL), specified as Y (Yes) or N (No):

Boolean Flag	%SQL.StatementColumnOpens in a new tab Property	Description
1: AutoIncrement	isAutoIncrement	The RowID and IDENTITY fields returns Y.
2: CaseSensitive	isCaseSensitive	A string data type field with %EXACT collation returns Y. A property that references a %SerialObject embedded object returns Y.
3: Currency	isCurrency	A field defined with a data type of %Library.CurrencyOpens in a new tab, such as the MONEY data type.
4: ReadOnly	isReadOnly	An Expression, Aggregate, Literal, HostVar, or Subquery returns Y. The RowID, IDENTITY, and RowVersion fields returns Y.
5: RowVersion	isRowVersion	The RowVersion field returns Y.
6: Unique	isUnique	A field defined as having a unique value constraint. The RowID and IDENTITY fields returns Y.
7: Aliased	isAliased	The system supplies an alias to a non-field select-item. Therefore, an Expression, Aggregate, Literal, HostVar, or Subquery returns Y, whether or not the user replaced the system alias by specifying a column alias. This flag is not affected by user-specified column aliases.
8: Expression	isExpression	An Expression returns Y.
9: Hidden	isHidden	If the table is defined with %PUBLICROWID or SqlRowIdPrivate=0 (the default), the RowID field returns N. Otherwise, the RowID field returns Y. A property that references a %SerialObject embedded object returns Y.
10: Identity	isIdentity	A field defined as an IDENTITY field returns Y. The RowID field if the RowID is not hidden returns Y.
11: KeyColumn	isKeyColumn	A field defined as a primary key field or the target of a foreign key constraint. The RowID field returns Y.
12: RowID	isRowId	The RowID and IDENTITY fields returns Y.
13: isList	isList	A field defined as data type %Library.List or %Library.ListOfBinary, or a field that is a list or array collection returns Y. CType (client data type)=6. An expression using the $LISTBUILD or $LISTFROMSTRING function to generate a list returns Y.

The Extended Column Info metadata table lists the Column Name (the SQL name or column alias), the Linked Prop (linked persistent class property) and Type Class (data type class) for each of the selected fields. Note that the Linked Prop lists the persistent class name (not the SQL table name) and the property name (not the column alias).

For an ordinary table field (SELECT Name FROM Sample.Person): Linked Prop=Sample.Person.Name, Type Class=%Library.String.
For the table’s RowID (SELECT %ID FROM Sample.Person): Linked Prop= [none], Type Class=Sample.Person.
For an Expression, Aggregate, Literal, HostVar, or Subquery (SELECT COUNT(Name) FROM Sample.Person): Linked Prop= [none], Type Class=%Library.BigInt.
For a referenced %SerialObject embedded object property (SELECT Home_State FROM Sample.Person). Linked Prop=Sample.Address.State, Type Class=%Library.String.
For a field referencing a %SerialObject embedded object (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 for a called stored procedure with one formal parameter, which is also a statement parameter:

  set mysql = "CALL Sample.SP_Sample_By_Name(?)"
  set tStatement = ##class(%SQL.Statement).%New()
  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"

It returns not only column (field) information, but also values for Statement Parameters, Formal Parameters, and Objects.

The following example returns the metadata for a with three formal parameters. One of these three parameters is designated with a question mark (?) making it a statement parameter:

  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"

Note that this metadata returns no column information, but the Statement Parameters, Formal Parameters lists contain the column names and data types.

Query Arguments Metadata

Following a Prepare using the %SQL.StatementOpens in a new tab class, you can return metadata about query arguments: input parameters (specified as a question mark (?)), input host variables (specified as :varname), and constants (literal values). The following metadata can be returned:

Count of ? parameters: parameterCountOpens in a new tab property
ODBC data types of ? parameters: %SQL.StatementMetadataOpens in a new tab %Display()Opens in a new tab instance method Statement Parameters list.
List of ?, v (:var), and c (constant) parameters: %GetImplementationDetails()Opens in a new tab instance method, as described in Results of a Successful Prepare.
ODBC data types of ?, v (:var), and c (constant) parameters: formalParametersOpens in a new tab property.
%SQL.StatementMetadataOpens in a new tab %Display()Opens in a new tab instance method Formal Parameters list.
Text of query showing these arguments: %GetImplementationDetails()Opens in a new tab instance method, as described in Results of a Successful Prepare.

The statement metadata %Display() method lists the Statement Parameters and Formal parameters. For each parameter it lists the sequential parameter number, ODBC data type, precision, scale, whether it is nullable (2 means that a value is always supplied), and its corresponding property name (colName), and column type.

Note that some ODBC data types are returned as negative integers. For a table of ODBC data type integer codes, see the Data Types reference page in the InterSystems SQL Reference.

The following example returns the ODBC data types of each of the query arguments (?, :var, and constants) in order. Note that the TOP argument is returned as data type 12 (VARCHAR) rather than 4 (INTEGER) because it is possible to specify TOP ALL:

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

Following an Execute, arguments metadata is not available from the query result set metadata. In a result set all parameters are resolved. Therefore parameterCount = 0, and formalParameters contains no data.

Query Result Set Metadata

Following an Execute using the %SQL.StatementOpens in a new tab class, you can return result set metadata by invoking:

%SQL.StatementResultOpens in a new tab class properties.
%SQL.StatementResultOpens in a new tab %GetMetadata()Opens in a new tab method, accessing %SQL.StatementMetadataOpens in a new tab class properties.

%SQL.StatementResult Properties

Following an Execute query operation, %SQL.StatementResultOpens in a new tab returns:

The %StatementTypeOpens in a new tab property returns an integer code that corresponds to the SQL statement most recently executed. The following 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. For a complete list of these values, refer to %SQL.StatementResultOpens in a new tab in the InterSystems Class Reference.
The %StatementTypeNameOpens in a new tab calculated property returns the command name of the SQL statement most recently executed, based on the %StatementType. This name is returned in uppercase letters. Note that a TRUNCATE TABLE operation is returned as DELETE. An INSERT OR UPDATE is returned as INSERT, even when it performed an update operation.
The %ResultColumnCountOpens in a new tab property returns the number of columns in the result set rows.

The following example shows these properties:

  set myquery = "SELECT TOP ? Name,DOB,Age FROM Sample.Person WHERE Age > ?"
  set tStatement = ##class(%SQL.Statement).%New()
  set qStatus = tStatement.%Prepare(myquery)
    if qStatus'=1 {write "%Prepare failed:" do $System.Status.DisplayError(qStatus) quit}
  set rset = tStatement.%Execute(10,55)
  if 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 }

%SQL.StatementResult %GetMetadata()

Following an Execute, you can use the %SQL.StatementResultOpens in a new tab %GetMetadata()Opens in a new tab method to access the %SQL.StatementMetadataOpens in a new tab class properties. These are the same properties accessed by the %SQL.StatementOpens in a new tab %Metadata property following a Prepare.

The following example shows the properties:

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

Note that the result set metadata does not provide arguments metadata. This is because the Execute operation resolves all parameters. Therefore, in a result set, parameterCount = 0, and formalParameters contains no data.

Auditing Dynamic SQL

Returning Multiple Result Sets