Question
Jonathan Anglin · Jun 29

Problem with Dynamic SQL parameterized UPDATE vs INSERT

Hello All

I'm running into an issue performing UPDATES that I'm not getting on INSERTS. It's probably obvious, but I'm just not seeing it and could use a little help.

I'm going over an HL7 message and depending upon varying criteria, the relevent variables will get items added to them like the following:

Set patientId = pRequest.GetValueAt("PID:3")
Set sqlColumns = sqlColumns_",patient_id"
Set sqlValues = sqlValues_",?"
Set par($i(p)) = patientId

After compiling the variables, I check to see if accession number is found in the table.

set performInsert = 1
set performUpdate = 0
...
set sqlQueryText = "SELECT id FROM table_name WHERE accession_number= ?"
set sc = ..Adapter.ExecuteQuery(.resultSet,sqlQueryText,accessionNumber)

  if $$$ISERR(sc) {return sc}

  while (resultSet.%Next() '= 0){
    Set performUpdate = 1
    Set performInsert = 0
  }

  Set par = p

Which determines if the record should be inserted or updated. This is a proof of concept. I may later explore the INSERT or UPDATE methodology, but the PK is set to auto-increment and it looks like I'd need to change that so its value can be specified in an INSERT or UPDATE query.

if (performInsert = 1) {
  Set tSQL = "INSERT INTO table_name ("_sqlColumns_") VALUES ("_sqlValues_")"
  Set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,tSQL,.par)
}
if (performUpdate = 1) {
 Set tSQL = "UPDATE table_name ("_sqlColumns_") VALUES ("_sqlValues_") WHERE accession_number='"_accessionNumber_"'"
  Set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,tSQL,.par)
}
return tSC

The INSERT works as expected, but when the UPDATE is attempted I get the error: Remote JDBC error: Incorrect syntax near '('.. .

Looking at the UPDATE reference, it does not appear I need to use a "SET" command. Can I not use a parameterized list in the UPDATE, or do I have to reference it in some other way?

As always, any help, guidance, pointers, or disabuse of preconceived notions is appreciated.

Product version: HealthShare 2020.1
$ZV: IRIS for UNIX (Red Hat Enterprise Linux for x86-64) 2020.1 (Build 215U) Mon Mar 30 2020 20:23:13 EDT [HealthConnect:2.1.0]
00
4 0 14 149
Log in or sign up to continue

It would be good to have a look at the final value of tSQL in performUpdate.

As expected, tSQL looks like:

UPDATE table_name (updated,order_system_name,site_code,patient_id,patient_alternate_id,patient_last_name,patient_first_name,patient_middle_name,patient_dob,patient_sex,patient_address_1,patient_address_2,patient_city,patient_state,patient_zip,patient_account_number,patient_ssn,visit_class,visit_unit,visit_facility,attending_doctor_id,attending_doctor_last_name,attending_doctor_first_name,referring_doctor_id,referring_doctor_last_name,referring_doctor_first_name,consulting_doctor_id,consulting_doctor_last_name,consulting_doctor_first_name,admitting_doctor_id,admitting_doctor_last_name,admitting_doctor_first_name,visit_number,admit_datetime,discharge_datetime,accession_number,status_code,order_completed_datetime,placer_order_number,filler_order_number,procedure_code,procedure_description,ordering_doctor_id,ordering_doctor_last_name,ordering_doctor_first_name,procedure_modality,order_priority,reason_for_exam) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) WHERE accession_number='123456789'

Hi Jonathan,

I am very interested in this problem (I wrote the early version of dynamic statement parameter binding). My first question is simple - can you execute this statement using embedded SQL? If you can then we need to dig further to discover the correct error (some errors get masked as syntax when there is a different problem).

Thanks,

Dan

PS:

Of course, you will have to supply values for ? to test compile this as an embedded statement (:hv1, :hv2... can work) - or - can you try this using an SQL utility or as a ODBC/JDBC statement?

