[Michael Burrows]

…and this is how we call it from another proc:

#First, set all your parameters in variables such as par1, par2, etc.

set comma “,”

#Set name of server in ODBC connection here:
set serv “server”

#Set DB username here
set uname “username”

#Set DB passsword here
set pw “password”

#Set name of stored procedure here
set spName “storedprocedure”

# Put previously defined parameter variables into a list. Note that the first and last parameter are missing their first and last quote marks, respectively.
set pList

#Join the list into a single string
set p [join $pList $comma]

#echo “Parameter list: $p”

#Call the sql_sp_query proc and set the result to the vDone variable
set vDone [sql_sp_query $serv $uName $pw $spName $p]

 

A few notes:

When I first set this up, it was only set up to handle one stored procedure parameter. But I later found that I could pass multiple parameters in a single parameter by separating them properly with quotes and commas. (I.e., build a list of parameters, leaving off the first and last quote marks, then join the list with commas.)

When calling it, if you only need one parameter, just set the parameter to $p.

As you can see in the proc, there are some things I didn’t completely understand, such as the return length. I have had problems returning long strings from the query, where anthing past a certain character length will be garbled. I think this has to do with the return length parameter, but I haven’t had chance to tinker with it to find out.

I hope this helps, and the formatting makes it clear. Let me know if you have questions or comments. I’d love to improve this code, if I could.

[Michael Burrows]

The following is the custom TCL proc (also attached in a txt file):

#############################################################################
# Name: sql_sp_query
#
# Purpose: Executes a stored procedure on a remote SQL Server DB and returns the value.
# This should be used when you are passing a single parameter and expecting a single value returned.
# Works well when expecting a boolean (yes/no, 0/1, T/F) result. Stored procedures should be written
# to return ‘0’ when false or no result found.
#
#
# Arguments: dsn – The name of the ODBC connection in the $HCIROOT/lib/Connect7.0/odbc.ini file
# (There is also a home/hci/odbc.ini file, but I don’t think we need to update this one manually.)
# usr – Username to connect to the DB
# pwd – Password
# sp – Stored procedure
# parm- Parameter for stored procedure *ONLY ONE PARAMETER MAY BE PASSED, OR BLANK STRING “”*
# *Example, when calling from another proc:
# “sql_sp_query sqlserverdb myusername mypassword sp_storedprocname parameter”*
#
#
# History:
# 1. 6/23/2016 – Mike Burrows – Created
#

proc sql_sp_query { dsn usr pwd sp parm } {

if {[catch {
package require odbc
# Allocate an environment handle/pointer (henv)
odbc SQLAllocHandle SQL_HANDLE_ENV SQL_NULL_HANDLE henv
# Set up the environment
odbc SQLSetEnvAttr $henv SQL_ATTR_ODBC_VERSION SQL_OV_ODBC3 0
# Allocate a DB handle / pointer (hdbc)
odbc SQLAllocHandle SQL_HANDLE_DBC $henv hdbc
# Connect to DB using hdbc, passing usr / pw
odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pwd SQL_NTS
# Allocate a Statement handle/pointer (hsmt)

odbc SQLAllocHandle SQL_HANDLE_STMT $hdbc hstmt
# Init variables for the returned value(s). Apparently length is going to be required for the query, so init a var for that too.
set sqlret “POSSIBLE_SQL_ERROR”
set sqlretlength “”
# SQL INS CONT.
# Command to send to DB
# Syntax: set SQLCmd “sp_STORED_PROC_NAME \’$parameter\’”
if {$parm != “”} {
set SQLCmd “EXEC $sp \’$parm\’”
} else {
set SQLCmd “EXEC $sp”
}
# Execute command
odbc SQLExecDirect $hstmt $SQLCmd SQL_NTS
# Bind the results to sqlret variable. Using the correct min. length is important here.
odbc SQLBindCol $hstmt 1 SQL_NVARCHAR sqlret 90 sqlretlength
# The SQLFetch actually retrieves the results for your vars… (Seems extraneous… not sure?)
odbc SQLFetch $hstmt
# The variables are now populated with the return value(s) and length
#echo “sqlret variable: $sqlret”
#echo “sqlretlength variable: $sqlretlength”
set returnVal $sqlret

# The following is to free the handles and disconnect from the database
# Critically important for the OS
odbc SQLFreehandle SQL_HANDLE_STMT $hstmt
odbc SQLDisconnect $hdbc
odbc SQLFreeHandle SQL_HANDLE_DBC $hdbc
odbc SQLFreeHandle SQL_HANDLE_ENV $henv
} sqlErr]} {
set returnVal “POSSIBLE_SQL_ERROR”
}

#echo $returnVal
#echo $sqlErr
return $returnVal

}

More to follow…

• This reply was modified 2 years, 2 months ago by Michael Burrows. Reason: formatting

Attachments:

You must be logged in to view attached files.

[Michael Burrows]

(Edited and broken up into multiple posts to make code formatting more clear…)

Here is how we do it, purely with TCL.

Some of this you may know already, and some of it may not be “best practice”.

We contracted with a TCL developer that knew all this stuff well. I took his code, simplified it, and made it more readable. There are some things I did not understand about the TCL he used for the ODBC package, but I figured it out and documented it as best as I could. From there, I created a TCL proc called “sql_sp_query” which I can reuse in any TCL proc that needs to query a remote DB.

