web stats
Calling stored procedure - Mirth Community

Go Back   Mirth Community > Mirth Connect > Development

Reply
 
Thread Tools Display Modes
  #1  
Old 09-19-2006, 09:06 AM
nshaik nshaik is offline
Mirth Guru
 
Join Date: Sep 2006
Posts: 302
nshaik
Default Calling stored procedure

=====================
Environment:

Windows Server 2000 SP4, Microsoft SQL Server 2000 SP3, Mirth HL7 v1.1

=====================
I get "Write statement should be an insert / update / delete sql statement" when I try to call a stored proc.

Is there a reference code for calling stored procedures from Mirth HL7?

Thanks in advance!


====================
E.g. "spHL7AddUpdatePatient" stored proc would do an update if the patient record exists or does an insert otherwise.

//////////////////////////////////////////////

Channel has 2 database destinations and both call "spHL7AddUpdatePatient" using the folliwng syntax.

DestForA04 --> SQL Statement:

EXEC spHL7AddUpdatePatient ${patAccount}, ${patPrefix}, ${patFirstName}, ${patMiddleName}, ${patLastName}, ${patSuffix}, ${patAddress1}, ${patAddress2}, ${patCity}, ${patState}, ${patZipCode}, ${patPhone1}, ${patPhone1Type}, ${patPhone2}, ${patPhone2Type}, ${patGender}, ${patDOB}, ${patDeathDate}, ${patSSN};

DestForA08 --> SQL Statement:

EXEC spHL7AddUpdatePatient ${patAccount}, ${patPrefix}, ${patFirstName}, ${patMiddleName}, ${patLastName}, ${patSuffix}, ${patAddress1}, ${patAddress2}, ${patCity}, ${patState}, ${patZipCode}, ${patPhone1}, ${patPhone1Type}, ${patPhone2}, ${patPhone2Type}, ${patGender}, ${patDOB}, ${patDeathDate}, ${patSSN};

//////////////////////////////////////////////

Getting following runtime error:

********************************
ERROR [5_destination_1.dispatcher.1] org.mule.impl.DefaultExceptionStrategy: Caught exception in Exception Strategy: Write statement should be an insert / update / delete sql statement
java.lang.IllegalArgumentException: Write statement should be an insert / update / delete sql statement
at org.mule.providers.jdbc.JdbcMessageDispatcher.doDi spatch(JdbcMessageDispatcher.java:84)
at org.mule.providers.AbstractMessageDispatcher$Worke r.run(AbstractMessageDispatcher.java:257)
at org.mule.impl.work.WorkerContext.run(WorkerContext .java:290)
at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor$Worker.runTask(ThreadPoolExecutor. java:650)
at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor$Worker.run(ThreadPoolExecutor.java :675)
at java.lang.Thread.run(Unknown Source)
********************************
Reply With Quote
  #2  
Old 09-19-2006, 10:55 AM
chrisl chrisl is offline
Mirth Employee
 
Join Date: Aug 2006
Posts: 967
chrisl is an unknown quantity at this point
Default Re: Calling stored procedure

Unfortunantly Mirth does not support stored procedures. If you would like this functionality, please add it to the issue tracker and we can get to work on it for a future release.

Thank you!
__________________
Chris Lang
Reply With Quote
  #3  
Old 09-20-2006, 12:00 PM
nshaik nshaik is offline
Mirth Guru
 
Join Date: Sep 2006
Posts: 302
nshaik
Default Re: Calling stored procedure

Chris,

Thank you very much for your email review feedback. I?m glad this turned out to be an ?acceptable? workaround.

If the Mirth HL7 team feels this would be a good workaround example for Mirth users, this may be uploaded to File Repository.

Following has been explained in detail below.

1. Creating Channel.
2. ?Fake Insert? SQL Statement
3. Filter to check A01, A04, A08, A10, A28, A31 message types
4. Transformer Step which calls stored proc with arguments
5. spHL7AddOrUpdatePatient - Microsoft SQL Server 2000 Stored Procedure

NOTE: Mirth throws this warning which is being ignored for now:
WARN [6_destination_1.dispatcher.1] org.mule.providers.jdbc.JdbcMessageDispatcher: Row count for write should be 1 and not 0

Hope it helps!

Thank you,

===================================
Workaround to call a stored procedure from Mirth is explained below. Limited testing has been done.
===================================

Environment: Windows Server 2000 SP4, Microsoft SQL Server 2000 Sp3, Mirth HL7 v1.1

============== XXXXXXXXXXX ==============

