web stats
Javascript/SQL Query Question - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 10-19-2015, 12:00 PM
atibbits atibbits is offline
Mirth Guru
 
Join Date: May 2012
Location: Salina, KS
Posts: 381
atibbits is on a distinguished road
Angry Javascript/SQL Query Question

I am connecting to a database and executing a SQL query using JavaScript. For some reason I am struggling to write a SELECT FROM WHERE AND statement. I need to say:

SELECT Response
FROM AdmVisitClinicalQueries
WHERE VisitID=visitID
AND QueryID ="MORS.FRD"

Here is my code:


Code:
//Save the account number

var pid18=msg['PID']['PID.18']['PID.18.1'].toString();

//FInd out the visit ID
var qt ='\'';

var dbUri = "jdbc:jtds:sqlserver://                 /"
var sqldb_name = "";
var sqldb_user = "";
var sqldb_password = "";

var resultQuery = 'SELECT VisitID FROM dbo.AdmVisits WHERE AccountNumber = ' + qt + pid18 + qt;

var dbConn = DatabaseConnectionFactory.createDatabaseConnection ("net.sourceforge.jtds.jdbc.Driver", dbUri + sqldb_name, sqldb_user, sqldb_password);
var result = dbConn.executeCachedQuery(resultQuery);

dbConn.close();

if (result.next())
{
var visitID=result.getString(1);
}

//Use the visit ID to get the patient's fall risk

var qt ='\'';

var dbUri = "jdbc:jtds:sqlserver://                /"
var sqldb_name = "";
var sqldb_user = "";
var sqldb_password = "";

var resultQuery='SELECT Response FROM dbo.AdmVisitClinicalQueries WHERE VisitID = ' + qt + visitID + qt 'AND QueryID="MORS.FRD"';


var dbConn = DatabaseConnectionFactory.createDatabaseConnection ("net.sourceforge.jtds.jdbc.Driver", dbUri + sqldb_name, sqldb_user, sqldb_password);
var result = dbConn.executeCachedQuery(resultQuery);

dbConn.close();

if (result.next())
{
var response=result.getString(1);
}
I have removed my connection information and I highlighted the code I am struggling with in purple! Any suggestions??? I am sure this is probably something simple that I am overthinking............


Thanks for your help!!
Reply With Quote
  #2  
Old 10-19-2015, 11:32 PM
krmurrell krmurrell is offline
OBX.2 Kenobi
 
Join Date: Mar 2013
Location: London, UK
Posts: 61
krmurrell is on a distinguished road
Default

Hi

I would have written the offending line as:

var resultQuery="SELECT Response FROM dbo.AdmVisitClinicalQueries WHERE VisitID = '" + visitID + "' AND QueryID='MORS.FRD'";

Basically swapping ' for " and " for '

Kevin
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 03:07 AM.


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