Accessing External Database via JDBC in Cache Object Script

https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=BSQG_jdbc
 

Hi James,

A colleague of mine developed JDBC based solution in question which works with Oracle, mySQL and Caché a while ago.
It's based on the following classes:

   %Net.Remote.Java.JavaGateway
   %Net.Remote.Java.JDBCGateway (This class is used internally by Caché. You should not make direct use of it within your applications.)

Despite the last remark, InterSystems follows the similar approach in its Ensemble / IRIS outbound adapters.
Our solution is compatible with actual versions of Caché and IRIS. Regretfully, it's too bound to our app, so I'm not sure whether it is the best source of sample code at the moment.
 

You can use JDBC gateway via Interoperability productions (EnsLib.SQL package).

How are you using ODBC in InterSystems ObjectScript?

I have created a Cache Operation that uses JDBC to connect to Oracle  using the EnsLib.SQL.OutboundAdapter, if that is what you are looking for. I try keep the read, and write operations different as it can make it less confusing on which operation is doing what.

Here is just one example..

Include (EnsSQLTypes, %occODBC) Class osuwmc.IWEnc.IWDBPollBusinessOperation Extends Ens.BusinessOperation [ ClassType = "", ProcedureBlock ]
{ Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter"; Parameter INVOCATION = "Queue"; Property InitDSN As %String; Method OnInit() As %Status
{
Set ..InitDSN = ..Adapter.DSN
//Set ..Adapter.ConnectAttrs = "QueryTimeout:45" ; try this too just in case...
Quit $$$OK
} Method SelectIWLinkedResults(pRequest As osuwmc.IWEnc.DataStructures.IWtoERSLTSLnk, Output pResponse As EnsLib.SQL.Snapshot) As %Status
{
set Select = "SELECT MRN,ACCOUNTNUM,SUMMARYID,ANCILLARY,DEPARTMENT,REPORTCODE,REPORTDESCRIPTION,DATEOFSERVICE,STATUS,INSERTDATE,"
set Select = Select_"TRANSMITAPP,TRANSMITDATE,RENDDRNUM "
set Select = Select_"FROM IW_TO_ERSLTS_LNK "
set Select = Select_"WHERE TRANSMITDATE IS NULL AND TEXTID = ?"
set tSC = ..Adapter.ExecuteQuery(.rs,Select,pRequest.TextID)
set tSC = rs.GetSnapshot(.pResponse)
Quit tSC
} Method UpdateIWLinkedResults(pRequest As osuwmc.IWEnc.DataStructures.UpdateIWtoERSLTSLnk, Output pResponse As Ens.Response) As %Status
{
set update = "UPDATE IWDINTF.IW_TO_ERSLTS_LNK SET TRANSMITDATE = SYSDATE WHERE SUMMARYID = '"_pRequest.SummaryID_"'"
set tSC = ..Adapter.ExecuteUpdate(.rows,update)
Quit tSC
} XData MessageMap
{
<MapItems>
<MapItem MessageType="osuwmc.IWEnc.DataStructures.IWtoERSLTSLnk">
<Method>SelectIWLinkedResults</Method>
</MapItem>
<MapItem MessageType="osuwmc.IWEnc.DataStructures.UpdateIWtoERSLTSLnk">
<Method>UpdateIWLinkedResults</Method>
</MapItem>
</MapItems>
}