UPDATE function on SELECT statements???

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

Hi there,

Using Hibernate 2.1.4
MS SQL 2000

While trying to perform simple SELECT statements using a named SQL I noticed the following in a stack trace

Code:

HIBERNATE: WARN  - SQL Error: 229, SQLState: 42000
HIBERNATE: ERROR - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]UPDATE permission denied on object 'companies', database 'os', owner 'dbo'.
HIBERNATE: ERROR - could not update: [gr.forthnet.openseas.models.Company#ANEK]
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]UPDATE permission denied on object 'companies', database 'os', owner 'dbo'.
   at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
   at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
   at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
   at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
   at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
   at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
   at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
   at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
   at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
   at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
   at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
   at com.microsoft.jdbc.base.BaseStatement.executeUpdateInternal(Unknown Source)
   at com.microsoft.jdbc.base.BasePreparedStatement.executeUpdate(Unknown Source)
   at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
   at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:689)
   at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:642)
   at net.sf.hibernate.impl.ScheduledUpdate.execute(ScheduledUpdate.java:52)
   at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2414)
   at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2368)
   at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2236)
   at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
   at gr.forthnet.services.connectivity.databases.HiberDatabase.select(HiberDatabase.java:293)

The following code was trying to execute

Code:

   public List select(String query, Hashtable params)
   {
      Session session = getClientSession();
      Transaction tx = null;
      
      try 
      {                     
         tx = session.beginTransaction();
         
         Query sqlQuery = session.getNamedQuery(query);
         
         for(Iterator keys = params.keySet().iterator(); keys.hasNext();)
         {
            String param = (String)keys.next();
            String value = (String)params.get(param);
            
            sqlQuery.setString(param,value);
         }
            

      return sqlQuery.list();

....

using the mapping documents below

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
<hibernate-mapping package="gr.forthnet.openseas.models">   
<sql-query name="shippingCompanies">
    <return alias="company" class="Company"/>
       SELECT {company}.COMPANYID AS {company.abbreviation}, 
                 {company}.COMPANY_NAME AS {company.name}
       FROM companies {company}, agencies_companies agencies_companies
       WHERE {company}.COMPANYID = agencies_companies.COMPANY_CODE
       AND {company}.COMPANY_TYPE='SEALINES'
       AND agencies_companies.AGENCY_CODE= :agencyCode
</sql-query>
</hibernate-mapping>

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping package="gr.forthnet.openseas.models">   

<class name="Company" table="companies">
        <id name="abbreviation" column="companyID" type="java.lang.String" unsaved-value="null" length="32"> 
            <generator class="hilo"/> 
        </id> 

        <property name="name" column="company_name" type="java.lang.String" not-null="true" /> 
</class>
</hibernate-mapping>

Just today I also noticed that the data in some tables has changed and I was wondering how is it possible if only select statements are made :/

Thanks in advance

anthony · **Posted:** Tue Jul 20, 2004 1:38 pm

you can see update when a query is called because flush is called in some cases. So if you've made updates before a query, you can see update in the logs

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

Lost you....

So you mean that the Update isn't performed by Hibernate?

anthony · **Posted:** Tue Jul 20, 2004 1:48 pm

no i'll try to explain better:

case 1
1-open new session
2- find
3- commit
here you won't see any update

case 2
1- open new session
2- get find or load --> see a select in log
3- update loaded object --> nothing on log yet because flush has not been called
4- ... other actions that logs....
5- later, another get find or load --> here flush can be automatically called before the "select" by hibernate core, so you'll update (to flush the object modification) and after a new "select"

are you in case 1 or 2?

but i'm not sure this can be your problem...

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

and i'm not sure I understand!...

You can see the code i'm executing. Also, in every finally block, i close the session.

Still I can't seem to understand how could the data in a table change :/

anthony · **Posted:** Tue Jul 20, 2004 2:03 pm

Session session = getClientSession();
what does getClientSession(); do?
is the Session a new one?

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

Code:

   protected static Session getClientSession() 
   {      
      Session session = (Session) sessionThread.get();
      
      if (session == null) 
      {
         try {
            session = sessionFactory.openSession();
         } 
         catch (HibernateException e) {
            Logger.error(e.getMessage());
            e.printStackTrace();
         }
      }
      
      if(session != null)
         if (!session.isConnected()) 
         {
            try {
               session.reconnect();
            } 
            catch (HibernateException e) 
            {
               Logger.error(e.getMessage());
               e.printStackTrace();
               session.clear();
            }
         }
         
      sessionThread.set(session);
      
   return session;
   }

baliukas · **Posted:** Tue Jul 20, 2004 2:24 pm

Code:

 
protected static Session getClientSession() throws Exception {      

      Session session = (Session) sessionThread.get();
      
      if (session == null) {       

            session = sessionFactory.openSession();
            sessionThread.set(session);
        
      }

     return session;
   } 

 protected static void closeClientSession() throws Exception{
  
       try {

            Session session = (Session) sessionThread.get();
      
           if ( session != null) {       
                      session.close();                
           }

        } finally {
               sessionThread.set(null);
        }  

 }

[/code]

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

So wait...that solves the problem with the updating?

Still I fail to see why the update takes place

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

Also, is it not possible that the session somehow loses the connection and I have to reconnect? :/

In the closeSession I have the following code...

Code:

   protected static void closeSession()
   {
      Session session = (Session) sessionThread.get();
      sessionThread.set(null);
      
      if (session != null)
         try {
            session.close();
         } 
         catch (HibernateException e) 
         {
            Logger.error(e.getMessage());
            e.printStackTrace();
            session.clear();
         }
         finally 
         {
            session.clear();
            
            if(session != null)
               try {
                  session.close();
               } catch (HibernateException ingore) {}
         }
   }

Cue · **Joined:** Tue May 04, 2004 6:15 am **Posts:** 51

Can plz someone answer me as it's critical to ensure that no accidental changes are made to the db? :/

emmanuel · **Posted:** Wed Jul 21, 2004 8:24 am

This is an ORM tool so every persistent object (ie loaded or attached by an open session) modification will be persisted. There is no explicit call to any hibernate api to persist changes for such object (excet the flush or the commit)