-->
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
  [ 3 posts ] 
  Previous topic | Next topic 
Author Message
Myk
 Post subject: SQL server and parameter collections
PostPosted: Tue Apr 13, 2004 6:27 am 
Senior
Senior

Joined: Sun Jan 04, 2004 2:46 pm
Posts: 147
Info:

Hibernate 2.1.1
SQLServer 2000
Driver jTDS version 0.8

I am having a problem with Query.setParameterList( .. ) running on SQLServer. The query generated isn't accepted by the DB and gives the following error/stacktrace.

Code:
java.sql.SQLException: Line 1: Incorrect syntax near '@P1'.
   at net.sourceforge.jtds.jdbc.SqlMessage.toSQLException(SqlMessage.java:85)
   at net.sourceforge.jtds.jdbc.SQLWarningChain.addOrReturn(SQLWarningChain.java:99)
   at net.sourceforge.jtds.jdbc.Tds.submitProcedure(Tds.java:331)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.submitProcedure(PreparedStatement_base.java:190)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.findOrCreateProcedure(PreparedStatement_base.java:175)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.execute(PreparedStatement_base.java:131)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.executeQuery(PreparedStatement_base.java:258)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
   at net.sf.hibernate.loader.Loader.list(Loader.java:940)
   at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
   at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
   at xxx.TaskManagerDelegator.getStreamStageCount(TaskManagerDelegator.java:254)
   at xxx.TaskManagerDelegator.doDelegateTasks(TaskManagerDelegator.java:169)
   at xxx.TaskManagerDelegator.run(TaskManagerDelegator.java:131)
   at java.lang.Thread.run(Thread.java:566)
DEBUG [xxx.StreamController] [Calculus Socket] (StreamController.java:255) - Task 1 completed successfully
xxx.TaskManagerException: Error getting running task stream stages
   at xxx.TaskManagerDelegator.getStreamStageCount(TaskManagerDelegator.java:273)
   at xxx.TaskManagerDelegator.doDelegateTasks(TaskManagerDelegator.java:169)
   at xxx.TaskManagerDelegator.run(TaskManagerDelegator.java:131)
   at java.lang.Thread.run(Thread.java:566)
Caused by: net.sf.hibernate.JDBCException: Could not execute query
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1478)
   at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
   at xxx.TaskManagerDelegator.getStreamStageCount(TaskManagerDelegator.java:254)
   ... 3 more
Caused by: java.sql.SQLException: Line 1: Incorrect syntax near '@P1'.
   at net.sourceforge.jtds.jdbc.SqlMessage.toSQLException(SqlMessage.java:85)
   at net.sourceforge.jtds.jdbc.SQLWarningChain.addOrReturn(SQLWarningChain.java:99)
   at net.sourceforge.jtds.jdbc.Tds.submitProcedure(Tds.java:331)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.submitProcedure(PreparedStatement_base.java:190)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.findOrCreateProcedure(PreparedStatement_base.java:175)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.execute(PreparedStatement_base.java:131)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.executeQuery(PreparedStatement_base.java:258)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
   at net.sf.hibernate.loader.Loader.list(Loader.java:940)
   at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
   ... 5 more


Hibernate debug log

