Sometimes it fails to parse valid SQL leading to "Method executeUpdate cannot be used for query"

• 回報者: malyvelky
• 負責人: (無)
• 類型: 問題回報
• 狀態: 關閉
• 元件: (無)
• 里程碑: (無)
• 優先權: 5 - 中
• 嚴重程度: 5 - 中
• 處理結果: 修正
• 檔案: 無

細節

DBViewer sometimes fails to process a valid DB2 SQL correctly and the query fails with the dialog window "ZIGEN'S DBViewer Plugin" displaying the message "ibmdb2jcc1010410942 Method executeUpdate cannot be used for query.". At the same time there is an exception in the error log:

eclipse.buildId=I20080617-2000
java.version=1.6.0_20
java.vendor=Sun Microsystems Inc.
BootLoader constants: OS=linux, ARCH=x86, WS=gtk, NL=en_US
Command-line arguments:  -os linux -ws gtk -arch x86

Error
Wed Sep 08 14:52:53 CEST 2010
java.lang.NullPointerException
	at zigen.sql.parser.SqlParser.parse(SqlParser.java:88)
	at zigen.sql.parser.SqlParser.parseExpression(SqlParser.java:1383)
	at zigen.sql.parser.SqlParser.parseExpression(SqlParser.java:1508)
	at zigen.sql.parser.SqlParser.parseExpression(SqlParser.java:1491)
	at zigen.sql.parser.SqlParser.parseWhereClause(SqlParser.java:747)
	at zigen.sql.parser.SqlParser.parse(SqlParser.java:193)
	at zigen.sql.parser.SqlParser.parseExpression(SqlParser.java:1426)
	at zigen.sql.parser.SqlParser.parseExpression(SqlParser.java:1508)
	at zigen.sql.parser.SqlParser.parse(SqlParser.java:122)
	at zigen.sql.parser.SqlParser.parseFromClause(SqlParser.java:710)
	at zigen.sql.parser.SqlParser.parse(SqlParser.java:210)
	at zigen.sql.parser.SqlParser.parse(SqlParser.java:92)
	at zigen.sql.parser.SqlParser.parseSelectStatement(SqlParser.java:450)
	at zigen.sql.parser.SqlParser.parse(SqlParser.java:142)
	at zigen.sql.parser.SqlParser.parseExpression(SqlParser.java:1397)
	at zigen.sql.parser.SqlParser.parseExpression(SqlParser.java:1508)
	at zigen.sql.parser.SqlParser.parseExpression(SqlParser.java:1491)
	at zigen.sql.parser.SqlParser.parse(SqlParser.java:122)
	at zigen.sql.parser.SqlParser.parseCase(SqlParser.java:1265)
	at zigen.sql.parser.SqlParser.parseSelectStatement(SqlParser.java:611)
	at zigen.sql.parser.SqlParser.parse(SqlParser.java:142)
	at zigen.sql.parser.SqlParser.parse(SqlParser.java:107)
	at zigen.plugin.db.ui.jobs.SqlExecJob.parseSql(SqlExecJob.java:287)
	at zigen.plugin.db.ui.jobs.SqlExecJob.executeSingleSQL(SqlExecJob.java:240)
	at zigen.plugin.db.ui.jobs.SqlExecJob.run(SqlExecJob.java:112)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)

An example of such a query is:

 with tmp_table as (
 SELECT
         CASE
             WHEN (
                 1 = 
                     (SELECT count(1) FROM syscat.SCHEMATA s JOIN syscat.tables t ON s.SCHEMANAME = t.TABSCHEMA 
                      WHERE s.SCHEMANAME = 'SYSCAT  '
                     )
             ) THEN 'A' 
             ELSE 'B' 
         END AS dummy_value       
     FROM
         syscat.SCHEMATA
 )
 select * from tmp_table

Interestingly, if you either change the "1 = ..." to "1 IN ..." or if you remove the part "WHERE s.SCHEMANAME = 'SYSCAT '", it will execute correctly.

Notice that the query uses only standard DB2 views and thus you can try it for yourself by downloading the free DB2 Express-C.

DBViewer Plugin version: 1.1.0v20090121

Ticket History (3/4 Histories)

2010-09-08 22:08 Updated by: malyvelky

• New Ticket "Sometimes it fails to parse valid SQL leading to "Method executeUpdate cannot be used for query"" created

2010-10-08 16:54 Updated by: zigen

評語

Hi,

Thank you for your message.

I worked out the bugs.

It will release it in the near future.

2010-10-09 20:51 Updated by: zigen

評語

Hi,

A new version(1.2.2.v20101009) was released. Please try.

thank you.

2010-11-02 18:02 Updated by: malyvelky

• 處理結果 Update from 無 to 修正
• 狀態 Update from 開啟 to 關閉
• Ticket Close date is changed to 2010-11-02 18:02

評語

Reply To zigen

Hi, A new version(1.2.2.v20101009) was released. Please try. thank you.

Thanks a lot, it works!