web stats
Inserting duplicate rows? - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 07-06-2016, 12: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
  #2  
Old 07-06-2016, 01:58 PM
phatty phatty is offline
What's HL7?
 
Join Date: Jul 2015
Posts: 1
phatty is on a distinguished road
Default

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?

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

var result5 = dbConn.executeCachedQuery( query5 );

var cmExists = result5.next(); // RETURNS TRUE IF ID EXISTS, FALSE OTHERWISE  WORKS PERFECTLY

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

result5.close();
Reply With Quote
  #3  
Old 07-07-2016, 01:21 AM
siddharth siddharth is offline
Mirth Guru
 
Join Date: Feb 2013
Posts: 832
siddharth is on a distinguished road
Default

This statement

var cmExists = result5.next();

is used wrongly.

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

while(result5.next())
{
//begin operations
}

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

More info here
http://docs.oracle.com/javase/6/docs...Set.html#next()

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

Tags
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 07:34 PM.


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