SQL Statement Metadata
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 |
Comments
JUST Great !
I like it.
Robert - what do you think? Should I just post the class text (single class, simple) or try to put it on GitHub?
Just post the class.
And then Github ) And Open Exchange ;)
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 = ""
}
}Looks interesting.