Data not getting inserted into mySQL DB

Question

Question

ED Coder · Aug 16, 2019

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

Log in or sign up to continue

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

score 0 · Answer 1 · 2019-08-16T18:26:50-04:00

Some thoughts/hints to your problem:

1) set stat=conn.Connect(, ,
)
is a MySQL user.
Does this user have the rights for UPDATE and INSERT?

2) What are the status codes after
- set sc=conn.Prepare(...) and
- set sc=conn.Execute(...)?

3) Your query-string should have blanks after the table name and also before and after the VALUES keyword.

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

5) If the content of the variable is an alphanumeric value then put a single quote char (') around the value. Beforehand remove (or replace) all single quotes (with something else) from .

score 0 · Answer 2 · 2019-08-16T18:36:40-04:00

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.

1) set stat=conn.Connect("odbcname", "username" ,"password")
"username" is a MySQL user.

5) 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".

score 0 · Answer 3 · 2019-08-19T04:42:23-04:00

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

score 0 · Answer 4 · 2019-08-18T08:35:51-04:00

Eduard Lebedyuk · Aug 18, 2019

Use BindParameters / SetParameter to set parameters instead of concatinating.

0 0

score 0 · Answer 5 · 2019-08-19T04:46:14-04:00

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

score 0 · Answer 6 · 2019-08-19T05:25:03-04:00

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;

score 0 · Answer 7 · 2019-08-19T05:45:51-04:00

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

score 0 · Answer 8 · 2019-08-19T07:47:02-04:00

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?

score 0 · Answer 9 · 2019-08-19T08:57:58-04:00

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

score 0 · Answer 10 · 2019-08-19T09:47:06-04:00

I can see it right now, do you use
- conn.Prepare(...) or
- conn.PrepareW(...) ?

This should be coordinated with your MySQL installation.

score 0 · Answer 11 · 2019-08-19T09:52:33-04:00

Julius Kavay · Aug 19, 2019

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

0 0

score 0 · Answer 12 · 2019-08-20T07:47:58-04:00

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?

score 0 · Answer 13 · 2019-08-20T08:10:18-04:00

Eduard Lebedyuk · Aug 20, 2019

What's the error with

SELECT * FROM DUAL;

and W methods?

0 0

score 0 · Answer 14 · 2019-08-20T08:49:47-04:00

Martin Taylor · Aug 20, 2019

is this post any help?

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

0 0

score 0 · Answer 15 · 2019-08-21T09:00:40-04:00

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

score 0 · Answer 16 · 2019-08-21T09:11:32-04:00

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.