New post
Question
· Mar 16, 2018

Sql result set to a %Stream

#Business Operation #Databases #Object Data Model #ODBC #Ensemble

I am trying to read data from MySQL Server 2012 to a stream  and I keep getting an error here is my code so far

ERROR

An error was received : ERROR <Ens>ErrException: <INVALID OREF>zGetBatchDetails+14^DDQTools.DQTGetBatchOpp.3 -- logged as '-' number - @' set sc=reStream.WriteLine(rec) '
< set tSC=$$$OK
 
  set ^tvalue=1
 
  set ^tvalue=$INCREMENT(^tvalue)
 
    #dim rs as EnsLib.SQL.GatewayResultSet
    #dim reStream as %Stream.GlobalCharacter
    if $$$ISERR(pRequest) quit pRequest
    set sc=..Adapter.ExecuteQuery(.rs,..GetTheSubmissionData(pRequest.pMonth, pRequest.pApp, pRequest.pRef, pRequest.pInPat))
   
    if $$$ISERR(sc) quit sc
   
   
    while rs.Next() {
        set (comma,rec)=""
        for i=1:1:rs.GetColumnCount() {
            set rec=rec_comma_""""_rs.GetData(i)_""""
            set comma=","   
        }
        set reStream=""
        set sc=reStream.WriteLine(rec)  
        
    }
    do reStream.%Save()
      
     
       if ($$$ISOK(sc)){ set pResponse.pReqDetails=reStream
     set reStream=""}else{$$$TRACE("There is nothing on the stream")} 
   
 
     set tSC=pResponse.%Save()
      
 set tSC=..SendRequestSync(..TargetConfigNames,pResponse,.pOutput)
 Quit tSC
Discussion (3)1
Log in or sign up to continue
Robert Cemper · Mar 19, 2018

If you didn't correct the other 2 mistakes it's no surprise.

The code should look like this:

 set tSC=$$$OK
 set ^tvalue=1
 set ^tvalue=$INCREMENT(^tvalue)
 
    #dim rs as EnsLib.SQL.GatewayResultSet
    #dim reStream as %Stream.GlobalCharacter
    if $$$ISERR(pRequest) quit pRequest
    set sc=..Adapter.ExecuteQuery(.rs,..GetTheSubmissionData(pRequest.pMonth, pRequest.pApp, pRequest.pRef, pRequest.pInPat))
   
    if $$$ISERR(sc) quit sc
  
    set reStream=##class(%Stream.GlobalCharacter).%New()
    while rs.Next() {
        set (comma,rec)=""
        for i=1:1:rs.GetColumnCount() {
            set rec=rec_comma_""""_rs.GetData(i)_""""
            set comma=","   
        }
     ;;   set reStream=""   ;; dont kill your stream before using !!! OMG
        set sc=reStream.WriteLine(rec)
        
    }
    do reStream.%Save()
     ;; sc doesn't reflect if there is a content in reStream. check Size instead
   set sc=''reStream.Size
       
       if ($$$ISOK(sc)){ set pResponse.pReqDetails=reStream
           set reStream=""} 
        else {$$$TRACE("There is nothing on the stream")} 
    
     set tSC=pResponse.%Save()
      
 set tSC=..SendRequestSync(..TargetConfigNames,pResponse,.pOutput)
 Quit tSC
 
0 0