Issue: Numeric column returns NULL if the preceding column with String type is NULL.

Database version : Oracle Rdb , Version :V7.1-411,

JDBC Driver: Oracle Rdb Native Thin JDBC Driver,v71

Hibernate version : can’t tell from Hibernate2.jar , inside jar , it has hibernate-configuration-2.0.dtd , so probably it’s 2.0??

Database table info:
SQL> show ta test_hbn_null;
Information for table TEST_HBN_NULL

Columns for table TEST_HBN_NULL:
Column Name Data Type Domain
----------- --------- ------
TEST_HBN_NULL_ID INTEGER
Not Null constraint TEST_HBN_NULL_ID_NOT_NULL_1
MY_STRING1 CHAR(5)
MY_STRING2 CHAR(5)
MY_INTEGER INTEGER
MY_STRING3 CHAR(10)
MY_TIMEWSTAMP1 DATE VMS
MY_STRING4 CHAR(10)
MY_TIMESTAMP2 DATE VMS

The records in table:
SQL> select * from test_hbn_null where my_string3='str3' and my_string4='str4';
TEST_HBN_NULL_ID MY_STRING1 MY_STRING2 MY_INTEGER MY_STRING3 MY_TIMEWSTAMP1 MY_STRING4
MY_TIMESTAMP2
1 NULL str2 100 str3 NULL str4
NULL

2 str1 NULL 100 str3 NULL str4
NULL

2 rows selected

Hibernate mapping file: Generated by xdoclet
<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class
name="edu.mnscu.housing.vo.TestHbnNull"
table="test_hbn_null"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="test_hbn_null_id"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="assigned">
</generator>
</id>

<property
name="myString1"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="my_string1"
/>

<property
name="myString2"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="my_string2"
/>

<property
name="myInteger"
type="java.lang.Integer"
update="true"
insert="true"
access="property"
column="my_Integer"
/>

<property
name="myString3"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="my_string3"
/>

<property
name="myTimeStamp1"
type="java.util.Date"
update="true"
insert="true"
access="property"
column="my_timewstamp1"
/>

<property
name="myString4"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="my_string4"
/>

<property
name="myTimestamp2"
type="java.util.Date"
update="true"
insert="true"
access="property"
column="my_timestamp2"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-TestHbnNull.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>

Java object:
package edu.mnscu.housing.vo;

import java.io.Serializable;
import java.util.Date;

/**
* @hibernate.class table="test_hbn_null"
*/
public class TestHbnNull implements Serializable {

private Long id;
private String myString1;
private String myString2;
private Integer myInteger;
private String myString3;
private Date myTimestamp1;
private String myString4;
private Date myTimestamp2;

/**
* @hibernate.id column="test_hbn_null_id"
* generator-class="assigned"
* not-null="true" unsaved-value="null"
*/
public Long getId(){
return id;
}
public void setId ( Long id ){
this.id = id;
}
/**
* @hibernate.property column="my_string1"
*/
public String getMyString1(){
return myString1;
}
public void setMyString1 ( String myString1 ){
this.myString1 = myString1;
}
/**
* @hibernate.property column="my_string2"
*/
public String getMyString2(){
return myString2;
}
public void setMyString2 ( String myString2 ){
this.myString2 = myString2;
}
/**
* @hibernate.property column="my_Integer"
*/
public Integer getMyInteger(){
return myInteger;
}
public void setMyInteger( Integer myInteger ){
this.myInteger = myInteger;
}
/**
* @hibernate.property column="my_string3"
*/
public String getMyString3(){
return myString3;
}
public void setMyString3 ( String myString3 ){
this.myString3 = myString3;
}
/**
* @hibernate.property column="my_timewstamp1"
*/
public Date getMyTimeStamp1(){
return myTimestamp1;
}
public void setMyTimeStamp1 ( Date myTimestamp1 ){
this.myTimestamp1 = myTimestamp1;
}
/**
* @hibernate.property column="my_string4"
*/
public String getMyString4(){
return myString4;
}
public void setMyString4 ( String myString4 ){
this.myString4 = myString4;
}

/**
* @hibernate.property column="my_timestamp2"
*/
public Date getMyTimestamp2(){
return myTimestamp2;
}
public void setMyTimestamp2 ( Date myTimestamp2 ){
this.myTimestamp2 = myTimestamp2;
}

}

Test service:

String ms3 = "str3";
String ms4 = "str4";

String Q_TEST_HBN_NULL = "from edu.mnscu.housing.vo.TestHbnNull as thn where thn.myString3 =:ms3"+" and thn.myString4 = :ms4";
Object[] vals = {ms3, ms4};
Type[] types = {Hibernate.STRING, Hibernate.STRING};
list = session.find(Q_TEST_HBN_NULL, vals, types );

