-->
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
  [ 5 posts ] 
  Previous topic | Next topic 
Author Message
bdog42
 Post subject: setMaxResults()
PostPosted: Mon Sep 06, 2004 2:12 pm 
Newbie

Joined: Mon Sep 06, 2004 1:40 pm
Posts: 2
Hi all, I'm having trouble getting setMaxResults to work on an ancient SQL Anywhere database. I've been able to make it work on Sybase 11.9.2/12.5 using the same JDBC driver, but there seems to be some hang up against sql any.

Looking through the code it appears that the Dialect's for both Sybase and SybaseAnywhere do not implement the limit clause (Sybase doesn't have one exaclty, but possible to do with 'set rowcount'). I'm posting all the relevant data below, the thing that stands out to me is the syntax error returned from the jdbc driver says "syntax error near p0" but the SQL generated by hibernate doesn't contain the text "p0".

Thanks in advance for the help in figuring this out, I'm willing to do anything on this end to help the process along.

Brian

Hibernate version: 2.1.6

Mapping documents:
<?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="spidermonkey.providers.Provider" table="Providers">
<id name="providerCode" column="ProviderCode">
<generator class="assigned"/>
</id>
<property name="name" column="ProviderName" not-null="true"/>
<property name="lastName" column="LastName" not-null="true"/>
<property name="firstName" column="FirstName"/>
</class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
String query = "from Provider";
Query q = session.createQuery(query);
q.setMaxResults(10);
List providerList = q.list();

Full stack trace of any exception that occurs:
(in log)

Name and version of the database you are using:
Sybase SQL Anywhere 5.5.03 with OpenServer Gateway. JDBC driver is jConnect 5.5

Debug level Hibernate log excerpt:

2684 [main] DEBUG net.sf.hibernate.impl.SessionImpl - find: from Provider
2687 [main] DEBUG net.sf.hibernate.engine.QueryParameters - named parameters: {}
2706 [main] DEBUG net.sf.hibernate.hql.QueryTranslator - compiling query
2727 [main] DEBUG net.sf.hibernate.impl.SessionImpl - flushing session
2728 [main] DEBUG net.sf.hibernate.impl.SessionImpl - Flushing entities and processing referenced collections
2728 [main] DEBUG net.sf.hibernate.impl.SessionImpl - Processing unreferenced collections
2728 [main] DEBUG net.sf.hibernate.impl.SessionImpl - Scheduling collection removes/(re)creates/updates
2728 [main] DEBUG net.sf.hibernate.impl.SessionImpl - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
2728 [main] DEBUG net.sf.hibernate.impl.SessionImpl - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
2728 [main] DEBUG net.sf.hibernate.impl.SessionImpl - Dont need to execute flush
2728 [main] DEBUG net.sf.hibernate.hql.QueryTranslator - HQL: from spidermonkey.providers.Provider
2728 [main] DEBUG net.sf.hibernate.hql.QueryTranslator - SQL: select provider0_.ProviderCode as Provider1_, provider0_.ProviderName as Provider2_, provider0_.LastName as LastName, provider0_.FirstName as FirstName, provider0_.Credentials as Credenti5_, provider0_.LicenseNumber as LicenseN6_ from Providers provider0_
2728 [main] DEBUG net.sf.hibernate.impl.BatcherImpl - about to open: 0 open PreparedStatements, 0 open ResultSets
2728 [main] DEBUG net.sf.hibernate.connection.DriverManagerConnectionProvider - total checked-out connections: 0
2728 [main] DEBUG net.sf.hibernate.connection.DriverManagerConnectionProvider - using pooled JDBC connection, pool size: 0
2732 [main] DEBUG net.sf.hibernate.SQL - select provider0_.ProviderCode as Provider1_, provider0_.ProviderName as Provider2_, provider0_.LastName as LastName, provider0_.FirstName as FirstName, provider0_.Credentials as Credenti5_, provider0_.LicenseNumber as LicenseN6_ from Providers provider0_
2732 [main] DEBUG net.sf.hibernate.impl.BatcherImpl - preparing statement
2737 [main] DEBUG net.sf.hibernate.util.JDBCExceptionReporter - SQL Exception
com.sybase.jdbc2.jdbc.SybSQLException: syntax error near 'p0'
at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2636)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1996)
at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:204) at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:187) at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1615)
at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1598)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:89)
at com.sybase.jdbc2.tds.Tds.adjustMaxRows(Tds.java:2783)
at com.sybase.jdbc2.tds.Tds.language(Tds.java:692)
at com.sybase.jdbc2.jdbc.SybStatement.sendQuery(SybStatement.java:1424) at com.sybase.jdbc2.jdbc.SybPreparedStatement.sendQuery(SybPreparedStatement.java:1025)
at com.sybase.jdbc2.jdbc.SybStatement.executeQuery(SybStatement.java:1494)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:70)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at spidermonkey.providers.ProviderTest.testProvidersLoad(Unknown Source)
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 junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.run(JUnitTestRunner.java:289)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.launch(JUnitTestRunner.java:656)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.main(JUnitTestRunner.java:558)
2738 [main] WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Error: -131, SQLState: 42W04
2739 [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - syntax error near 'p0'
2739 [main] DEBUG net.sf.hibernate.impl.BatcherImpl - done closing: 0 open PreparedStatements, 0 open ResultSets
2739 [main] DEBUG net.sf.hibernate.impl.BatcherImpl - closing statement
2742 [main] DEBUG net.sf.hibernate.util.JDBCExceptionReporter - SQL Exception
com.sybase.jdbc2.jdbc.SybSQLException: syntax error near 'p0'
at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2636)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1996)
at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:204) at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:187) at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1615)
at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1598)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:89)
at com.sybase.jdbc2.tds.Tds.adjustMaxRows(Tds.java:2783)
at com.sybase.jdbc2.tds.Tds.language(Tds.java:692)
at com.sybase.jdbc2.jdbc.SybStatement.sendQuery(SybStatement.java:1424) at com.sybase.jdbc2.jdbc.SybPreparedStatement.sendQuery(SybPreparedStatement.java:1025)
at com.sybase.jdbc2.jdbc.SybStatement.executeQuery(SybStatement.java:1494)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:70)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at spidermonkey.providers.ProviderTest.testProvidersLoad(Unknown Source)
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 junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.run(JUnitTestRunner.java:289)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.launch(JUnitTestRunner.java:656)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.main(JUnitTestRunner.java:558)
2744 [main] WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Error: -131, SQLState: 42W04
2744 [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - syntax error near 'p0'
2744 [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - Could not execute query
com.sybase.jdbc2.jdbc.SybSQLException: syntax error near 'p0'
at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2636)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1996)
at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:204) at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:187) at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1615)
at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1598)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:89)
at com.sybase.jdbc2.tds.Tds.adjustMaxRows(Tds.java:2783)
at com.sybase.jdbc2.tds.Tds.language(Tds.java:692)
at com.sybase.jdbc2.jdbc.SybStatement.sendQuery(SybStatement.java:1424) at com.sybase.jdbc2.jdbc.SybPreparedStatement.sendQuery(SybPreparedStatement.java:1025)
at com.sybase.jdbc2.jdbc.SybStatement.executeQuery(SybStatement.java:1494)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeQuery(SybPreparedStatement.java:70)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at spidermonkey.providers.ProviderTest.testProvidersLoad(Unknown Source)
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 junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.run(JUnitTestRunner.java:289)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.launch(JUnitTestRunner.java:656)
at org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.main(JUnitTestRunner.java:558)
2754 [Finalizer] DEBUG net.sf.hibernate.impl.SessionImpl - running Session.finalize()
Top
 Profile  
 
holmberg
 Post subject: LIMIT
PostPosted: Thu Sep 23, 2004 5:39 pm 
Newbie

Joined: Mon Jul 12, 2004 12:33 pm
Posts: 6
Have you gotten an answer to you question yet? I have a similar issue where I would like to limit the total results I get for a specific query we are using SQLServer.
Top
 Profile  
 
bdog42
 Post subject:
PostPosted: Thu Sep 23, 2004 6:52 pm 
Newbie

Joined: Mon Sep 06, 2004 1:40 pm
Posts: 2
No answer yet. I dug around the code and couldn't determine why it wasn't working, it seems like it should have. I ended up passing the JDBC connection to Hibernate manually and calling "set rowcount x" via JDBC before and after the query.
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Fri Sep 24, 2004 7:09 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
bdog42: this is surely a bug in your JDBC driver - Hibernate just calls PreparedStatement.setMaxRows() on Sybase; p0 obviously refers to some internal variable used by Sybase to implement setMaxRows()

holmberg: use Query.setMaxResults(), this is very well documented, and your question has nothing to do with the original post.
Top
 Profile  
 
stolley
 Post subject:
PostPosted: Sun Sep 26, 2004 7:14 pm 
Pro
Pro

Joined: Tue Aug 26, 2003 8:07 pm
Posts: 229
Location: Brisbane, Australia
Are you using prepared statement caching, bdog42?
(It will be a configurable option on whichever connection pool you're using)

_________________
Cheers,
Shorn.

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
  [ 5 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.