Code:
DEBUG [net.sf.hibernate.impl.SessionImpl] [Task manager delegator] (SessionImpl.java:528) - opened session
DEBUG [net.sf.hibernate.impl.SessionImpl] [Task manager delegator] (SessionImpl.java:1460) - find: select t.StreamStageDfn.id, count(*) from Task t where t.TskId in :runningtasklist0_ group by t.StreamStageDfn
DEBUG [net.sf.hibernate.engine.QueryParameters] [Task manager delegator] (QueryParameters.java:108) - named parameters: {runningtasklist0_=1}
DEBUG [net.sf.hibernate.hql.QueryTranslator] [Task manager delegator] (QueryTranslator.java:147) - compiling query
DEBUG [net.sf.hibernate.impl.SessionImpl] [Task manager delegator] (SessionImpl.java:2193) - flushing session
DEBUG [net.sf.hibernate.impl.SessionImpl] [Task manager delegator] (SessionImpl.java:2321) - Flushing entities and processing referenced collections
DEBUG [net.sf.hibernate.impl.SessionImpl] [Task manager delegator] (SessionImpl.java:2664) - Processing unreferenced collections
DEBUG [net.sf.hibernate.impl.SessionImpl] [Task manager delegator] (SessionImpl.java:2678) - Scheduling collection removes/(re)creates/updates
DEBUG [net.sf.hibernate.impl.SessionImpl] [Task manager delegator] (SessionImpl.java:2217) - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
DEBUG [net.sf.hibernate.impl.SessionImpl] [Task manager delegator] (SessionImpl.java:2222) - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
DEBUG [net.sf.hibernate.impl.SessionImpl] [Task manager delegator] (SessionImpl.java:1745) - Dont need to execute flush
DEBUG [net.sf.hibernate.hql.QueryTranslator] [Task manager delegator] (QueryTranslator.java:199) - HQL: select t.StreamStageDfn.id, count(*) from xxx.Task t where t.TskId in :runningtasklist0_ group by t.StreamStageDfn
DEBUG [net.sf.hibernate.hql.QueryTranslator] [Task manager delegator] (QueryTranslator.java:200) - SQL: select task0_.ssd_id as x0_0_, count(*) as x1_0_ from task task0_ where (task0_.tsk_id in ? ) group by  task0_.ssd_id
DEBUG [net.sf.hibernate.impl.SessionImpl] [Finalizer] (SessionImpl.java:3239) - running Session.finalize()
DEBUG [net.sf.hibernate.impl.BatcherImpl] [Task manager delegator] (BatcherImpl.java:192) - about to open: 0 open PreparedStatements, 0 open ResultSets
DEBUG [net.sf.hibernate.SQL] [Task manager delegator] (BatcherImpl.java:223) - select task0_.ssd_id as x0_0_, count(*) as x1_0_ from task task0_ where (task0_.tsk_id in ? ) group by  task0_.ssd_id
DEBUG [net.sf.hibernate.impl.BatcherImpl] [Task manager delegator] (BatcherImpl.java:227) - preparing statement
DEBUG [net.sf.hibernate.type.IntegerType] [Task manager delegator] (NullableType.java:46) - binding '1' to parameter: 1
DEBUG [net.sf.hibernate.util.JDBCExceptionReporter] [Task manager delegator] (JDBCExceptionReporter.java:36) - SQL Exception
java.sql.SQLException: Line 1: Incorrect syntax near '@P1'.
   at net.sourceforge.jtds.jdbc.SqlMessage.toSQLException(SqlMessage.java:85)
   at net.sourceforge.jtds.jdbc.SQLWarningChain.addOrReturn(SQLWarningChain.java:99)
   at net.sourceforge.jtds.jdbc.Tds.submitProcedure(Tds.java:331)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.submitProcedure(PreparedStatement_base.java:190)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.findOrCreateProcedure(PreparedStatement_base.java:175)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.execute(PreparedStatement_base.java:131)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.executeQuery(PreparedStatement_base.java:258)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
   at net.sf.hibernate.loader.Loader.list(Loader.java:940)
   at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
   at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
   at xxx.TaskManagerDelegator.getStreamStageCount(TaskManagerDelegator.java:254)
   at xxx.TaskManagerDelegator.doDelegateTasks(TaskManagerDelegator.java:169)
   at xxx.TaskManagerDelegator.run(TaskManagerDelegator.java:131)
   at java.lang.Thread.run(Thread.java:566)
