Get columns dynamically?

Question

Question

Scott Beeson · Feb 12, 2016

In MSSQL I think you can do something like this:

select *
from HS_IHE_ATNA_Repository.COLUMNS
where TABLE_NAME=&#39;Aggregation&#39;

How can I do this in Cache SQL?

[%msg: < Table 'HS_IHE_ATNA_REPOSITORY.COLUMNS' not found>]

Discussion (11)1

Log in or sign up to continue

Timothy Leavitt · Feb 16, 2016

This has been bothering me a little bit; %Dictionary.* should really be a last-resort option, in my opinion, and it isn't easy to use it to get the full picture from an SQL perspective.

Here are some alternative/possibly-better solutions, using %SQL.StatementMetadata and INFORMATION_SCHEMA. It looks like INFORMATION_SCHEMA is more exactly what you were looking for, if you're running on a recent enough Caché version (2015.1+). I haven't been able to find documentation on it other than the class reference, though.

/// NOTE: It could be good to validate pTableName to avoid SQL injection. (Outside the scope of this demo.)
/// This works pre-2015.1 (since %SQL.Statement was introduced - maybe 2012.2+?)
ClassMethod GetTableColumns(pTableName As %String) As %List
{
    #dim tResult As %SQL.StatementResult
    #dim tMetadata As %SQL.StatementMetadata
    Set tStmt = ##class(%SQL.Statement).%New()
    $$$ThrowOnError(tStmt.%Prepare("select top 0 * from "_pTableName))
    Set tResult = tStmt.%Execute(), tMetadata = tResult.%GetMetadata()
    Set tCols = ""
    For i=1:1:tMetadata.columnCount {
        Set tCols = tCols_$ListBuild(tMetadata.columns.GetAt(i).colName)
    }
    Quit tCols
}

/// This will only work on 2015.1+; INFORMATION_SCHEMA is a new feature. For more information, see the class reference for it in the documentation.
ClassMethod GetTableColumnsNew(pTableName As %String) As %List
{
    #dim tResult As %SQL.StatementResult
    Set tStmt = ##class(%SQL.Statement).%New()
    Set tSchema = $Piece(pTableName,".")
    Set tTableName = $Piece(pTableName,".",2)
    $$$ThrowOnError(tStmt.%Prepare("select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = ? and TABLE_NAME = ?"))
    Set tResult = tStmt.%Execute(tSchema,tTableName)
    Set tCols = ""
    While tResult.%Next(.tSC) {
        Set tCols = tCols_$ListBuild(tResult.%Get("COLUMN_NAME"))
    }
    $$$ThrowOnError(tSC)
    Quit tCols
}

Using this:

SAMPLES>set cols = ##class(Demo.TableColumns).GetTableColumns("Sample.Person")
SAMPLES>w $lts(cols)
ID,Age,DOB,FavoriteColors,Name,SSN,Spouse,Home_City,Home_State,Home_Street,Home_Zip,Office_City,Office_State,Office_Street,Office_Zip
SAMPLES>set cols = ##class(Demo.TableColumns).GetTableColumnsNew("Sample.Person")
SAMPLES>w $lts(cols) ID,Age,DOB,FavoriteColors,Name,SSN,Spouse,Home_City,Home_State,Home_Street,Home_Zip,Office_City,Office_State,Office_Street,Office_Zip

4 0

score 0 · Answer 1 · 2016-02-12T14:11:54-05:00

As you may already know, tables in Caché is a classes. And Class defintion stores in %Dictionary Package.

SELECT * FROM %Dictionary.PropertyDefinition where parent='Sample.Person'

score 0 · Answer 2 · 2016-02-12T14:40:31-05:00

Awesome! But I have a problem.

SELECT * FROM %Dictionary.PropertyDefinition
WHERE parent = 'HS.IHE.ATNA.Repository.Aggregation'

This returns 11 results, but that table has 41 columns.

score 3 · Answer 3 · 2016-02-12T14:49:38-05:00

Perhaps try:

SELECT NVL(SqlFieldName,Name) FROM %Dictionary.CompiledProperty
WHERE parent = 'HS.IHE.ATNA.Repository.Aggregation' and Transient = 0

%Dictionary.PropertyDefinition only includes properties defined in a given class; %Dictionary.CompiledProperty includes properties that are inherited.

score 0 · Answer 4 · 2016-02-12T14:55:42-05:00

Scott Beeson · Feb 12, 2016

Perfect!

0 0

score 0 · Answer 5 · 2016-02-16T17:00:01-05:00

I appreciate you taking the time to respond and clarify but I really need to be able to do this via SQL syntax.

score 0 · Answer 6 · 2016-02-16T17:06:11-05:00

The original suggestion might still work, but there are caveats with serial objects and probably some other edge cases.

What's your use case, if you don't mind elaborating? Why is SQL syntax necessary? What Caché version are you running (run in terminal: write $zversion)?

score 0 · Answer 7 · 2016-02-25T15:41:48-05:00

Scott Beeson · Feb 25, 2016

I'm using SQL because this is for a 3rd party reporting platform.

0 0

score 3 · Answer 8 · 2016-02-17T01:44:46-05:00

Scott,

the INFORMATION_SCHEMA sample works with pure SQL. Just run the query and substitute parameters:

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = ? and TABLE_NAME = ?

Tim just wrote a COS wrapper to output the column names as a string.

score 0 · Answer 9 · 2016-02-25T15:42:03-05:00

Scott Beeson · Feb 25, 2016

Ahh, thanks for the clarification.

0 0

score 2 · Answer 10 · 2016-02-25T15:46:06-05:00

For the record, the following did work once Stefan Wittmann pointed out I could strip it out of your code :)

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'HS_IHE_ATNA_Repository' and TABLE_NAME = 'Aggregation'