Hello, can I ask You to add DB2 plugin functionality "Statement Separator" and "Procedure/Function Separator" just like Oracle plugin already has? I guess, it is necessary for writing (running) definition of stored procedures and functions, which has multiple statements inside them. Thank You. Stepan
Test case:
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
ends with following error:
Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=END-OF-STATEMENT;sqlCmd varchar(139);<psm_semicolon>, 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 \
One more information, I am using "com.ibm.db2.jcc.DB2Driver", not "com.ibm.db2.jcc.uw.DB2StoredProcDriver" or "COM.ibm.db2os390.sqlj.jdbc.DB2SQLJDriver" defined in my "db2jcc4.jar" on MS Windows 64bit and Oracle Java 1.7:
java version "1.7.0_17"
Java(TM) SE Runtime Environment (build 1.7.0_17-b02)
Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)
Below is copy from SQuirreL -> menu -> Help -> About -> System. I am not sure, if there should be listed all plugin jars from "squirrel-sql-3.4.0\plugins", should not be?
Although 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
FOUND EASY APPLICABLE WAY TO DO IT!
So this ticket could be closed as solved or so.
But could be nice to have it as static property of DB2 plugin, but with much more less priority comparing to above described problem.
WORKAROUND:
Use SQuirreL -> menu -> Session -> Session properties -> SQL -> Statement Separator to set up character (string?), which become ONLY PER SESSION (so set it AFTER connect to database) statement separator. So then above definition of procedure looks like:
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 ~ --set up tilda as per session Statement SeparatorMany thanks to Erwan Duroselle for this help.
Stepan
Dupe of https://sourceforge.net/p/squirrel-sql/feature-requests/433/ ?