web stats
executeCachedQuery Return set - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 12-04-2012, 07:25 AM
R A R A is offline
Mirth Guru
 
Join Date: Jan 2012
Location: London, UK
Posts: 181
R A is on a distinguished road
Default executeCachedQuery Return set

I have a stored procedure that I am passing values to via my channel:

var stored_proc = "EXEC stp_AddUpdatePatientfromHL7 '"+$('HOSP_NO')+"', '"+$('MS_SURNAME')+"' , '"+$('MS_FORNAME')+"', '"+$('MS_MIDDLE')+"', '"+$('MS_PREFIX')+"' , '"+$('MS_SEX')+"' ";
var result = dbConn.executeCachedQuery(stored_proc);
result.next()
// do Error Processing Here if you like
dbConn.close();

Now I have tried executeupdate as opposed to executeCachedQuery and this doesnt make a difference, from an older thread advice was to add 'select -1' to the end of my stored procedure and this indeed did satisfy the return and Mirth reported no errors.

No I was wondering if I could add? Doing some searching did find;

var returnValue = result.getInt(1); after result.next() and will this allow me to avoid the select-1 in my stored procedure?

Would like to avoid changes to some stored procedure as I am not the owner or writer of them, hence, avoiding adding the select -1 to the end of the store procedure would be preffered?

Any help appreciated.

Many Thanks
Reply With Quote
  #2  
Old 12-04-2012, 08:06 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,126
narupley is on a distinguished road
Default

Quote:
Originally Posted by R A View Post
I have a stored procedure that I am passing values to via my channel:

var stored_proc = "EXEC stp_AddUpdatePatientfromHL7 '"+$('HOSP_NO')+"', '"+$('MS_SURNAME')+"' , '"+$('MS_FORNAME')+"', '"+$('MS_MIDDLE')+"', '"+$('MS_PREFIX')+"' , '"+$('MS_SEX')+"' ";
var result = dbConn.executeCachedQuery(stored_proc);
result.next()
// do Error Processing Here if you like
dbConn.close();

Now I have tried executeupdate as opposed to executeCachedQuery and this doesnt make a difference, from an older thread advice was to add 'select -1' to the end of my stored procedure and this indeed did satisfy the return and Mirth reported no errors.

No I was wondering if I could add? Doing some searching did find;

var returnValue = result.getInt(1); after result.next() and will this allow me to avoid the select-1 in my stored procedure?

Would like to avoid changes to some stored procedure as I am not the owner or writer of them, hence, avoiding adding the select -1 to the end of the store procedure would be preffered?

Any help appreciated.

Many Thanks
I don't think you should need a "select -1" in there to make it work. However, it doesn't look like you're doing anything with that result set, so that may the root of your problems. After you call result.next(), you need to then extract data from it using one of the getters. See here: http://docs.oracle.com/javase/6/docs...ResultSet.html
__________________
Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

Nicholas Rupley
Work: 949-237-6069
Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


- How do I foo?
- You just bar.
Reply With Quote
  #3  
Old 12-05-2012, 01:30 AM
R A R A is offline
Mirth Guru
 
Join Date: Jan 2012
Location: London, UK
Posts: 181
R A is on a distinguished road
Default

Quote:
Originally Posted by narupley View Post
I don't think you should need a "select -1" in there to make it work. However, it doesn't look like you're doing anything with that result set, so that may the root of your problems. After you call result.next(), you need to then extract data from it using one of the getters. See here: http://docs.oracle.com/javase/6/docs...ResultSet.html
Many Thanks, well I am new to JDBC and Mirth. All I wanted to do is execute my values into the stored procedure and jobs a goodun.

However from reading on here, the execute cached query 'Requires' a response hence the 'select -1' in my stored procedure. I was originally looking if it would be possible to use a execute a method that didnt require a response nut my search wasnt a successful one.

I wouldnt mind a print response of success returned to Mirth meaning that the select -1 is more meaningful.

What could I do with the resultset?

Any suggestions appreciated?
Reply With Quote
  #4  
Old 12-05-2012, 07:45 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,126
narupley is on a distinguished road
Default

