New post
Discussion
· Nov 24, 2020

SQL Statement Metadata

#InterSystems IRIS

InterSystems Queryable Statement Metadata

Dynamic SQL allows the user to dynamic create, populate and query tables, call procedures and functions, and so on - all without writing any ObjectScript. Of course, this capability is completely normal for xDBC clients. InterSystems Dynamic SQL has been available in Caché for several years and is available in all versions of InterSystems IRIS. What I am about to describe should work for all versions of InterSystems IRIS and for all versions of InterSystems CE that support SQL Table-Valued functions.

If people are interested then I'll learn how to post the single class needed to support this functionality.  First a quick demo.

Basic Query - return column names as a JSON Array

SELECT json_arrayagg(colName)
  FROM utility.statement_columns('SELECT Name,DOB,Home_City,Home_State from Sample.Person')

 

The single row returned by this query is a JSON array.

 

["Name","DOB","Home_City","Home_State"]

While this is a useful query, there is more.

Query Statement Columns Returning Array of Objects

If you need more than just the column name for each column in the SQL statement, then simply use JSON_OBJECT in the JSON_ARRAYAGG.

 

SELECT json_arrayagg(json_object('name':colname,'type':odbctype,'nullable':isnullable,'readonly':isreadonly))
  FROM utility.statement_columns('SELECT Name,DOB,Home_City,Home_State from Sample.Person')
[ { "name": "Name", "type": 12, "nullable": false, "readonly": false }, { "name": "DOB", "type": 9, "nullable": true, "readonly": false }, { "name": "Home_City", "type": 12, "nullable": true, "readonly": false }, { "name": "Home_State", "type": 12, "nullable": true, "readonly": false } ]

 

Of Course, Basic Queries Work Too

No need to return this column metadata as JSON formatted strings. Simple SELECT works as well.

 

SELECT colname,odbctype,isnullable,isreadonly FROM utility.statement_columns('SELECT Name,DOB,Home_City,Home_State from Sample.Person')
 
colName ODBCType isNullable isReadOnly
Name 12 0 0
DOB 9 1 0
Home_City 12 1 0
Home_State 12 1 0
Discussion (6)1
Log in or sign up to continue
Dan Pasco Nov 25, 2020 to Robert Cemper 
Class utility.StatementColumns Extends %SQL.CustomQuery
{

Parameter SQLNAME As String = "statement_columns";

Property columns As %Collection.ListOfObj [ Internal, Private ];

Property columnPtr As %String [ Internal, Private ];

Property atEnd As %Boolean [ Internal, Private ];

Property columnType As %String;

Property colName As %String(MAXLEN = 255);

Property ODBCType As %Integer;

Property precision As %Integer;

Property scale As %Integer;

Property isNullable As %Boolean;

Property label As %String(MAXLEN = 255);

Property tableName As %String;

Property schemaName As %String;

Property qualifier As %String;

Property isAutoIncrement As %Boolean;

Property isCaseSensitive As %Boolean;

Property isCurrency As %Boolean;

Property isReadOnly As %Boolean;

Property isRowVersion As %Boolean;

Property isUnique As %Boolean;

Property isAliased As %Boolean;

Property isExpression As %Boolean;

Property isHidden As %Boolean;

Property isIdentity As %Boolean;

Property isKeyColumn As %Boolean;

Property isRowId As %Boolean;

Property isList As %Boolean;

Property property As %Dictionary.CompiledProperty;

/// The objects type class
Property typeClass As %Dictionary.CompiledClass;

Property clientType As %Integer;

Method %OpenCursor(statement As %String) [ Private ]
{
    set ..columns = $system.SQL.Prepare(statement).%Metadata.columns
    set ..atEnd = 0
    set ..columnPtr = ""
}

Method %FetchCursor() As %Library.Integer
{
    set response = 0
    if '..atEnd {
        set next = ..columnPtr
        set column = ..columns.GetNext(.next)
        if next '= "" {
            set response = 1
            set ..columnPtr = next
            do ..mapColumnToRow(column)
        } else {
            set ..atEnd = 1
            set ..columnPtr = ""
            do ..clearRow()
        }
    }
    return response
}

Method mapColumnToRow(column As %SQL.StatementColumn)
{
    set ..columnType = "SQLRESULTCOL"
    set ..colName = column.colName
    set ..ODBCType = column.ODBCType
    set ..precision = column.precision
    set ..scale = column.scale
    set ..isNullable = column.isNullable
    set ..label = column.label
    set ..tableName = column.tableName
    set ..schemaName = column.schemaName
    set ..qualifier = column.qualifier
    set ..isAutoIncrement = column.isAutoIncrement
    set ..isCaseSensitive = column.isCaseSensitive
    set ..isCurrency = column.isCurrency
    set ..isReadOnly = column.isReadOnly
    set ..isRowVersion = column.isRowVersion
    set ..isUnique = column.isUnique
    set ..isAliased = column.isAliased
    set ..isExpression = column.isExpression
    set ..isHidden = column.isHidden
    set ..isIdentity = column.isIdentity
    set ..isKeyColumn = column.isKeyColumn
    set ..isRowId = column.isRowId
    set ..isList = column.isList
}

Method clearRow()
{
    set ..columnType = ""
    set ..colName = ""
    set ..ODBCType = ""
    set ..precision = ""
    set ..scale = ""
    set ..isNullable = ""
    set ..label = ""
    set ..tableName = ""
    set ..schemaName = ""
    set ..qualifier = ""
    set ..isAutoIncrement = ""
    set ..isCaseSensitive = ""
    set ..isCurrency = ""
    set ..isReadOnly = ""
    set ..isRowVersion = ""
    set ..isUnique = ""
    set ..isAliased = ""
    set ..isExpression = ""
    set ..isHidden = ""
    set ..isIdentity = ""
    set ..isKeyColumn = ""
    set ..isRowId = ""
    set ..isList = ""
}

}
1 1