4 Replies Latest reply on Oct 9, 2006 11:15 PM by Brett L. Schuchert

    problems with ms sql

    mark smith Newbie

      can some one tell me how to do this. I'm trying to bulk load into Ms Sql 2000 and I need to execute this sql statement

       Query setIdentity = entityManager.createNativeQuery("SET IDENTITY_INSERT AssignmentType ON");
       setIdentity.executeUpdate();
      



      16:59:31,500 WARN [TxConnectionManager] Prepare called on a local tx. Use of local transactions on a jta transaction with more than one branch may result in inconsistent data in some cases of failure.
      16:59:35,718 ERROR [STDERR] regionBuilder is created.
      16:59:35,718 ERROR [STDERR] Processing an RegionBuilder conversion now...about to execite QUERY Please wait.
      16:59:35,734 ERROR [STDERR] java.lang.UnsupportedOperationException: Update queries only supported through HQL
      16:59:35,734 ERROR [STDERR] at org.hibernate.impl.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:760)
      16:59:35,734 ERROR [STDERR] at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:43)
      16:59:35,734 ERROR [STDERR] at com.mfa.healthtrack.dbconv.controller.RegionBuilderImpl.convertRegion(RegionBuilderImpl.java:95)
      16:59:35,734 ERROR [STDERR] at com.mfa.healthtrack.dbconv.controller.RegionBuilderImpl$$EnhancerByCGLIB$$73eeecb2.CGLIB$convertRegion$5()
      16:59:35,734 ERROR [STDERR] at com.mfa.healthtrack.dbconv.controller.RegionBuilderImpl$$EnhancerByCGLIB$$73eeecb2$$FastClassByCGLIB$$83b49971.invoke()
      16:59:35,734 ERROR [STDERR] at net.sf.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:167)
      16:59:35,734 ERROR [STDERR] at org.jboss.seam.interceptors.JavaBeanInterceptor$1.proceed(JavaBeanInterceptor.java:80)
      16:59:35,734 ERROR [STDERR] at org.jboss.seam.interceptors.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
      16:59:35,734 ERROR [STDERR] at org.jboss.seam.interceptors.ValidationInterceptor.validateTargetComponent(ValidationInterceptor.java:64)
      16:59:35,734 ERROR [STDERR] at sun.reflect.GeneratedMethodAccessor115.invoke(Unknown Source)
      16:59:35,734 ERROR [STDERR] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)


        • 1. Re: problems with ms sql
          Maurice Zeijen Master

          isn't this a EJB3 topic?

          • 2. Re: problems with ms sql
            mark smith Newbie

            I don't think so. I've tried to go out to jdbc to do that but it always fails because I have a managed transaction. This is seam managing the transaction and not allowing me a way to talk direct sql to database engine.

            • 3. Re: problems with ms sql
              Pete Muir Master

              I would suggest asking on the hibernate.org forum or in the JBoss EJB3 forum. Seam is only wrapping EJB3 persistence (to provide support for long-runing conversations) in it's transaction management.

              • 4. Re: problems with ms sql
                Brett L. Schuchert Newbie

                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