Using ODBC SQL Gateway programmatically
I'm trying to connect to a database (outside Cache) using the examples here:
https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cl…
and https://docs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=BGOD_…
and i'm still getting an error on running the Connect method (Connect(pDSN,usr,pwd,0))
"0 "_$lb($lb(6022,"DSN/User Connect",,,,,,,,$lb(,"TRAK",$lb("e^zConnect+15^%Library.SQLGatewayConnection.1^2","d^zConnect+13^%Library.SQLGatewayConnection.1^2","e^^^0"))))/* ERROR #6022: Gateway failed: DSN/User Connect. */
i verified the credentials though.
Comments
Long time ago I did some connections to external databases (MySql and PostGres).
The essential parts such a connection are:
1) First, you have to create in your OS the corresponding ODBC Data Source entries
(System-DSN) after installing the required DB-Driver
2) The connection
set gtwConn=##class(%SQLGatewayConnection).%New(), gtwHandle=0
if gtwConn.Connect(OdbcName, OdbcUser, OdbcPass) {
if gtwConn.AllocateStatement(.gtwHandle) {
// check gtwConn.GatewayStatus
// etc.
} else { write "Can't Allocate: "_OdbcName }
} else { write "Can't connect to "_OdbcName }
3) SQL-Commands
do gtwConn.CloseCursor(gtwHandle)
if gtwConn.PrepareW(gtwHandle, sqlStatement) {
if gtwConn.Execute(gtwHandle) {
...
...
} else { /* check gtwConn.GatewayStatus */ }
} else { /* check.gtwConn.GatewayStatus */ }
4) Finish
if gtwConn {
do gtwConn.DropStatement(gtwHandle), gtwConn.Disconnect()
set gtwConn="", gtwHandle=""
}