Quote:
Originally Posted by R A View Post
Many Thanks, well I am new to JDBC and Mirth. All I wanted to do is execute my values into the stored procedure and jobs a goodun.

However from reading on here, the execute cached query 'Requires' a response hence the 'select -1' in my stored procedure. I was originally looking if it would be possible to use a execute a method that didnt require a response nut my search wasnt a successful one.

I wouldnt mind a print response of success returned to Mirth meaning that the select -1 is more meaningful.

What could I do with the resultset?

Any suggestions appreciated?
Ah so your stored procedure doesn't actually return anything, so there wouldn't really be any need for a result set. In that case, use executeUpdate rather than executeCachedQuery.
__________________
Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

Nicholas Rupley
Work: 949-237-6069
Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


- How do I foo?
- You just bar.
Reply With Quote
  #5  
Old 12-05-2012, 08:00 AM
R A R A is offline
Mirth Guru
 
Join Date: Jan 2012
Location: London, UK
Posts: 181
R A is on a distinguished road
Default

I did try executeupdate but certain I had the same issue, maybe I can eliminate the resultset line altogehter? i.e below?

var stored_proc = "EXEC stp_AddUpdatePatientfromHL7 '"+$('@HOSP_No_varchar')+"','"+$('@EXT_No_varchar' )+"','"+$('@NHS_No_varchar')+"','"+$('@SURNAME_var char')+"','"+$('@FIRSTNAME_varchar')+"','"+$('@MID DLENAME_varchar')+"','"+$('@TITLE_varchar')+"','"+ $('@DOB_varchar')+"','"+$('@GENDER_varchar')+"','" +$('@ADDRESS1_varchar')+"' ";
var result = dbConn.executeUpdate(stored_proc);

dbConn.close();

Would that suffice, no harm in trying I guess
Reply With Quote
  #6  
Old 12-05-2012, 08:04 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,126
narupley is on a distinguished road
Default

Quote:
Originally Posted by R A View Post
I did try executeupdate but certain I had the same issue, maybe I can eliminate the resultset line altogehter? i.e below?

var stored_proc = "EXEC stp_AddUpdatePatientfromHL7 '"+$('@HOSP_No_varchar')+"','"+$('@EXT_No_varchar' )+"','"+$('@NHS_No_varchar')+"','"+$('@SURNAME_var char')+"','"+$('@FIRSTNAME_varchar')+"','"+$('@MID DLENAME_varchar')+"','"+$('@TITLE_varchar')+"','"+ $('@DOB_varchar')+"','"+$('@GENDER_varchar')+"','" +$('@ADDRESS1_varchar')+"' ";
var result = dbConn.executeUpdate(stored_proc);

dbConn.close();

Would that suffice, no harm in trying I guess
Reading this and your original post, I'm struggling to understand what the issue is. Is an error being thrown or something?
__________________
Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

Nicholas Rupley
Work: 949-237-6069
Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


- How do I foo?
- You just bar.
Reply With Quote
  #7  
Old 12-06-2012, 12:53 AM
R A R A is offline
Mirth Guru
 
Join Date: Jan 2012
Location: London, UK
Posts: 181
R A is on a distinguished road
Default

Thats right, I was getting the same error with executeupdate as I would with executecachequery, hence just sticking with executecachequery. The error being that I didnt have a returnset in my stored proc. I then added the select -1 and problem solved.

I was just wondering if I eliminate my result.next() and use executeupdate instead if that would negate my need for the select -1 in my stored proc.

Forgive me if it doesnt make sense, all very new to me.
Reply With Quote
  #8  
Old 12-06-2012, 02:31 AM
R A R A is offline
Mirth Guru
 
Join Date: Jan 2012
Location: London, UK
Posts: 181
R A is on a distinguished road
Default

Good news I commented out the "result.next()" and changed the

var result = dbConn.executeCachedQuery(stored_proc);

to

var result = dbConn.executeUpdate(stored_proc);

whilst also removing the select -1 from my stored procedure and all looks well seems to be executing without errors so i think my issue was that I had result.next() with both previously hence the result being required. All looks well now.
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:13 PM.


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