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

[Erwan D. <erw...@gm...>]

Hi Stepan,
You may try to set the command separator to something else than 
semicolumn. I usually set it to ~ to setup stored procedures on DB2/400

You must connect first, and then set it in menu Session/ Session 
properties / SQL tab / Command Separator

Then try to execute:

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
~

Regards,
Erwan



Le 11/03/2013 17:26, Stepan RYBAR a écrit :
>> 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
> ------------------------------------------------------------------------------
> 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