James Casazza · 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?


0 591
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?

Use a Java Gateway if that is available to you.

There's a JDBC example (it uses InterSystems' JDBC driver, but the code can be replaced with the proper driver if you're familiar with JDBC since it users DriverManager)  under %Net.Remote.Java.Test in the JDBC method

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.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?

Just using Cache, not IRIS so no Interoperability.

The Object Script I'm using for ODBC is both in SQL Queries and Cache Object Classes. I think using JDBC will be faster having multiple JDBC SQL Gateways. Just trying to find an example of someone having a external Database with JDBC using Cache Object Script. 

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_"FROM IW_TO_ERSLTS_LNK "
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 tSC = ..Adapter.ExecuteUpdate(.rows,update)
Quit tSC
} XData MessageMap
<MapItem MessageType="osuwmc.IWEnc.DataStructures.IWtoERSLTSLnk">
<MapItem MessageType="osuwmc.IWEnc.DataStructures.UpdateIWtoERSLTSLnk">

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!

Hi James,

Could you please share the code for new JDBC SQL Gateway?