problems with ms sql

[max522over]

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)

[mzeijen]

isn't this a EJB3 topic?

[max522over]

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.

[pmuir]

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.

[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