web stats
Using the SQL UPDATE Statement Question - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 04-13-2016, 06:42 AM
atibbits atibbits is offline
Mirth Guru
 
Join Date: May 2012
Location: Salina, KS
Posts: 381
atibbits is on a distinguished road
Thumbs down Using the SQL UPDATE Statement Question

I am trying to update a value in a postgres database table, but I am having a heck of a time writing the UPDATE statement. I am able to update every value in the table, but not just a specific one. I know I need a WHERE clause, but I cannot figure out how to get my javascript to work. Here is what I currently have:

Code:
if (result.next())
{
msg['PID']['PID.2']['PID.2.1']=result.getString(1);
var order_num=msg['ORC']['ORC.2']['ORC.2.1'].toString();

var deleteFlag="Y";
var qt ='\'';

var dbUri = 'jdbc:postgresql://myIP:5432/';
var mirthdb_name = 'Comcare_xlate'
var mirthdb_user = 'user';
var mirthdb_password = 'password';

var updateQuery = 'UPDATE public.greenway_id_table SET delete_flag = ' + qt + deleteFlag + qt;

var dbConn = DatabaseConnectionFactory.createDatabaseConnection ("org.postgresql.Driver", dbUri + mirthdb_name, mirthdb_user, mirthdb_password);

dbConn.executeUpdate(updateQuery);

dbConn.close();
}
I want to add a WHERE clause that looks for a particular match between my order_num variable and the order_num column in my table, but I cannot figure out how to get that to work. I have tried this:


Code:
if (result.next())
{
msg['PID']['PID.2']['PID.2.1']=result.getString(1);
var order_num=msg['ORC']['ORC.2']['ORC.2.1'].toString();

var deleteFlag="Y";
var qt ='\'';

var dbUri = 'jdbc:postgresql://myIP:5432/';
var mirthdb_name = 'Comcare_xlate'
var mirthdb_user = 'user';
var mirthdb_password = 'password';

var updateQuery = 'UPDATE public.greenway_id_table SET delete_flag = ' + qt + deleteFlag + qt ' WHERE order_num=' + qt + order_num + qt ;

var dbConn = DatabaseConnectionFactory.createDatabaseConnection ("org.postgresql.Driver", dbUri + mirthdb_name, mirthdb_user, mirthdb_password);

dbConn.executeUpdate(updateQuery);

dbConn.close();
I know it's my quotes because I always struggle with that. Can anyone help me?

Thanks!!!!!
Reply With Quote
  #2  
Old 04-13-2016, 11:26 AM
BCMirthUser BCMirthUser is offline
OBX.2 Kenobi
 
Join Date: May 2015
Posts: 93
BCMirthUser is on a distinguished road
Default

try:

Code:
if (result.next())
{
msg['PID']['PID.2']['PID.2.1']=result.getString(1);
var order_num=msg['ORC']['ORC.2']['ORC.2.1'].toString();

var deleteFlag="Y";

var dbUri = 'jdbc:postgresql://myIP:5432/';
var mirthdb_name = 'Comcare_xlate'
var mirthdb_user = 'user';
var mirthdb_password = 'password';

var updateQuery = "UPDATE public.greenway_id_table SET delete_flag = '" +  deleteFlag + "' WHERE order_num='" + order_num + "'" ;

var dbConn = DatabaseConnectionFactory.createDatabaseConnection ("org.postgresql.Driver", dbUri + mirthdb_name, mirthdb_user, mirthdb_password);

dbConn.executeUpdate(updateQuery);

dbConn.close();
Reply With Quote
  #3  
Old 04-14-2016, 04:49 AM
spycom spycom is offline
OBX.2 Kenobi
 
Join Date: Jun 2014
Posts: 71
spycom is on a distinguished road
Default

Use parameters:
Code:
if (result.next())
{
msg['PID']['PID.2']['PID.2.1']=result.getString(1);
var order_num=msg['ORC']['ORC.2']['ORC.2.1'].toString();

var deleteFlag="Y";

var dbUri = 'jdbc:postgresql://myIP:5432/';
var mirthdb_name = 'Comcare_xlate'
var mirthdb_user = 'user';
var mirthdb_password = 'password';

//parameters
var params = Packages.java.util.ArrayList();
params.add(deleteFlag);
params.add(order_num);

// you'll need to cast your parameters though
// cast to the column type
var updateQuery = "UPDATE public.greenway_id_table SET delete_flag = ?::text WHERE order_num=?::text";

try {
   var dbConn = DatabaseConnectionFactory.createDatabaseConnection ("org.postgresql.Driver", dbUri + mirthdb_name, mirthdb_user, mirthdb_password);
dbConn.executeUpdate(updateQuery, params);
} catch (ex) {
   throw ex.message;
} finally {
   if(dbConn)
     dbConn.close();
}
}
Reply With Quote
  #4  
Old 04-19-2016, 12:07 PM
atibbits atibbits is offline
Mirth Guru
 
Join Date: May 2012
Location: Salina, KS
Posts: 381
atibbits is on a distinguished road
Default

This worked great!!!! Thank you!!
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 02:49 AM.


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