I’m a little better at SQL than I am at TCL, so I rely on SQL stored procedures to do everything I need to do from Cloverleaf, then just call it from our custom “sql_sp_query” proc.

First, we set up the odbc connection in the odbc.ini file. The entry should look like this. (Just change the entries for Address, Database, LogonID, and Password.)

[sqlserverdb]
Driver=/quovadx/cis19.1/integrator/lib/Connect8.0/lib/CVsqls27.so
Description=DataDirect 8.0 SQL Server Wire Protocol
Address=10.10.x.x, 1433
AlternateServers=
AnsiNPW=Yes
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=DatabaseName
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
LoadBalancing=0
LogonID=USERNAME
Password=PASSWORD
QuotedId=No
ReportCodepageConversionErrors=0
ReportDateTimeType=1
QEWSD=2456728
SnapshotSerializable=0

 

…more to follow.

• This reply was modified 2 years, 2 months ago by Michael Burrows. Reason: formatting
• This reply was modified 2 years, 2 months ago by Michael Burrows. Reason: formatting

Attachments:

You must be logged in to view attached files.

[Michael Burrows]

Oh, wow. That’s good to know.

Process configuration is not where I would have thought something like that would be found.

Much appreciated!

[Michael Burrows]

Hi again, Lisa.

My attachment outlines the way we’re doing this. (HL7 -> SOAP MSG -> SOAP REPLY -> HL7)

Let me know if you have questions, suggestions, or feedback.

Attachments:

You must be logged in to view attached files.

[Michael Burrows]

Did some more poking around and found the java/ws-client protocol which I think may be what you’re referencing there. Having never used that protocol and having little-to-no experience with Java, it would have never crossed my mind to use it and probably wouldn’t know how anyway. But hopefully your solution helps someone else.

Thanks again!

-Michael

[Michael Burrows]

Joe,

We are working on that, but not via Cloverleaf, as the preferred medium (maybe the only medium?) is via Direct Messaging, which we have set up with a direct connection between our Meditech EMR and the HISP.

I would have never even considered using Cloverleaf for Direct Messaging, although I am sure it could be done.

I’m curious, are you planning to implement ECR with something other than Direct Messages to AIMS, or are you interfacing Direct Messages through Cloverleaf?

-Michael

[Michael Burrows]

Hi Lisa.

Thanks for the info!

Unfortunately I don’t think this solution will work for us because we don’t have a WS-Client protocol available (unless I am overlooking it somewhere). I think you may have either a more recent Cloverleaf install than us (19.1), or you have a Web Services add-on that we don’t have. I do recall them offering something like that to us at a hefty premium.

Nevertheless, we did find a good workaround of our own by using a regular http-client protocol combined with our web proxy server which lives in the DMZ.

We created a new http entry on our proxy server, which we can reach locally from Cloverleaf, which redirects to the GRITS https connection. In this way, we offload the security to our proxy server without having to do anything extra in Cloverleaf.

We haven’t finished implementing it yet, but we have had several successful tests and should have it implemented this summer.

Thanks again for the help. It’s good to make the acquaintance of another Cloverleaf user familiar with our struggles here in Georgia!

-Michael

[Michael Burrows]

Thanks, Charlie.

So the cert is not being validated with the CA when I use that option, as I guessed.

Any ideas *how* I can make sure that Cloverleaf does it? I am pretty sure it’s not a problem with the state’s cert.

Thanks,

 

Michael

[Michael Burrows]

I should have mentioned:

We are on Cloverleaf 19.1 on an AIX 7.1 system.

[Michael Burrows]

When I create a static raw route, it names the route “_HCI_STATIC_ROUTE_”. If I try to create another one, it defaults to that same name, and gives me an error “…’_HCI_STATIC_ROUTE_’ already exists for this thread…“.

I can think of at least one way around this, maybe by using wild cards in a defined TRXID route, but on further contemplation it really that wouldn’t matter anyway for what I’m trying to do here.

Thanks,

Michael

[Michael Burrows]

That would be the way we’d normally do it. But this ADT feeds about 50 threads, so I wanted to avoid having to put a translation on the other 49 to null it out.

Although now that you mention it, surely there’s a way to reconfigure my routes to allow for this. But you can only have one static route on each inbound thread, right?

I don’t doubt there’s something more obvious that I am overlooking with routes. But we have never used anything but a single static route with TCL scripts on each route detail.

Thanks!

[Michael Burrows]

Ah! I like this idea. It’s kind of non-standard but then so is our methodology!

We rarely use and never *set* metadata in our normal day-to-day operations, but I think this will work. I can place this on the IB tab, then use a proc on the route of the single destination to pull it out.

Thanks a lot!

[Michael Burrows]

I see, so the code for that would be something to the effect of:

 

#Get the destination
set n [msgmetaget $mh DESTCONN]

#If destination is not the one that needs the customization…
if ![cequal $n “name_of_destination_to_allow_new_val”] {
#Code to blank out IN1.15 and then continue the message
} else {
#Continue the message without blanking out IN1.15
}

 

Then I’d have to figure out at what point to place the code for it to be effective, but this is something to think about. Thanks!

[Michael Burrows]

Yes, that’s what we’d do normally. But in this case, the “change” is that new data (inaccessible from elsewhere) will be coming from the source system, so it will be in every message.

 

Thanks,

 

Michael

[Author]

Replies