Use TCL to lookup items in SQlite database for XLT

[Jeremy Bumgarner]

I have a SQlite database with one table.  I am storing three items in it – the patients admission number, our lab systems order number, and the requesting system’s order number.  I am able to store the previously mentioned items in my SQlite table using a TCL proc on the ORR message with no errors.

I now have moved on to the ORU message I need to send back.  I now need to use the patient’s admission number and our lab system’s order number to look up the requester’s order number.  I figure that I will need to do this in my translation table, since I am already doing some substitution in the MSH segment.  My question is “How?”

I know where my admission number and lab order number will be in the ORU message (PID 18 and ORC 2 respectively).

Topic

[Jeremy Bumgarner]

Update:

In my XLT, I am using a copy command.  I use PID 18 and ORC 2 as my source values, and a Post Proc with the following code:

set visitnumber [string length [lindex $xlateInVals 0]]

set order [string length [lindex $xlateInVals 1]]

package require sqlite

set dbName “/cloverleaf/quovadx/cis5.8/integrator/eisenhower/tclprocs/hms2allscripts.db”

sqlite DBCMD $dbName

set xlateOutVals

]]

I am attempting to place the resulting order number (from the requester) into the a temp variable @ASOrder.

My problem now is that I am getting my patient admission number into my temp variable @ASOrder, not the requesting system’s order number.

Any ideas??  Just about at my wit’s end.

[Jim Kosloskey]

Jeremy,

Have you tried this as a pre proc rather than post?

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Rob Abbott]

string length gives you the length of the string.  I’m pretty sure this isn’t what you want, and it means your select is most likely returning nothing useful.

You should also put some more debugging in here to make sure you are doing the right thing:

How about this:

Code:

set visitnumber  [lindex $xlateInVals 0]
set order [lindex $xlateInVals 1]

package require sqlite
set dbName “/cloverleaf/quovadx/cis5.8/integrator/eisenhower/tclprocs/hms2allscripts.db”
sqlite DBCMD $dbName

set sql “select allscriptsorder from hms2allscriptsxlate where hmspatno = $visitnumber and hmsorder = $order”

echo “executing sql: ‘$sql'”
if [catch {DBCMD eval $sql}] result] {
  echo “Error from sqlite: $result”
  set xlateOutVals ERROR
} else {
  echo “sql success, result=’$result'”
  set xlateOutVals [list $result]
}

Rob Abbott
Cloverleaf Emeritus

[Rob Abbott]

Also what Jim said.  Make it a pre proc 🙂

Rob Abbott
Cloverleaf Emeritus

[Jeremy Bumgarner]

Thanks for the information – didn’t realize I picked post over pre.

[Jeremy Bumgarner]

Think I’m on the right track now.  Thanks to everyone.

Now I’m getting what I think is a TCL error.  I am very new to TCL, so please excuse the lack of knowledge.  Here’s the output from the Cloverleaf testing tool:

Please Wait ……

Command Issued: hcixlttest -L -n -i -e ASCII -d 3 -f nl hms_oru_allscriptslab.xlt /home/hci/AllscriptsResults/RESULT_Allscripts_ALC.hl7

Command output:

executing sql: ‘select allscriptsorder from hms2allscriptsxlate where hmspatno = 1407332 and hmsorder = 2600’

MESSAGE 1

Tcl callout error

erroCode: NONE

errorInfo:

wrong # args: extra words after “else” clause in “if” command

   while executing

“if [catch {DBCMD eval $sql}] result] {

  echo “Error from sqlite: $result”

  set xlateOutVals ERROR

} else {

  echo “sql success, result=’$result'”…”

Checking for leaked handles…

[Jim Kosloskey]

Jeremy,

I think you have an extra close sqaure bracket here:

“if [catch {DBCMD eval $sql}] result] {

 echo “Error from sqlite: $result”

 set xlateOutVals ERROR

} else {

 echo “sql success, result=’$result'”…”

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jeremy Bumgarner]

Thank you everyone!  Everything is working beautifully now.  

After I do some more testing to make sure that everything is OK, I will be done.

Again, thank you everyone!

[Author]

Replies