-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
frenchfrywpepper
 Post subject: Can I bind mult params to PreparedStatement on single column
PostPosted: Tue May 16, 2006 5:02 pm 
Newbie

Joined: Tue May 16, 2006 4:38 pm
Posts: 4
I am attempting to use the custom sql-insert so that I may call an SQL function, which takes 2 parameters, to be the value for a single property/column. As you can see in the Hibernate Debug output, the parameter index is incremented by 1 for each property/column, Hibernate has no way to know that I have bound index and index+1 in the nullSafeSet method of my custom UserType. Therefore parameter 22 is written twice and parameter 24 is not written at all leading to the Oracle exception (ORA-01008: not all variables bound).

Is there any way I can tell Hibernate to increment the next index by 2, or am I taking the wrong approach to this problem?

Hibernate version: 3.1.3

Mapping documents:

...
<property column="SHAPE" name="coordinatePoint" type="gov.nyc.doitt.gis.pt.dao.hibernate.CoordinatePointUserType"/>

<sql-insert>INSERT INTO RESERVATION (POLE_ID, COMPANY_ID, STATUS, NOTES, REF_ID, CHECKOUT_DATE, DECISION_DATE, X_COORD, Y_COORD, DISTANCE, DIRECTION, CROSS_STREET1, ON_STREET, CROSS_STREET2, POLE_TYPE, LOCATION_TYPE, SURFACE_TYPE, DESIGN_TYPE, BOROUGH, OWNER_ID, SHAPE, CHECKOUT_NOTIFIED, RESERVATION_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, MDSYS.SDO_GEOMETRY (2001, 41088, MDSYS.SDO_POINT_TYPE (?, ?, NULL), NULL, NULL), ?, ?)</sql-insert>
...

Full stack trace of any exception that occurs:

java.sql.BatchUpdateException: ORA-01008: not all variables bound

at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:458)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:3907)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeBatch(DelegatingPreparedStatement.java:231)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:91)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:86)
at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:171)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2048)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2427)
at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:51)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:41)
at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:954)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1099)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:831)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:366)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:822)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:818)
at gov.nyc.doitt.gis.pt.dao.hibernate.HibernateReservationDao.getReservationsForSamePole(HibernateReservationDao.java:238)
at gov.nyc.doitt.gis.pt.dao.hibernate.TxSupportHibernateReservationDaoTest.testGetReservationsByPole(TxSupportHibernateReservationDaoTest.java:515)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
2006-05-16 16:35:39,306 WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 1008, SQLState: 72000
2006-05-16 16:35:39,306 ERROR org.hibernate.util.JDBCExceptionReporter - ORA-01008: not all variables bound

2006-05-16 16:35:39,306 WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 1008, SQLState: 72000
2006-05-16 16:35:39,306 ERROR org.hibernate.util.JDBCExceptionReporter - ORA-01008: not all variables bound

2006-05-16 16:35:39,306 ERROR org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:91)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:86)
at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:171)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2048)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2427)
at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:51)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:41)
at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:954)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1099)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:831)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:366)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:822)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:818)
at gov.nyc.doitt.gis.pt.dao.hibernate.HibernateReservationDao.getReservationsForSamePole(HibernateReservationDao.java:238)
at gov.nyc.doitt.gis.pt.dao.hibernate.TxSupportHibernateReservationDaoTest.testGetReservationsByPole(TxSupportHibernateReservationDaoTest.java:515)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.BatchUpdateException: ORA-01008: not all variables bound

at oracle.jdbc.dbaccess.DBError.throwBatchUpdateException(DBError.java:458)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:3907)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeBatch(DelegatingPreparedStatement.java:231)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
... 35 more

Name and version of the database you are using:

Oracle 9i

The generated SQL (show_sql=true):

INSERT INTO RESERVATION (POLE_ID, COMPANY_ID, STATUS, NOTES, REF_ID, CHECKOUT_DATE, DECISION_DATE, X_COORD, Y_COORD, DISTANCE, DIRECTION, CROSS_STREET1, ON_STREET, CROSS_STREET2, POLE_TYPE, LOCATION_TYPE, SURFACE_TYPE, DESIGN_TYPE, BOROUGH, OWNER_ID, SHAPE, CHECKOUT_NOTIFIED, RESERVATION_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, MDSYS.SDO_GEOMETRY (2001, 41088, MDSYS.SDO_POINT_TYPE (?, ?, NULL), NULL, NULL), ?, ?)

Debug level Hibernate log excerpt:

2006-05-16 16:53:06,069 DEBUG org.hibernate.type.IntegerType - binding '2112' to parameter: 1
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.IntegerType - binding '1' to parameter: 2
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.StringType - binding 'SHOP_CART' to parameter: 3
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.StringType - binding null to parameter: 4
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.StringType - binding null to parameter: 5
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.DateType - binding null to parameter: 6
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.DateType - binding null to parameter: 7
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.DoubleType - binding '993000.0' to parameter: 8
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.DoubleType - binding '227000.0' to parameter: 9
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.DoubleType - binding '2.0' to parameter: 10
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.IntegerType - binding '1' to parameter: 11
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.StringType - binding 'W. 99th Street' to parameter: 12
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.StringType - binding 'Amsterdam Avenue' to parameter: 13
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.StringType - binding 'W. 100th Street' to parameter: 14
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.StringType - binding 'STREETLIGHT' to parameter: 15
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.StringType - binding 'MID_BLOCK' to parameter: 16
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.StringType - binding 'PAVEMENT' to parameter: 17
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.StringType - binding 'OTHER' to parameter: 18
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.IntegerType - binding '1' to parameter: 19
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.IntegerType - binding '5' to parameter: 20
2006-05-16 16:53:06,069 DEBUG gov.nyc.doitt.gis.pt.dao.hibernate.CoordinatePointUserType - binding '993000.0' to parameter: 21
2006-05-16 16:53:06,069 DEBUG gov.nyc.doitt.gis.pt.dao.hibernate.CoordinatePointUserType - binding '227000.0' to parameter: 22
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.YesNoType - binding 'false' to parameter: 22
2006-05-16 16:53:06,069 DEBUG org.hibernate.type.IntegerType - binding '2124' to parameter: 23

