| Author |
Message
|
| Lex |
 Posted: Fri Feb 09, 2007 1:34 am Post subject: WMB 6.0: problem with Sybase Stored Procedure |
 |
|
Acolyte
Joined: 14 Nov 2006
Posts: 73
|
Heya,
I have problems with the call of a Sybase stored procedure. In this stored procedure, there is the creation and deletion of a temporary table. This stored procedure works properly when I call it directly in Sybase, but when i test the message flow calling this procedure, i got an error telling me the temporary table can't be found...
Is there any special settings for this kind of procedure to work? |
|
| Back to top |
|
 |
| Lex |
| Posted: Fri Feb 09, 2007 2:41 am Post subject: |
|
|
Acolyte
Joined: 14 Nov 2006
Posts: 73
|
I must add that this stored procedure worked with WMQI 2.1 and MQSeries 5.3.
The original developper of the message flow had created a specific module in order for the sybase stored procedure to be able being called by the version 2.1 message flows, which couldn't originally call a sybase stored procedure in its compute node.
With the new version (6.0), I already made some test after the migration of my flows, and some of them calling sybase stored procedure too are working fine, but it seems that when a stored procedure create and drops table (temporary tables), it's not working fine in this 6.0 version.
Can it be linked with a bad .odbc.ini configuration? Plz help me  |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Feb 09, 2007 2:53 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
|
| Back to top |
|
|
| Lex |
| Posted: Fri Feb 09, 2007 3:01 am Post subject: |
|
|
Acolyte
Joined: 14 Nov 2006
Posts: 73
|
Well our database are on AIX and it's Sybase 12.5 so it's ok.
About the user's privilege, since the WMQI 2.1/Mqseries5.3 version of our broker can call this procedure without problem (and it has the effect wanted in the database), i doubt the problem comes from here.
But since it worked without using ODBC.ini on this old version with a custom node which could call sybase stored procedure, i think it can be linked with the .odbc.ini file.
I hope to find someone who has encountered a similar issue with sybase stored procedure! |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Feb 09, 2007 4:14 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
If the custom node was written in Java and used JDBC, you can migrate that very easily into a JavaCompute node.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Lex |
| Posted: Fri Feb 09, 2007 4:49 am Post subject: |
|
|
Acolyte
Joined: 14 Nov 2006
Posts: 73
|
| Indeed, the code of the custom node was written in java. There is a command in order to migrate in a JavaCompute Node? |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Feb 09, 2007 5:01 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
No, there isn't a command.
But it should be a straight forward programming effort - they both use the same APIs to access and populate the message trees.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Lex |
| Posted: Fri Feb 09, 2007 6:04 am Post subject: |
|
|
Acolyte
Joined: 14 Nov 2006
Posts: 73
|
| I would have prefer to keep my Compute Node, just in order to keep linked the odbc definition with the call of the stored procedure, as it is in a compute node. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Feb 09, 2007 6:18 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
It might work.
I can't find anything that says that the Sybase drivers do not support calling procedures that create temporary tables. I can't, really, think of a reason why the ODBC driver would make a difference here.
If you want to keep your compute node, then open a PMR.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Lex |
| Posted: Fri Feb 09, 2007 6:24 am Post subject: |
|
|
Acolyte
Joined: 14 Nov 2006
Posts: 73
|
Well i learn that the table concerned in the procedure is a session table, i mean it's named #Table_Name, and my teammate told me it was a table lasting only the duration of a session.
Maybe it's changing the nature of the problem? |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Feb 09, 2007 6:28 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Maybe, but maybe not.
Open a PMR.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Lex |
| Posted: Thu Feb 15, 2007 3:05 am Post subject: |
|
|
Acolyte
Joined: 14 Nov 2006
Posts: 73
|
Some new question about the calling of a stored procedure within a compute node: What is the difference between the usage of PASSTHRU statement and the usage of the CALL statement used with the CREATE PROCEDURE statement?
What is the prefered solution generally? |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Feb 15, 2007 3:18 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Everything else being equal, passthru ties your code to a specific database.
Everything else not being equal, if passthru is the only thing that works - use it.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Lex |
| Posted: Thu Feb 15, 2007 4:43 am Post subject: |
|
|
Acolyte
Joined: 14 Nov 2006
Posts: 73
|
| Sorry Jeff what you mean by everything else being or not being equal? |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Feb 15, 2007 5:06 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Sorry, it's an expression.
Um.
PASSTHRU allows you to use database specific commands, and in fact requires you to use database specific commands. So if you're testing your code against Derby and your produciton enviornment uses Oracle, then you have to change your code to deploy to production, or write your code to be smart enough to know which statement to run.
This might be a reason to avoid using Passthru.
BUT, if you can't do what you need to do without using PASSTHRU, then use it!
There are also differences in what a Procedure can return and what PASSTHRU can return.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