//logs – row1 , the preceding column my_string2 is ‘str2’, my_integer returns correctly. Row2 , the preceding column my_string2 is NULL, my integer returns null.

[net.sf.hibernate.loader.Loader] Hydrating entity: edu.mnscu.housing.vo.TestHbnNull#1
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.StringType] returning null as column: my_string1
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.StringType] returning 'str2 ' as column: my_string2
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.IntegerType] returning '100' as column: my_Integer
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.StringType] returning 'str3 ' as column: my_string3
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.TimestampType] returning null as column: my_timew6_
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.StringType] returning 'str4 ' as column: my_string4
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.TimestampType] returning null as column: my_times8_
2005-09-20 09:44:14,739 DEBUG [org.jboss.mx.loading.UnifiedClassLoader] New jmx UCL with url null
2005-09-20 09:44:14,739 DEBUG [org.jboss.mx.loading.UnifiedClassLoader] setRepository, r=org.jboss.mx.loading.HeirarchicalLoaderRepository3@796e1c, ucl=org.jboss.mx.loading.UnifiedClassLoader3@1feab48{ url=null ,addedOrder=0}
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.LongType] returning '2' as column: test_hbn1_
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.loader.Loader] result row: 2
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.loader.Loader] Initializing object from ResultSet: 2
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.loader.Loader] Hydrating entity: edu.mnscu.housing.vo.TestHbnNull#2
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.StringType] returning 'str1 ' as column: my_string1
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.StringType] returning null as column: my_string2
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.IntegerType] returning null as column: my_Integer
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.StringType] returning 'str3 ' as column: my_string3
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.TimestampType] returning null as column: my_timew6_
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.StringType] returning 'str4 ' as column: my_string4
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.type.TimestampType] returning null as column: my_times8_
2005-09-20 09:44:14,739 DEBUG [net.sf.hibernate.loader.Loader] done processing result set (2 rows)
2005-09-20 09:44:14,786 DEBUG [net.sf.hibernate.impl.BatcherImpl] done closing: 0 open PreparedStatements, 0 open ResultSets
2005-09-20 09:44:14,786 DEBUG [net.sf.hibernate.impl.BatcherImpl] closing statement
2005-09-20 09:44:14,801 DEBUG [net.sf.hibernate.loader.Loader] total objects hydrated: 2

//The column 'my_integer' of row 2 returns correctly in oracle.rdb.jdbc.common.ResultSet , see the following logs , result row (2) : column my_integer=100


14:28:54,083 INFO [STDOUT] connection database MetaData
14:28:54,083 INFO [STDOUT] database :Oracle Rdb , Version :V7.1-411
, JDBC driver name : Oracle Rdb Native Thin JDBC Driver,v71, nullPlusNonNullsNu
ll =true,nullSortedAtStart=false, nullSortedAtEnd=false
14:28:54,192 INFO [STDOUT] ResultSetMetaData info --
rsmeta.getColumnCount()=8
rsmeta.toString()=oracle.rdb.jdbc.common.ResultSetMetaData@14da0ba
14:28:54,270 INFO [STDOUT] rs.toString()=oracle.rdb.jdbc.common.ResultSet@1731b
2f
14:28:54,270 INFO [STDOUT] result row (1)
14:28:54,270 INFO [STDOUT] test_hbn_null_id =1
my_string1=null
my_string2=str2
my_integer=100
my_string3=str3
my_timestamp1=null
my_string4=str4
my_timestamp2=null
14:28:54,270 INFO [STDOUT] result row (2)
14:28:54,270 INFO [STDOUT] test_hbn_null_id =2
my_string1=str1
my_string2=null
my_integer=100
my_string3=str3
my_timestamp1=null
my_string4=str4
my_timestamp2=null
14:28:54,348 INFO [Engine] StandardContext[/housing] Velocity [info] Resource

//code
conn = session.connection();
…
PreparedStatement pstmt = conn.prepareStatement(
"select * from test_hbn_null where my_string3 = ? and my_string4 = ? ");
pstmt.setString(1, ms3);
pstmt.setString(2, ms4);

….
ResultSetMetaData rsmeta = pstmt.getMetaData();

System.out.println("ResultSetMetaData info --"+
"\nrsmeta.getColumnCount()=" + rsmeta.getColumnCount() +
"\nrsmeta.toString()=" + rsmeta.toString());

