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.