WARN [net.sf.hibernate.util.JDBCExceptionReporter] [Task manager delegator] (JDBCExceptionReporter.java:38) - SQL Error: 170, SQLState: S1000
ERROR [net.sf.hibernate.util.JDBCExceptionReporter] [Task manager delegator] (JDBCExceptionReporter.java:46) - Line 1: Incorrect syntax near '@P1'.
DEBUG [net.sf.hibernate.impl.BatcherImpl] [Task manager delegator] (BatcherImpl.java:199) - done closing: 0 open PreparedStatements, 0 open ResultSets
DEBUG [net.sf.hibernate.impl.BatcherImpl] [Task manager delegator] (BatcherImpl.java:240) - closing statement
DEBUG [net.sf.hibernate.util.JDBCExceptionReporter] [Task manager delegator] (JDBCExceptionReporter.java:36) - SQL Exception
java.sql.SQLException: Line 1: Incorrect syntax near '@P1'.
   at net.sourceforge.jtds.jdbc.SqlMessage.toSQLException(SqlMessage.java:85)
   at net.sourceforge.jtds.jdbc.SQLWarningChain.addOrReturn(SQLWarningChain.java:99)
   at net.sourceforge.jtds.jdbc.Tds.submitProcedure(Tds.java:331)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.submitProcedure(PreparedStatement_base.java:190)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.findOrCreateProcedure(PreparedStatement_base.java:175)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.execute(PreparedStatement_base.java:131)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.executeQuery(PreparedStatement_base.java:258)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
   at net.sf.hibernate.loader.Loader.list(Loader.java:940)
   at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
   at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
   at xxx.TaskManagerDelegator.getStreamStageCount(TaskManagerDelegator.java:254)
   at xxx.TaskManagerDelegator.doDelegateTasks(TaskManagerDelegator.java:169)
   at xxx.TaskManagerDelegator.run(TaskManagerDelegator.java:131)
   at java.lang.Thread.run(Thread.java:566)
WARN [net.sf.hibernate.util.JDBCExceptionReporter] [Task manager delegator] (JDBCExceptionReporter.java:38) - SQL Error: 170, SQLState: S1000
ERROR [net.sf.hibernate.util.JDBCExceptionReporter] [Task manager delegator] (JDBCExceptionReporter.java:46) - Line 1: Incorrect syntax near '@P1'.
ERROR [net.sf.hibernate.util.JDBCExceptionReporter] [Task manager delegator] (JDBCException.java:38) - Could not execute query
java.sql.SQLException: Line 1: Incorrect syntax near '@P1'.
   at net.sourceforge.jtds.jdbc.SqlMessage.toSQLException(SqlMessage.java:85)
   at net.sourceforge.jtds.jdbc.SQLWarningChain.addOrReturn(SQLWarningChain.java:99)
   at net.sourceforge.jtds.jdbc.Tds.submitProcedure(Tds.java:331)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.submitProcedure(PreparedStatement_base.java:190)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.findOrCreateProcedure(PreparedStatement_base.java:175)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.execute(PreparedStatement_base.java:131)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.executeQuery(PreparedStatement_base.java:258)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
   at net.sf.hibernate.loader.Loader.list(Loader.java:940)
   at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
   at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
   at xxx.TaskManagerDelegator.getStreamStageCount(TaskManagerDelegator.java:254)
   at xxx.TaskManagerDelegator.doDelegateTasks(TaskManagerDelegator.java:169)
   at xxx.TaskManagerDelegator.run(TaskManagerDelegator.java:131)
   at java.lang.Thread.run(Thread.java:566)
DEBUG [net.sf.hibernate.impl.SessionImpl] [Task manager delegator] (SessionImpl.java:546) - closing session
DEBUG [net.sf.hibernate.impl.SessionImpl] [Task manager delegator] (SessionImpl.java:3187) - disconnecting session
DEBUG [net.sf.hibernate.impl.SessionImpl] [Task manager delegator] (SessionImpl.java:558) - transaction completion
xxx.TaskManagerException: Error getting running task stream stages
   at xxx.TaskManagerDelegator.getStreamStageCount(TaskManagerDelegator.java:273)
   at xxx.TaskManagerDelegator.doDelegateTasks(TaskManagerDelegator.java:169)
   at xxx.TaskManagerDelegator.run(TaskManagerDelegator.java:131)
   at java.lang.Thread.run(Thread.java:566)
Caused by: net.sf.hibernate.JDBCException: Could not execute query
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1478)
   at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
   at xxx.TaskManagerDelegator.getStreamStageCount(TaskManagerDelegator.java:254)
   ... 3 more
