Null-value problem on IDatabaseConnection.createQueryTable(java.lang.String tableName, java.lang.String sql)

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

Null-value problem on IDatabaseConnection.createQueryTable(java.lang.String tableName, java.lang.String sql)

Stian Skjørestad

I use an XlsDataSet to provide the expected results to my test. When ever a column has a null-value the test would fail because the resultset from the createQueryTable method returns "null" as the actual value where the value is null in the database.

Do I need to handle this explicitly on the XlsDataSet (or somewhere in between XlsDataSet and expected ITable) or can I do something to make sure the actual dataset column will actually be null instead of "null" when there's no value?

I use dbunit 2.4.4 and OracleConnection subclass of IDatabaseConnection.


I appreciate if someone could help me on this.


Regards,
Stian


------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Null-value problem on IDatabaseConnection.createQueryTable(java.lang.String tableName, java.lang.String sql)

Matthias Gommeringer
So just for my understanding: you get a "null" (String) from the DatabaseConnection.createQueryTable()? If so it depends very much on which oracle and hence dbunit datatype is used.
Or is the other thing the case (XlsDataSet/XlsTable provides "null" (String) instead of null for a null column)?
In any case you might want to wrap your DataSets with a ReplacementDataSet replacing all the "null" (String) with real nulls or vice versa so that they match. Please provide an as simple as possible testcase to let us reproduce this.

thx and regards,
matthias


> -----Ursprüngliche Nachricht-----
> Von: "Stian Skjørestad" <[hidden email]>
> Gesendet: 25.09.09 13:06:34
> An: [hidden email]
> Betreff: [dbunit-user] Null-value problem on IDatabaseConnection.createQueryTable(java.lang.String tableName, java.lang.String sql)

I use an XlsDataSet to provide the expected results to my test. When

> ever a column has a null-value the test would fail because the
> resultset from the createQueryTable method returns "null" as the
> actual value where the value is null in the database.
>
> Do I need to handle this explicitly on the XlsDataSet (or somewhere
> in between XlsDataSet and expected ITable) or can I do something to
> make sure the actual dataset column will actually be null instead of "
> null" when there's no value?
>
> I use dbunit 2.4.4 and OracleConnection subclass of IDatabaseConnection
> .
>
> I appreciate if someone could help me on this.
>
> Regards,
> Stian
>
> ----------------------------------------------------------------------
> -------- Come build with us! The BlackBerry® Developer Conference
> in SF, CA is the only developer event you need to attend this year.
> Jumpstart your developing skills, take BlackBerry mobile applications
> to market and stay ahead of the curve. Join us from November 9-12,
> 2009. Register now! http://p.sf.net/sfu/devconf___________________
> ____________________________ dbunit-user mailing list dbunit-user@
> lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/
>
> dbunit-user


______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de


------------------------------------------------------------------------------
Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
http://p.sf.net/sfu/devconf
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Null-value problem on IDatabaseConnection.createQueryTable(java.lang.String tableName, java.lang.String sql)

John Hurst-2
I was just testing the XlsDataSet with ReplacementDataSet for nulls to answer this question, when Matthias beat me to it (with a better answer).

However, just to add something, you can put "NULL" into your XLS spreadsheet, and have it replaced with database NULLs using ReplacementDataSet using code like this:

  @Override
  protected IDataSet getDataSet() throws Exception {
    IDataSet xlsDataSet = new XlsDataSet(this.getClass().getResourceAsStream("NullXlsDataSetTest.xls"));
    ReplacementDataSet result = new ReplacementDataSet(xlsDataSet);
    result.addReplacementObject("NULL", null);
    return result;
  }

