Written by

Question ED Coder · Aug 16, 2019

Data not getting inserted into mySQL DB

#Ensemble

Hi, I am trying to get my values into a mySQL DB but for some reason it doesnt seem to go into the DB. I do not get any errors. Would appreciate some guidance on this.

ClassMethod Orders(pRequest As EnsLib.HL7.Message) As %String [ Final ]
{
/
s pidSeg = pRequest.FindSegment("PID")
s mrn = $p(pidSeg.GetValueAt(3),"^",1)
s obrSeg = pRequest.FindSegment("OBR")
s obrdate = obrSeg.GetValueAt(6)
s obrdesc = $p(obrSeg.GetValueAt(4),"^",2)
s obrstat1 = $p(obrSeg.GetValueAt(40),"^",2)
s obrstat2 = $p(obrSeg.GetValueAt(40),"^",3)
s obrstatus = obrstat1_" "_obrstat2
s orcSeg = pRequest.FindSegment("ORC")
s consultantfname = $p(orcSeg.GetValueAt(12),"^",3)
s consultantsname = $p(orcSeg.GetValueAt(12),"^",2)
s orderconsultant = consultantfname_" "_consultantsname
set conn=##class(%SQLGatewayConnection).%New()
If conn=$$$NULLOREF quit $$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")
set sc=conn.Connect("databasename","username","password")

If $$$ISERR(sc) quit sc
if conn.ConnectionHandle="" quit $$$ERROR($$$GeneralError,"Connection failed")

set sc=conn.AllocateStatement(.hstmt)
if $$$ISERR(sc) quit sc

   //Prepare statement for execution
   set pQuery = "INSERT INTO ORDERS(column1, column2, column3, column4, column5) "
   set pQuery = pQuery_"VALUES("_value1_","_value2_","_value3_","_value4_","_value5_")"

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

UPDATE: So, it seems that I am able to connect to the database, but getting the following Error

ERROR #6022: Gateway Failed. Execute

Discussion (17)0

Add reply

Comments

Julius Kavay · Aug 16, 2019

Some thoughts/hints to your problem:

set stat=conn.Connect(, , ) is a MySQL user. Does this user have the rights for UPDATE and INSERT?
What are the status codes after
- set sc=conn.Prepare(...) and
- set sc=conn.Execute(...)?
Your query-string should have blanks after the table name and also before and after the VALUES keyword.
If the variables value1, value2, ... valueN CAN CONATIN a backslash character then you should either duplicate them set valueX = $replace(valueX, "", "\") or switch the escaping off at the start your query-string : set pQuery="SET sql_mode='NO_BACKSLASH_ESCAPES'; INSERT INTO ..." see also: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
If the content of the variable <valueX> is an alphanumeric value then put a single quote char (') around the value. Beforehand remove (or replace) all single quotes (with something else) from <variableX>.

0 0

Julius Kavay Aug 16, 2019 to Julius Kavay

HELP: what are the markdown characters?

In the above answer, I put some words between angle braces, now all they are lost! OK, I try once again with an apostrophe.

set stat=conn.Connect("odbcname", "username" ,"password") "username" is a MySQL user.
If the content of the variable "variableX" is an alphanumeric value then put a single quote char (') around the value. Beforehand remove (or replace) all single quotes (with something else) from "variableX".

0 0

ED Coder Aug 19, 2019 to Julius Kavay

Hi Julius, thank you for responding to my query. It seems like it is failing on "EXECUTE".

The Error is : ERROR #6022: Gateway failed: Execute

Tried the set Param to bind the parameters and getting the same error. Any suggestions what could be causing this? The user has full access to the Database. It is a MYSQL DB, I have had no issues connecting to a SQL DB using the same steps.

ODBC Connection is successful. Driver I am using is MySQL ODBC 8.0 Unicode Driver

Any suggestions or advice on what I could check? I am able to ping the server as well

Regards,

Eric

0 0

Eduard Lebedyuk · Aug 18, 2019

Use BindParameters / SetParameter to set parameters instead of concatinating.

0 0

ED Coder Aug 19, 2019 to Eduard Lebedyuk

Hi Eduard, thank you. I tried the set param binding as well but it seems that it could be an issue with my Gateway connection.

I tried linking the table and then testing my query and got the following error. The connections are all fine so dont know what is causing this

Any advice or suggestions?

Regards,

Eric

0 0

Eduard Lebedyuk Aug 19, 2019 to ED Coder

Some good ideas.

If you're unable to receive data at ll, start with a simple query and check if it works:

SELECT * FROM DUAL;

0 0

ED Coder Aug 19, 2019 to Eduard Lebedyuk

Thank you Eduard,

Same error running a SELECT statement as well.

Is there anything that you suggest before I consider having to make changes to the conf file?

Regards,

Eric

0 0

Julius Kavay Aug 19, 2019 to ED Coder

Some hints/questions:

did you installed the correct driver? I mean, do have Cache/Iris and the
driver the same architecture (32 vs. 64 bit)?
I'm not an MySQL expert (I do not even have an MySQL DB), so I ask, is there any problem with the character size (1 byte vs. 2 bytes/unicode), if this applies?
with a DB-Tool, like WinSQL, try to make an SELECT (as suggested by Eduard Lebedyuk) statement. What is the error message?

0 0

ED Coder Aug 19, 2019 to Julius Kavay

Hi Julius, Thank you

Yes, the driver installed is matching the cache architecture and using WINSQL I am able to successfully run the SELECT and INSERT query.

So its something in my code I guess? Will keep trying

Regards,

Eric

0 0

Julius Kavay Aug 19, 2019 to ED Coder

I can see it right now, do you use

conn.Prepare(...) or
conn.PrepareW(...) ?

This should be coordinated with your MySQL installation.

0 0

Julius Kavay Aug 19, 2019 to ED Coder

Of course, the same goes for the other methods, which have a wide (...W) variant

0 0

ED Coder Aug 20, 2019 to Julius Kavay

Hi Julius, updated the statements to include the W variant and still doesnt work for me.

I think it could be something with the statement, because a select statement doesnt give me an error. Can you spot anything in the following error message?

0 0

Eduard Lebedyuk Aug 20, 2019 to ED Coder

What's the error with

SELECT * FROM DUAL;

and W methods?

0 0

Martin Taylor Aug 20, 2019 to Eduard Lebedyuk

is this post any help?

https://community.intersystems.com/post/connecting-mysql-odbc

0 0

ED Coder Aug 21, 2019 to Eduard Lebedyuk

Hi Eduard, After using the prepareW statements the select query started working.

I simply deleted the entire function, and re-wrote it again and it now works. Did nothing different, just deleted the function, and copied the code back into the new function.

Cannot understand what the issue was. crazy. but solved now

Eric

0 0

Evgeny Shvarov Aug 21, 2019 to ED Coder

That's why I like to develop with IRIS docker containers - every time you have clean IRIS with no garbage code, cache, temp globals, whatever... - you have only what you setup in a build dockerfile script.

0 0

ED Coder · Aug 21, 2019

This was solved by writing a new function with the PrepareW command. I had the mySQL 8.0 Unicode driver installed.

I cannot understand why I had to write a new function but it works. Following are the checks to consider (that I made):

1. Check the quotes against the values that you enter

2. Test with PrepareW statements

3. Increase the max_allocated_packet from 4M to 16M [in the more recent versions of mySQL it is default to 512M]

Regards,

Eric

0 0