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

Question

Question

Rubens Silva · Jun 2, 2017

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

Sean Connelly · Jun 2, 2017

Hi Rubens,

Is this what you are after...

set rs=statement.%Execute()
set meta=rs.%GetMetadata()
set colmeta=meta.columns.GetAt(1)
set runtimeType=colmeta.property.RuntimeType

Sean.

0 0

score 0 · Answer 1 · 2017-06-02T09:04:27-04:00

Oh, cool.

Too bad that %ResultSet for Caché 2010 doesn't have this property metadata though.

I guess I'll need to workaround it and disable the feature I'm trying to implement.

score 0 · Answer 2 · 2017-06-02T09:07:38-04:00

That's perfect to diagnosticate which infos I've available. I didn't know about that %Display method on metadata.
But ultimately I need this info for internal management.

score 1 · Answer 3 · 2017-06-02T09:16:00-04:00

What about...

&sql(select RuntimeType into :qRuntimeType from %Dictionary.CompiledProperty where ID1='Foo.MyClass||MyProperty')

score 1 · Answer 4 · 2017-06-02T10:18:00-04:00

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.

score 1 · Answer 5 · 2017-06-02T08:56:51-04:00

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.