Written by

Question heng a · Aug 10, 2023

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

#Ensemble #ODBC #SQL

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

Add reply

Comments

Iryna Mykhailova · Aug 10, 2023

Where is the code for PersonSets2()? And how do you connect to external database?

0 0

heng a Aug 10, 2023 to Iryna Mykhailova

just the second classmethod ,name “odbcTest”,SqlName=“PersonSets2”

0 0

Vitaliy Serdtsev · Aug 11, 2023

One of the possible options:

<FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">odbcTest() </FONT><FONT COLOR="#000080">As %Integer </FONT><FONT COLOR="#000000">[ </FONT><FONT COLOR="#000080">ReturnResultsets</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">SqlName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">PersonSets2</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">SqlProc </FONT><FONT COLOR="#000000">]
{
  </FONT><FONT COLOR="#0000ff">#dim </FONT><FONT COLOR="#800000">%sqlcontext </FONT><FONT COLOR="#0000ff">As </FONT><FONT COLOR="#008080">%ProcedureContext
  </FONT><FONT COLOR="#0000ff">if </FONT><FONT COLOR="#000000">'</FONT><FONT COLOR="#0000ff">$isobject</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$Get</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">%sqlcontext</FONT><FONT COLOR="#000000">)) </FONT><FONT COLOR="#800080">{ </FONT><FONT COLOR="#0000ff">set </FONT><FONT COLOR="#800000">%sqlcontext </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%ProcedureContext</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">() </FONT><FONT COLOR="#800080">} 
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">tReturn </FONT><FONT COLOR="#000000">= 0
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">conn</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQLGatewayConnection</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">sc</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#800000">conn</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Connect</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"TEST Samples"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"_system"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"SYS"</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#008000">//datasource
  </FONT><FONT COLOR="#0000ff">if $$$ISOK</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">sc</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">conn</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">AllocateStatement</FONT><FONT COLOR="#000000">(.</FONT><FONT COLOR="#800000">h1</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">conn</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Prepare</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">h1</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"select name,dob,spouse from sample.person where name %STARTSWITH 'A'"</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">conn</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Execute</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">h1</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">%sqlcontext</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">AddResultSet</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">conn</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">getResultSet</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">h1</FONT><FONT COLOR="#000000">))
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">conn</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">AllocateStatement</FONT><FONT COLOR="#000000">(.</FONT><FONT COLOR="#800000">h2</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">conn</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Prepare</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">h2</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"select name,age,home_city,home_state from sample.person where home_state = 'MA'"</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">conn</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Execute</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">h2</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">%sqlcontext</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">AddResultSet</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">conn</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">getResultSet</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">h2</FONT><FONT COLOR="#000000">))
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">tReturn </FONT><FONT COLOR="#000000">= 1
  </FONT><FONT COLOR="#800080">}</FONT><FONT COLOR="#0000ff">else</FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">sqlcode</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#0000ff">$system</FONT><FONT COLOR="#008080">.Status</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">StatusToSQLCODE</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">sc</FONT><FONT COLOR="#000000">,.</FONT><FONT COLOR="#800000">msg</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">%sqlcontext</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%SQLCODE </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800000">sqlcode</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#800000">%sqlcontext</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%Message </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800000">msg
  </FONT><FONT COLOR="#800080">}
  </FONT><FONT COLOR="#0000ff">q </FONT><FONT COLOR="#800000">tReturn
</FONT><FONT COLOR="#000000">}</FONT>

Output:

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

Surely there is a way to make it even easier.

0 0

heng a Aug 13, 2023 to Vitaliy Serdtsev

that works for me，many many thanks！

0 0