· Aug 16, 2019

Data not getting inserted into mySQL DB

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 ]
pidSeg = pRequest.FindSegment("PID")
mrn = $p(pidSeg.GetValueAt(3),"^",1)
obrSeg = pRequest.FindSegment("OBR")
obrdate = obrSeg.GetValueAt(6)
obrdesc = $p(obrSeg.GetValueAt(4),"^",2)
obrstat1 = $p(obrSeg.GetValueAt(40),"^",2)
obrstat2 = $p(obrSeg.GetValueAt(40),"^",3)
obrstatus = obrstat1_" "_obrstat2
orcSeg = pRequest.FindSegment("ORC")
consultantfname = $p(orcSeg.GetValueAt(12),"^",3)
consultantsname = $p(orcSeg.GetValueAt(12),"^",2)
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

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:

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 .

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

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



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?

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]