How to connect to JDBC database using SQL Gateway?

Primary tabs

I have to connect to an external database and I'd like to use the JDBC SQL Gateway.

I followed this tutorial [1] to create a JDBC SQL Gateway and after configured it works as expected, but I don't know

how use this connection into source code. Maybe these other tutorial is related [2] and [3].

 

[1] http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

[2] https://community.intersystems.com/product-documentation/intersystems-pr...

[3] http://docs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?A...

 

Thanks in Advance.

Replies

Hi David

Defining the SQL Gateway connection is only one part of the problem solved.

Once you have ano SQL gateway defined, you can then create 'proxy' classes in Cache that  know something about, and represent the tables in the remote database system.

With these proxy classes on hand, you can then use them to query and update data as you would with any other persistant Cache vlass yout have.

Hi David,

Well you're in luck, because you're almost done!  First you have to link the table.  To do this go to the Management Portal: System Explorer->SQL.  Then go to Actions->Linked Table Wizard.   Choose the SQL Gateway connection from the drop down and choose your table.  Go through the couple of screens where you can normally accept the defaults.  

Once you link a table, then you can interact with it as if it were local!  That is, you can pretend the table is not linked, and use the SQL and/or Object access you're used to in Caché, and access the data in the remote tables.   If you are having ANY trouble with this, contact the WRC and one of us will be happy to walk you through the procedure.

Thanks,

But this method only allow to access one table. I'd like to use dynamic SQL. I'd like something like this:

 

conector = new conector('IP:porta/squema','user',''pass')

ds = conector.eval('select * from TABLE....')

 

I'd like to configure all connection in source code, or at least use the SQL gateway.

If you have/use Ensemble you can check how EnsLib.SQL.CommonJ  class operates.

This really looks like what I want!

Thanks!

But, as I'm newbie, do you have any usage example? any class that uses this would be awesome!