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:
{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:
proc tpsPrintMsg { args } {
set debug 1
set module “tpsPrintMsg:”
keylget args MODE mode ;# Fetch mode
switch -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 ” ”
}
}
}
.
