New post
Question
· Aug 31, 2022

SQL Gateway Connection via ODBC to MariaDB connection failed

#ODBC #InterSystems IRIS

Hi 

I try to create an ODBC connection to MariaDB using SQL Gateway Connection. It works when testing with isql command at OS level but in the SQL Gateway Connection test is failed as this message :

Connection failed.
SQLState: () NativeError: [2002] Message:

And another way try to testing with this command

d $SYSTEM.SQLGateway.TestConnection("MariaDBodbc")

I got the error message look like this :

INST01:USER>d $SYSTEM.SQLGateway.TestConnection("MariaDBodbc")

Connection failed
  SQLState: () NativeError: [2002] Message:

INST01:USER>zw %objlasterror
%objlasterror="0 "_$lb($lb(5023,"  SQLState: () NativeError: [2002] Message: "_$c(13,10),,,,,,,,$lb(,"USER",$lb("e^TestODBCConnection+28^%apiGTW^1","e^TestConnection+28^%apiGTW^1","e^zTestConnection+1^%SYSTEM.SQLGateway.1^1","d^^^0"))))/* ERROR #5023: Remote Gateway Error:   SQLState: () NativeError: [2002] Message: -  */
Here is /etc/odbc.ini file

[MariaDBodbc]
Description=MariaDB server
Driver=/inst01/iris/bin/libmaodbc.so
SERVER=xx.xx.xx.xx
USER=user
PASSWORD=pwd
DATABASE=trydb
PORT=3306 

 And SQL Gateway Connection configulation file path is /inst01/iris/mgr/irisodbc.ini 

[MariaDBodbc]
Description=MariaDB server
Driver=/inst01/iris/bin/libmaodbc.so
SERVER=xx.xx.xx.xx
USER=user
PASSWORD=pwd
DATABASE=trydb
PORT=3306

 Anyone has experience with this?

Product version: IRIS 2021.1
$ZV: IRIS for UNIX (Red Hat Enterprise Linux for x86-64) 2021.1 (Build 215_0_21450U) Tue Feb 22 2022 16:27:06 EST
Discussion (2)2
Log in or sign up to continue
Alexander Koblov · Aug 31, 2022

Hi Thanongsak.

In the <iris>/bin folder you should have different odbcgateway*.so files.

IRIS uses odbcgateway.so when it connects to 3rd party database via ODBC.

However in UNIX world, different ODBC drivers are compiled differently and they can use different driver managers -- iODBC or unixODBC.

Thus IRIS comes with several odbcgateway libraries that are suitable for different drivers.

On my IRIS 2021.1 for macOS I have:

odbcgateway.so -- default one, for iODBC driver 8-bit
odbcgatewayiw.so -- for iODBC driver manager with Unicode support
odbcgatewayur64.so -- supports 8-bit ODBC for 64-bit unixODBC

IRIS documentation covers this here (though filenames are not correct, should be odbcgateway instead of cgate)
https://docs.intersystems.com/iris20221/csp/docbook/DocBook.UI.Page.cls?...

So the question is -- what driver manager is libmaodbc.so linked against.

In any case -- Rename odbcgateway.so to odbcgatewayi.so. And then try replacing odbcgateway.so with odbcgatewayiw.so and odbcgatewayur64.so and see with which connection works.

2 0