Search code examples
javahibernatesqlanywhere

Hibernate SQLQuery (Not enough values for host variable)

We're using Hibernate with Sybase Anywhere 12 and experience a problem with a query on a view using SQLQuery.

SQLQuery query = session.createSQLQuery(" SELECT * FROM myview WHERE x = :x AND y IN (:y) ");
query.setParameter("x", "z1234");
query.setParameterList("y", Arrays.asList(1, 2, 3, 4));
query.setReadOnly(true);
query.addEntity(ClassX.class);
List result = query.list();

The query fails after restarting the database and only one time. If we're executing the same query a second time with the same parameters, it's running smoothly.

The view is based on a table and another view based on two tables. These tables does have triggers. Just in case this might be the source for our problem, but it does not make sence to me since I did debug down to Connection#prepareStatement(...) where the SQLException occured.

Caused by: java.sql.SQLException: [Sybase][JDBC Driver][SQL Anywhere]Not enough values for host variables
        at sybase.jdbc4.sqlanywhere.IConnection.nativePrepareStatement(Native Method) ~[sajdbc4.jar:na]
        at sybase.jdbc4.sqlanywhere.IConnection.prepareStatement(IConnection.java:599) ~[sajdbc4.jar:na]
        at sun.reflect.GeneratedMethodAccessor53.invoke(Unknown Source) ~[na:na]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.7.0_09]
        at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.7.0_09]
        at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:125) ~[com.springsource.org.apache.tomcat.jdbc-1.1.0.1.jar:na]
        at $Proxy104.prepareStatement(Unknown Source) ~[na:na]
        at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534) ~[hibernate-core-3.6.9.Final.jar:3.6.9.Final]

Any ideas?

10:16:33.851 WARN  [http-9443-2] org.hibernate.util.JDBCExceptionReporter.logExceptions[233] - SQL Error: -188, SQLState: 07002
10:16:33.851 ERROR [http-9443-2] org.hibernate.util.JDBCExceptionReporter.logExceptions[234] - [Sybase][JDBC Driver][SQL Anywhere]Nicht genügend Werte für Hostvariablen

References: Sybase Docs

Solution

  • I think I've found the source of my problem. myview did have a warning symbol in Sybase Central. myview is based on another view which was dropped and created again. Therefore, myview has required a recompile.

    However, the SQL Code of the thrownSQLException is wrong and leads into a wrong direction finding the source of the problem. Connection#prepareStatement(sqlQuery) does not pass any parameters.