calling an SQL stored procedure from a tcl script

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]

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

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

(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