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 |