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.
/// 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
Comments
Where is the code for PersonSets2()? And how do you connect to external database?
just the second classmethod ,name “odbcTest”,SqlName=“PersonSets2”
One of the possible options:
ClassMethod odbcTest() As %Integer [ ReturnResultsets, SqlName = PersonSets2, SqlProc ]
{
#dim %sqlcontext As %ProcedureContext
if '$isobject($Get(%sqlcontext)) { set %sqlcontext = ##class(%ProcedureContext).%New() }
s tReturn = 0
s conn=##class(%SQLGatewayConnection).%New()
s sc=conn.Connect("TEST Samples","_system","SYS") //datasource
if $$$ISOK(sc) {
d conn.AllocateStatement(.h1)
d conn.Prepare(h1,"select name,dob,spouse from sample.person where name %STARTSWITH 'A'")
d conn.Execute(h1)
d %sqlcontext.AddResultSet(conn.getResultSet(h1))
d conn.AllocateStatement(.h2)
d conn.Prepare(h2,"select name,age,home_city,home_state from sample.person where home_state = 'MA'")
d conn.Execute(h2)
d %sqlcontext.AddResultSet(conn.getResultSet(h2))
s tReturn = 1
}else{
s sqlcode=$system.Status.StatusToSQLCODE(sc,.msg)
s %sqlcontext.%SQLCODE = sqlcode, %sqlcontext.%Message = msg
}
q tReturn
}Output:
SAMPLES>d ##class(%SQL.Statement).%ExecDirect(,"call Sample.PersonSets2()").%Display() ...
Surely there is a way to make it even easier.
that works for me，many many thanks！