Problem with field name case sensitivity while using DbUnit task via Ant

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem with field name case sensitivity while using DbUnit task via Ant

Алексей Гладков
Hello!

I started to work with DbUnit a few time ago and have strange problem.
I am using DbUnit task by Ant.

Column in my database has name "id_RemoteReceivingStation" (in table request).
EXPORT operation works fine, i.e. in exported xml file (flat format) field name is "id_RemoteReceivingStation".
But INSERT operation faild with error:
"org.postgresql.util.PSQLException: ERROR: column "id_remotereceivingstation" of relation "request" does not exist"

By the way, if I rename column in lower case way ("id_remotereceivingstation" ) than INSERT operation works without problem.

Just in case I have tried this under Linux and Windows. There was the same result.


Possibly there are some problem with caseSensitiveTableNames database property setting or with some internal components, that works with db.
Another possiblity is that DbUnit makes sql queries to database without quotes, so Potgresql automatically convert it to lower case (database log is attaged).

I'm using
DbUnit 2.4.9
Apache Ant version 1.7.1 compiled on May 24 2013
java:
java version "1.6.0"
Java(TM) SE Runtime Environment (build pxa6460sr13fp1-20130325_01(SR13 FP1))
IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 Linux amd64-64 jvmxa6460sr13-20130114_134867 (JIT enabled, AOT enabled)
J9VM - 20130114_134867
JIT  - r9_20130108_31100
GC   - 20121212_AA)
JCL  - 20130315_01

Postgresql database



Ant error message:
Buildfile: build.xml

insert:
   [dbunit] Executing operation: INSERT
   [dbunit]           on   file: /home/gladkov/projects/testing/data/requestOneExternal.xml
   [dbunit]           with format: flat

BUILD FAILED
org.dbunit.DatabaseUnitException: Exception processing table name='request.request'
    at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:216)
    at org.dbunit.operation.TransactionOperation.execute(TransactionOperation.java:78)
    at org.dbunit.ant.Operation.execute(Operation.java:192)
    at org.dbunit.ant.DbUnitTask.execute(DbUnitTask.java:380)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
    at java.lang.reflect.Method.invoke(Method.java:611)
    at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at org.apache.tools.ant.Target.execute(Target.java:357)
    at org.apache.tools.ant.Target.performTasks(Target.java:385)
    at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1337)
    at org.apache.tools.ant.Project.executeTarget(Project.java:1306)
    at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
    at org.apache.tools.ant.Project.executeTargets(Project.java:1189)
    at org.apache.tools.ant.Main.runBuild(Main.java:758)
    at org.apache.tools.ant.Main.startAnt(Main.java:217)
    at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257)
    at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104)
Caused by: org.postgresql.util.PSQLException: ERROR: column "id_remotereceivingstation" of relation "request" does not exist
  Позиция: 155
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:360)
    at org.dbunit.database.statement.SimplePreparedStatement.addBatch(SimplePreparedStatement.java:80)
    at org.dbunit.database.statement.AutomaticPreparedBatchStatement.addBatch(AutomaticPreparedBatchStatement.java:70)
    at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:198)
    ... 20 more

Total time: 8 seconds