1. Create an LLP channel.
Send ACK: No.

Destination Name: PatientTable
Connector Type: Database Writer

============== XXXXXXXXXXX ==============

2. Under ?Destinations? tab in Mirth Administrator used a fake insert for SQL Statement:
INSERT INTO PATIENT (Account)
SELECT 'Fake Insert' WHERE 1 = 0
This is basically to ?trick? Mirth which expects an Insert, Update or delete for a database write.

============== XXXXXXXXXXX ==============
3. Filter

// Filter for A01, A04, A08, A10, A28, A31 to handle add or update patient.
// Refer transformer step "Call stored proc spHL7AddOrUpdatePatient"
globalMap.put('jdbcDriver', "net.sourceforge.jtds.jdbc.Driver");
globalMap.put('dbAddress', "jdbc:jtds:sqlserver://TestServer:1433/TestDB;");
globalMap.put('dbLogin', "sa");
globalMap.put('dbPassword', "test");

if (msg['MSH']['MSH.9']['CM_MSG.2'] == 'A01' ||
msg['MSH']['MSH.9']['CM_MSG.2'] == 'A04' ||
msg['MSH']['MSH.9']['CM_MSG.2'] == 'A08' ||
msg['MSH']['MSH.9']['CM_MSG.2'] == 'A10' ||
msg['MSH']['MSH.9']['CM_MSG.2'] == 'A28' ||
msg['MSH']['MSH.9']['CM_MSG.2'] == 'A31') {
return true;
}
else {
logger.info('Not an A01, A04, A08, A10, A28, or A31 message - Msg. ignored');
return false;
}

============== XXXXXXXXXXX ==============
4. Transformer Step
Name: Call stored proc spHL7AddOrUpdatePatient
Type: JavaScript

JavaScript Code:

var tempAcct = "NULL";
var tempPrefix = "NULL";
var tempFirstName = "NULL";
var tempLastName = "NULL";
var tempMiddleName = "NULL";
var tempSuffix = "NULL";
var tempBirthDate = "NULL";
var tempGender = "NULL";
var tempAddress1 = "NULL";
var tempAddress2 = "NULL";
var tempCity = "NULL";
var tempState = "NULL";
var tempZipCode = "NULL";
var tempPhone1 = "NULL";
var tempPhone1Type = "NULL";
var tempPhone2 = "NULL";
var tempPhone2Type = "NULL";
var tempDeathDate = "NULL";
var tempModifiedSSN = "NULL";

