Hi,

I am trying to connect to a specific database in informix and am running a native sql to do that and getting an exception.

Following are the details

Sessionfactory is set up at the instance level. I am using SQLQuery to execute the native query "Database testdb"

Code

final SQLQuery query = session.createSQLQuery("Database testdb" );
query.executeUpdate();


Hibernate version: 3.2.5.ga
Database: Informix
JDBC Driver: ifxjdbc.jar
Connection URL:jdbc:informix-sqli://<HOSTNAME>:<PORT#>:informixserver=<INSTANCE_NAME>
Generated SQL: "Database testdb"

I get the following exception

Full stack trace of any exception that occurs:
WARN [JDBCExceptionReporter] SQL Error: -260, SQLState: 07005
ERROR [JDBCExceptionReporter] Cannot execute a SELECT statement that is PREPAREd - must use cursor.
ERROR [[default]] Servlet.service() for servlet default threw exception
Caused by: java.sql.SQLException: Cannot execute a SELECT statement that is PREPAREd - must use cursor.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:379)
at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3109)
at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3419)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2282)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2202)
at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:724)
at com.informix.jdbc.IfxResultSet.executeUpdate(IfxResultSet.java:310)
at com.informix.jdbc.IfxStatement.executeUpdateImpl(IfxStatement.java:885)
at com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedStatement.java:279)
at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeUpdate(CachedPreparedStatement.java:95)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:165)
... 126 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:174)
at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1163)
at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:334)
... 121 more

Please let me know how to fix this. I appreciate your help.


Thanks

Why do I get the feeling that you program simply does not have the rights to create a database? This might be a user rights issue.

Can you use the SchemaExport class to create database tables? Give it a try and see if you' don't get the same SQLException.

Thank you for the response.

Firstly, I am sure its not a credential/permissions issue. I created a JDBC program to test the same with the same credentials and it works fine.

Secondly, I am not trying to create a Database or Tables. My use case is as follows:

1. I have one instance of a Informix which has multiple schemas (or databases).
2. SessionFactory is created at the instance level. Based on the each request I want to select the database I want to work with.
3. The way thats accomplished in Oracle is by running the following query "alter session set current schema=testdb". This works well with Hibernate and Oracle.
4. The way to achieve this in Informix is by running the query "Database testdb"
5. This is causing the exception I was referring to.

Hope the testcase is clear now.

Thanks again.