#467 DB2: Add "Statement Separator" and "Procedure/Function Separator"

General
open
nobody
DB2 Plugin (12)
5
2014-09-12
2013-03-11
Stepan RYBAR
No

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 \

Discussion

  • Stepan RYBAR
    Stepan RYBAR
    2013-03-11

    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

    Key Value
    java.runtime.name   Java(TM) SE Runtime Environment
    sun.boot.library.path   C:\Program Files\Java\jre7\bin
    java.vm.version 23.7-b01
    user.country.format CZ
    java.vm.vendor  Oracle Corporation
    java.vendor.url http://java.oracle.com/
    path.separator  ;
    java.vm.name    Java HotSpot(TM) 64-Bit Server VM
    file.encoding.pkg   sun.io
    user.script 
    user.country    US
    sun.java.launcher   SUN_STANDARD
    sun.os.patch.level  Service Pack 1
    java.vm.specification.name  Java Virtual Machine Specification
    user.dir    C:\Program Files\squirrel-sql-3.4.0
    java.runtime.version    1.7.0_17-b02
    java.awt.graphicsenv    sun.awt.Win32GraphicsEnvironment
    java.endorsed.dirs  C:\Program Files\Java\jre7\lib\endorsed
    os.arch amd64
    java.io.tmpdir  C:\Users\*******\AppData\Local\Temp\
    line.separator
    
    java.vm.specification.vendor    Oracle Corporation
    user.variant    
    os.name Windows 7
    sun.java2d.noddraw  true
    sun.jnu.encoding    Cp1250
    java.library.path   C:\Windows\system32;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\Program Files\CollabNet\Subversion Client;C:\Program Files (x86)\IBM\WebSphere MQ\Java\lib;C:\Program Files (x86)\IBM\WebSphere MQ\Java\lib64;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\WinZip\;C:\Program Files\Gemalto\Classic Client\BIN;C:\Program Files (x86)\Gemalto\Classic Client\BIN;C:\Program Files (x86)\NetManage\RUMBA\System;C:\Program Files (x86)\NetManage\RUMBA\;C:\Program Files (x86)\IBM\WebSphere MQ\bin64;C:\Program Files (x86)\IBM\WebSphere MQ\bin;C:\Program Files\TortoiseSVN\bin;.
    sun.awt.enableExtraMouseButtons true
    java.specification.name Java Platform API Specification
    java.class.version  51.0
    sun.management.compiler HotSpot 64-Bit Tiered Compilers
    os.version  6.1
    user.home   C:\Users\*******
    user.timezone   Europe/Prague
    java.awt.printerjob sun.awt.windows.WPrinterJob
    java.specification.version  1.7
    file.encoding   Cp1250
    user.name   *******
    java.class.path C:\Program Files\squirrel-sql-3.4.0\squirrel-sql.jar;C:\Program Files\squirrel-sql-3.4.0\lib\antlr.jar;C:\Program Files\squirrel-sql-3.4.0\lib\asm-attrs.jar;C:\Program Files\squirrel-sql-3.4.0\lib\asm.jar;C:\Program Files\squirrel-sql-3.4.0\lib\autocomplete.jar;C:\Program Files\squirrel-sql-3.4.0\lib\axis-jaxrpc.jar;C:\Program Files\squirrel-sql-3.4.0\lib\axis-saaj.jar;C:\Program Files\squirrel-sql-3.4.0\lib\axis-wsdl4j.jar;C:\Program Files\squirrel-sql-3.4.0\lib\axis.jar;C:\Program Files\squirrel-sql-3.4.0\lib\cglib.jar;C:\Program Files\squirrel-sql-3.4.0\lib\common.jar;C:\Program Files\squirrel-sql-3.4.0\lib\commons-cli.jar;C:\Program Files\squirrel-sql-3.4.0\lib\commons-codec.jar;C:\Program Files\squirrel-sql-3.4.0\lib\commons-collections.jar;C:\Program Files\squirrel-sql-3.4.0\lib\commons-discovery.jar;C:\Program Files\squirrel-sql-3.4.0\lib\commons-httpclient.jar;C:\Program Files\squirrel-sql-3.4.0\lib\commons-lang.jar;C:\Program Files\squirrel-sql-3.4.0\lib\commons-logging.jar;C:\Program Files\squirrel-sql-3.4.0\lib\core.jar;C:\Program Files\squirrel-sql-3.4.0\lib\ext;C:\Program Files\squirrel-sql-3.4.0\lib\forms.jar;C:\Program Files\squirrel-sql-3.4.0\lib\fw.jar;C:\Program Files\squirrel-sql-3.4.0\lib\hibernate-annotations.jar;C:\Program Files\squirrel-sql-3.4.0\lib\hibernate-commons-annotations.jar;C:\Program Files\squirrel-sql-3.4.0\lib\hibernate-entitymanager.jar;C:\Program Files\squirrel-sql-3.4.0\lib\hibernate.jar;C:\Program Files\squirrel-sql-3.4.0\lib\icu4j.jar;C:\Program Files\squirrel-sql-3.4.0\lib\javahelp.jar;C:\Program Files\squirrel-sql-3.4.0\lib\jide-oss.jar;C:\Program Files\squirrel-sql-3.4.0\lib\jmeld.jar;C:\Program Files\squirrel-sql-3.4.0\lib\jxl.jar;C:\Program Files\squirrel-sql-3.4.0\lib\log4j.jar;C:\Program Files\squirrel-sql-3.4.0\lib\nanoxml.jar;C:\Program Files\squirrel-sql-3.4.0\lib\org.eclipse.equinox.common.jar;C:\Program Files\squirrel-sql-3.4.0\lib\osgi.jar;C:\Program Files\squirrel-sql-3.4.0\lib\rsyntaxtextarea.jar;C:\Program Files\squirrel-sql-3.4.0\lib\rtext.jar;C:\Program Files\squirrel-sql-3.4.0\lib\spring-beans.jar;C:\Program Files\squirrel-sql-3.4.0\lib\spring-context-support.jar;C:\Program Files\squirrel-sql-3.4.0\lib\spring-context.jar;C:\Program Files\squirrel-sql-3.4.0\lib\spring-core.jar;C:\Program Files\squirrel-sql-3.4.0\lib\stringtemplate.jar;C:\Program Files\squirrel-sql-3.4.0\lib\swing-worker.jar;C:\Program Files\squirrel-sql-3.4.0\lib\treetable.jar;C:\Program Files\squirrel-sql-3.4.0\lib\versioncheck.jar;C:\Program Files\squirrel-sql-3.4.0\lib\x86.jar;C:\Program Files\squirrel-sql-3.4.0\lib\xml-apis.jar
    java.vm.specification.version   1.7
    sun.arch.data.model 64
    java.home   C:\Program Files\Java\jre7
    sun.java.command    net.sourceforge.squirrel_sql.client.Main --log-config-file C:\Program Files\squirrel-sql-3.4.0\log4j.properties --squirrel-home C:\Program Files\squirrel-sql-3.4.0
    java.specification.vendor   Oracle Corporation
    user.language   en
    user.language.format    cs
    awt.toolkit sun.awt.windows.WToolkit
    java.vm.info    mixed mode
    java.version    1.7.0_17
    java.ext.dirs   C:\Program Files\Java\jre7\lib\ext;C:\Windows\Sun\Java\lib\ext
    sun.boot.class.path C:\Program Files\Java\jre7\lib\resources.jar;C:\Program Files\Java\jre7\lib\rt.jar;C:\Program Files\Java\jre7\lib\sunrsasign.jar;C:\Program Files\Java\jre7\lib\jsse.jar;C:\Program Files\Java\jre7\lib\jce.jar;C:\Program Files\Java\jre7\lib\charsets.jar;C:\Program Files\Java\jre7\lib\jfr.jar;C:\Program Files\Java\jre7\classes
    java.vendor Oracle Corporation
    file.separator  \
    java.vendor.url.bug http://bugreport.sun.com/bugreport/
    sun.cpu.endian  little
    sun.io.unicode.encoding UnicodeLittle
    sun.desktop windows
    sun.cpu.isalist amd64
    
     
  • Stepan RYBAR
    Stepan RYBAR
    2013-03-12

    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 Separator
    

    Many thanks to Erwan Duroselle for this help.

    Stepan