Connecting to MYSQL ODBC

Question

Question

ED Coder · Aug 15, 2019

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

Log in or sign up to continue

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

Class Batch.Example.SqlInsertOperation Extends Ens.BusinessOperation

{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";

Property Adapter As EnsLib.SQL.OutboundAdapter;

Parameter INVOCATION = "Queue";

Method SetResultSetView(pRequest As Ens.StringRequest, Output pResponse As Ens.StringResponse) As %Status

{

set tStatus = $$$OK

try{

set pResponse = ##class(Ens.StringResponse).%New()

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

set param(1) = 1

set param(1,"SqlType")=$$$SqlInteger

set param(2) = ##class(%PopulateUtils).Name()

set param(2,"SqlType")=$$$SqlVarchar

set param(3) = $ZDATETIME($NOW(),3)

set param(3,"SqlType")=$$$SqlVarchar

set param = 3

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

}

catch exp

{

Set tStatus = exp.AsStatus()

}

Quit tStatus

}

XData MessageMap

{

<Method>SetResultSetView</Method>

</MapItem>

</MapItems>

}

Or with the %SQLGatewayConnection :

//Create new Gateway connection object

set gc=##class(%SQLGatewayConnection).%New()

If gc=$$$NULLOREF quit $$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")

//Make connection to target DSN

s pDSN="Samples"

s usr="_system"

s pwd="SYS"

set sc=gc.Connect(pDSN,usr,pwd,0)

If $$$ISERR(sc) quit sc

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

set sc=gc.AllocateStatement(.hstmt)

if $$$ISERR(sc) quit sc

//Prepare statement for execution

set pQuery= "select * from Sample.Person"

set sc=gc.Prepare(hstmt,pQuery)

if $$$ISERR(sc) quit sc

//Execute statement

set sc=gc.Execute(hstmt)

if $$$ISERR(sc) quit sc

0 0

score 0 · Answer 1 · 2019-08-16T04:15:36-04:00

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

score 0 · Answer 2 · 2021-08-17T12:41:30-04:00

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

score 0 · Answer 3 · 2021-08-19T06:43:09-04:00

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 :
- https://docs.intersystems.com/irisforhealthlatest/csp/docbook/DocBook.UI...
- https://docs.intersystems.com/irisforhealthlatest/csp/docbook/DocBook.UI...

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

score 0 · Answer 4 · 2019-08-15T20:16:50-04:00

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):

1) download (https://dev.mysql.com/downloads/connector/odbc/) and install the proper ODBC driver
2) go to ControlPanel-->AdministrativeTools-->DataSources(ODBC)
3) 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
4) 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
 }

score 0 · Answer 5 · 2019-08-16T04:11:39-04:00

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.