Written by

Question Touggourt · Apr 6

Query variable column names

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 (0)0

Add reply

Comments

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.cl…

Regards

0 0

John Murray Apr 7 to Kurro Lopez

With code like this, beware SQL Injection exploits.

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 validationIf columns = "" {
        Write"No columns specified.", !
        Quit$$$ERROR
    }
    
    // Build the SQL querySet 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