New post
Question
· May 12, 2017

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

#Object Data Model #Caché

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
D Sun · May 12, 2017

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. 

0 0