web stats
Javascript SELECT query to return COUNT() - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 06-10-2011, 06:44 AM
edwardw edwardw is offline
OBX.1 Kenobi
 
Join Date: Aug 2010
Posts: 43
edwardw is on a distinguished road
Default Javascript SELECT query to return COUNT()

Hi

I'm executing a javascript query to count the number of records in a SQL SELECT query. The simplified version looks like this:

var sqlQuery = "SELECT COUNT(*) TABLE1";
var result= dbConn.executeCachedQuery(sqlQuery);

My question is should I use executeCachedQuery? How do I get the count from the returned object?

Thanks
Reply With Quote
  #2  
Old 06-11-2011, 03:13 AM
mourisj mourisj is offline
Mirth Guru
 
Join Date: Feb 2007
Location: Luxembourg
Posts: 407
mourisj is an unknown quantity at this point
Default

if (result.next()) {
num_records = result.getString(1);
}
__________________
JJ Mouris
FFE Luxembourg
IT-Department
Reply With Quote
  #3  
Old 06-14-2011, 12:52 PM
edwelch4 edwelch4 is offline
OBX.1 Kenobi
 
Join Date: Oct 2007
Location: Columbia, SC
Posts: 27
edwelch4 is an unknown quantity at this point
Default

try this example...
var newsql = "SELECT Count(*) FROM tbl_Patient WHERE PatCorpNumber = '" + pi + "'";

var newresult = dbConn.executeCachedQuery(newsql);

// Fetch the result row.
newresult.next();

//If no match, Insert new row into Patients and visits
if (newresult.getInt(1) == 0)


Ed
Reply With Quote
  #4  
Old 06-21-2011, 11:35 AM
edwardw edwardw is offline
OBX.1 Kenobi
 
Join Date: Aug 2010
Posts: 43
edwardw is on a distinguished road
Default

Thanks. Worked great.
Reply With Quote
  #5  
Old 12-31-2016, 08:21 PM
jcreedon jcreedon is offline
Mirth Newb
 
Join Date: Jan 2016
Posts: 7
jcreedon is on a distinguished road
Default

Quote:
Originally Posted by edwelch4 View Post
try this example...
var newsql = "SELECT Count(*) FROM tbl_Patient WHERE PatCorpNumber = '" + pi + "'";

var newresult = dbConn.executeCachedQuery(newsql);
newresult.next();
if (newresult.getInt(1) == 0)


Ed
I get com.mirth.connect.server.userutil.MirthCachedRowSe t@1bfc6a7 as my count... what am I doing wrong:
var newsql = "SELECT count(*) FROM table WHERE MRN = '+MRN+'";
var newresult = dbConn.executeCachedQuery(newsql);
newresult.next();
if (newresult.getInt(1) == 0)
Reply With Quote
  #6  
Old 12-31-2016, 08:30 PM
jcreedon jcreedon is offline
Mirth Newb
 
Join Date: Jan 2016
Posts: 7
jcreedon is on a distinguished road
Default

Quote:
Originally Posted by mourisj View Post
if (result.next()) {
num_records = result.getString(1);
}
When I run this I get a count of 0, I am expecting 143.
var newsql = "SELECT count(*) FROM table WHERE MRN = '+MRN+'";
var result = dbConn.executeCachedQuery(newsql);
if (result.next()) {
num_records = result.getString(1);
}

Running select count(*) from table WHERE MRN = '+MRN+'"; from my server I get 143 so why is this different? This is driving me nuts.
Reply With Quote
  #7  
Old 12-31-2016, 08:38 PM
jcreedon jcreedon is offline
Mirth Newb
 
Join Date: Jan 2016
Posts: 7
jcreedon is on a distinguished road
Default

OMG, talk about a brain dead mistake..........

My issue was the MRN variable in my SELECT statement was '+MRN+' and not "+MRN+"
double quotes for variables and single for text..... that was a 4+ hour mistake.

Sorry for necroing this post.
Reply With Quote
Reply

Tags
database, javascript, query, sql

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:32 AM.


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