New post
Question
· Apr 6

Query variable column names

#InterSystems IRIS

Hi,

 

How can I query fields by passing them in variable names, eg. I would like to query columns Book & Author fields from eg. the following :

S =var1="Book", var2="Author"

Select  var1,var2 from myTables.Books

I tried @var1 or %var1 and didn't work

 

Thanks

Product version: IRIS 2024.3
Discussion (3)2
Log in or sign up to continue
Kurro Lopez · Apr 7

Hi @Touggourt 

If you want to retrieve the values, prepare the query by concatenating the values ​​you want to retrieve

set var1 = "Book"
set var2 = "Author"
set query = "SELECT "_var1_","_var2_" FROM myTables.Books"
set statement = ##class(%SQL.Statement).%New()

set status = statement.%Prepare(query)
set rset = statement.%Execute()
do rset.%Display()

Have a look the following link:

https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

Regards
 

0 0
Kurro Lopez · Apr 7

Also... you can create a ClassMethod to pass the fields to retrieve

ClassMethod DynamicQuery(columns As %String) As %Status
{
    // columns: comma-separated string, e.g. "Book,Author"
    
    // Basic validation
    If columns = "" {
        Write "No columns specified.", !
        Quit $$$ERROR
    }
    
    // Build the SQL query
    Set sql = "SELECT " _ columns _ " FROM myTables.Books"
    
    Try {
        Set stmt = ##class(%SQL.Statement).%New()
        Set sc = stmt.%Prepare(sql)
        If $$$ISERR(sc) {
            Write "Error preparing the query.", !
            Quit sc
        }
        
        Set rset = stmt.%Execute()
        
        While rset.%Next() {
            For i = 1:1:rset.%ColumnCount {
                Write rset.%GetColumnName(i), ": ", rset.%Get(i), " | "
            }
            Write !
        }
    } Catch ex {
        Write "Error executing the query: ", ex.DisplayString(), !
        Quit ex.AsStatus()
    }

    Quit $$$OK
}

Then, call it using this command:

Do ##class(MyApp.Utils).DynamicQuery("Book,Author")
0 0