Question
· May 13, 2020

Accessing External Database via JDBC in Cache Object Script

Is it possible to access (read, write) to an external Oracle database via Cache SQL Gateway using JDBC in Cache Object Script?  I am currently using ODBC successfully but wanted to see if JDBC was an option too.  If it is possible, does anyone have a basic Object Script example(s) that I can review?

Thanks!

Discussion (9)3
Log in or sign up to continue

Hello Kevin Chan,

I know how to setup jdbc in SQL Gateway; that is not the Question. The Question is can I code in Cache Object Script to an external Oracle database using jdbc SQL Gateway in place? And if I can, do you or anyone else have examples of doing this. I have Cache Object Script working with an External Oracle database using ODBC but wanted to see how to do this (or replace this) with 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.
 

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>
}

After discussing this with Intersystems, they stated that the existing Cache Object Methods and/or SQL Queries do not change when switching from an ODBC to JDBC connection, which was my main concern. After switching, though, I had to recompile/regenerate the SQL Object Classes to change to the new SQL Gateway being used. So thanks everyone for responding. All is working and I appreciate your input & help!