SQL Metadata

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:

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):

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

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, see %SQL.StatementResultOpens in a new tab.

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