Kurro Lopez view profile on InterSystems Developer Community

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")

go to post

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

go to post

Kurro Lopez · Apr 1

Thanks @Brett Saviano

I've already taken a look at the api/atelier APIs, and they've been very helpful in retrieving namespace names.

In our check method, in addition to server information, we return more information about production, such as the version number, last installation date, connection status with external databases, etc.

We have two mirrored servers, so if one of the servers goes down, the backup server is activated.

If the server name is different than expected, it's because the backup server has been activated and is providing service without issue, but we have an alert that the primary server has gone down.

This is an example of what our check service returns:

{
  "result": "OK",
  "methods": [
    {
      "test": "SQL_USERS",
      "result": "OK"
    },
    {
      "test": "SQL_APPOINTMENT",
      "result": "OK"
    },
    {
      "test": "VERSION",
      "result": "1.14.0.0327"
    },
    {
      "test": "INSTALLED",
      "result": "2024-12-04T08:04:52+01:00"
    }
  ],
  "serverName": "SRVPROD01",
  "serverApi": "10.168.1.1",
  "serverInstance": "HEALTHCONNECT",
  "execution_time": "00:00:00.0597418"
}