Re: [Squirrel-sql-users] DB2: How to set up SQuirreL to be able to create (and run) multiline and multicommand stored procedure?

[Stepan R. <xr...@se...>]

> I wonder if this is similar to a question about oracle procedures not working: 

...I guess, it is not similar to that question, because I already checked list of plugins in SQuirreL -> menu -> Plugins -> Summary and I see there: 

true	db2	DB2 Plugin	true	0.04	Rob Manning	Christoph Schmitz, Tilmann Brenk, Lars Heller

> You need the DB2 plugin for SQuirreL. Go to Help -> Software Update -> Check(button) -> Ok(button) 

...I am sorry, I can not connect to Internet from SQuirreL. But, I guess, I have DB2 Plugin in SQuirreL as I wrote above. Or is there any other test to verify, that I have DB2 Plugin and it is functional? Also I do not see any "db2" string in SQuirreL -> menu -> Help -> About -> Threads, so i guess, DB2 plugin should be loaded OK. 

Stepan 

---------- Původní zpráva ----------
Od: Drav Sloan 
Datum: 11. 3. 2013
Předmět: Re: [Squirrel-sql-users] DB2: How to set up SQuirreL to be able to create (and run) multiline and multicommand stored procedure?

Stepan RYBAR wrote:
> Hello,
>
> is anybody successful with running SQL code to create stored procedure on DB2
> (let say 9.1 running on AIX, but I guess, it is not important) and the stored
> procedure is multiline and multicommand? Am I missing some SQuirreL setting or
>  configuration SQL command or JDBC driver property (like @ terminator)? I read
>  many articles about thin on Internet but no recommendation lead to success. I
>  am using "com.ibm.db2.jcc.DB2Driver" JDBC driver in SQuirreL in Win7 64bit
> (Java 64bit) and DB2 9.1 on AIX on server.
>
> Thank You in advance. Stepan

I wonder if this is similar to a question about oracle procedures not working:

You need the DB2 plugin for SQuirreL. Go to Help -> Software Update ->
Check(button) -> Ok(button)

Then find the artifact called db2.jar and click on the action
value of "NONE" and change it to install.

After its downloaded you will need to restart and agree to install the
update.

Regards

D.

>
> Because
>
> create procedure dropTestIfExists(in myIndex varchar(128)) language SQL
> begin
>   declare sqlCmd varchar(139);
>   if exists(select 1 from syscat.indexes where indschema = current_schema and
> indname = myIndex) then
>     set sqlCmd = 'drop index ' || myIndex;
>     execute immediate sqlCmd;
>   end if;
> end
>
> end with:
>
> Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601,
> SQLERRMC=END-OF-STATEMENT;sqlCmd varchar(139);, DRIVER=4.7.85
> SQLState:  42601
> ErrorCode: -104
> Error occured in:
> create procedure dropTestIfExists(in myIndex varchar(128)) language SQL
> begin
>   declare sqlCmd varchar(139)
>
> Using DB2 CLP on AIX is OK with following command on my environment:
>
> create procedure dropIndexIfExists(in myIndex varchar(128)) language SQL \
> begin \
>   declare sqlCmd varchar(139); \
>   if exists(select 1 from syscat.indexes where indschema = current_schema and
> indname = myIndex) then \
>     set sqlCmd = 'drop index ' || myIndex; \
>     execute immediate sqlCmd; \
>   end if; \
> end \
>
> ------------------------------------------------------------------------------
> Symantec Endpoint Protection 12 positioned as A LEADER in The Forrester
> Wave(TM): Endpoint Security, Q1 2013 and "remains a good choice" in the
> endpoint security space. For insight on selecting the right partner to
> tackle endpoint security challenges, access the full report.
> http://p.sf.net/sfu/symantec-dev2dev
> _______________________________________________
> Squirrel-sql-users mailing list
> Squ...@li...
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users