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

  • 0
  • 0
  • 107
  • 14
  • 3

Answers

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 .

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

Regards,

Eric

Use BindParameters / SetParameter to set parameters instead of concatinating.

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

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

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?

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

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

This should be coordinated with your MySQL installation.

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

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?

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

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.

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