Native SQL

nahmed · **Joined:** Fri Mar 25, 2005 3:07 pm **Posts:** 11

A newbie to Hibernate but very interested so far.

Is Hibernate able to execute generalised SQL statements which are not bound to any objects.

eg

- perform some clean up of another table for which we have defined no objects

- call arbitary Stored Procedres which do not return any objects

Basically I want to know if we can issue SQL statements as if it were a JDBC connections

max · **Posted:** Fri Mar 25, 2005 4:56 pm

sure - as per docs and feature list:

Use session.connection() to access the underlying jdbc connection which hibernates uses for a specific session/transaction.

you can do everything you can with jdbc here (just dont expect hibernate to automatically detect those "behind-its-back" changes ,)

-mx

nahmed · **Joined:** Fri Mar 25, 2005 3:07 pm **Posts:** 11

Thanks for the reply.

Having got the connection, anyway I could put my own sql and those generated by Hibernate into one logical transaction.

eg

Code:

hsf = new Configuration().configure().buildSessionFactory();
     Session session = hsf.openSession();
            
     Connection dbConn = session.connection();

     Statement stmnt = dbConn.createStatement();

     tx = session.beginTransaction();         
         
          stmnt.execute("delete markit_entity_mappings");

          // load objects from XML file via digester into arrayList
         //  Loop thru arrayList and persist each object.
         // omited code for above here
          
         session.save(mem)

    tx.commit

Also in my opinion i think it would have been cleaner to use session.beginTransaction() and session.commitTransaction() (as I thikn nested trasaction are now allowed????)

Anyway would appreciate if you could advise on how I mix manual issues SQL statements with that generated by HSQL under one logical transaction.

Could I do the following?

issue my manual SQL stament (this should start the transaction

do some Hiberate stuff

issue a commit from the connection I obtained via session.connection()

Many Thanks

Naz

max · **Posted:** Fri Mar 25, 2005 5:40 pm

eh why dont you just use hibernates transaction api as the way to start and stop the transactions ?

nahmed · **Joined:** Fri Mar 25, 2005 3:07 pm **Posts:** 11

I am newbie so pls excuse any stupid questions/responses...

but I think hibernate wont allow you to dick around with a connection using it for manual SQL plus HSQL. Here is the error I get

Quote:

ERROR [main] (JDBCExceptionReporter.java:58) - [Microsoft][SQLServer 2000 Driver for JDBC]Can't start a cloned connection while in manual transaction mode.
ERROR [main] (AbstractFlushingEventListener.java:277) - Could not synchronize database state with session

max · **Posted:** Fri Mar 25, 2005 6:00 pm

tx = session.beginTransaction()

native sql query

hibernate session code

native sql query

hibernate session code

tx.commit();

should be working without any problems.

nahmed · **Joined:** Fri Mar 25, 2005 3:07 pm **Posts:** 11

Thats exactly what I tried, but it complains with the error message which I just posted

Can't start a cloned connection while in manual transaction mode.

Would apprecite it if could give that a wizz and let me know if you are seeing what I see.

Thanks

PS: Do you know if nested transactions are allowed?

naz

max · **Posted:** Fri Mar 25, 2005 6:24 pm

all of this depends on your driver/db.

this error i havent seen before.

mchyzer · **Joined:** Sat Jul 17, 2004 5:16 pm **Posts:** 143

nahmed wrote:

Thats exactly what I tried, but it complains with the error message which I just posted

Can't start a cloned connection while in manual transaction mode.

Would apprecite it if could give that a wizz and let me know if you are seeing what I see.

Thanks

PS: Do you know if nested transactions are allowed?

naz

Looks like you need to change your connect string for JDBC:

http://support.microsoft.com/default.as ... s%3B313181

You will be back to dicking around in no time. Chris