[jboss-user] [JBoss Seam] - Re: problems with ms sql

[brett.schuchert]

I had the same problem. Here's partial code that does the trick. Note that I'm using an EntityManager in a JSE environment, not a JEE environment.

Also, since I'm using hibernate, I did one hacky thing... I down casted the EntityManager to EntityManagerImpl.
  
Here's what I wrote to set identity on and off (this is a first pass, I'll properly handle exceptions soon):
 
    private void executeSql(final EntityManager em, final String sql) {
  |         try {
  |             getDestinationEm().getTransaction().begin();
  | 
  |             EntityManagerImpl emi = (EntityManagerImpl)em; //THE HACK
  |             Connection con = emi.getSession().connection();
  |             Statement s = con.createStatement();
  |             s.execute(sql);
  |             s.close();
  |             con.close();
  | 
  |             getDestinationEm().getTransaction().commit();
  |         } catch (Exception e) {
  |             e.printStackTrace();
  |         }
  |     }
  |     
  |     private void setInsertModeOn() {
  |         executeSql(getDestinationEm(), "SET IDENTITY_INSERT Application ON");
  |     }
  |     
  |     private void setInsertModeOff() {
  |         executeSql(getDestinationEm(), "SET IDENTITY_INSERT Application OFF");
  |     }
 
Then here's the code to read the from one database (something called TagApplication), create an instance of application (with the id set from the TagApplication row's value) and persist it:
 
    // Note that this just performs a query returning all applications after
  |     // a particular date
  |     private List<TagApplication> retrieveAllTagApplications() {
  |         final Query query = getSourceEm()
  |                 .createQuery("SELECT ta from TagApplication ta where ta.applicationDate >= ?1");
  |         query.setParameter(1, getIgnoreDate(), TemporalType.DATE);
  |         return query.getResultList();
  |     }
  |  
  |     public void translate() {
  |         // I've already been given my EntityManagerFactories, so create my EntityManager
  |         if (getSourceEm() == null) {
  |             setSourceEm(sourceEmf.createEntityManager());
  |         }
  |         if (getDestinationEm() == null) {
  |             setDestinationEm(destinationEmf.createEntityManager());
  |         }
  |  
  |         try {
  |             // Set IDENTITY_INSERT on Application in HealthTrackDB and begin a transaction
  |             setInsertModeOn();
  |             getDestinationEm().getTransaction().begin();
  |  
  |             // retrieve all TagApplication rows
  |             final List<TagApplication> tagApplications = retrieveAllTagApplications();
  | 
  |             // For each TagApplication row, create an Application based on the
  |             // TagApplication (field-by-field hand copy) and (batch) insert it.
  |             for (TagApplication ta : tagApplications) {
  |                 Application a = convert(ta);
  |                 getDestinationEm().persist(a);
  |             }
  |  
  |             // Finish off the (implicit) batch insert & reset the IDENTITY_INSERT to off
  |             getDestinationEm().getTransaction().commit();
  |             setInsertModeOff();
  |         } finally {
  |             getSourceEm().close();
  |             getDestinationEm().close();
  |         }
  |     } 
Brett


View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3977115#3977115

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3977115