UserType Code sample

Code:
public class CoordinatePointUserType implements UserType
{
    ...   
    private static final int[] SQL_TYPES = { Types.OTHER }; 
    ...
    public void nullSafeSet (PreparedStatement statement, Object value, int index) throws HibernateException, SQLException
    {
        int xCoordIndex = index;
        int yCoordIndex = index+1;
       
        if (value == null)
        {
            log.debug("binding NULL to parameter: " + xCoordIndex);
            statement.setNull(xCoordIndex, Types.NUMERIC);
            log.debug("binding NULL to parameter: " + yCoordIndex);
            statement.setNull(yCoordIndex, Types.NUMERIC);
        } else {
            CoordinatePoint coordinatePoint = (CoordinatePoint) value;
            log.debug("binding '" + coordinatePoint.getXCoordinate() + "' to parameter: " + xCoordIndex);
            statement.setDouble(xCoordIndex, coordinatePoint.getXCoordinate());
            log.debug("binding '" + coordinatePoint.getYCoordinate() + "' to parameter: " + yCoordIndex);
            statement.setDouble(yCoordIndex, coordinatePoint.getYCoordinate());
        }
    }
    ...
}
Top
 Profile  
 
r1ch
 Post subject:
PostPosted: Wed May 17, 2006 3:49 am 
Regular
Regular

Joined: Wed Aug 25, 2004 6:23 am
Posts: 91
I think that there are three problems here:-

Firstly, you need your UserType to implement CompositeUserType instead of UserType to allow it to access multiple columns.

Secondly, it should also return the types of both columns from the sqlTypes() method, so you'll need to change it to something like:

Code:
private static final int[] SQL_TYPES = { Types.NUMERIC, Types.NUMERIC };


Finally, you'll also need to map two columns for the property, eg:-

Code:
<property name="coordinatePoint" type="gov.nyc.doitt.gis.pt.dao.hibernate.CoordinatePointUserType">
   <column name="shape1" />
   <column name="shape2" />
</property>


See here for more info.

Hope that helps.
Cheers,
Rich.
Top
 Profile  
 
frenchfrywpepper
 Post subject: Can I bind mult params to PreparedStatement on single column
PostPosted: Wed May 17, 2006 9:09 am 
Newbie

Joined: Tue May 16, 2006 4:38 pm
Posts: 4
Thanks for taking the time to reply to my post. Looks like I need to clarify the situation a bit. I actually only have 1 column in the database...

Code:
SHAPE SDO_GEOMETRY NOT NULL


It is a special column type used in Oracle's Spatial db. To insert data into this column you must call some of Oracle's spatial functions, like this...

Code:
MDSYS.SDO_GEOMETRY (2001, 41088, MDSYS.SDO_POINT_TYPE (?, ?, NULL), NULL, NULL)


Hopefully this shows more clearly that there are 2 parameters required to insert the single column, SHAPE.

It may be true that CompositeCustomType would still be of some use to me, but I am not sure how, since I am mapping just a single column.
Top
 Profile  
 
frenchfrywpepper
 Post subject: Can I bind mult params to PreparedStatement on single column
PostPosted: Wed May 17, 2006 9:12 am 
Newbie

Joined: Tue May 16, 2006 4:38 pm
Posts: 4
Correction to my last post:

When I mentioned CompositeCustomType, I really meant CompositeUserType.
Top
 Profile  
 
r1ch
 Post subject:
PostPosted: Wed May 17, 2006 9:14 am 
Regular
Regular

Joined: Wed Aug 25, 2004 6:23 am
Posts: 91
Sorry - I missed that. Will Hibernate let you reference the same column twice for a user type?

Code:
<property name="coordinatePoint" type="gov.nyc.doitt.gis.pt.dao.hibernate.CoordinatePointUserType">
   <column name="SHAPE" />
   <column name="SHAPE" />
</property>
Top
 Profile  
 
frenchfrywpepper
 Post subject: Can I bind mult params to PreparedStatement on single column
PostPosted: Wed May 17, 2006 9:38 am 
Newbie

Joined: Tue May 16, 2006 4:38 pm
Posts: 4
I get the exact same problem when I map coordinatePoint to the SHAPE column twice. Probably because I only have one SHAPE in my insert.

I think what I need is a custom Type/Value. Since my property is not a collection or many-to-one, it defaults to SimpleValue, which returns a column span of 1, that might be the number that is used for incrementing the PreparedStatement index.

If I could specify a custom Type somewhere on my property, that in conjunction with the custom UserType might do the trick.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.