New post
Question
· Jun 29, 2021

Problem with Dynamic SQL parameterized UPDATE vs INSERT

#ObjectScript #SQL #HealthShare #InterSystems IRIS

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]
Discussion (14)3
Log in or sign up to continue
Jonathan Anglin · Jun 30, 2021

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'
0 0
Dan Pasco · Jul 6, 2021

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?

0 0
Jonathan Anglin · Jul 6, 2021

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.

1 0
Dan Pasco · Jul 8, 2021

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.

0 0
Jonathan Anglin · Jul 8, 2021

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.

1 0
Jonathan Anglin · Jul 13, 2021

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=?
0 0