// External patient account number
if (msg['PID']['PID.3']['CX.1'].text()[0] != null) {
tempAcct = "'" + msg['PID']['PID.3']['CX.1'].text()[0] + "'";
}
// Patient name prefix
if (msg['PID']['PID.5']['XPN.5'].text()[0] != null) {
tempPrefix = "'" + msg['PID']['PID.5']['XPN.5'].text()[0] + "'";
}
// Patient first name
if (msg['PID']['PID.5']['XPN.2'].text()[0] != null) {
tempFirstName = msg['PID']['PID.5']['XPN.2'].text()[0].toString();
// Doubleup single quote for names link O'Brian
tempFirstName = tempFirstName.replace(/'/g, "''");
// SQL Server stored proc expects varchar parameters to be passed within single quotes
tempFirstName = "'" + tempFirstName + "'";
}
// Patient last name
if (msg['PID']['PID.5']['XPN.1'].text()[0] != null) {
tempLastName = msg['PID']['PID.5']['XPN.1'].text()[0].toString();
// Doubleup single quote for names link O'Brian
tempLastName = tempLastName.replace(/'/g, "''");
// SQL Server stored proc expects varchar parameters to be passed within single quotes
tempLastName = "'" + tempLastName + "'";
}
// Patient middle name
if (msg['PID']['PID.5']['XPN.3'].text()[0] != null) {
tempMiddleName = msg['PID']['PID.5']['XPN.3'].text()[0].toString();
// Doubleup single quote for names link O'Brian
tempMiddleName = tempMiddleName.replace(/'/g, "''");
// SQL Server stored proc expects varchar parameters to be passed within single quotes
tempMiddleName = "'" + tempMiddleName + "'";
}
// Patient name suffix
if (msg['PID']['PID.5']['XPN.4'].text()[0] != null) {
tempSuffix = "'" + msg['PID']['PID.5']['XPN.4'].text()[0] + "'";
}
// Patient birth date
if (msg['PID']['PID.7']['TS.1'].text()[0] != null) {
// Get birth date in mm/dd/yyyy format
var dob = msg['PID']['PID.7']['TS.1'].text()[0];
var ar = dob.match(/(\d\d\d\d)(\d\d)(\d\d)/);
tempBirthDate = RegExp.$2 + '/' + RegExp.$3 + '/' + RegExp.$1;
tempBirthDate = "'" + tempBirthDate + "'";
}
// Patient gender
if (msg['PID']['PID.8'].text()[0] != null) {
tempGender = "'" + msg['PID']['PID.8'].text()[0] + "'";
}
// Patient address1
if (msg['PID']['PID.11']['XAD.1'].text()[0] != null) {
tempAddress1 = msg['PID']['PID.11']['XAD.1'].text()[0].toString();
// Doubleup single quote for address like St.Peter's Hospital Rd
tempAddress1 = tempAddress1.replace(/'/g, "''");
// SQL Server stored proc expects varchar parameters to be passed within single quotes
tempAddress1 = "'" + tempAddress1 + "'";
}
// Patient address2
if (msg['PID']['PID.11']['XAD.2'].text()[0] != null) {
tempAddress2 = msg['PID']['PID.11']['XAD.2'].text()[0].toString();
// Doubleup single quote for address like St.Peter's Hospital Rd
tempAddress2 = tempAddress2.replace(/'/g, "''");
// SQL Server stored proc expects varchar parameters to be passed within single quotes
tempAddress2 = "'" + tempAddress2 + "'";
}
// Patient city
if (msg['PID']['PID.11']['XAD.3'].text()[0] != null) {
tempCity = msg['PID']['PID.11']['XAD.3'].text()[0].toString();
// Doubleup single quote in case city name has a single quote
tempCity = tempCity.replace(/'/g, "''");
// SQL Server stored proc expects varchar parameters to be passed within single quotes
tempCity = "'" + tempCity + "'";
}
// Patient state
if (msg['PID']['PID.11']['XAD.4'].text()[0] != null) {
tempState = "'" + msg['PID']['PID.11']['XAD.4'].text()[0] + "'";
}

// Patient zipcode
if (msg['PID']['PID.11']['XAD.5'].text()[0] != null) {
tempZipCode = "'" + msg['PID']['PID.11']['XAD.5'].text()[0] + "'";
}

// Get patient's phone1 w/o "( )" or "-"
var tempPhone1AreaCode = "";
var tempPhone1Number = "";
// Patient phone1 - area code
if (msg['PID']['PID.13']['XAD.6'].text()[0] != null) {
tempPhone1AreaCode = msg['PID']['PID.13']['XTN.6'].text()[0];
}
// Patient phone1 - phone number
if (msg['PID']['PID.13']['XTN.7'].text()[0] != null) {
tempPhone1Number = msg['PID']['PID.13']['XTN.7'].text()[0];
}
if (tempPhone1AreaCode != "") {
tempPhone1 = "'" + tempPhone1AreaCode + tempPhone1Number + "'";
}
else {
tempPhone1 = "'" + tempPhone1Number + "'";
}
// Patient phone1 type
if (msg['PID']['PID.13']['XTN.9'].text()[0] != null) {
tempPhone1Type = "'" + msg['PID']['PID.13']['XTN.9'].text()[0] + "'";
}

// Get patient's phone2 w/o "( )" or "-"
var tempPhone2AreaCode = "";
var tempPhone2Number = "";
// Patient phone2 - area code
if (msg['PID']['PID.14']['XAD.6'].text()[0] != null) {
tempPhone2AreaCode = msg['PID']['PID.14']['XTN.6'].text()[0];
}
// Patient phone2 - phone number
if (msg['PID']['PID.14']['XTN.7'].text()[0] != null) {
tempPhone2Number = msg['PID']['PID.14']['XTN.7'].text()[0];
}
if (tempPhone2AreaCode != "") {
tempPhone2 = "'" + tempPhone2AreaCode + tempPhone2Number + "'";
}
else {
tempPhone2 = "'" + tempPhone2Number + "'";
}
// Patient phone2 type
if (msg['PID']['PID.14']['XTN.9'].text()[0] != null) {
tempPhone2Type = "'" + msg['PID']['PID.14']['XTN.9'].text()[0] + "'";
}

// Get patient's death date in mm/dd/yyyy format
if (msg['PID']['PID.29']['TS.1'].text()[0] != null) {
var deathDate = msg['PID']['PID.29']['TS.1'].text()[0];
if (deathDate != null) {
var ar = deathDate.match(/(\d\d\d\d)(\d\d)(\d\d)/);
tempDeathDate = RegExp.$2 + '/' + RegExp.$3 + '/' + RegExp.$1;
tempDeathDate = "'" + tempDeathDate + "'";
}
}

// Get patient SSN with "-" removed
if (msg['PID']['PID.19'].text()[0] != null) {
tempSSN = "'" + msg['PID']['PID.19'].text()[0].toString() + "'";
tempModifiedSSN = tempSSN.replace(/-/g, "");
}

//DatabaseConnection
var dbConn = DatabaseConnectionFactory.createDatabaseConnection (globalMap.get('jdbcDriver'), globalMap.get('dbAddress'), globalMap.get('dbLogin'), globalMap.get('dbPassword'));

//Get parameters for the stored proc separated by ,
// Stored proc call will look like "EXEC spHL7AddOrUpdatePatient '1234', 'Mr', 'TestFN', ..., '111223333'";
var spParams = tempAcct + ', ' + tempPrefix + ', ' +
tempFirstName + ', ' + tempMiddleName + ', ' +
tempLastName + ', ' + tempSuffix + ', ' +
tempAddress1 + ', ' + tempAddress2 + ', ' +
tempCity + ', ' + tempState + ', ' +
tempZipCode + ', ' + tempPhone1 + ', ' +
tempPhone1Type + ', ' + tempPhone2 + ', ' +
tempPhone2Type + ', ' + tempGender + ', ' +
tempBirthDate + ', ' + tempDeathDate + ', ' +
tempModifiedSSN;

var expression = "EXEC spHL7AddOrUpdatePatient " + spParams;
// Print the SQL statement for debugging purpose
logger.info('SQL: ' + expression);
var result = dbConn.executeCachedQuery(expression);
//go to the first result
result.next();
//get the value from the first column as an integer
var iReturnVal = result.getInt(1);
//cleanup
result.close();
dbConn.close();
if (iReturnVal > 0) {
// Stored proc returns +ive no. for success
logger.info('Processed add or update patient successfully.');
}
else {
logger.info('Error: spHL7AddOrUpdatePatient failed.');
}

============== XXXXXXXXXXX ==============
5. Microsoft SQL Server 2000 Stored Procedure

CREATE PROCEDURE spHL7AddOrUpdatePatient (
@Account varchar (15),
@Prefix varchar (10),
@FirstName varchar (30),
@MiddleName varchar (30),
@LastName varchar (30),
@Suffix varchar (20),
@Address1 varchar (50),
@Address2 varchar (50),
@City varchar (30),
@State varchar (3),
@ZipCode varchar (10),
@Phone1 varchar (30),
@Phone1Type varchar (25),
@Phone2 varchar (30),
@Phone2Type varchar (25),
@Gender varchar (1),
@Birthdate varchar (12),
@DeathDate varchar (12),
@SSN varchar (9)
)
AS
BEGIN
IF EXISTS (SELECT PatientID FROM Patient WHERE Account = @Account)
BEGIN
-- UPDATE existing patient
UPDATE Patient
SET Account = @Account, Prefix = @Prefix, FirstName = @FirstName, MiddleName = @MiddleName,
LastName = @LastName, Suffix = @Suffix, Address1 = @Address1, Address2 = @Address2,
City = @City, State = @State, ZipCode = @ZipCode, Phone1 = @Phone1, Phone1Type = @Phone1Type,
Phone2 = @Phone2, Phone2Type = @Phone2Type, Gender = @Gender, BirthDate = @Birthdate,
DeathDate = @DeathDate, SSN = @SSN
WHERE Account = @Account
IF @@ERROR <> 0
SELECT -1
ELSE
SELECT 1
END
ELSE
BEGIN
-- Add new patient
INSERT INTO Patient (Account, Prefix, FirstName, MiddleName, LastName, Suffix,
Address1, Address2, City, State, ZipCode, Phone1, Phone1Type,
Phone2, Phone2Type, Gender, BirthDate, DeathDate, SSN)
VALUES (@Account, @Prefix, @FirstName, @MiddleName, @LastName, @Suffix,
@Address1, @Address2, @City, @State, @ZipCode, @Phone1, @Phone1Type,
@Phone2, @Phone2Type, @Gender, @Birthdate, @DeathDate, @SSN)
IF @@ERROR <> 0
SELECT -1
ELSE
SELECT 1
END

END
Reply With Quote
  #4  
Old 12-11-2006, 04:26 PM
goodenough goodenough is offline
What's HL7?
 
Join Date: Dec 2006
Posts: 1
goodenough
Default Re: Calling stored procedure

Was just looking at some of the old posts and had a thought ... What if I am more comfortable with the 'heavy lifting' in SQL?

1. Create an inbound queue table on the SQL server with all the fields needed by the incomming HL7 messages (including the message type)
2. Use Mirth to make the simple inserts into this table
3. Create a trigger on the table for inserts
4. Have the trigger call my stored procedure with all the fancy insert/udpate logic

Just an idea for those that would rather see this in SQL rather than JavaScript.

Hope this helps somebody.
Don
Reply With Quote
  #5  
Old 12-19-2006, 08:32 AM
rbmangeles rbmangeles is offline
Mirth Newb
 
Join Date: Dec 2006
Posts: 7
rbmangeles
Default Re: Calling stored procedure

Sir,

I was wondering if you can post a sample Insert statement? I am not that good in doing Javascript and more familiar in doing things in SQL Server. Our current setup for a channel is the Source is a file reader of HL7 messages and our Destination is a Database writer. How can me map the fields from a message into an INSERT statement for SQL Server?

Thank you very much!
Reply With Quote
  #6  
Old 12-19-2006, 08:46 AM
nshaik nshaik is offline
Mirth Guru
 
Join Date: Sep 2006
Posts: 302
nshaik
Default Re: Calling stored procedure

I'm not sure if I understood your question correctly.

Do you need a sample to call an INSERT statement from Mirth HL7 instead of doing the insert through a stored procedure?

Reply With Quote
  #7  
Old 12-19-2006, 08:48 AM
rbmangeles rbmangeles is offline
Mirth Newb
 
Join Date: Dec 2006
Posts: 7
rbmangeles
Default Re: Calling stored procedure

Yes sir. Sorry for confusing you. We have a File Reader as our Source and Database writer for our destination. I have no idea how to get the values of the field from the message and insert it into a table.

Thanks!
Reply With Quote
  #8  
Old 12-19-2006, 09:09 AM
nshaik nshaik is offline
Mirth Guru
 
Join Date: Sep 2006
Posts: 302
nshaik
Default Re: Calling stored procedure

1. Please refer to "Getting Started With Mirth", Contributed by VocalEZ

http://www.mirthproject.org/index.ph...d=45&Itemid=81

2. Also, refer to samples in "File Repository".

3. As regards sample INSERT statement to SQL Server, here's one:

Hope it helps!


========== SQL INSERT Statement in the Destination tab ===============

INSERT INTO Patient (Account, Prefix, FirstName, MiddleName, LastName, Suffix, Address1, Address2, City, State, ZipCode, Phone1, Phone1Type, Phone2, Phone2Type, Gender, BirthDate, DeathDate, SSN)
VALUES (${patAccount}, ${patPrefix}, ${patFirstName}, ${patMiddleName}, ${patLastName}, ${patSuffix}, ${patAddress1}, ${patAddress2}, ${patCity}, ${patState}, ${patZipCode}, ${patPhone1}, ${patPhone1Type}, ${patPhone2}, ${patPhone2Type}, ${patGender}, ${patDOB}, ${patDeathDate}, ${patSSN});

================================================== =======
Reply With Quote
  #9  
Old 12-19-2006, 09:31 AM
rbmangeles rbmangeles is offline
Mirth Newb
 
Join Date: Dec 2006
Posts: 7
rbmangeles
Default Re: Calling stored procedure

Is SQL Server 2005 already supported by Mirth regarding drivers?

Thanks!
Reply With Quote
  #10  
Old 12-19-2006, 10:15 AM
chrisl chrisl is offline
Mirth Employee
 
Join Date: Aug 2006
Posts: 967
chrisl is an unknown quantity at this point
Default Re: Calling stored procedure

Yes, the JTDS driver should support it. You can drop any JDBC driver into the custom folder and use it from Mirth.

-Chris
__________________
Chris Lang
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error calling stored procedure on mysql through a CALL Stmt. d.voelkerts Support 3 08-30-2017 06:14 AM
calling mssql stored procedure julia1231 Support 4 05-17-2009 11:59 PM
calling stored procedures on the Source connector rkedilaya Support 4 06-04-2007 11:32 AM
need some help with calling stored procedure julia1231 Development 3 04-05-2007 01:04 PM
Error calling a Stored Procedure d.voelkerts Support 1 03-15-2007 03:59 AM


All times are GMT -8. The time now is 01:13 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation