Skip to main content

SQL Metadata

SQL Metadata

Dynamic SQL provides the following types of metadata:

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

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:

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.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.

FeedbackOpens in a new tab