My build.xml file for Ant:
<project name="dbunit-simple-db-export" basedir=".">
    <description>Build file for DbUnit.</description>
   
    <!-- Global properties for this build -->
    <property name="jdbcDriver" value="org.postgresql.Driver"/>
    <property name="dbUrl" value="jdbc:postgresql://192.168.45.240:5432/global3"/>

    <property name="dbUsername" value="default"/>
    <property name="dbPassword" value="default"/>
    <property name="dataFilePath" value="data/default.xml"/>
   
    <!-- libraries for DBUnit -->
    <path id="libs">
        <fileset dir="/opt/slf4j/lib" includes="*.jar"/>
        <fileset dir="/usr/share/java" includes="log4j.jar"/>
       
        <fileset dir="/opt/libPgsqlDriverJava-9/lib64" includes="postgresql-9.3-1100.jdbc4.jar"/>
        <fileset dir="/opt/libPostgisJava-2/lib64" includes="postgis-2.0.0SVN.jar"/>
       
        <fileset dir="/opt/dbunitWithPostgisPatch/bin" includes="*.jar"/>
    </path>

    <typedef name="dbunit"
            classname="org.dbunit.ant.DbUnitTask"
            classpathref="libs"
            loaderref="lib.path.loader"
            classpath="."/>

           
    <!-- Export -->
    <target name="export" description="Экспорт из БД.">
        <dbunit driver="${jdbcDriver}"
                url="${dbUrl}"
                userid="${dbUsername}"
                password="${dbPassword}">
           
            <dbconfig>
                <property name="datatypeFactory" value="org.dbunit.ext.postgresql.PostgresqlDataTypeFactory" />
                <feature name="caseSensitiveTableNames" value="true" />
                <feature name="qualifiedTableNames" value="true" />
            </dbconfig>
           
            <export dest="${dataFilePath}" format="flat">
                <table name="request.request"/>
            </export>
        </dbunit>
    </target>
   

    <!-- Insert -->
    <target name="insert" description="Insert to DB.">

        <dbunit driver="${jdbcDriver}"
                url="${dbUrl}"
                userid="${dbUsername}"
                password="${dbPassword}">
           
            <dbconfig>
                <property name="datatypeFactory" value="org.dbunit.ext.postgresql.PostgresqlDataTypeFactory" />
                <feature name="caseSensitiveTableNames" value="true" />
                <feature name="qualifiedTableNames" value="true" />
            </dbconfig>
           
            <operation type="INSERT" src="${dataFilePath}" format="flat" transaction="true"/>
        </dbunit>
    </target>
</project>









