calling an SQL stored procedure from a tcl script

[Feda Birani]

Can you call an SQL stored procedure from a tcl script? I have the connection established and i can manually insert, select from within the TCL script, but I want to call the stored procedure instead and pass in the elements to insert. I’m aware you can do it from database protocol, but again, for what I’m using it for, i need it in the TCL. Anyone have some suggestions? I’ve tried {call storedprocedurename ($elm1, $elm2….)} no luck. I’ve tried exec {call …..} no luck.

Thank you

Topic

[Peter Heggie]

Are you using ODBC or Cloverleaf tables with DB Lookup? Either one can call stored procedures. I recommend using DB Lookup.

Table Definition: Advanced Database Lookup

SQL: {?=CALL prBatchReport_getClmsgid( <@Docid> , <@SeqV> )};

IN: @Docid,@SeqV

OUT: _CLRC_,RS_clmsgid

 

tcl code:

set result [dblookup “dbl_prBatchReport_getClmsgid” “$docid” “$seq”]
if {$debug > 1} {echo “$module batch_report_get_clmsgid result: $result”}
set resultlist [split “$result” “,”]
set clmsgid [lindex $resultlist 1]

Peter Heggie

[Feda Birani]

Hi Peter,

Thanks for the quick response. I don’t believe an insert stored procedure within a lookup table is allowed? I receive “Only SELECT statement is allowed in DBLookup”

I’m hoping for the same idea via tcl script.  {?=CALL stored procedure( all passed in elements to insert)};

Thoughts? else resorting back to the same ODBC dbconnect run insert query etc….

 

[Peter Heggie]

We call stored procedures that do Inserts using DB Lookup, here is an example:

code:

set result [dblookup dbl_prCCDA_insertInboundCCDA “$MSID” “$RECDATE” “$FRFACILITY” “$FRADDRESS” “$FRDOCNAME” “$FRDOCTELE” “$TOADDRESS” “$SUBJECT” “$DOCTYPE” “$DOCFILE” “$DOCDATE” “$DOCSTATUS” “$LASTNAME” “$FIRSTNAME” “$DOB” “$GENDER” “$ADDRESS” “$COMPLAINT” “$NEXTNUMBER” ]

table:

{?=CALL prCCDA_insertInboundCCDA( <@MailMsgId> , <@Rec_date> , <@From_facility> , <@From_address> , <@From_provider_name> , <@From_provider_tele> , <@To_address> , <@Subject> , <@Doc_type> , <@Doc_file> , <@Doc_date> , <@Doc_status> , <@Patient_lastname> , <@Patient_firstname> , <@Patient_dob> , <@Patient_gender> , <@Patient_address> , <@Chief_complaint> , <@Nextnumber> , @RCode OUT , @RDesc OUT )};

IN: @MailMsgId,@Rec_date,@From_facility,@From_address,@From_provider_name,@From_provider_tele,@To_address,@Subject,@Doc_type,@Doc_file,@Doc_date,@Doc_status,@Patient_lastname,@Patient_firstname,@Patient_dob,@Patient_gender,@Patient_address,@Chief_complaint,@Nextnumber

OUT: _CLRC_,OUT_@RCode,OUT_@RDesc

SQL Server stored procedure (beginning):

ALTER PROCEDURE [dbo].[prCCDA_insertInboundCCDA]
@MailMsgId varchar(128),
@Rec_date varchar(23),
@From_facility varchar(50),
@From_address varchar(50),
@From_provider_name varchar(50),
@From_provider_tele varchar(50),
@To_address varchar(100),
@Subject varchar(120),
@Doc_type varchar(20),
@Doc_file varchar(256),
@Doc_date varchar(23),
@Doc_status varchar(30),
@Patient_lastname varchar(50),
@Patient_firstname varchar(50),
@Patient_dob varchar(8),
@Patient_gender varchar(1),
@Patient_address varchar(100),
@Chief_complaint varchar(100),
@Nextnumber varchar(20),
@RCode INT OUTPUT,
@RDesc varchar(200) OUTPUT
AS
BEGIN
DECLARE @myERROR int — Local @@ERROR
, @myRowCount int — Local @@ROWCOUNT
, @myAppCount int — Value from select count
, @RecDate datetime
, @DocDate datetime
SET NOCOUNT ON;

SELECT @myAppCount = count(*) from dbo.ccda_inbound where doc_file = @Doc_file;
SELECT @myERROR = @@ERROR, @myRowCOUNT = @@ROWCOUNT
IF @myERROR != 0 GOTO HANDLE_ERROR

SELECT @RecDate = convert(datetime,@Rec_date)
SELECT @DocDate = convert(datetime,@Doc_date)

IF @myAppCount = 0
BEGIN
— Insert new row into inbound docs table
INSERT INTO dbo.ccda_inbound
(rec_date,from_facility,from_address,from_provider_name,from_provider_tele,to_address,mail_msg_id,[subject],doc_type,doc_file,
doc_date,doc_status,patient_lastname,patient_firstname,patient_dob,patient_gender,patient_address,chief_complaint,ext_account_number)
VALUES
(@RecDate,@From_facility,@From_address,@From_provider_name,@From_provider_tele,@To_address,@MailMsgId,@Subject,@Doc_type,@Doc_file,
@Docdate,@Doc_status,@Patient_lastname,@Patient_firstname,@Patient_dob,@Patient_gender,@Patient_address,@Chief_complaint,@Nextnumber)

SELECT @myERROR = @@ERROR, @myRowCOUNT = @@ROWCOUNT
IF @myERROR != 0 GOTO HANDLE_ERROR

SELECT @RCode = 0
SELECT @RDesc = ‘new row inserted’
RETURN 0
END

Peter Heggie

[Feda Birani]

Thanks again Peter, Not sure what I’m missing, keeps telling me same thing “Only SELECT statement is allowed in DBLookup”. Could it be the version i’m on? 6.2.4?

[Peter Heggie]

I think that is the issue. I remember that DB Lookup functionality expanded in later versions, so this is likely the reason why only SELECT is allowed. We are on version 19.1

Peter Heggie

[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]

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]

…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.

[Author]

Replies