web stats
Inserting duplicate rows? - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Thread Tools Display Modes
Old 07-06-2016, 01:58 PM
psanders psanders is offline
OBX.2 Kenobi
Join Date: Nov 2011
Posts: 55
psanders is on a distinguished road
Default Inserting duplicate rows?

Building a channel to query an external database and insert the results into an internal database (both SQL Server) and to do the following things:
(for reference the "aht" database is the internal db I'm trying to do the insert)
1) Get the aht userid for integration
2) Get the aht client information from aht database.
3) Get the aht patient id from aht database, blank if they donít exist yet
4) Get the aht Diagnosis id from aht database, blank if doesnít match
5) Get the aht CaseManager id from aht database, 0 if doesnít exist (QUERY IS DONE BUT always returning nothing even though the query works in regular sql)
6) IF casemanager is 0 (not in aht database) Insert casemanager into aht database and get the new case manager id
7) IF aht patient id is blank, create the patient (using data values from mirth and from javascript (ahtDiagId,ahtClientId,ahtCMID,ahtUserId)
8) IF aht patient id is not blank, UPDATE the patient (using data values from mirth and from javascript (ahtDiagId,ahtClientId,ahtCMID,ahtUserId)

The problem is the query to select a casemanager id is always returning a blank. But if I take the logged query it returns values. So its inserting tons of duplicates.

Any suggestions would be appreciated. I've attached the channel for review.
Attached Files
File Type: xml SnowlinePatient_TESTDEID.xml (69.7 KB, 0 views)
Reply With Quote
Old 07-06-2016, 02:58 PM
phatty phatty is offline
What's HL7?
Join Date: Jul 2015
Posts: 1
phatty is on a distinguished road

A little more information. I am able to determine if the ID exists, but the ID is blank...

Why does it tell me that it found an ID, but not return it?

var query5 = "SELECT ID FROM oncall_staff WHERE casemanagername = '" + cmname + "' and organization_id = " + ahtClientId;

var result5 = dbConn.executeCachedQuery( query5 );


var ahtCMID = (result5.length > 0) ? result5.getInt(1) : 0; // ALWAYS FAILS, RESULT5 does not have id value as expected

Reply With Quote
Old 07-07-2016, 02:21 AM
siddharth siddharth is offline
Mirth Guru
Join Date: Feb 2013
Posts: 835
siddharth is on a distinguished road

This statement

var cmExists = result5.next();

is used wrongly.

It acts as a row iterator over the result set. Something like

//begin operations

If the query returns 10 rows, this loop runs 10 times. Your operations shall be inside this loop.

More info here

Last edited by siddharth; 07-07-2016 at 02:21 AM. Reason: forgot link
Reply With Quote

javascript writer, sql server

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

All times are GMT -8. The time now is 01:49 PM.

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