ResultSet rs = pstmt.executeQuery();
System.out.println("rs.toString()=" + rs.toString()); //oracle.rdb.jdbc.common.ResultSet
int row =1;
while ( rs.next()){
System.out.println(" result row (" + row + ")");

int id = rs.getInt(1);
String my_string1 = rs.getString(2);
String my_string2 = rs.getString(3);
int my_integer = rs.getInt(4);
String my_string3 = rs.getString(5);
Date my_timestamp1 = rs.getTimestamp(6);
String my_string4 = rs.getString(7);
Date my_timestamp2 = rs.getTimestamp(8);

System.out.println("test_hbn_null_id =" + id +
"\nmy_string1=" + my_string1 +
"\nmy_string2=" + my_string2 +
"\nmy_integer=" + my_integer +
"\nmy_string3=" + my_string3 +
"\nmy_timestamp1=" + my_timestamp1 +
"\nmy_string4=" + my_string4+
"\nmy_timestamp2=" +my_timestamp2 );
row++;
}

I'd go JIRA to file a bug report because it's significant for us and we've been struggling with nulls since the very beginning. Now we realized when we get around one null the 'Worm Null' moves to another field we didn't get/detect it before .

One minor remark: to get the Hibernate version (certainly a very important information when posting a bug report): read it from hibernate2.jar's manifest file.

Sorry to be of so little help...

Erik

I've double checked it , here is all in manifest file ...

Manifest-Version: 1.0
Ant-Version: Apache Ant 1.5.4
Created-By: 1.4.2_03-b02 (Sun Microsystems Inc.)

I download the hibernate-2.0 and get the same issue on Oracle RDB.

We test it against Oracle 9.2 with the same data set , it works fine. We dig into a little bit and found hibernate got a good ResultSet at some point and screwed up later if the back-end is Oracle RDB.

So apparently it's database/jdbc driver related, not quite sure to which we should file a report. I'd like to start with Hibernate if Hibernate supports Oracle RDB.

the version number is printed when hibernate starts.

it really sounds like a bug in the driver if it only occurs on some db's - the code reading from the resultset is the same for all dialects.

/max

Thanks for the reply. We agreed. I think hibernate should do something with oracle rdb driver since that's the communication between hibernate code and rdb driver. It shouldn't be our call even we're interested to dig into what from rdb driver screwed up hibernate result.

So i'll file a bug report on JIRA.
p.s. I'm just done my test with hibernate-3.0.5 - same result.

eh, I wrote that i considered it a bug in the driver, not hibernate.

Don't think oracle has a jira do they ? :)

It doesn't mean the bug is in hibernate, hibernate may want to find what's wrong on driver and forward that to Oracle.

Again, we're able to get a good ResultSet from rdb driver without hibernate involved. The problem occurred only on hibernate + rdb.

we can't forward anything to oracle, that is up to oracles customers to do that.

and besides this is oracle 7 you are running this on correct ? Not much we can do about that.

Have you considered upgrading the database drivers ? They normally have quite good back compability drivers at oracle.

/max

Does it mean hibernates customers should figure out why some jdbc drivers are not working well with hibernate?

If thinking about the dependencies, shouldn't the persistence layer (e.g. hibernte) detect jdbc drivers?

Anyhow we'll try to dig into and figure that out.

As far as I can see you are a Hibernate community user, not a customer ;)

Just because you use a persistence layer you should not be ignorant to the other parts of the stack below. You don't just start using any database vendor without checking if it obeys your requirements.

We at the hibernate team actually do check jdbc drivers, but only for the *supported* databases and definitly not databases that are three versions behind the current version (oracle 7 in your case, correct ?)

And as far as I can tell I already wrote to you to use/try a more uptodate driver - can't see how we as free opensource community persistence layer should solve issues with old 3rd party drivers if there exist more uptodate drivers which might already have the bug fixed ?

p.s. Hibernate *customers* pay for support (see http://www.hibernate.org/148.html)

That's the question i want to ask: Does Hibernate *support * Oracle RDB database?
The database we're using is not oracle 7 as we usually speak, see http://www.oracle.com/technology/products/rdb/index.html , just in case it's confusing you.

The current databse we are using : Oracle Rdb , Version :V7.1-411.
Driver : Oracle Rdb Native Thin JDBC Driver, version: V7.1-100
They are newer version in RDB world not oracle.

We'll try to get the latest rdb driver and see. Thanks for the info about customers support we'll think about it.

We don't currently have direct support for Oracle RDB (sorry i missed that part)

But I also can see that it has had some recent releases where such quirky jdbc issues might be solved, so try that.

-max

Thanks very much for the info. We should double check Hibernate Matrix before continuing to work on it .