Write XML to SQL BLOB

[Mike Duncan]

Hi,

 

I have read in an xml file, used dtl to do some manipulation and have a message class holding the xml. Now i need to write it to an sql table. I have to use a stored procedure that stores this information. 

 

One of the parameters in the stored procedure is a sql blob which is to store the XML. 

 

I have no idea how to proceed.  If anyone could point me in the right direction on how to do it, that would be great.  Any example code would also be much appreciated.

 

Thanks

 

Mike

 

[Marc Lang]

Hi Mike, 

What is the datatype on the SPR?

Is it an "xml" or NVARCHAR(MAX), or varbinary?

I had big problems with this also.

 

Mike

 

--
You received this message because you are subscribed to the Google Groups "InterSystems: Ensemble in Healthcare Community" group.
To post to this group, send email to Ensemble-in...@googlegroups.com
To unsubscribe from this group, send email to Ensemble-in-Healt...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Ensemble-in-Healthcare?hl=en

[Mike Duncan]

Marc,

 

The Stored Procedure Parameter is sql Type "Image". 

 

Thanks

 

Mike

 

[Mike Duncan]

[Dale du Preez]

Hi Mike,

I suspect that there are two parts to your question. I think the first is about getting your request data into an XML stream, and the second is about sending that stream to your target stored procedure. I am assuming that this is a standard request object and not a virtual document (i.e. not an HL7 message).

The XML stream part is likely the simplest and can be achieved using code like the following:
Method OnMessage(pRequest As Your.Request, Output pResponse As Your.Response) As %Status
{
    Set tWriter = ##class(%XML.Writer).%New()
    // You may need to modify some of the properties of the writer, like NoXMLDeclaration, DefaultXmlns or Indent to control the XML output.
    // If so, change those values here before output
    Set tXMLStream = ##class(%Stream.TmpCharacter).%New()
    Set tStatus = tWriter.OutputToStream(.tXMLStream)
    If $$$ISERR(tStatus) Quit tStatus
    // This assumes you want to write out the base request object
    Set tStatus = tWriter.RootObject(pRequest)
    If $$$ISERR(tStatus) Quit tStatus
   
    // Now we have the XML in the tXMLStream object
    Do tXMLStream.Rewind()
   
    // I am assuming you will be using the SQL Outbound Adapter and the stored procedure takes two arguments
    // I am also assuming that your ODBC driver can report what the columns/arguments for the procedure are, so the pIO argument is empty below
    Set tStatus = ..Adapter.ExecuteProcedure(.tRSList,.tParmList, "CALL Your_Stored_Procedure(?,?)", /* Empty pIO */,pRequest.ID,tXMLStream)
    Quit tStatus
}

The tricky part with actually running the SQL is that the actual arguments are specified by the other system and the behaviour from there on depends on the ODBC (or JDBC) driver implementation as to what it supports and reports about the columns in the SP. I would give the above a try and then see whether there are any errors.

I hope that helps,
Dale

[Mike Duncan]

Hi Dale,

 

That was great. I've actually two problems:-1)  I want to write the Blob to an external Cache Namespace.

2) I want to write it to an MS sql table. preferrably through a Stored Procedure.

 

For 1, I use what you suggested..within  SQL.OutboundAdapter using ODBC to the external CACHE namespace.

 

     Set tWriter = ##class(%XML.Writer).%New()
     Set tXMLStream = ##class(%Stream.GlobalBinary).%New()

     Set tStatus = tWriter.OutputToStream(.tXMLStream)
     If $$$ISERR(tStatus) Quit tStatus

     Set tStatus = tWriter.RootObject(pRequest)
     If $$$ISERR(tStatus) Quit tStatus
        // Now we have the XML in the tXMLStream object
     Do tXMLStream.Rewind()
    

//now write to cache table
    Set sql="insert into "_..BOSTable_" (MessageID ,Trader , MessageType, MessageData, MessageTaken, MessageConfirmed, Binary, Zipped, ReadDataFromDisk, TakenCount, InsertDate) values (?,?,?,?,?,?,?,?,?,?,?)"
   Set sc=..Adapter.ExecuteUpdate (.rowsAffected,sql,

     pRequest.BossPatient.MailerTypeID_"."_pRequest.Transmission.TransmissionReference
    ,pRequest.Transmission.ReceiversId
    ,"BOSS"
    ,tXMLStream
    ,0,0,0,0,0,0
    ,$ZDate($piece($horolog,",",1),3) _ " " _ $ZTime($piece($horolog,",",2),1)
    )

and it seems to work. I get rows added and the Blob appears to have my XML data.

 

When i try the exact same code (i.e. not using the Stored Procedure), but using ODBC to an MS sql database.

 

I get an error...

 

ERROR #6022: Gateway failed: BindParameters. + ERROR <Ens>ErrGeneral: SQLState: (HY104) NativeError: [0] Message: [Microsoft][ODBC SQL Server Driver]Invalid precision value

 

Investigating it is the MessageData Field that is causing the problem.

 

Where "MessageData" is defined in the SQL database table  as "Image" 

 

Any thoughts..

 

Thanks

 

Mike