New post
Question
· Jun 2, 2017

ResultSet / SQL.StatementResult: How to know if a column value is $list'ed

#Object Data Model #SQL #Caché

I'm breaking my head trying to figure how to solve this issue. 

When using SQL, If the column (property) is populated with at least one value and is a %List, then I can check if it's $listvalid and $listlength(column) > 0.

Now let's suppose there's no data inserted for that column on next row.
I can't simply use $listvalid because $listvalid("") = 1. Yeah, empty values is a $list for Caché.

So my question is, how do I discover if the regarding column is indeed a %List independently of being empty or not when retrieving it's value via SQL?

I tried checking for the row metadata to discover any %List reference, but with no success.

Any ideas?

Discussion (6)2
Log in or sign up to continue
Rubens Silva · Jun 2, 2017

Nice approach.

I got almost everything I need, except the property name. When the column is not aliased I can take it's name normally from label and colName.

However it breaks when SQL column is aliased, both colName and label refers to the same aliased column.

By the way, you can get it using:

resultset.%GetMetadata().columns.GetAt(index).colName // and label.

Notice that columns must be an instance of %ResultSet.MD.Column.

1 0
Kyle Baxter · Jun 2, 2017

Wellll technically NULL is a valid value for all datatypes.  I think what you want to do is to interrogate the metadata information which you can do like so:

SAMPLES>s stmt=##class(%SQL.Statement).%New()

SAMPLES>w stmt.%Prepare("SELECT ID,Name FROM Sample.Person")
1
SAMPLES>w stmt.%Metadata.%Display()
(You'll get lots of output)

Note that you can do this even without running the query!  You can look at %SQL.StatementMetadata in the Class Reference for more methods and properties that can help you.  

Note 2 - Sean's method is also perfectly valid.  The only advantage to mine is that you don't have to run the query.

1 0