possible bug (JdbcSQLException: Parameter #16 is not set)

[Andy]

I am getting an error. Hibernate is generating the SQL statement. It
works on HSQL and on Oracle. But it doesn't work with H2. I am
having issues with HSQL for testing and would like to switch to H2.
But this issue is preventing the change. Any help is appreciated.
Included is the first part of the stacktrace and the Hibernate mapping
file. I assume this is caused by using the "discriminator" in the
hibernate map. Please let me know if more information is required.

org.springframework.jdbc.UncategorizedSQLException: Hibernate
operation: could not insert: [gov.va.med.mhv.sm.model.Patient];
uncategorized SQLException for SQL [insert into SMS_USER (OPLOCK,
ACTIVE, CREATED_DATE, MODIFIED_DATE, LAST_NAME, FIRST_NAME,
EMAIL_ADDRESS, EMAIL_NOTIFICATION, DEFAULT_MESSAGE_FILTER,
LAST_EMAIL_NOTIFICATION, STATUS, SSN, NSSN, DOB, ICN, USER_TYPE,
USER_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?)];
SQL state [90012]; error code [90012]; Parameter #16 is not set
[90012-66]; nested exception is org.h2.jdbc.JdbcSQLException:
Parameter #16 is not set [90012-66]
Caused by: org.h2.jdbc.JdbcSQLException: Parameter #16 is not set
[90012-66]
at org.h2.message.Message.getSQLException(Message.java:89)
at org.h2.message.Message.getSQLException(Message.java:93)
at org.h2.message.Message.getSQLException(Message.java:71)
at org.h2.expression.Parameter.checkSet(Parameter.java:65)





<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD
3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>

<typedef name="UserType" class="model.type.GenericEnumUserType">
<param name="enumClass">enumeration.UserTypeEnum</param>
</typedef>

<typedef name="UserStatusType"
class="model.type.GenericEnumUserType">
<param name="enumClass">enumeration.UserStatusEnum</param>
</typedef>

<typedef name="EmailNotificationType"
class="model.type.GenericEnumUserType">
<param name="enumClass">enumeration.EmailNotificationEnum</
param>
</typedef>

<typedef name="MessageFilterType"
class="model.type.GenericEnumUserType">
<param name="enumClass">enumeration.MessageFilterEnum</param>
</typedef>

<class name="model.User"
table="SMS_USER"
optimistic-lock="version"
select-before-update="true"
dynamic-update="true"
discriminator-value="-1">

<!-- begin inherited fields -->
<id name="id" column="USER_ID" type="long">
<generator class="native">
<param name="sequence">GEN_SMS_SEQ</param>
</generator>
</id>

<!-- DISCRIMINATOR this is position dependent; must be after <id/> --
>
<discriminator column="USER_TYPE" type="long"/>
<!-- END DISCRIMINATOR -->

<version column="OPLOCK" name="oplock" type="long"/>
<property name="active" type="boolean">
<column name="ACTIVE" precision="1" scale="0" not-
null="true" />
</property>
<property name="createdDate" type="timestamp" insert="true"
update="false">
<column name="CREATED_DATE"/>
</property>
<property name="modifiedDate" type="timestamp">
<column name="MODIFIED_DATE"/>
</property>
<!-- inherited fields -->

<property name="lastName" type="string">
<column name="LAST_NAME" length="50"/>
</property>

<property name="firstName" type="string">
<column name="FIRST_NAME" length="50"/>
</property>

<property name="email" type="string">
<column name="EMAIL_ADDRESS" length="100"/>
</property>

<property name="emailNotification"
type="EmailNotificationType">
<column name="EMAIL_NOTIFICATION"/>
</property>

<property name="messageFilter" type="MessageFilterType">
<column name="DEFAULT_MESSAGE_FILTER"/>
</property>

<property name="lastNotification" type="timestamp">
<column name="LAST_EMAIL_NOTIFICATION"/>
</property>

<property name="status" type="UserStatusType">
<column name="STATUS"/>
</property>

<property name="ssn" type="string">
<column name="SSN"/>
</property>

<property name="nssn" type="string">
<column name="NSSN" length="5"/>
</property>

</class>

<subclass name="model.Patient"
extends="model.User"
discriminator-value="0">
<property name="dob" type="date">
<column name="DOB" not-null="false"/>
</property>
<property name="icn" type="string">
<column name="ICN" not-null="false"/>
</property>
<bag name="facilities" cascade="all">
<key column="USER_ID" not-null="true" />
<one-to-many class="model.PatientFacility" />
</bag>
</subclass>

<subclass name="model.Clinician"
extends="model.User"
discriminator-value="1">
<property name="stationNo" type="string">
<column name="STATION_NO" not-null="false"/>
</property>
<property name="duz" type="string">
<column name="DUZ" not-null="false"/>
</property>
</subclass>

<subclass name="model.Administrator"
extends="model.User"
discriminator-value="2">
</subclass>




</hibernate-mapping>

[Thomas Mueller]

Hi,

Could you run it again using the JDBC URL of the form:

jdbc:h2:test;TRACE_LEVEL_FILE=3

Afterwards you will get a fairly big .trace.db file. Could you compress this file and post it (or send it to me)?

Regards,
Thomas

[Andy]

Well, this may be something I am doing wrong on my part. It has
nothing to do with the discriminator. I eliminated that.
This seems to be the problem.

I am initializing my test data with dbunit.
<SMS_USER USER_ID="156" ...../>
<SMS_USER USER_ID="179" ...../>
<SMS_USER USER_ID="180" ...../>
<SMS_USER USER_ID="181" ...../>
.....

but my hibernate ID is mapped as such

<id name="id" column="USER_ID" type="long">
<generator class="native">
<param name="sequence">GEN_SMS_SEQ</param>
</generator>
</id>

This setup results in the stacktrace posted earlier. I am sending the
trace file directly to you.

However, when I change my hibernate id to "increment" everything
works. It doesn't work with "identity". I assume that this is an
error in my environment and that I will need to create a sequence to
use before the tests kick off.

Andy

[Thomas Mueller]

Hi,

Thanks for the trace file. I'm not sure where the problem is, so far it doesn't seem to be in the database. From the trace file (only the relevant parts):

PreparedStatement prep56 = conn1.prepareStatement("insert into SMS_USER

(OPLOCK, ACTIVE, CREATED_DATE, MODIFIED_DATE, LAST_NAME, FIRST_NAME,
EMAIL_ADDRESS, EMAIL_NOTIFICATION, DEFAULT_MESSAGE_FILTER,
LAST_EMAIL_NOTIFICATION, STATUS, SSN, NSSN, DOB, ICN, USER_TYPE, USER_ID)

values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?)", 1);
prep56.setLong(1, 0L);
prep56.setBoolean(2, true);
prep56.setTimestamp(3, Timestamp.valueOf("2008-02-13 15:01:09.732"));
prep56.setTimestamp(4, Timestamp.valueOf("2008-02-13 15:01:09.732"));
prep56.setString(5, "Granger");
prep56.setString(6, "Hermione");
prep56.setNull(7, 12);
prep56.setLong(8, 0L);
prep56.setLong(9, 0L);
prep56.setNull(10, 93);
prep56.setLong(11, 0L);
prep56.setNull(12, 12);
prep56.setNull(13, 12);
prep56.setNull(14, 91);
prep56.setNull(15, 12);
prep56.executeUpdate();
SQLException: org.h2.jdbc.JdbcSQLException: Parameter #16 is not set [90012-64]

It seems Hibernate creates a prepared statement with 16 parameters, but then only fills 15. H2 supports both sequences and auto-increment columns, and Hibernate does that as well... So this is strange, but I don't think I can solve the problem at the moment.

Regards,
Thomas

[Andy]

I was under the same impression but it seems that Oracle handles this
in some fashion.

After some playing around I got it to work like I wanted.

I was originally told to map the generator in my ID columns in
hibernate this way.
And while this works with Oracle (and HSQL seems to ignore it), it
sends the
apparently malformed statement to H2.

<generator class="native">
<param name="sequence">GEN_SMS_SEQ</param>
</generator>

When I changed it to this, everything works.

<generator class="sequence">

<param name="sequence">GEN_SMS_SEQ</param>
</generator>

So at this time I am good.
Thanks for your help.
Andy