- This topic has 6 replies, 4 voices, and was last updated 6 years, 2 months ago by
.
-
Topic
-
Hello, I am writing my first DB call to a stored procedure on an SQL server and having issues. I am able to call the procedure, and update the database, only the column headings are updating the table and not the values that I am sending in.
I created an xlate that converts an HL7 ORM message to a VRL record layout. The VRL formated data is sent to a PROTOCOL:database-outbound thread. The properties of this thread have been set up to call a stored Procedure (below). I have been able to hit the stored procedure, and update the correct columns of the database. The issue is that the values from the VRL are not populating into the stored procedure query. For example, if the below variable of patientTransportTest.visitNumber is ‘V123456’, the SQL database entry is actually “patientTransportTest.visitNumber” and not the value of “V123456” for that record.
I feel like it’s a simple syntax issue I am overlooking.
Content of call:
{CALL [dbo].[sp_patientTransportTest] (@OrderStatus = ‘
‘, @Gender = ‘
‘, @visitNumber = ‘
‘ )} Thanks,
Jay
-
Replies
-
-
-
Hi Jason,
what version of Cloverleaf are you using? There was a change between 5.8.x and 6.x in the call format.
Other than that, yes I think it is a simple syntax issue.
Here is a working example:
Code:{call prPFlags_insert(, ,<patient_flags.notification_consent,)}
The stored procedure name is “prPFlags_insert”.
1) we do not use the schema qualifier because we specified the schema in the Site Preferences/Database Connection Properties. So in this case the ‘dbo’ schema was already specified in the Site Preferences.
2) regarding the square brackets – I know SQL Server likes them, and probably they are not doing any harm in your invocation; we don’t use them, but maybe we will have to in the future if the name conflicts with a reserved word.
3) we don’t use the SP @name=value convention. I just copied some other syntax I had seen here, which is basically a positional parameter syntax instead of a named parameter syntax.
4) but after all that, I’m thinking that the issue is that the value is in quotes. Have you tried running it without quotes?
If you have, and/or you think you need them because you are using the name=value syntax, maybe you could try using the positional syntax without quotes.
Possibly using the name=value method, and having quotes, lets you overcome problems introduced if your data has embedded commas – embedded commas can be an issue, but I have resolved that by using quotes in a previous step – i.e. perhaps the data is stored in the VRL by using an Xlate; in the Xlate I COPY the data which may have embedded commas to the output VRL within quotes – I use a tcl fragment in the COPY action to surround the data by quotes and pass the updated string to the xlateOutVals. When the assembled VRL is sent to the outbound thread, the data has the embedded commas safely inside quotes. Then I don’t need quotes or apostrophes in the DB Outbound query syntax.
I highly recommend temporarily putting in a tcl proc in your outbound thread’s output UPOC that writes the entire message to the process log, just so you can see if there is unexpected data in the message being passed to the DB outbound VRL.
Here is one I use a lot:
Code:proc tpsPrintMsg { args } {
set debug 1
set module “tpsPrintMsg:”
keylget args MODE mode ;# Fetch modeswitch -exact — $mode {
start {
return {}
}
run {
keylget args MSGID mh
set dispList “”
lappend dispList “CONTINUE $mh”# Get msgcontent
set msg [msgget $mh]# print msg content
if {$debug} {echo “$module msg:n$msg”}return $dispList
}shutdown {
return {}
}default {
echo “Unknown mode in : $mode ”
return ” ”
}
}
}Peter Heggie
-
Hello all,
Thank you for the responses. I have listened and attempted all the suggestions.
I have tried removing the quotes, and I get the below syntax error:
[java:wrte:ERR /0:mt_orm_transport_update_0:07/05/2018 09:27:02] Call to “doMsg” returned error string “BADDATA caught exception trying to processMessageFromCloverleaf: Failed to write message to database: Incorrect syntax near ‘<'.". Returning bad send to engine.
I also did you use the temp TCL code to see what is leaving the engine. That was a nice debugging tool thanks. The VRL is returning the correct values with the commas in the right spot.
I was trying to use the SP @name=value format yes. I thought it may be easier for others to follow behind me. However; after having no luck with this, I think that I will just use the positional parameter syntax. I have had success with that format after populating the database just fine. With the time frame that I have, I will get it to work for now, then maybe go back to the SP @name=value format at another time.
Thanks again!
Jay
-
Well its too bad the name = value syntax is not working – I’d like to see how to use that style. But I think we can use the positional argument format for pretty much everything – Its just harder to code, because we are constantly flipping back and forth between the SQL tool and Cloverleaf GUI to make sure we have all of the arguments in the right order.
Peter Heggie
-
-
The error you are getting refers to the symbols. Try removing those as well from around each of your parameters.
Example:
Code:{call prPFlags_insert(patient_flags.encounter, patient_flags.ecd,patient_flags.notification_consent,patient_flags.PCP_provid_and_name)}
It’s at least worth a quick shot 🙂
-
- The forum ‘Cloverleaf’ is closed to new topics and replies.
