5 Replies Latest reply on Dec 1, 2010 4:29 AM by cinephil

    How to perform a native SQL query?

    skot
      How do you execute a native SQL insert?

      Calling the broadcast() method below breaks with the stack trace further below.

      Any ideas?

      ---

      @Name("broadcaster")
      @Scope(ScopeType.CONVERSATION)
      public class Broadcaster {

           @In("entityManager") EntityManager em;

           public String broadcast() {
                String sql =
                     "insert into Player_Announcment_assn " +
                     "  (player_id, announcement_id) " +
                     "  values (1, 1)";
                em.createNativeQuery(sql)
                     .executeUpdate();  // <-- line 36
                return "broadcast";
           }

      }

      ---

      javax.persistence.TransactionRequiredException: Executing an update/delete query
           at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:47)
           at com.thisispop.fuse.yci.session.Broadcaster.broadcast(Broadcaster.java:36)
           at org.jboss.seam.util.Reflections.invoke(Reflections.java:21)
           at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
           at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
           at org.jboss.seam.core.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:46)
           at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
           at org.jboss.seam.persistence.ManagedEntityIdentityInterceptor.aroundInvoke(ManagedEntityIdentityInterceptor.java:48)
           at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
           at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:31)
           at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
           at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:42)
           at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
           at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
           at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:166)
           at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:102)
           at com.thisispop.fuse.yci.session.Broadcaster_$$_javassist_7.broadcast(Broadcaster_$$_javassist_7.java)
           at com.thisispop.fuse.yci.test.AnnouncementTest$2.testComponents(AnnouncementTest.java:80)
           at org.jboss.seam.mock.BaseSeamTest$ComponentTest.run(BaseSeamTest.java:169)
           at com.thisispop.fuse.yci.test.AnnouncementTest.broadcast(AnnouncementTest.java:97)
      ... Removed 26 stack frames

        • 1. Re: How to perform a native SQL query?
          ericjava.eric.chiralsoftware.net

          A wild guess here: maybe you're violating a foreign key constraint?


          -----------


          JBoss Seam training

          • 2. Re: How to perform a native SQL query?
            christian.bauer

            Scott Shepherd wrote on Aug 02, 2008 23:24:

            com.thisispop.fuse.yci.test.AnnouncementTest$2.testComponents(AnnouncementTest.java:80)
                 at org.jboss.seam.mock.BaseSeamTest$ComponentTest.run(BaseSeamTest.java:169)
                 at com.thisispop.fuse.yci.test.AnnouncementTest.broadcast(AnnouncementTest.java:97)
            ... Removed 26 stack frames
            





            Don't use ComponentTest, use a FacesRequest if you want transaction wrapping.

            • 3. Re: How to perform a native SQL query?
              taccart.thierry.accart.name

              Hi Christian


              couldn't he do something like


              UserTransaction utx = getUserTransaction();
              try {
                  utx.begin();
                  query.executeUpdate();
                  utx.commit();
              } catch (Exception e) {
                  utx.rollback()
              }



              where getUserTransaction() gets UserTransaction from context ?

              • 4. Re: How to perform a native SQL query?
                skot

                Christian Bauer wrote on Aug 03, 2008 07:25:


                Don't use ComponentTest, use a FacesRequest if you want transaction wrapping.


                That was it! Thanks CB.


                Same method called from FacesRequest.invokeApplication (instead of ComponentTest.testComponents) works fine.


                Excellent.


                • 5. Re: How to perform a native SQL query?
                  cinephil

                  thierry accart wrote on Aug 03, 2008 10:29:


                  Hi Christian

                  couldn't he do something like

                  
                  UserTransaction utx = getUserTransaction();
                  try {
                      utx.begin();
                      query.executeUpdate();
                      utx.commit();
                  } catch (Exception e) {
                      utx.rollback()
                  }
                  
                  




                  This could be useful for me but what do you mean by this :


                  where getUserTransaction() gets UserTransaction from context ?


                  How can I code getUserTransaction function and where ? (I'm beginner with Seam)