9 Replies Latest reply on May 6, 2003 8:28 PM by juhalindfors

    SELECT MAX to find primary key

    marc.dorchain

      Hi all,

      I need t find the last inserted row of a table. Without ejb and cmp, I simple do a
      "SELECT max(id) from table".
      I just can't get it to work.
      I always get errors trying ejb-ql like this in ejb-jar.xml:
      "SELECT MAX (t.id) FROM TableEJB AS t".

      Any help woul really be appreciated! Thank you!
      Marc

        • 1. Re: SELECT MAX to find primary key
          frits.jensen

          You wil have to wait for EJB 2.1

          • 2. Re: SELECT MAX to find primary key
            marc.dorchain

            Well, I just can't wait until EJB 2.1.

            One thing I can do is to open a connection manually and query the sql statement as prepared statement direct ignoring the container at all.
            I don't like this very much.
            How do other people solve this problem? Any other ideas?

            • 3. Re: SELECT MAX to find primary key
              nicfournier

              Why not use get-generated-keys entity commands and auto-increment??

              • 4. Re: SELECT MAX to find primary key
                marc.dorchain

                Well, thats not exactly what I wanted.
                I have to transfer data from one table to another (2 databases) and have to know from what id to start on.
                And, btw, all I read about get-generated-PKs and auto-increment was people having trouble with it and didn't find any docs at all.

                • 5. Re: SELECT MAX to find primary key
                  frits.jensen

                  I think your only option is to build your entitybean as a BMP type, where you code the SQL yourself.

                  • 6. Re: SELECT MAX to find primary key
                    marc.dorchain

                    I coded the sql in a hand-made connection, leaving the container for this statement. Then I have the id I wanted and can use my beans as usual.
                    It's a pity theres actually no way to code the sql in ejb-ql.

                    • 7. Re: SELECT MAX to find primary key
                      juanflorez

                      Hi there, I?m having the same problem, what i didd was a simple find all ordered by id. and then get the last one.
                      I know it is not a polite solution, but, I picked as the "less worst" for tow reasons:
                      -First, this query is going to be very seldom because only will happen when my counter table is not set with the last valid id.

                      -Second, because as far as I have read, jboss, does not load the whole entity, when you send a query, there for, the overload would not be so much.......

                      Any way, I?m just a rokie, if you find a better one, let me know.

                      • 8. Re: SELECT MAX to find primary key
                        juanflorez

                        Hi there, I?m having the same problem, what i didd was a simple find all ordered by id. and then get the last one.
                        I know it is not a polite solution, but, I picked as the "less worst" for tow reasons:
                        -First, this query is going to be very seldom because only will happen when my counter table is not set with the last valid id.

                        -Second, because as far as I have read, jboss, does not load the whole entity, when you send a query, there for, the overload would not be so much.......

                        Any way, I?m just a rokie, if you find a better one, let me know.

                        • 9. Re: SELECT MAX to find primary key

                          You might be able to do it with Delcared SQL in JBoss, or just by overriding the finder implementation by providing your own ejbFindSomething implementation inside your CMP entity.