Caused by: java.sql.SQLException: Line 1: Incorrect syntax near '@P1'.
   at net.sourceforge.jtds.jdbc.SqlMessage.toSQLException(SqlMessage.java:85)
   at net.sourceforge.jtds.jdbc.SQLWarningChain.addOrReturn(SQLWarningChain.java:99)
   at net.sourceforge.jtds.jdbc.Tds.submitProcedure(Tds.java:331)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.submitProcedure(PreparedStatement_base.java:190)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.findOrCreateProcedure(PreparedStatement_base.java:175)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.execute(PreparedStatement_base.java:131)
   at net.sourceforge.jtds.jdbc.PreparedStatement_base.executeQuery(PreparedStatement_base.java:258)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
   at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
   at net.sf.hibernate.loader.Loader.list(Loader.java:940)
   at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
   ... 5 more


executing code

Code:
            Query q = session.createQuery( "select t.StreamStageDfn.id, count(*) from Task t where t.TskId in :runningtasklist group by t.StreamStageDfn" );

            q.setParameterList( "runningtasklist", runningTasks );
            List results = q.list();


Mappings

Code:
  <class name="Task" table="task" lazy="true" >

    <id name="TskId" type="int" column="tsk_id" unsaved-value="null" >
      <generator class="assigned" />
    </id>
    <version name="VersionId" column="tsk_version_id" type="int" />

    <property name="TskStatus" column="tsk_status" type="string" not-null="true" />
    <property name="TskPriority" column="tsk_priority" type="int" not-null="true" />
    <property name="SsdId" column="ssd_id" type="int" not-null="true" />

    <many-to-one name="StreamStageDfn" class="StreamStageDfn" column="ssd_id" update="false" insert="false" />

  </class>

  <class name="StreamStageDfn" table="stream_stage_dfn" lazy="true" >

    <id name="SsdId" type="int" column="ssd_id" unsaved-value="null" >
      <generator class="assigned" />
    </id>
    <version name="VersionId" column="ssd_version_id" type="int" />

  </class>


The query seems to be creating the clause with "in @P1" and DB doesn't like the @P1 next to the in clause. If I replace @P1 with the actual parameter,( 1 ), then it works fine.

Here's what jTDS generates. It creates a PROC and runs it after

Code:
create proc #jdbc#8(@P1 integer) as
select task0_.ssd_id as x0_0_, count(*) as x1_0_ from task task0_ where (task0_.tsk_id in @P1 ) group by  task0_.ssd_id


I tried the Microsoft driver with the same error but different type of output

Code:
declare @P1 int
set @P1=0
declare @P2 int
set @P2=4100
declare @P3 int
set @P3=8193
declare @P4 int
set @P4=0
exec sp_cursoropen @P1 output, N'select task0_.ssd_id as x0_0_, count(*) as x1_0_ from task task0_ where (task0_.tsk_id in @P1 ) group by  task0_.ssd_id', @P2 output, @P3 output, @P4 output, N'@P1 int ', 1
select @P1, @P2, @P3, @P4


Any help or ideas would be appreciated.

Cheers.

Myk.
Top
 Profile  
 
alin_sinpalean
 Post subject:
PostPosted: Wed Apr 14, 2004 7:09 am 
jTDS Developer
jTDS Developer

Joined: Tue Feb 24, 2004 5:36 pm
Posts: 70
Location: Bucharest, Romania
Myk,

According to the Hibernate Reference, section 8.3.2 you should place parantheses around the list of values for "in". E.g.:

Code:
Query q = sess.createQuery("from DomesticCat cat where cat.name in (:namesList)");

I think that Hibernate could check this and not allow it to get through to JDBC, but the problem lies in your code anyway.

Alin.
Top
 Profile  
 
Myk
 Post subject:
PostPosted: Fri Apr 16, 2004 6:27 am 
Senior
Senior

Joined: Sun Jan 04, 2004 2:46 pm
Posts: 147
Thanks for spotting that for me. :)

Myk.
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
  [ 3 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.