Database log:
"2014-01-23 12:28:15 MSK||[unknown]|[unknown]|[unknown]|LOG:  connection received: host=192.168.45.13 port=51463
442 2014-01-23 12:28:15 MSK|192.168.45.13|[unknown]|global3|postgres|LOG:  connection authorized: user=postgres database=global3
443 2014-01-23 12:28:16 MSK|192.168.45.13|[unknown]|global3|postgres|LOG:  execute S_1: BEGIN
444 2014-01-23 12:28:16 MSK|192.168.45.13|[unknown]|global3|postgres|LOG:  execute <unnamed>: SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,  CASE n.nspname ~ '    ^pg_' OR n.nspname = 'information_schema'  WHEN true THEN CASE  WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TABLE'   WHE    N 'v' THEN 'SYSTEM VIEW'   WHEN 'i' THEN 'SYSTEM INDEX'   ELSE NULL   END  WHEN n.nspname = 'pg_toast' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TOAST TABLE'   WHEN 'i' THEN 'SYSTEM TO    AST INDEX'   ELSE NULL   END  ELSE CASE c.relkind   WHEN 'r' THEN 'TEMPORARY TABLE'   WHEN 'i' THEN 'TEMPORARY INDEX'   ELSE NULL   END  END  WHEN false THEN CASE c.relkind  WHEN 'r' THE    N 'TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'S' THEN 'SEQUENCE'  WHEN 'v' THEN 'VIEW'  ELSE NULL  END  ELSE NULL  END  AS TABLE_TYPE, d.description AS REMARKS  FROM pg_catalog.pg_namespace n,     pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')      LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')  WHERE c.relnamespace = n.oid  AND c.relname LIKE '%'  AND (false  OR ( c.relkind = 'r' AND     n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ) )  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME
445 2014-01-23 12:28:22 MSK|192.168.45.13|[unknown]|global3|postgres|LOG:  execute <unnamed>: SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,  CASE n.nspname ~ '    ^pg_' OR n.nspname = 'information_schema'  WHEN true THEN CASE  WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TABLE'   WHE    N 'v' THEN 'SYSTEM VIEW'   WHEN 'i' THEN 'SYSTEM INDEX'   ELSE NULL   END  WHEN n.nspname = 'pg_toast' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TOAST TABLE'   WHEN 'i' THEN 'SYSTEM TO    AST INDEX'   ELSE NULL   END  ELSE CASE c.relkind   WHEN 'r' THEN 'TEMPORARY TABLE'   WHEN 'i' THEN 'TEMPORARY INDEX'   ELSE NULL   END  END  WHEN false THEN CASE c.relkind  WHEN 'r' THE    N 'TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'S' THEN 'SEQUENCE'  WHEN 'v' THEN 'VIEW'  ELSE NULL  END  ELSE NULL  END  AS TABLE_TYPE, d.description AS REMARKS  FROM pg_catalog.pg_namespace n,     pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')      LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')  WHERE c.relnamespace = n.oid  AND n.nspname LIKE 'request'  AND c.relname LIKE 'request'  O    RDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME
446 2014-01-23 12:28:22 MSK|192.168.45.13|[unknown]|global3|postgres|LOG:  execute <unnamed>: SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,pg_ca    talog.pg_get_expr(def.adbin, def.adrelid) AS adsrc,dsc.description,t.typbasetype,t.typtype  FROM pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid)  JOIN p    g_catalog.pg_attribute a ON (a.attrelid=c.oid)  JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid)  LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum    )  LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)  LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class')  LEFT JO    IN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')  WHERE a.attnum > 0 AND NOT a.attisdropped  AND n.nspname LIKE 'request'  AND c.relname LIKE 'reques    t'  AND a.attname LIKE '%'  ORDER BY nspname,relname,attnum
447 2014-01-23 12:28:22 MSK|192.168.45.13|[unknown]|global3|postgres|LOG:  execute <unnamed>: SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM,  ct.relname AS TABLE_NAME,  a.attname AS COL    UMN_NAME,  a.attnum AS KEY_SEQ,  ci.relname AS PK_NAME  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute a, pg_catalog.pg_index i      WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid  AND a.attrelid=ci.oid AND i.indisprimary  AND ct.relname = 'request'  AND ct.relnamespace = n.oid  AND n.nspname = 'request'  ORDER BY ta    ble_name, pk_name, key_seq
448 2014-01-23 12:28:23 MSK|192.168.45.13|[unknown]|global3|postgres|ERROR:  column "id_remotereceivingstation" of relation "request" does not exist at character 155
449 2014-01-23 12:28:23 MSK|192.168.45.13|[unknown]|global3|postgres|STATEMENT:  insert into request.request (id, id_customer, number, received, required_date, agreed, comment, id_source, un    loaded, kvp_input_code, is_outgoing_request, id_RemoteReceivingStation) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)

450 2014-01-23 12:28:23 MSK|192.168.45.13|[unknown]|global3|postgres|LOG:  execute S_2: ROLLBACK
451 2014-01-23 12:28:23 MSK|192.168.45.13|[unknown]|global3|postgres|LOG:  disconnection: session time: 0:00:07.060 user=postgres database=global3 host=192.168.45.13 port=51463"

С уважением,
Алексей Гладков, [hidden email]
Отдел информационных технологий распространения данных ДЗЗ
ОАО "НИИ ТП"

Тел.: +7 (499) 202-27-09
Моб. +7 (929) 596 44 13

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Problem with field name case sensitivity while using DbUnit task via Ant

Алексей Гладков
A few days of searching get result!

The problem was with sql queries that were sent to database without quotes, so Potgresql automatically convert it to lower case.
The solving of this situation is to use "escapePattern" property - http://www.dbunit.org/properties.html#escapepattern.
I think there is good idea to add this note to dbUnit documentation (for example to FAQ about working with PostgreSQL).


By the way I have tried to build DBUnit by myself and perfom tests with "postgresql" configuration. Unfortunatelly it failed.
The first problem that I found is that after creating database (as described here: http://dbunit.sourceforge.net/integrationtests.html#Running_PostgreSQL_Integration_Tests) test queries in src\test\resources\sql\postgresql.sql can't executes.
I think queries "DROP TABLE ...;" should be replaced with "DROP TABLE IF EXISTS ...;".

But after such changings test failed again.
Messages was that tabels that should be created in postgresql.sql doesn't exists...


Best regards,
Alexey Gladkov




------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user