Written by

Question ED Coder · Aug 15, 2019

Connecting to MYSQL ODBC

Hi, Is there a way to connect to a MYSQL ODBC? I tried using the SQL Connect class but getting an error.

Set conn=##class(%SQLGatewayConnection).%New()
Set sc=conn.Connect("databasename","username","password")

&sql(insert into ORDERS (column1, column2, column3, column4, column5) values(:value1,:value2,:value3,:value4,:value5))
s sc=res.Close()
Set sc=conn.Disconnect()

But I am getting an error :

Please can you advice how I can do this?

Discussion (6)2

Add reply

Comments

Guillaume Rongier · Aug 19, 2021

Hi Eric,

First you are using &sql who is for internal SQL use : doc

If you want to do an external query to a remote database you can do it with Ensemble :

Include EnsSQLTypes

ClassBatch.Example.SqlInsertOperationExtendsEns.BusinessOperation

{

ParameterADAPTER = "EnsLib.SQL.OutboundAdapter";

PropertyAdapterAsEnsLib.SQL.OutboundAdapter;

ParameterINVOCATION = "Queue";

MethodSetResultSetView(pRequestAsEns.StringRequest, OutputpResponseAsEns.StringResponse) As%Status

{

settStatus = $$$OK

try{

setpResponse = ##class(Ens.StringResponse).%New()

setSqlInsertView = "INSERT into ODS_Products (ID,ProductName,Date_Alimentation) values (?,?,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'))"

setparam(1) = 1

setparam(1,"SqlType")=$$$SqlInteger

setparam(2) = ##class(%PopulateUtils).Name()

setparam(2,"SqlType")=$$$SqlVarchar

setparam(3) = $ZDATETIME($NOW(),3)

setparam(3,"SqlType")=$$$SqlVarchar

setparam = 3

$$$ThrowOnError(..Adapter.ExecuteUpdateBatchParamArray(.nrows,SqlInsertView,.param))

}

catchexp

{

SettStatus = exp.AsStatus()

}

QuittStatus

}

XData MessageMap

{

<MapItemMessageType="Ens.StringRequest">

<Method>SetResultSetView</Method>

</MapItem>

</MapItems>

}

Or with the %SQLGatewayConnection :

//Create new Gateway connection object

setgc=##class(%SQLGatewayConnection).%New()

Ifgc=$$$NULLOREFquit$$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")

//Make connection to target DSN

spDSN="Samples"

susr="_system"

spwd="SYS"

setsc=gc.Connect(pDSN,usr,pwd,0)

If$$$ISERR(sc) quitsc

ifgc.ConnectionHandle=""quit$$$ERROR($$$GeneralError,"Connection failed")

setsc=gc.AllocateStatement(.hstmt)

if$$$ISERR(sc) quitsc

//Prepare statement for execution

setpQuery= "select * from Sample.Person"

setsc=gc.Prepare(hstmt,pQuery)

if$$$ISERR(sc) quitsc

//Execute statement

setsc=gc.Execute(hstmt)

if$$$ISERR(sc) quitsc

0 0

ED Coder Aug 16, 2019 to Guillaume Rongier

Hi Guillaume, thank you so much for the screenshots and explaining it to me. I was able to establish the connection and no errors when my query runs but the data doesnt get updated in mySQL DB. Can you advice if my insert statement is correct?

set pQuery = "INSERT INTO TABLENAME(column1, column2, column3, column4, column5) "
set pQuery = pQuery_"VALUES("_value1_","_value2_","_value3_","_value4_","_value5_")"
set sc=conn.Prepare(hstmt,pQuery)

set sc=conn.Prepare(hstmt,pQuery)
   if $$$ISERR(sc) quit sc
   //Execute statement
   set sc=conn.Execute(hstmt)
   if $$$ISERR(sc) quit sc

0 0

Gregor Sieber Aug 17, 2021 to Guillaume Rongier

Hi,

I am struggling with same/similar issue. I tried your code but it does not work or i do not execute it correclty. The latter of which is rather likely.

My setup:

I have an Instance of InterSystems on a Ubuntu 20.04 VM

On the same VM i am running a MySQL DB and a PostgresQL DB.

I would like to connect first to MySQL and installed the necessary ODBC drivers.

I tried to configure via this: System Administration > Configuration > Connectivity > SQL Gateway

But here can not specify the DSN name of the MySQL DB. but it asks me to specify a DSN name "that you previously created" but nowhere i can see how to create such a DSN name. In my odbc.ini on the ubuntu VM i have specified the DSN name, user, password, etc. so the MySQL database can receive a connection via ODBC. In this "wizard" i cannot specify these variables.

Second, i tried your code block and changed the values DSN, user, pw and execute statement. But when I run this code under Systen -> SQL it returns an error (its in german but goes liek this:

"An extrinsic function call must have the form $$tag^rou(..."

Its daunting.. Please advise.

Thanks in advance

0 0

Guillaume Rongier Aug 19, 2021 to Gregor Sieber

Hi Gregor,

First off all, try to connect to MySql directly by a shell command :

echo "select 1" | isql -v my-connector

Expected response :

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1
+---------------------+
| 1                   |
+---------------------+
| 1                   |
+---------------------+
SQLRowCount returns 1
1 rows fetched

Where /etc/odbc.ini :

[my-connector]
Description           = MySQL connection to  database
Driver                = MySQL
Database              = example
Server                = localhost
User                  = example
Password              = example
Port                  = 3306
Socket                = /var/run/mysqld/mysqld.sock

and /etc/odbcinst.ini

[MySQL]
Description = ODBC for MySQL
Driver = /usr/local/lib/libmyodbc8a.so
Setup = /usr/local/lib/libmyodbc8w.so
FileUsage = 1

If you successfully connected to your mysql database, then you can use it in IRIS/Caché/Ensemble :

Here is an example with %SQLGatewayConnection

set gc=##class(%SQLGatewayConnection).%New()
set pDSN="my-connector"
set sc=gc.Connect(pDSN,"example","example")
set sc=gc.AllocateStatement(.hstmt) 
set pQuery= "select 1"
set sc=gc.Prepare(hstmt,pQuery)
set sc=gc.Execute(hstmt)
set sc=gc.Fetch(hstmt)
set sc=gc.GetData(hstmt, 1, 1, .val)
zw val
set sc=gc.CloseCursor(hstmt)
set sc=gc.Disconnect()

To go further check those links :

Or even better check this training, it's with an JDBC connector but most part is applicable has DSN will fit your odbc config.

https://github.com/grongierisc/formation-template

0 0

Julius Kavay · Aug 15, 2019

To make a connection to a MySQL Database via ODBC is easy. In the next steps I assume you are on a Windows system (Linux/Unix is similar):

download (https://dev.mysql.com/downloads/connector/odbc/) and install the proper ODBC driver
go to ControlPanel-->AdministrativeTools-->DataSources(ODBC)
select the SystemDNS-Tab, click Add
- give a unique name to this datasource (and remember it for step 4)
- fill in the necessary fields as desired
use the %SQLGatewayConnection class to get/put your data from/into MySQL, something like:
set gtw=##class(%SQLGatewayConnection).%New() if gtw.Connect(datasourceName, user, pass) { // do your work } else { // can't connect }

0 0

ED Coder Aug 16, 2019 to Julius Kavay

Thank You Julius, I was able to connect to the mySQL DB using the SQL gateway Connection Class. I assumed that there would be a different class to connect.

0 0