9 Replies Latest reply on Jun 15, 2007 7:42 AM by Sandor Fiedler

    [JDBCExceptionReporter]'last_insert_id' is not a recognized

    shipra ahuja Newbie

      Hi
      I have been trying to connect to a DB on sql server using EJB3.0. I'm able to retrieve the data from the tables but cannot insert.
      The tables are 'Address' and 'Person'.
      The Unique auto generated 'id' of 'Address' table has to be inserted in the 'AddressId' field of Person.
      Besides other fields, my EntityBean Person contains:
      @Id
      @GeneratedValue(strategy=GenerationType.AUTO)
      public int getId() {
      return id;
      }
      public void setId(int id) {
      this.id = id;
      }

      @ManyToOne(cascade=CascadeType.ALL)
      @JoinColumn(name="addressId",
      referencedColumnName="id")
      public Address getAddress() {
      return address;
      }
      public void setAddress(Address address) {
      this.address = address;
      }


      ....

      Besides other fields, Address Entity Bean contains
      @Id
      @GeneratedValue
      public int getId() {
      return id;
      }


      Now while I'm trying to insert a new Record, I'm getting the following Exception:
      [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]'last_insert_id' is not a recognized function name.
      14:30:58,108 ERROR [STDERR] javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not retrieve generated id after insert: [com.j3ltd.server.entities.Address]
      14:30:58,108 ERROR [STDERR] at org.jboss.ejb3.tx.Ejb3TxPolicy.handleExceptionInOurTx(Ejb3TxPolicy.java:69)
      14:30:58,108 ERROR [STDERR] at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:83)
      14:30:58,108 ERROR [STDERR] at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:191)
      14:30:58,108 ERROR [STDERR] at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
      14:30:58,124 ERROR [STDERR] at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:76)
      14:30:58,124 ERROR [STDERR] at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
      14:30:58,124 ERROR [STDERR] at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
      14:30:58,124 ERROR [STDERR] at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
      14:30:58,124 ERROR [STDERR] at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:77)
      14:30:58,124 ERROR [STDERR] at org.jboss.ejb3.security.Ejb3AuthenticationInterceptor.invoke(Ejb3AuthenticationInterceptor.java:102)
      14:30:58,124 ERROR [STDERR] at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
      14:30:58,124 ERROR [STDERR] at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:47)
      14:30:58,124 ERROR [STDERR] at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
      14:30:58,124 ERROR [STDERR] at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
      14:30:58,124 ERROR [STDERR] at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
      14:30:58,124 ERROR [STDERR] at org.jboss.ejb3.stateless.StatelessContainer.dynamicInvoke(StatelessContainer.java:263)
      14:30:58,124 ERROR [STDERR] at org.jboss.ejb3.remoting.IsLocalInterceptor.invoke(IsLocalInterceptor.java:58)
      14:30:58,124 ERROR [STDERR] at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
      14:30:58,124 ERROR [STDERR] at org.jboss.ejb3.stateless.StatelessRemoteProxy.invoke(StatelessRemoteProxy.java:102)
      14:30:58,124 ERROR [STDERR] at $Proxy85.createPerson(Unknown Source)
      14:30:58,124 ERROR [STDERR] at com.j3ltd.web.Register.register(Register.java:92)

      .
      .
      .
      .
      .

      and
      .
      .
      .
      .

      Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not retrieve generated id after insert: [com.j3ltd.server.entities.Address]
      14:30:58,124 ERROR [STDERR] at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629)
      14:30:58,124 ERROR [STDERR] at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:218)
      14:30:58,124 ERROR [STDERR] at org.jboss.ejb3.entity.TransactionScopedEntityManager.persist(TransactionScopedEntityManager.java:175)
      14:30:58,124 ERROR [STDERR] at com.j3ltd.server.session.EntityFacadeBean.createPerson(EntityFacadeBean.java:53)
      14:30:58,124 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      14:30:58,124 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
      14:30:58,124 ERROR [STDERR] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      14:30:58,124 ERROR [STDERR] at java.lang.reflect.Method.invoke(Unknown Source)

      Please could anyone help me out in solving this problem




        • 1. Re: [JDBCExceptionReporter]'last_insert_id' is not a recogni
          wayne baylor Apprentice

          Your code is telling Hibernate to auto-generate both ids.

          Are you setting the id manually, after Hibernate assigns it?

          [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]'last_insert_id' is not a recognized function name.


          • 2. Re: [JDBCExceptionReporter]'last_insert_id' is not a recogni
            shipra ahuja Newbie

            Hi,
            Thanks. Actually the id was set as Identity column in the Tables also.
            I removed the @GeneratedValue from both the entities.
            But again I'm getting an error.
            15:05:25,757 ERROR [JDBCExceptionReporter] [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot insert explicit value for identity column in table 'Address' when IDENTITY_INSERT is set to OFF.
            15:05:25,757 ERROR [AbstractFlushingEventListener] Could not synchronize database state with session

            org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

            at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)

            at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

            at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)

            at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:92)

            at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)

            at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:218)

            at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2220)

            at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2656)


            In the Tables, I have id set as an Identity Column.

            I looked for this on the Internet:
            http://www.microsoft.com/downloads/details.aspx?FamilyId=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en

            It asks for SQL Server Service Pack 4.
            I installed that but still getting the same error.
            I'm new to EJB. Please help me out. :(

            • 3. Re: [JDBCExceptionReporter]'last_insert_id' is not a recogni
              wayne baylor Apprentice

              It should work if both Person and Address have their ids annotated like

              @Id
              @GeneratedValue
              private int id;
              

              Hibernate should take care of all the details.

              How are you using EntityManager to persist them?

              • 4. Re: [JDBCExceptionReporter]'last_insert_id' is not a recogni
                Pakhi Sharma Newbie

                Hi, I ve been reading your issues, and I am also finiding a similar issue in my application. I am jus trying to insert in one table in whihc one field in Identity"Id".
                My Entity Bean is like follows:


                @Id
                @GeneratedValue(strategy = GenerationType.IDENTITY)
                @Column (name="id")
                public int getId() {
                return id;
                }
                public void setId(int id) {
                this.id = id;
                }


                and my table contains this field id as set to IDENTITY....

                then also i m getting this error...

                sed by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]'last_insert_id' is not a recognized function name.
                14:18:36,232 ERROR [STDERR] at
                com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
                14:18:36,232 ERROR [STDERR] at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
                14:18:36,232 ERROR [STDERR] at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
                14:18:36,232 ERROR [STDERR] at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
                14:18:36,232 ERROR [STDERR] at com.microsoft.jdbc.sqlserver.tds.TDSExecuteRequest.processReplyToken(Unknown Source)
                14:18:36,232 ERROR [STDERR] at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
                14:18:36,232 ERROR [STDERR] at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
                14:18:36,232 ERROR [STDERR] at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
                14:18:36,232 ERROR [STDERR] at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
                14:18:36,232 ERROR [STDERR] at com.microsoft.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
                14:18:36,232 ERROR [STDERR] at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
                14:18:36,232 ERROR [STDERR] at com.microsoft.jdbc.base.BaseStatement.executeQuery

                Has anyone tried to look into such Entitiy insertion...

                Please reply... I ve been looking into it for so long...
                THanks alot...

                • 5. Re: [JDBCExceptionReporter]'last_insert_id' is not a recogni
                  Sandor Fiedler Newbie

                  hi folks,

                  you should get around this by setting strategy to GenreationType.TABLE. This should result in a table created at database named hibernate_sequences (or something similar). This table provides the next id as an integer mapped by the name of the entity as key.

                  maybe this could help.

                  regards

                  • 6. Re: [JDBCExceptionReporter]'last_insert_id' is not a recogni
                    Pakhi Sharma Newbie

                    Hey.. thanks for your reply... I really appreciate the same so soon...
                    But now i am getting the error as follows:

                    15:56:14,638 ERROR [MultipleHiLoPerTableGenerator] could not read or init a hi value
                    java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
                    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.TDSExecuteRequest.processReplyToken(Unknown Source)
                    at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(

                    I made this change as you guided...
                    @Id
                    @GeneratedValue(strategy = GenerationType.TABLE)
                    @Column (name="id")
                    public int getId() {
                    return id;
                    }


                    Please guide, is there some constaint in inserting into the table though this entity bean.

                    Thanks again... :)

                    • 7. Re: [JDBCExceptionReporter]'last_insert_id' is not a recogni
                      Sandor Fiedler Newbie

                      you could try to create a wrapperservice, which creates your id instead of doing it by using hibernate. the service could look something like this:

                      public Long createId(String entity){
                      //read current value
                      //increment that
                      //write back to database
                      //return original value to caller
                      }
                      

                      This creates a simple ID-Service with no dependancies. Don't forget to remove the annotation for the strategy. If that works probably there is something wrong in the combination of hibernate and ms sql server.

                      regards

                      • 8. Re: [JDBCExceptionReporter]'last_insert_id' is not a recogni
                        Pakhi Sharma Newbie

                        Hey thanks for your concern, i will try this way and get back to you, but I want to intimate that I tried my code like this:
                        @Id
                        @Column (name="id")
                        public int getId() {
                        return id;
                        }
                        public void setId(int id) {
                        this.id = id;
                        }


                        In this i m not generating the value with the @GenerateValue, and thus it inserts one id in the table and does not allows us to enter the second record id. i m getting the error, of Inserting duplicate key...

                        Please look into the issue, if you could help us with some code sample in such a case. Thanks...

                        • 9. Re: [JDBCExceptionReporter]'last_insert_id' is not a recogni
                          Sandor Fiedler Newbie

                          did i unterstand you right? Preparing the code like your last post you can get the entity to the table stored an it has an id. If so the table is created with id=autoincrement and thus it should be turned off. Looked at the ddl to correct this.

                          regards