Data Integrator – use in Xlates

[Jim Kosloskey]

All who are using Data Integrator (ODBC Drivers) inside Xlates for Table lookup or whatever:

What action do you take when you cannot connect to the Database? Do you do the sleep and retry or what???

What do you do when connection finally just fails?

How do you handle/communcate other errors (connected but activity failed)?

We are contemplating using Data Integrator to utilize dynamic Tables for lookup purposes. That is not something we have allowed in the past (dynamic lookup tables that is).

We are concerned that when error conditions happen there may not be savory recovery potentials.

Ignoring messages or erroring them out due to Xlate issues is not something we do.

By the same token we have always been concerned about the action to take when something is not found in the Table – if the Table is not accessible is that the same as ‘not found’ or should some action be taken when the situation arises? If so, what action do you take?

I am curious as to what your positions are regarding the issues surrounding dynamic access via Data Integrator from Xlates.

Thanks

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

Topic

[Anonymous]

Jim I have not used the ODBC stuff inside a translation for all the reasons you just mentioned.  It just adds a level of complexity that can be avoided pretty easily but modifying the message before hand.

Depending on what I’m connecting to and it’s reliability I do either a shutdown or a try x time to connect then shutdown.

[Jim Kosloskey]

John,

OK so you probably put your ODBC stuff in the Inbound Tps or pre-route.

However, the sleep you use for the retries stops the entire process does it not?

If you have an error condition then I think you are saying you stop the inbound thread – did I read that correctly?

Thanks

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

[Sergey Sevastyanov]

Jim

I know I saw it somewhere but can’t find it now.

Last night I put a new code in a Tcl proc that is called from within xlate by CALL. That proc uses Tcl package that I created to access MS SQL server database to pull data and insert them into message.

That package has been working fine for a few weeks. But I never called it from within xlate – I used it pre-xlate and post-xlate procedures.

So last night I decided to give it a try and call it from within xlate and it worked! I was so excited!

This morning I saw lot of errors in the log – package variable not exists.

Here is exact error message:

 

Code:

Tcl callout error

erroCode: NONE

errorInfo:

can’t read “db”: no such variable

   while executing

“$db $queries(stmt_$qryname) $queries(args_$qryname) $qryparms”

   (procedure “par_sql::run_qry” line 12)

   invoked from within

“par_sql::run_qry get_culture_exam_quantity [list $visit_ext_id $order $child_order $seq”

   (procedure “get_ce_qnty” line 50)

   invoked from within

“get_ce_qnty”

I don’t know why it worked last night, but it’s not so important for me now.

I wonder if my tcl called from within Xlate (using CALL) runs in a new process every time? That would explain why the package variable doesn’t exist. That variable “$db” is handler to the SQL connection.

Here is code of my proc:

Code:

######################################################################
# Name: get_ce_qnty – get Culture Exam quantities from SQL database
# Purpose:
# UPoC type: xltp
# Args: none
# Notes: All data is presented through special variables.  The initial
# upvar in this proc provides access to the required variables.
#
# This proc style only works when called from a code fragment
# within an XLT.
#
# This proc called from within Xlate and the following fields are passed into it:
#
# Visit External Id – patient visit number
# Paragon Order Number – first four digits are order#, the rest are child order#
# Culture Exam Sequence number
#

package require par_sql

proc get_ce_qnty {} {
global db
global access_paragon

set data_source_name paragon_puwt
set ignore_errors 1

par_sql::setDebug 1

if {![info exists db]} {
# try to connect to paragon db
if {[catch {par_sql::connect $data_source_name} err_msg]} {
if {$ignore_errors} {
set access_paragon 0
echo $err_msg
} else {
set access_paragon 0
error $err_msg
}
} else {
set db $err_msg
set access_paragon 1
}

# if can’t access db – just return
if {!$access_paragon} {
return
}
}

   upvar xlateId       xlateId
 xlateInList   xlateInList
 xlateInTypes  xlateInTypes
 xlateInVals   xlateInVals
 xlateOutList  xlateOutList
 xlateOutTypes xlateOutTypes
 xlateOutVals  xlateOutVals

set visit_ext_id [lindex $xlateInVals 0]
set par_order [lindex $xlateInVals 1]
set seq [lindex $xlateInVals 2]

set order “*”
set child_order “*”
regexp — {(d{4})(d{1,4})} $par_order -> order child_order

### echo “visit# $visit_ext_id”
### echo “order $order/$child_order”
### echo “seq $seq”

set cult_exam [par_sql::run_qry get_culture_exam_quantity [list $visit_ext_id $order $child_order $seq]]

### echo “culture exam: $cult_exam”

# return result – quantity is in 1st element of the returned list; seq – in 2nd
set ce_quantity [string trim [lindex [lindex $cult_exam 0] 0]]
xpmstore $xlateId [lindex $xlateOutList 0] c $ce_quantity
}

I wonder if I just should move my access to MS SQL to pre-xlate proc where it works. This is kind of urgent issue so at this point I more concerned about making it working than spending too much time on understanding why it’s not and how to fix it  😕

Thanks,

Sergey

[Jim Kosloskey]

Sergey,

I am not sure how you wrote your package but the issue could be there.

I have successfully invoked the Cloverleaf Data Integrator package from within an Xlate without execution issue.

My questions have to do with procedural situations.

If it were me, I would make sure the package you wrote really is working in the pre or post Xlate environment.

One thing that comes to mind is that the package may not have db defined as global.

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

[Sergey Sevastyanov]

Jim,

Thank you for your reply. I don’t think the prpblem is in the package – I’ve been using it in other threads in pre-xlate context for a few weeks now.

As the problem that I experienced on Friday morning (when I wrote my question) was urgent I actually wrote pre-xlate procedure that uses the same package without changing a single line of code in the package and removed my code from xlate. It worked fine.

In the package db defined with “variable” (I use a namespace in the package) – I’m not an expert in Tcl but had an impression that “variable” is almost the same as global in namespace – please correct me if I’m wrong.

If you successfully use data integrator from within xlate I wonder if the odbc package can make the difference – I use tclodbc as it doesn’t require license (I know that you use licensed ODBC that is recommended by Cloverleaf).

[Author]

Replies