(See also http://dbunit.sourceforge.net/components.html#ReplacementDataSet.)

Hope this helps.

John Hurst


On Sat, Sep 26, 2009 at 12:09 PM, Matthias Gommeringer <[hidden email]> wrote:
So just for my understanding: you get a "null" (String) from the DatabaseConnection.createQueryTable()? If so it depends very much on which oracle and hence dbunit datatype is used.
Or is the other thing the case (XlsDataSet/XlsTable provides "null" (String) instead of null for a null column)?
In any case you might want to wrap your DataSets with a ReplacementDataSet replacing all the "null" (String) with real nulls or vice versa so that they match. Please provide an as simple as possible testcase to let us reproduce this.

thx and regards,
matthias


> -----Ursprüngliche Nachricht-----
> Von: "Stian Skjørestad" <[hidden email]>
> Gesendet: 25.09.09 13:06:34
> An: [hidden email]
> Betreff: [dbunit-user] Null-value problem on IDatabaseConnection.createQueryTable(java.lang.String tableName, java.lang.String sql)

I use an XlsDataSet to provide the expected results to my test. When
> ever a column has a null-value the test would fail because the
> resultset from the createQueryTable method returns "null" as the
> actual value where the value is null in the database.
>
> Do I need to handle this explicitly on the XlsDataSet (or somewhere
> in between XlsDataSet and expected ITable) or can I do something to
> make sure the actual dataset column will actually be null instead of "
> null" when there's no value?
>
> I use dbunit 2.4.4 and OracleConnection subclass of IDatabaseConnection
> .
>
> I appreciate if someone could help me on this.
>
> Regards,
> Stian
>
> ----------------------------------------------------------------------
> -------- Come build with us! The BlackBerry® Developer Conference
> in SF, CA is the only developer event you need to attend this year.
> Jumpstart your developing skills, take BlackBerry mobile applications
> to market and stay ahead of the curve. Join us from November 9-12,
> 2009. Register now! http://p.sf.net/sfu/devconf___________________
> ____________________________ dbunit-user mailing list dbunit-user@
> lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/
>
> dbunit-user


______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de


------------------------------------------------------------------------------
Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
http://p.sf.net/sfu/devconf
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
COH: Level 10 US

------------------------------------------------------------------------------
Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
http://p.sf.net/sfu/devconf
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Null-value problem on IDatabaseConnection.createQueryTable(java.lang.String tableName, java.lang.String sql)

Stian Skjørestad

Matthias / John,


Thanks for your replies.
I consider my problems to be related to my "actual" data set. I haven't tried using the ReplacementDataSet. I will try that, but since I''d prefer to "fix" my actual data instead of tweaking the spreadsheet, I was hoping to find a solution where the createQueryTable method simply left null/void where there weren't any data in the row/column.

My Oracle version is currently 9.2.0.8.0. Although my test might also run on 10. I've seen my problem for varchar and date datatypes, but not yet for numbers.

I use a DefaultDatabaseTester with an OracleConnection.

How would it be to use the ReplacementDataSet on the actual data?
I'll try to provide a testcase.


Regards,
Stian





On Sat, Sep 26, 2009 at 2:22 AM, John Hurst <[hidden email]> wrote:
I was just testing the XlsDataSet with ReplacementDataSet for nulls to answer this question, when Matthias beat me to it (with a better answer).

However, just to add something, you can put "NULL" into your XLS spreadsheet, and have it replaced with database NULLs using ReplacementDataSet using code like this:

  @Override
  protected IDataSet getDataSet() throws Exception {
    IDataSet xlsDataSet = new XlsDataSet(this.getClass().getResourceAsStream("NullXlsDataSetTest.xls"));
    ReplacementDataSet result = new ReplacementDataSet(xlsDataSet);
    result.addReplacementObject("NULL", null);
    return result;
  }

(See also http://dbunit.sourceforge.net/components.html#ReplacementDataSet.)

Hope this helps.

John Hurst



On Sat, Sep 26, 2009 at 12:09 PM, Matthias Gommeringer <[hidden email]> wrote:
So just for my understanding: you get a "null" (String) from the DatabaseConnection.createQueryTable()? If so it depends very much on which oracle and hence dbunit datatype is used.
Or is the other thing the case (XlsDataSet/XlsTable provides "null" (String) instead of null for a null column)?
In any case you might want to wrap your DataSets with a ReplacementDataSet replacing all the "null" (String) with real nulls or vice versa so that they match. Please provide an as simple as possible testcase to let us reproduce this.

thx and regards,
matthias


> -----Ursprüngliche Nachricht-----
> Von: "Stian Skjørestad" <[hidden email]>
> Gesendet: 25.09.09 13:06:34
> An: [hidden email]
> Betreff: [dbunit-user] Null-value problem on IDatabaseConnection.createQueryTable(java.lang.String tableName, java.lang.String sql)

I use an XlsDataSet to provide the expected results to my test. When
> ever a column has a null-value the test would fail because the
> resultset from the createQueryTable method returns "null" as the
> actual value where the value is null in the database.
>
> Do I need to handle this explicitly on the XlsDataSet (or somewhere
> in between XlsDataSet and expected ITable) or can I do something to
> make sure the actual dataset column will actually be null instead of "
> null" when there's no value?
>
> I use dbunit 2.4.4 and OracleConnection subclass of IDatabaseConnection
> .
>
> I appreciate if someone could help me on this.
>
> Regards,
> Stian
>
> ----------------------------------------------------------------------
> -------- Come build with us! The BlackBerry® Developer Conference
> in SF, CA is the only developer event you need to attend this year.
> Jumpstart your developing skills, take BlackBerry mobile applications
> to market and stay ahead of the curve. Join us from November 9-12,
> 2009. Register now! http://p.sf.net/sfu/devconf___________________
> ____________________________ dbunit-user mailing list dbunit-user@
> lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/
>
> dbunit-user


______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de


------------------------------------------------------------------------------
Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
http://p.sf.net/sfu/devconf
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
COH: Level 10 US

------------------------------------------------------------------------------
Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
http://p.sf.net/sfu/devconf
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
http://p.sf.net/sfu/devconf
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Null-value problem on IDatabaseConnection.createQueryTable(java.lang.String tableName, java.lang.String sql)

Matthias Gommeringer
Hi Stian,

I think best would be to provide a testcase, as simple as possible.
Looking at the code I cannot imagine that varchar or date would return
an empty string if loaded from the database ("actual" dataset) since
there are explicit checks for null values on the resultSet. Maybe the
oracle table does not really contain a <null> but simply an empty
string? (e.g. if you look at your tables with TOAD <null> and "empty
string" look the same in the data grid by default).

Rgds,
matthias

--
***********************************
Matthias Gommeringer
Schmitzinger Str. 31
79761 Waldshut
Mobil:  +49 (0) 174 2017065
Tel:    +49 (0) 7751 8962657
mailto: [hidden email]
***********************************





Stian Skjørestad wrote:

>
> Matthias / John,
>
>
> Thanks for your replies.
> I consider my problems to be related to my "actual" data set. I
> haven't tried using the ReplacementDataSet. I will try that, but since
> I''d prefer to "fix" my actual data instead of tweaking the
> spreadsheet, I was hoping to find a solution where the
> createQueryTable method simply left null/void where there weren't any
> data in the row/column.
>
> My Oracle version is currently 9.2.0.8.0. Although my test might also
> run on 10. I've seen my problem for varchar and date datatypes, but
> not yet for numbers.
>
> I use a DefaultDatabaseTester with an OracleConnection.
>
> How would it be to use the ReplacementDataSet on the actual data?
> I'll try to provide a testcase.
>
>
> Regards,
> Stian
>
>
>
>
>
> On Sat, Sep 26, 2009 at 2:22 AM, John Hurst <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     I was just testing the XlsDataSet with ReplacementDataSet for
>     nulls to answer this question, when Matthias beat me to it (with a
>     better answer).
>
>     However, just to add something, you can put "NULL" into your XLS
>     spreadsheet, and have it replaced with database NULLs using
>     ReplacementDataSet using code like this:
>
>       @Override
>       protected IDataSet getDataSet() throws Exception {
>         IDataSet xlsDataSet = new
>     XlsDataSet(this.getClass().getResourceAsStream("NullXlsDataSetTest.xls"));
>         ReplacementDataSet result = new ReplacementDataSet(xlsDataSet);
>         result.addReplacementObject("NULL", null);
>         return result;
>       }
>
>     (See also
>     http://dbunit.sourceforge.net/components.html#ReplacementDataSet.)
>
>     Hope this helps.
>
>     John Hurst
>
>
>
>     On Sat, Sep 26, 2009 at 12:09 PM, Matthias Gommeringer
>     <[hidden email] <mailto:[hidden email]>>
>     wrote:
>
>         So just for my understanding: you get a "null" (String) from
>         the DatabaseConnection.createQueryTable()? If so it depends
>         very much on which oracle and hence dbunit datatype is used.
>         Or is the other thing the case (XlsDataSet/XlsTable provides
>         "null" (String) instead of null for a null column)?
>         In any case you might want to wrap your DataSets with a
>         ReplacementDataSet replacing all the "null" (String) with real
>         nulls or vice versa so that they match. Please provide an as
>         simple as possible testcase to let us reproduce this.
>
>         thx and regards,
>         matthias
>
>
>         > -----Ursprüngliche Nachricht-----
>         > Von: "Stian Skjørestad" <[hidden email]
>         <mailto:[hidden email]>>
>         > Gesendet: 25.09.09 13:06:34
>         > An: [hidden email]
>         <mailto:[hidden email]>
>         > Betreff: [dbunit-user] Null-value problem on
>         IDatabaseConnection.createQueryTable(java.lang.String
>         tableName, java.lang.String sql)
>
>         I use an XlsDataSet to provide the expected results to my
>         test. When
>         > ever a column has a null-value the test would fail because the
>         > resultset from the createQueryTable method returns "null" as the
>         > actual value where the value is null in the database.
>         >
>         > Do I need to handle this explicitly on the XlsDataSet (or
>         somewhere
>         > in between XlsDataSet and expected ITable) or can I do
>         something to
>         > make sure the actual dataset column will actually be null
>         instead of "
>         > null" when there's no value?
>         >
>         > I use dbunit 2.4.4 and OracleConnection subclass of
>         IDatabaseConnection
>         > .
>         >
>         > I appreciate if someone could help me on this.
>         >
>         > Regards,
>         > Stian
>         >
>         >
>         ----------------------------------------------------------------------
>         > -------- Come build with us! The BlackBerry® Developer
>         Conference
>         > in SF, CA is the only developer event you need to attend
>         this year.
>         > Jumpstart your developing skills, take BlackBerry mobile
>         applications
>         > to market and stay ahead of the curve. Join us from November
>         9-12,
>         > 2009. Register now!
>         http://p.sf.net/sfu/devconf___________________
>         > ____________________________ dbunit-user mailing list
>         dbunit-user@
>         > lists.sourceforge.net <http://lists.sourceforge.net>
>         https://lists.sourceforge.net/lists/listinfo/
>         >
>         > dbunit-user
>
>
>         ______________________________________________________
>         GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
>         Jetzt freischalten unter http://movieflat.web.de
>
>
>         ------------------------------------------------------------------------------
>         Come build with us! The BlackBerry&reg; Developer Conference
>         in SF, CA
>         is the only developer event you need to attend this year.
>         Jumpstart your
>         developing skills, take BlackBerry mobile applications to
>         market and stay
>         ahead of the curve. Join us from November 9&#45;12, 2009.
>         Register now&#33;
>         http://p.sf.net/sfu/devconf
>         _______________________________________________
>         dbunit-user mailing list
>         [hidden email]
>         <mailto:[hidden email]>
>         https://lists.sourceforge.net/lists/listinfo/dbunit-user
>
>
>
>
>     --
>     COH: Level 10 US
>
>     ------------------------------------------------------------------------------
>     Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
>     is the only developer event you need to attend this year.
>     Jumpstart your
>     developing skills, take BlackBerry mobile applications to market
>     and stay
>     ahead of the curve. Join us from November 9&#45;12, 2009. Register
>     now&#33;
>     http://p.sf.net/sfu/devconf
>     _______________________________________________
>     dbunit-user mailing list
>     [hidden email]
>     <mailto:[hidden email]>
>     https://lists.sourceforge.net/lists/listinfo/dbunit-user
>
>
> ------------------------------------------------------------------------
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
> http://p.sf.net/sfu/devconf
> ------------------------------------------------------------------------
>
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>  

------------------------------------------------------------------------------
Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
http://p.sf.net/sfu/devconf
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Null-value problem on IDatabaseConnection.createQueryTable(java.lang.String tableName, java.lang.String sql)

Mike Kienenberger
Columns containing empty strings are not possible under Oracle.
Oracle stores empty strings as null.

This means [1]:

    * The expression X='' is never true, regardless of what X is. In particular:
    * The expression ''='' is not true.
    * If you store an empty string ('') and read it back, the result
will be null.
    * The expression trim(' ') returns null.



[1] http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/


On Mon, Sep 28, 2009 at 4:02 PM, Matthias Gommeringer
<[hidden email]> wrote:
> Hi Stian,
>
> I think best would be to provide a testcase, as simple as possible.
> Looking at the code I cannot imagine that varchar or date would return
> an empty string if loaded from the database ("actual" dataset) since
> there are explicit checks for null values on the resultSet. Maybe the
> oracle table does not really contain a <null> but simply an empty
> string? (e.g. if you look at your tables with TOAD <null> and "empty
> string" look the same in the data grid by default).

------------------------------------------------------------------------------
Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
http://p.sf.net/sfu/devconf
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Null-value problem on IDatabaseConnection.createQueryTable(java.lang.String tableName, java.lang.String sql)

John Hurst-2
In reply to this post by Matthias Gommeringer
Note that in Oracle by default, an empty string is considered NULL. It's bizarre, it's not ANSI, but it is Oracle.

I think that there is an "ANSI compatible" parameter that can be set to make it behave like other databases. But I believe that most sites run it "as Oracle".

JH

On Tue, Sep 29, 2009 at 9:02 AM, Matthias Gommeringer <[hidden email]> wrote:
Hi Stian,

I think best would be to provide a testcase, as simple as possible.
Looking at the code I cannot imagine that varchar or date would return
an empty string if loaded from the database ("actual" dataset) since
there are explicit checks for null values on the resultSet. Maybe the
oracle table does not really contain a <null> but simply an empty
string? (e.g. if you look at your tables with TOAD <null> and "empty
string" look the same in the data grid by default).

Rgds,
matthias

--
***********************************
Matthias Gommeringer
Schmitzinger Str. 31
79761 Waldshut
Mobil:  +49 (0) 174 2017065
Tel:    +49 (0) 7751 8962657
mailto: [hidden email]
***********************************





Stian Skjørestad wrote:
>
> Matthias / John,
>
>
> Thanks for your replies.
> I consider my problems to be related to my "actual" data set. I
> haven't tried using the ReplacementDataSet. I will try that, but since
> I''d prefer to "fix" my actual data instead of tweaking the
> spreadsheet, I was hoping to find a solution where the
> createQueryTable method simply left null/void where there weren't any
> data in the row/column.
>
> My Oracle version is currently 9.2.0.8.0. Although my test might also
> run on 10. I've seen my problem for varchar and date datatypes, but
> not yet for numbers.
>
> I use a DefaultDatabaseTester with an OracleConnection.
>
> How would it be to use the ReplacementDataSet on the actual data?
> I'll try to provide a testcase.
>
>
> Regards,
> Stian
>
>
>
>
>
> On Sat, Sep 26, 2009 at 2:22 AM, John Hurst <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     I was just testing the XlsDataSet with ReplacementDataSet for
>     nulls to answer this question, when Matthias beat me to it (with a
>     better answer).
>
>     However, just to add something, you can put "NULL" into your XLS
>     spreadsheet, and have it replaced with database NULLs using
>     ReplacementDataSet using code like this:
>
>       @Override
>       protected IDataSet getDataSet() throws Exception {
>         IDataSet xlsDataSet = new
>     XlsDataSet(this.getClass().getResourceAsStream("NullXlsDataSetTest.xls"));
>         ReplacementDataSet result = new ReplacementDataSet(xlsDataSet);
>         result.addReplacementObject("NULL", null);
>         return result;
>       }
>
>     (See also
>     http://dbunit.sourceforge.net/components.html#ReplacementDataSet.)
>
>     Hope this helps.
>
>     John Hurst
>
>
>
>     On Sat, Sep 26, 2009 at 12:09 PM, Matthias Gommeringer
>     <[hidden email] <mailto:[hidden email]>>
>     wrote:
>
>         So just for my understanding: you get a "null" (String) from
>         the DatabaseConnection.createQueryTable()? If so it depends
>         very much on which oracle and hence dbunit datatype is used.
>         Or is the other thing the case (XlsDataSet/XlsTable provides
>         "null" (String) instead of null for a null column)?
>         In any case you might want to wrap your DataSets with a
>         ReplacementDataSet replacing all the "null" (String) with real
>         nulls or vice versa so that they match. Please provide an as
>         simple as possible testcase to let us reproduce this.
>
>         thx and regards,
>         matthias
>
>
>         > -----Ursprüngliche Nachricht-----
>         > Von: "Stian Skjørestad" <[hidden email]
>         <mailto:[hidden email]>>
>         > Gesendet: 25.09.09 13:06:34
>         > An: [hidden email]
>         <mailto:[hidden email]>
>         > Betreff: [dbunit-user] Null-value problem on
>         IDatabaseConnection.createQueryTable(java.lang.String
>         tableName, java.lang.String sql)
>
>         I use an XlsDataSet to provide the expected results to my
>         test. When
>         > ever a column has a null-value the test would fail because the
>         > resultset from the createQueryTable method returns "null" as the
>         > actual value where the value is null in the database.
>         >
>         > Do I need to handle this explicitly on the XlsDataSet (or
>         somewhere
>         > in between XlsDataSet and expected ITable) or can I do
>         something to
>         > make sure the actual dataset column will actually be null
>         instead of "
>         > null" when there's no value?
>         >
>         > I use dbunit 2.4.4 and OracleConnection subclass of
>         IDatabaseConnection
>         > .
>         >
>         > I appreciate if someone could help me on this.
>         >
>         > Regards,
>         > Stian
>         >
>         >
>         ----------------------------------------------------------------------
>         > -------- Come build with us! The BlackBerry® Developer
>         Conference
>         > in SF, CA is the only developer event you need to attend
>         this year.
>         > Jumpstart your developing skills, take BlackBerry mobile
>         applications
>         > to market and stay ahead of the curve. Join us from November
>         9-12,
>         > 2009. Register now!
>         http://p.sf.net/sfu/devconf___________________
>         > ____________________________ dbunit-user mailing list
>         dbunit-user@
>         > lists.sourceforge.net <http://lists.sourceforge.net>
>         https://lists.sourceforge.net/lists/listinfo/
>         >
>         > dbunit-user
>
>
>         ______________________________________________________
>         GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
>         Jetzt freischalten unter http://movieflat.web.de
>
>
>         ------------------------------------------------------------------------------
>         Come build with us! The BlackBerry&reg; Developer Conference
>         in SF, CA
>         is the only developer event you need to attend this year.
>         Jumpstart your
>         developing skills, take BlackBerry mobile applications to
>         market and stay
>         ahead of the curve. Join us from November 9&#45;12, 2009.
>         Register now&#33;
>         http://p.sf.net/sfu/devconf
>         _______________________________________________
>         dbunit-user mailing list
>         [hidden email]
>         <mailto:[hidden email]>
>         https://lists.sourceforge.net/lists/listinfo/dbunit-user
>
>
>
>
>     --
>     COH: Level 10 US
>
>     ------------------------------------------------------------------------------
>     Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
>     is the only developer event you need to attend this year.
>     Jumpstart your
>     developing skills, take BlackBerry mobile applications to market
>     and stay
>     ahead of the curve. Join us from November 9&#45;12, 2009. Register
>     now&#33;
>     http://p.sf.net/sfu/devconf
>     _______________________________________________
>     dbunit-user mailing list
>     [hidden email]
>     <mailto:[hidden email]>
> ------------------------------------------------------------------------
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
> http://p.sf.net/sfu/devconf
> ------------------------------------------------------------------------
>
> _______________________________________________
> dbunit-user mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>

------------------------------------------------------------------------------
Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
http://p.sf.net/sfu/devconf
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
COH: Level 10 US

------------------------------------------------------------------------------
Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
http://p.sf.net/sfu/devconf
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Null-value problem on IDatabaseConnection.createQueryTable(java.lang.String tableName, java.lang.String sql)

John Hurst-2
In reply to this post by Mike Kienenberger
Oracle stores empty strings as NULL, but for string operations it also treats NULL as an empty string.

So although TRIM(' ') IS NULL, it's also true that TRIM(' ') || 'X' = 'X'. (Compare NULL + 1 IS NULL.)

JH

On Tue, Sep 29, 2009 at 9:11 AM, Mike Kienenberger <[hidden email]> wrote:
Columns containing empty strings are not possible under Oracle.
Oracle stores empty strings as null.

This means [1]:

   * The expression X='' is never true, regardless of what X is. In particular:
   * The expression ''='' is not true.
   * If you store an empty string ('') and read it back, the result
will be null.
   * The expression trim(' ') returns null.



[1] http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/


On Mon, Sep 28, 2009 at 4:02 PM, Matthias Gommeringer
<[hidden email]> wrote:
> Hi Stian,
>
> I think best would be to provide a testcase, as simple as possible.
> Looking at the code I cannot imagine that varchar or date would return
> an empty string if loaded from the database ("actual" dataset) since
> there are explicit checks for null values on the resultSet. Maybe the
> oracle table does not really contain a <null> but simply an empty
> string? (e.g. if you look at your tables with TOAD <null> and "empty
> string" look the same in the data grid by default).

------------------------------------------------------------------------------
Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
http://p.sf.net/sfu/devconf
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
COH: Level 10 US

------------------------------------------------------------------------------
Come build with us! The BlackBerry&reg; Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9&#45;12, 2009. Register now&#33;
http://p.sf.net/sfu/devconf
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user