If I use a standard value assignment (SET mrn = '123456', lastName = 'Smith', etc.), it works just fine pulling the values from the HL7 message and writing them to the appropriate columns. I'm going through the code and adding corresponding lines for each variable I wish to store:

Set valueAssignment = valueAssignment_", site_code = '"_siteCode_"'"
...
Set tSQL = "UPDATE table_name"_valueAssignment_" WHERE accession_number='"_accessionNumber_"'"

I'm hoping that there may not be an issue with the parameter binding at all and it's just some odd value in one of the fields, although everything looks fine. Once I get through added that line for all of the variables in a day or two, I'll know for sure. So far, it's nothing in MSH, PID, or PV1.

I'll reply back once I know.

Standard value assignment does work for updating the record. I also looked at every open parenthesis in the code just in case something looked off, but it all seems to be fine.

It appears the issue may be the dynamic statement parameter binding. 

I didn't count the number of question marks but I assume you've verified that it matches the number of columns.

Also, there are limitations on the number of command line arguments you can pass but that number is rather high. I don't recall exactly what it is but it is certainly limited. At one time we established 16 as the maximum number of arguments. As I recall, we did nothing to impose an arbitrary limit but ObjectScript itself does have a limit.

My next questions are meant to establish where the error is reported. You can prepare a dynamic SQL statement and then execute it separately and multiple times. Is the error reported during prepare or during execute?

PS: (edited):

Sorry, I see now your code. You are using a HS Adapter to execute this statement. That leads me to a different question - can you prepare and execute this statement as a Dynamic SQL Statement?

set statement = ##class(%SQL.Statement).%New()

set status = statement.%Prepare(.tSQL)

If status is okay (1) then execute it - set result = statement.%Execute( <your parameter values go here>)

Then check result.%SQLCODE.

In the meantime, I will take a look at the Adapter (not my area but I can still look) to see if there is some limitation.

Well maybe I am hitting a limit then, because there are 48 variables.

The error is reported at execution. I see it in the interface's log. I get no errors when compiling the ObjectScript.

I suspect that is the case but I cannot verify it without seeing more of your code. Someone from our HealthShare team probably knows this but I don't work on that team. Sorry.

Odd(ish) the limitation is on the UPDATE but not on the INSERT. I will follow up with the WRC and see if I can get a definitive answer.

I am using the parameters to avoid code insertion (not that I'm terribly worried about it, but it's just good practice). If I cannot do that, then I'll have to do some kind of variable cleaning on the string, or perhaps define each as a %STRING literal or something. Whatever the best practice is for ObjectScript.

After conferring with the WRC, it seems there really wasn't a "problem" at all, but rather that while they share the same syntax for INSERT commands,  there is a syntax difference in how HealthShare and Microsoft each handle an UPDATE command.

The following code works in HealthShare/ObjectScript:

Set tSQL = "UPDATE table_name (col1, col2, col3) VALUES ('val1', 'val2', 'val3') WHERE accession_number='"_accessionNumber_"'"
Set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,tSQL,.par)

And this is how Microsoft wants it:

Set tSQL = "UPDATE IRIS_Study SET col1='val1', col2='val2', col3='val3' WHERE accession_number='"_accessionNumber_"'"
Set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,tSQL,.par)

And it can still be parameterized by using:

SET col1=?, col2=?, col3=?

Have you tried removing the comma(",patient_id")= from the sqlcolumns and sqlvalues?

SQL requires comma's between column names and the question mark placeholders, and patient_id is not the first column to be written. The sqlColumns and sqlValues strings are seeded with "updated" and "?" before new columns are added.

Sorry I misread the SET statement...

Have you tried running the same update (capture exact values being passed) from a JDBC client such as DBeaver? 

The generated tSQL looks good by just glancing at it.

I have not, but that's a good suggestion. I may also rework the script to not use a parameterized (is that a word) update query and see if that works.