web stats
Issue with SQL query execution - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 07-19-2019, 11:20 AM
Mits87 Mits87 is offline
OBX.2 Kenobi
 
Join Date: Nov 2016
Posts: 59
Mits87 is on a distinguished road
Default Issue with SQL query execution

I am using Mirth 3.6.1 and in the destination transformation step, I am executing a SQL statement (Microsoft SQL Standard 2012). It works 99.9% but once in a while, it will not populate the information even though the details are present in the database. The weird thing is no error generated. Have someone seen such issue. Or is there something I am doing incorrectly.


var dbConn = DatabaseConnectionFactory.createDatabaseConnection (
'net.sourceforge.jtds.jdbc.Driver',
'jdbc:jtds:sqlserver://localhost:1433/db',
'user',
'password'
);
var sql = "SELECT LastName, FirstName,MiddleName FROM Operator where code = '" +optid+"'";
results = dbConn.executeCachedQuery(sql);

if(results.next())
{
optlname = results.getString("LastName");
optmname = results.getString("MiddleName");
optfname = results.getString("FirstName");
optname = optlname + ' ' + optfname + ' ' + optmname ;

} else
{
optname = 'undefined user';
}
}
finally
{
if(dbConn)
dbConn.close();
}

Last edited by Mits87; 07-19-2019 at 12:51 PM.
Reply With Quote
  #2  
Old 07-25-2019, 04:01 AM
AlexNeiva AlexNeiva is offline
Mirth Guru
 
Join Date: Oct 2013
Location: Portugal
Posts: 277
AlexNeiva is on a distinguished road
Default

Hello,


can you post the error?
Thanks


Best Regards
__________________
Best Regards,
Alex Neiva
Reply With Quote
  #3  
Old 07-30-2019, 07:09 AM
Mits87 Mits87 is offline
OBX.2 Kenobi
 
Join Date: Nov 2016
Posts: 59
Mits87 is on a distinguished road
Default

Unfortunately, there is no error generated. It is just even though the data is available in DB the query still doesn't return it. When I resend the result again mirth finds the record no issue. This only happens roughly 5% of my total transmission.
Reply With Quote
  #4  
Old 07-31-2019, 11:34 AM
ISLITM ISLITM is offline
Mirth Newb
 
Join Date: Jun 2016
Posts: 6
ISLITM is on a distinguished road
Default

is it a possibility that the ones that fail are ones where "optid" has a single quote in it to escape your SQL query?

Also I believe you have an extra } in your code

maybe instead of:
Code:
if(results.next())
{
	optlname = results.getString("LastName");
	optmname = results.getString("MiddleName");
	optfname = results.getString("FirstName");
	optname = optlname + ' ' + optfname + ' ' + optmname ;
}
else 
{ 
	optname = 'undefined user';
}
}
finally
{
	if(dbConn)
	dbConn.close();
}
use:
Code:
if(results.size() == 1)
{
	results.next();
	optlname = results.getString("LastName");
	optmname = results.getString("MiddleName");
	optfname = results.getString("FirstName");
	optname = optlname + ' ' + optfname + ' ' + optmname;
}
else 
{ 
	optname = 'undefined user';
}
finally
{
	if(dbConn)
	dbConn.close();
}
Reply With Quote
  #5  
Old 08-02-2019, 06:20 AM
schillert schillert is offline
OBX.1 Kenobi
 
Join Date: Jul 2012
Posts: 30
schillert is on a distinguished road
Default

Are you sure optid is unique?
Reply With Quote
  #6  
Old 08-02-2019, 06:35 AM
Mits87 Mits87 is offline
OBX.2 Kenobi
 
Join Date: Nov 2016
Posts: 59
Mits87 is on a distinguished road
Default

Yes, they are unique and in my query, it will always have only one result returned. I have already taken care of the special character in previous steps.In addition to that, I placed the catch (e) in the try loop. but no error is shown.

I feel sql might be timing out before it gets the result of the query. is it possible to extend the wait time for query execution?
Reply With Quote
Reply

Tags
sql error, sql query, sql reader, 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 11:01 AM.


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