Thread: [Squirrel-sql-users] execute DB2 stored procedure from Squirrel
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: David S. <dav...@ho...> - 2007-02-21 01:23:57
|
Can you execute a zOS (mainframe) DB2 stored procedure from Squirrel? This is handy for testing the stored proc. I haven't been able to make it work. For example: call SCHDB1.J5143106(10,1,@x1,@x2); does not work. I don't think you can do this interactively with DB2 like you can with MySQL. I may not have the latest DB2 JDBC driver but I'm not sure this is the problem. If there's no way to do it is there a plugin available? I looked and couldn't find one. If there is no plugin available can any plugin experts tell me if it's feasible to write one. I've briefly looked over the example and MySQL plugin code and would be willing to take a crack at it. Thanks. Dave Schmitt dav...@ho... _________________________________________________________________ Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month. Intro*Terms http://www.NexTag.com |
From: Robert M. <rob...@gm...> - 2007-02-21 10:17:29
|
On 2/20/07, David Schmitt <dav...@ho...> wrote: > Can you execute a zOS (mainframe) DB2 stored procedure from Squirrel? This > is handy for testing the stored proc. I haven't been able to make it work. > For example: > > call SCHDB1.J5143106(10,1,@x1,@x2); does not work. > > I don't think you can do this interactively with DB2 like you can with > MySQL. I may not have the latest DB2 JDBC driver but I'm not sure this is > the problem. If there's no way to do it is there a plugin available? I > looked and couldn't find one. If there is no plugin available can any plugin > experts tell me if it's feasible to write one. I've briefly looked over the > example and MySQL plugin code and would be willing to take a crack at it. > Hi Dave, There is a DB2 plugin available in the latest snapshot release (It's new and will be available in 2.5). What is the error message you get when you execute that call as a statement? Rob |
From: Robert M. <rob...@gm...> - 2007-02-22 11:49:10
|
On 2/21/07, David Schmitt <dav...@ho...> wrote: > This is the code I'm trying to execute within Squirrel: call > DBSCH1.J5143106(10983,0,@x,@y); > > where @x, and @y are CHAR OUT parms. The two IN parms are INTEGER. The proc > also returns a result set. > > This is the error: > > Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0440N No > function by the name "J5143106" having compatible arguments was found in the > function path. SQLSTATE=42884 > , SQL State: 42884, Error Code: -440 I looked at some examples on IBM's website of calling stored procedures from various apps (CLI, Java, C). It appears that "db2" CLI parses the "call ..." and handles it different than "select * from .." or some other such query. I think we may need to detect this "call..." and handle it differently (instead of using Statement, using CallableStatement). The new pluggable QueryTokenizer could be used for part of this task, but I'll need to see how we are handling CallableStatements (I don't know that we are currently so that might be new functionality as well). If you come across any additional information (using Statement to execute stored procs for instance) please send it my way. Rob |
From: Robert M. <rob...@gm...> - 2007-02-24 01:49:58
|
On 2/23/07, David Schmitt <dav...@ho...> wrote: > I'll keep an eye out for the QueryTokenizer plugin. As far as I know the Just to be clear - QueryTokenizer *was* a utility class that was used to break up the selected region of the editor into individual statements to send to the database. Now it is more of a framework to allow plugins to implement their own query tokenizing behavior. (One example is statement separator which is different depending on what database you are using - "GO" for Sybase/MS-SQLServer, ";" for others). This is available now in CVS; the first release to include it will be 2.5 slated for late March. I was commenting that the new DB2 plugin might be able to take advantage of it by providing support for "CALL ...". The Oracle plugin uses this framework to parse "create procedure/function" statements by allowing any embedded ";" and terminating the statement on "/". The main issue with executing stored procs this way is that we would need to use CallableStatement instead of merely executing everything with Statement. I'll have to look at SQLExecuterTask to see how do-able this would be. It would be nice for all of the plugins to support stored procedure execution, but that's not likely to make it into version 2.5. Rob |