web stats
Row count from query - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 05-01-2018, 10:35 AM
mroberts mroberts is offline
OBX.1 Kenobi
 
Join Date: Jul 2010
Posts: 40
mroberts is on a distinguished road
Default Row count from query

I am losing my mind. been searching the forums and i can not find my mistake.

No matter which example or how i change it always get 0 as the total. Any assistance would be appreciated

var dbConn;
var pagecount;
//Total number of pages in the document
//sql connection string
var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://isql:1433/db1','user','password');
var doc_id = $('document_id');
var sql = ("select count(*) as total FROM page WHERE (document_id = '" + doc_id + "' )");
//execute sql to get total page count
var pagecount = dbConn.executeCachedQuery(sql);

if (pagecount.next()) {
num_records = pagecount.getString(1);
channelMap.put('totalpages',num_records);
}


//close sql channel
dbConn.close();
Reply With Quote
  #2  
Old 05-02-2018, 01:33 AM
siddharth siddharth is offline
Mirth Guru
 
Join Date: Feb 2013
Posts: 832
siddharth is on a distinguished road
Default

Change this

if (pagecount.next())

to

Code:
while(pagecount.next()) {}
__________________
HL7v2.7 Certified Control Specialist!
Reply With Quote
  #3  
Old 05-02-2018, 03:40 AM
aTom aTom is offline
OBX.3 Kenobi
 
Join Date: Feb 2016
Location: Lake Geneva Area
Posts: 113
aTom is on a distinguished road
Default

Hi,

The only problem I see in your code is the DB url, there's a space between "sql" and "server", an exception is fired when you execute it.

Otherwise it worked for me once query is set appropriately for my test db.

Is this the full JS code or just an exerpt?

And (sorry to ask) are you sure you have a $('document_id') value ? What is the data type of document_id column in your DB?
__________________
Tom
Reply With Quote
  #4  
Old 05-02-2018, 09:01 AM
mroberts mroberts is offline
OBX.1 Kenobi
 
Join Date: Jul 2010
Posts: 40
mroberts is on a distinguished road
Default

I made the change as you suggested but still get result of 0
The executed sql query returns 3
Not sure what is going wrong or what mistake i did

var doc_id = $('document_id');
var sql = ("select count(*) as total FROM page WHERE (document_id = '" + doc_id + "' )");
logger.info(sql);
//execute sql to get total page count
var pagecount = dbConn.executeCachedQuery(sql);

while(pagecount.next()) {
num_records = pagecount.getString(1);
channelMap.put('totalpages',num_records);
}


//close sql channel
dbConn.close();
Reply With Quote
  #5  
Old 05-02-2018, 09:11 AM
mroberts mroberts is offline
OBX.1 Kenobi
 
Join Date: Jul 2010
Posts: 40
mroberts is on a distinguished road
Default

the typo with the space was where i was removing the actual url and password

the $(document_id) is valid and the sql does return a vaid count
Reply With Quote
  #6  
Old 05-02-2018, 09:22 AM
aTom aTom is offline
OBX.3 Kenobi
 
Join Date: Feb 2016
Location: Lake Geneva Area
Posts: 113
aTom is on a distinguished road
Default

Same result if you don't use the num_record variable ? You don't declare it explicitely, so it's a global variable or it is declared and reused elsewhere?

Code:
   channelMap.put('totalpages', pagecount.getString(1));
__________________
Tom
Reply With Quote
  #7  
Old 05-02-2018, 11:08 AM
odo odo is offline
OBX.3 Kenobi
 
Join Date: Feb 2017
Location: Luxembourg
Posts: 137
odo is on a distinguished road
Default

Quote:
Originally Posted by mroberts View Post
var sql = ("select count(*) as total FROM page WHERE (document_id = '" + doc_id + "' )");
remove the brackets:

Code:
var sql = "select count(*) as total FROM page WHERE document_id = '" + doc_id + "'";
Reply With Quote
  #8  
Old 05-02-2018, 11:32 AM
mroberts mroberts is offline
OBX.1 Kenobi
 
Join Date: Jul 2010
Posts: 40
mroberts is on a distinguished road
Default

Quote:
Originally Posted by odo View Post
remove the brackets:

Code:
var sql = "select count(*) as total FROM page WHERE document_id = '" + doc_id + "'";
Same result from both of your suggestions

channel map total pages gets 0 versus 3 that should be returned.
Here is the total javasc transformer
var dbConn;
var pagecount;
//Total number of pages in the document
//sql connection string
var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://sql:1433/db','user','password');
var doc_id = $('document_id');
var sql = "select count(*) as total FROM page WHERE document_id = '" + doc_id + "'";
logger.info(sql);
//execute sql to get total page count
var pagecount = dbConn.executeCachedQuery(sql);

while(pagecount.next()) {
//num_records = pagecount.getString(1);
//channelMap.put('totalpages',num_records);
channelMap.put('totalpages', pagecount.getString(1));
}


//close sql channel
dbConn.close();
Reply With Quote
  #9  
Old 05-02-2018, 03:08 PM
agermano agermano is offline
Mirth Guru
 
Join Date: Apr 2017
Location: Indiana, USA
Posts: 841
agermano is on a distinguished road
Default

It helps legibility if you use [CODE]tags[/CODE] around your code. This forum also adds spaces when "words" get too long outside of any preformatted tags, which is probably what created the space in sql server.

Using if instead of while should have been fine, since your query is only going to return one row. while is used when you don't know how many rows will be returned.

Could it be that count() doesn't return a string value type?

Does this work?
Code:
num_records = pagecount.getObject(1);
If not, maybe try using getInt or getLong or using the column name instead of number?

Code:
num_records = pagecount.getInt('total');
Reply With Quote
  #10  
Old 05-02-2018, 08:31 PM
mroberts mroberts is offline
OBX.1 Kenobi
 
Join Date: Jul 2010
Posts: 40
mroberts is on a distinguished road
Default Still returning 0

Below returns 0.0 and the other way returns just 0
select count(*) as total FROM page WHERE document_id = '200845D4-159A-48F0-BE46-A94E197894EE' returns 3 as it should

is there anything different because this is in a transformer

var doc_id = $('document_id');
var sql = "select count(*) as total FROM page WHERE document_id = '" + doc_id + "'";
logger.info(sql);
//execute sql to get total page count
var pagecount = dbConn.executeCachedQuery(sql);

if (pagecount.next()) {
num_records = pagecount.getInt('total');
channelMap.put('totalpages',num_records);
}
//close sql channel
dbConn.close();
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


All times are GMT -8. The time now is 12:16 PM.


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