Connecting to MYSQL ODBC

SQL, InterSystems IRIS

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

sc=res.Close()
Set sc=conn.Disconnect()

But I am getting an error :

Please can you advice how I can do this?

 

  • 0
  • 0
  • 132
  • 2
  • 2

Respuestas

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 = "Batch.SQL.OutboundAdapter";
 
Property Adapter As Batch.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
{
<MapItems>
    <MapItem MessageType="Ens.StringRequest">
        <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

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

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
 }

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.