Query Arguments Metadata

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.