Need to find a way to get the columns in a query from Query object.

Question

Question

D Sun · May 12, 2017

Recently I have encounter an issue to get the columns out from a query without execute it.

For example:

I have a Query looks like below. How can I get the 'col1,col2,col3,col4' or the text of the query("select col1,col2,col3,col4 from mytable") back?

Query myquery() As %SQLQuery(CONTAINID = 0) [ SqlProc ]
{
SELECT col1, col2, col3,col4
FROM mytable
}

Discussion (2)0

Log in or sign up to continue

Eduard Lebedyuk · May 12, 2017

To get query text you can query %Dictionary.QueryDefinition. For example:

SELECT *
FROM %Dictionary.QueryDefinition
WHERE Parent = class

Would return all queries for a class.

As for getting only columns, here's some ideas:

If the query was executed somewhen and cached, there would be a generated class, holding metadata among other things
There are generated methods QueyNameGetInfo and QueryNameGetODBCInfo - they return metainformation about query columns
Execute the query and iterate over metadata

What do you want to achieve?

Why is executing a query not possible?

0 0

score 0 · Answer 1 · 2017-05-12T18:13:28-04:00

Thanks. I will try that next Week.

Our client's admin removed the sql access of the web users. And web users need to get to an excel report generated by the sql statement. Now I have to write a function that create the file and put it onto the system, so the web user can still get it via email. But I need to use the same order of columns as in the query itself.