Question
· Aug 10, 2023

about sqlproc using ReturnResultsets but get null after connected to external database

hi,
I followed the example of the system and wrote a method to connect to an external database to obtain a result set, but I couldn't get the result.
can anyone please help on this.
image

image

image

/// correct
ClassMethod PersonSets(name As %String = "", state As %String = "MA") As %Integer [ ReturnResultsets, SqlName = PersonSets, SqlProc ]
{
        // %sqlcontext is automatically created for a method that defines SQLPROC

        // SQL result set classes can be easily prepared using dynamic SQL. %Prepare returns a
        // status value. The statement's prepare() method can also be called directly. prepare() throws
        // an exception if something goes wrong instead of returning a status value.
    set tStatement = ##class(%SQL.Statement).%New()
    try {
        do tStatement.prepare("select name,dob,spouse from sample.person where name %STARTSWITH ? order by 1")
        set tResult = tStatement.%Execute(name)
        do %sqlcontext.AddResultSet(tResult)
        do tStatement.prepare("select name,age,home_city,home_state from sample.person where home_state = ? order by 4, 1")
        set tResult = tStatement.%Execute(state)
        do %sqlcontext.AddResultSet(tResult)
        set tReturn = 1
    }
    catch tException {
        #dim tException as %Exception.AbstractException
        set %sqlcontext.%SQLCODE = tException.AsSQLCODE(), %sqlcontext.%Message = tException.SQLMessageString()
        set tReturn = 0
    }
    quit tReturn
}

/// error
ClassMethod odbcTest() As %Integer [ ReturnResultsets, SqlName = PersonSets2, SqlProc ]
{
    if '$isobject($Get(%sqlcontext)) { set %sqlcontext = ##class(%ProcedureContext).%New() } 
    s conn=##class(%SQLGatewayConnection).%New()
    s sc=conn.Connect("samples","_system","sys") //datasource
    if $$$ISERR(sc) do $System.Status.DisplayError(sc) quit sc
    s rs=##class(%ResultSet).%New("%DynamicQueryGW:SQLGW")
    try {
        s sql = "select * from Sample.Person"
        d rs.Prepare(sql,,conn)
        d rs.Execute()
        s ^tmp("%ROWCOUNT")=rs.%ROWCOUNT
        d %sqlcontext.AddResultSet(rs)
        s tReturn = 1
    }catch{
        #dim tException as %Exception.AbstractException
        s %sqlcontext.%SQLCODE = tException.AsSQLCODE(), %sqlcontext.%Message = tException.SQLMessageString()
        s sc=conn.Disconnect() 
        s tReturn = 0
    }
    ;d conn.Disconnect() 
    q tReturn
}
Product version: Ensemble 2016.1
$ZV: Cache for Windows (x86-64) 2016.2.3 (Build 903_10_20040U) Wed Jul 1 2020 16:11:04 EDT
Discussion (4)2
Log in or sign up to continue

One of the possible options:

ClassMethod odbcTest() As %Integer ReturnResultsetsSqlName PersonSets2SqlProc ]
{
  #dim %sqlcontext As %ProcedureContext
  if '$isobject($Get(%sqlcontext)) set %sqlcontext ##class(%ProcedureContext).%New() } 

  tReturn = 0

  conn=##class(%SQLGatewayConnection).%New()
  sc=conn.Connect("TEST Samples","_system","SYS"//datasource
  if $$$ISOK(sc{
    conn.AllocateStatement(.h1)
    conn.Prepare(h1,"select name,dob,spouse from sample.person where name %STARTSWITH 'A'")
    conn.Execute(h1)
    %sqlcontext.AddResultSet(conn.getResultSet(h1))
    conn.AllocateStatement(.h2)
    conn.Prepare(h2,"select name,age,home_city,home_state from sample.person where home_state = 'MA'")
    conn.Execute(h2)
    %sqlcontext.AddResultSet(conn.getResultSet(h2))
    tReturn = 1
  }else{
    sqlcode=$system.Status.StatusToSQLCODE(sc,.msg)
    %sqlcontext.%SQLCODE sqlcode%sqlcontext.%Message msg
  }
  tReturn
}

Output:

SAMPLES>##class(%SQL.Statement).%ExecDirect(,"call Sample.PersonSets2()").%Display()
...

Surely there is a way to make it even easier.