| Author |
Message
|
| max power |
 Posted: Tue Feb 28, 2012 3:29 am Post subject: Database Error |
 |
|
Apprentice
Joined: 06 Sep 2007
Posts: 31
|
I am trying to call a function in Oracle, using the PASSTHRU statement.
The command I'm using is as follows:
SET Environment.DBResults[] =PASSTHRU('SELECT {schema}.{package}.{function}(?, ?, ?) FROM dual'
VALUES ('A', 'B', 'C'));
I can run the above command successfully from SQLPlus and if I run the command "PASSTHRU(select sysdate from dual)" through the Broker, this is also successful.
Any ideas on what could be the issue..?
This fails with the following error:
2012-02-28 11:02:43.954242 7924 DatabaseException BIP2321E: Database error: ODBC return code '-1' using ODBC driver manager ''odbc32.dll''.
The message broker encountered an error when processing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database concerning this error.
Use the following messages to determine the cause of the error. Typical problems are an incorrect datasource or table names. Correct either the database or message broker configuration.
2012-02-28 11:02:43.954254 7924 DatabaseException BIP2322E: Database error: SQL State ''HYC00''; Native Error Code '0'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver]Optional feature not implemented.''.
The error has the following diagnostic information: SQL State ''HYC00'' SQL Native Error Code '0' SQL Error Text ''[IBM][ODBC Oracle Wire Protocol driver]Optional feature not implemented.''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error. |
|
| Back to top |
|
 |
| vishnurajnr |
| Posted: Tue Feb 28, 2012 6:12 am Post subject: |
|
|
Centurion
Joined: 08 Aug 2011
Posts: 134
Location: Trivandrum
|
|
| Back to top |
|
|
| max power |
| Posted: Tue Feb 28, 2012 6:25 am Post subject: |
|
|
Apprentice
Joined: 06 Sep 2007
Posts: 31
|
This hasn't made any difference and the same error persists. 
This is a Windows v7 Broker for reference. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Feb 28, 2012 7:00 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| max power |
| Posted: Tue Feb 28, 2012 7:19 am Post subject: |
|
|
Apprentice
Joined: 06 Sep 2007
Posts: 31
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Feb 28, 2012 7:26 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| max power wrote: |
This hasn't made any difference and the same error persists.
This is a Windows v7 Broker for reference. |
Did you use the MQSeries DataDirect ODBC driver for Oracle?
Or did you use an Oracle driver?
Do you experience the same issues using mqsicvp to verify the DSN as you do in the runtime? |
|
| Back to top |
|
|
| max power |
| Posted: Tue Feb 28, 2012 7:40 am Post subject: |
|
|
Apprentice
Joined: 06 Sep 2007
Posts: 31
|
| mqjeff wrote: |
| max power wrote: |
This hasn't made any difference and the same error persists.
This is a Windows v7 Broker for reference. |
Did you use the MQSeries DataDirect ODBC driver for Oracle?
Or did you use an Oracle driver?
Do you experience the same issues using mqsicvp to verify the DSN as you do in the runtime? |
I am using the Websphere Message Broker DataDirect Technologies 6.0 32-BIT Oracle Wire Protocol driver.
The mqsicvp returns data without any errors.
When trying to run a function like PASSTHRU('SELECT sysdate from dual'), the correct data is returned.
This only seems to occur when calling a function that requires some input parameters. Maybe I can't use the PASSTHRU command for this..? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Feb 28, 2012 7:41 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| max power wrote: |
| v7.0.0.2 |
What is the "effective level" of your runtime? Use mqsireportbroker to find out.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| max power |
| Posted: Tue Feb 28, 2012 7:43 am Post subject: |
|
|
Apprentice
Joined: 06 Sep 2007
Posts: 31
|
| lancelotlinc wrote: |
| max power wrote: |
| v7.0.0.2 |
What is the "effective level" of your runtime? Use mqsireportbroker to find out. |
That's also v7.0.0.2 |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Feb 28, 2012 7:47 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| max power wrote: |
The mqsicvp returns data without any errors.
When trying to run a function like PASSTHRU('SELECT sysdate from dual'), the correct data is returned.
This only seems to occur when calling a function that requires some input parameters. Maybe I can't use the PASSTHRU command for this..? |
Okay.
So it's not a DSN configuration issue.
if I recall, there are two forms of PASSTHRU, one that is a function and one that is a statement. There's a note on the doc on the PASSTHRU *statement* that says don't use it for calling procedures, because it imposes restrictions, including not letting you use output parameters.
So I would convert your PASSTHRU to a CALL, and declare the procedure you're trying to call.
Also remember that every part of the inputs to PASSTHRU are indeed passed through, and not interpreted by Broker. So you might simply be running into issues with "{schema}.{package}.{function}" being forwarded as is to the database and not replaced by Broker up front. You could use || to concatenate the interpreted values instead. |
|
| Back to top |
|
|
| max power |
| Posted: Tue Feb 28, 2012 8:31 am Post subject: |
|
|
Apprentice
Joined: 06 Sep 2007
Posts: 31
|
Thanks mqjeff, with a few trials and tribulations with the CALL, PASSTHRU and CREATE PROCEDURE commands, I now have the required solution.
Thie following line of code works a treat....
Set Environment.Variables.Result = PASSTHRU('{ call {schema}.{procedure}.{function}(?,?,?) }', 'A', 'B', 'C' );
Cheers. |
|
| Back to top |
|
|
|
|
