New post
AllAccepted answers
Kurro Lopez · 6 hr ago

Hi community:

  • My name is Francisco López, AKA Kurro López
  • I'm from Spain, born and living in Málaga (Andalusia) in southern Spain, close to Mediterranean Sea.
  • Working as project manager in Salutic Soluciones, SL. This company is dedicated to creating software for medical management and services for health insurance companies.
  • I have extensive programming experience in several languages, from Clipper 87 to C#, including Python, PowerBuilder, Visual Basic, and, of course, ObjectScript. I've spent a long career dedicated to programming and the technical and functional analysis of new processes. I love programming in IRIS because it gives me a lot of freedom when generating complex workflows that would be much slower to complete with other languages.
  • My main hobby is the mountains. I love long walks, hiking, and discovering new places. I used to do mountain running, but my knees aren't what they used to be.
  • LinkedIn: https://www.linkedin.com/in/franciscolopezdelasheras/
  • I won't be attending READY 2025 this year, but I have attended in previous years and it's a great experience that everyone should go to at least once.
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")
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
 

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"
}
Kurro Lopez · Oct 18, 2024

Note: I've done the following code to catch the native error... but it is a bit "smell code"

// run the query
Set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,SQL,.param)

// Check if there is any error
If $$$ISERR(tSC)
{
	Set msgError = $System.Status.GetErrorText(tSC) 
	// Check here if the native error code is 2601 (Cannot insert duplicate key row into object 'MYPATIENTS' with unique index 'UQ_UNIQUE_INDEX')
	if $FIND(msgError, "[2601]") > 0
	{
	    // This is a insert/update that violates the unique code
	    // remove duplicate record
	    quit $$$OK
	}
	else
	{
	    // Generic error... thow excepction
	    quit tSC
	}
}