#6 insert failed when having title case column name

[Michel Chen]

Description
------------
Insert and duplicate functions do not work when having a column name with both upper and lower cases.

Env
------
Windows 7 / Simple Chinese, DB2 64bit v9.5.0.1 / UTF8,

Error Message
-------------------
(CN) "UUID_REGION" is invalid in its context. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.7.85

com.ibm.db2.jcc.am.mo: "UUID_REGION" 在使用它的上下文中无效。. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.7.85
at com.ibm.db2.jcc.am.dd.a(dd.java:676)
at com.ibm.db2.jcc.am.dd.a(dd.java:60)
at com.ibm.db2.jcc.am.dd.a(dd.java:127)
at com.ibm.db2.jcc.am.nm.c(nm.java:2493)
at com.ibm.db2.jcc.am.nm.d(nm.java:2470)
at com.ibm.db2.jcc.am.nm.a(nm.java:1950)
at com.ibm.db2.jcc.t4.db.g(db.java:139)
at com.ibm.db2.jcc.t4.db.a(db.java:39)
at com.ibm.db2.jcc.t4.t.a(t.java:32)
at com.ibm.db2.jcc.t4.sb.h(sb.java:135)
at com.ibm.db2.jcc.am.nm.gb(nm.java:1921)
at com.ibm.db2.jcc.am.om.kc(om.java:2806)
at com.ibm.db2.jcc.am.om.qc(om.java:2921)
at com.ibm.db2.jcc.am.lb.a(lb.java:729)
at com.ibm.db2.jcc.am.cm.wb(cm.java:4095)
at com.ibm.db2.jcc.am.cm.D(cm.java:2852)
at com.ibm.db2.jcc.am.cm.insertRow(cm.java:2814)
at dbedit.actions.DuplicateAction.store(DuplicateAction.java:46)
at dbedit.actions.EditAction.performThreaded(EditAction.java:85)
at dbedit.actions.CustomAction$1.execute(CustomAction.java:67)
at dbedit.ThreadedAction.run(ThreadedAction.java:48)
at java.lang.Thread.run(Unknown Source)

DBEdit 2.3.1 (14/05/2010)
Windows 7 6.1 x86
Java(TM) SE Runtime Environment 1.6.0_20-b02
jdbc:db2://10.23.13.41:50000/cyanway
com.ibm.db2.jcc.DB2Driver
select * from CW."CW_RegionInfo"

Possible Root Cause
----------------------
* Insert function use all uppen case column name by default. It fails if the table has title case column name.
* Not sure what happens when the schema name is title case. (just try this, it works well)
* DB2 use "" to quotate such column, but MySQL use `` by default. (not tried)

Recreate scripts
------------------------
$ cat t.sql
-- failed
create table "mySchema"."MyTab" ( "MyCol" varchar(20) )
insert into "mySchema"."MyTab"("MyCol") values ('test data')
commit
select * from "mySchema"."MyTab"
drop table "mySchema"."MyTab"

$ cat t2.sql
-- works
create table "mySchema"."TTT" ( "CCC" varchar(20) )
insert into "mySchema"."TTT"("CCC") values ('test data')
commit
select * from "mySchema"."TTT"
drop table "mySchema"."TTT";

[Jef Van Den Ouweland]

This failure only occurs with the IBM DB2 JDBC driver due to a bug in this driver.
This test scenario works with other databases such as Oracle, MySQL. It even works on DB2 when using the DataDirect driver (not free).
This can not be fixed in DBEdit because it just uses the standard JDBC methods of the driver.
Options:
- Wait for IBM to release a new version of the driver which fixes this issue ( I will update DBEdit when this happens)
- In the meantime, use insert statements instead of the insert dialog.
- Try the DataDirect driver (evaluation) http://web.datadirect.com/products/jdbc/db2/index.html