Javawriter DB query into local variables

[appsbyaaron]

I have a Javawriter connector that is running an Oracle query to pull back 2 fields from a table. How do I put those two values into a variable (channel map)?

I've tried the following:

var var_batch_count;
var var_error_text;
var local_batch_id = channelMap.get('temp_batch_id');

//Call CHIRP HL7 Function Part 1
var temp_SQL = 'SELECT COUNT (se.batch_id) as var_batch_count, se.ERROR_TEXT as var_error_text FROM h33_hl7_send_error se WHERE se.batch_id = ' + local_batch_id + ' AND TRUNC (se.received) = TRUNC (SYSDATE - 1) AND se.message_type_received = ' + 'ACK' + ' AND se.is_error = ' + 'Y' + 'GROUP BY se.batch_id, se.ERROR_TEXT';
var temppw = 'secret password';
var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('connection string data');

var result = dbConn.executeCachedQuery(temp_SQL);

var_batch_count = dbConn.executeCachedQuery(temp_SQL.var_batch_count);
var_error_text = dbConn.executeCachedQuery(temp_SQL.var_error_text);

channelMap.put('temp_total_count',var_batch_count) ;
channelMap.put('temp_error_count',var_error_text);

[narupley]

Originally Posted by appsbyaaron

var_batch_count = dbConn.executeCachedQuery(temp_SQL.var_batch_count);
var_error_text = dbConn.executeCachedQuery(temp_SQL.var_error_text);

What is that code trying to accomplish? The executeCachedQuery method takes in a string (temp_SQL.var_batch_count will probably return undefined) and returns a CachedRowSet. From there you need to iterate through it just as you would a ResultSet, using next() and the various getter methods.

[appsbyaaron]

thanks Nick. I think the real issue here is the password to the server. it contains a '(' and I've had issues trying to get a valid connection to the database using this method. When I try to simply connect and end the connection it still fails the channel.

I even tried to split the select into 2 separate ones but it still won't work.

thanks anyway.

[narupley]

Originally Posted by appsbyaaron

thanks Nick. I think the real issue here is the password to the server. it contains a '(' and I've had issues trying to get a valid connection to the database using this method. When I try to simply connect and end the connection it still fails the channel.

I even tried to split the select into 2 separate ones but it still won't work.

thanks anyway.

Whether or not the password is correct, the following code is incorrect:

var_batch_count = dbConn.executeCachedQuery(temp_SQL.var_batch_count);
var_error_text = dbConn.executeCachedQuery(temp_SQL.var_error_text);

Can you post the full error you're seeing here?

[appsbyaaron]

Originally Posted by narupley

The executeCachedQuery method takes in a string (temp_SQL.var_batch_count will probably return undefined) and returns a CachedRowSet. From there you need to iterate through it just as you would a ResultSet, using next() and the various getter methods.

I suck at life. I'm getting the CachedRowSet but am having issues with the iteration. Do you have an example?

[appsbyaaron]

Nevermind! I GOT IT!! Thank you all!!