New post
Question
· Jul 25, 2016

How to Connect to External Non-Caché DB With COS and Without DSN?

#JDBC #ODBC #ObjectScript #SQL #Caché

We are creating a package (written in Caché Object Script) that will provide access to an external DB (MySQL). Because applications that use our package will be run from machines with various, potentially unexpected, operating systems, we’d like to establish a connection to the external DB without using DSNs (we’ve heard that setting up DSNs on certain non-Windows machines can be cumbersome and problematic).

Does anyone know of  way to create a connection to an external DB without using a DSN? We have the ip address of the MySQL DB server, port number, the name of the database and the proper credentials (username and password). I was hoping for a flavor of a %SQLGatewayConnection “new” method that took the parameters listed above (as separate fields or as a connection string) and returned a handle to the database’s SQL processing engine (I couldn't find any). I was further hoping that we could then send queries and receive results using that handle.

Please let me know if you have any ideas or if the only way is using ODBC with DSNs and we'll bite that configuration bullet.

Thanks in advance,

Jean Millette

Discussion (4)2
Log in or sign up to continue
Michael Burstin · Jul 26, 2016

According to the %SQLGatewayConnection documentation, you should be able to pass in an empty string for username and password to have the code use SQLDriverConnect() which would do what you want it to do, however, I did some testing and see that it isn't working properly.  I put in prodlog 142095 for this problem.  Do note that when this is fixed, you will need to use an empty string for user and password and not simply leave them off of the call to Connect(). 

The code that I was using testing against Cache was:

s cs = "Driver=Intersystems ODBC35; Server=localhost; Database=samples; Port=1972; UID=_system; PWD=SYS"
s gc = ##class(%SQLGatewayConnection).%New()
s st = gc.Connect(cs, "", "")

I cannot say what the proper connection string would be to connect to MySQL - you would need to consult the MySQL ODBC driver documentation for that.

1 0
Jon Willeke · Jul 26, 2016

Christian in QD came up with a bit of a hack for this. You can use one of the DSNs that is installed automatically with the product, but override all of its attributes; e.g.:

s dsn=dsn_";Database="_$namespace_";Port="_^%SYS("SSPort")_";UID="_usn_";PWD="_pwd

To find an existing DSN, use the SQLDataSources query in the %GTWCatalog class.

He used this technique to make the TestODBC() method in the %UnitTest.SQLRegression class more reliable. I don't know for sure that it will work with a different DBMS and driver, but it's worth a shot.

0 0