9 Replies Latest reply: Jun 21, 2007 7:34 AM by 584281 RSS

    Oracle 9i, Sequences and ADO

    413073
      ok, here goes...

      Have an application that will require Oracle connectivity, it already has an abstracted data layer and currently works under MSSQL, Access, MySQL, etc..

      The problem is as follows: The centralized code we use to add a record to any of the above mentionned DB platforms follows the same code base;

      - Retrieve an ADO recordset containing the schema (columns) of the table you want to insert to, ensuring that it is blank ; for example: (this has been dumbed down to a ADO recordset for the sake of clairity and my own testing)

      Set poCmpsRS = mo_GetRS("select id, CMPTNC_GRP_ID from cmptncs where id = -1")

      then typically, an Addnew is called to create the new record:

      poCmpsRS.AddNew

      poCmpsRS.Fields("CMPTNC_GRP_ID").Value = 1

      poCmpsRS.UpdateBatch

      Now, on ALL OTHER DB platforms, the contents of .fields("ID") will contain the auto_increment value from access, mysql, mssql etc... but in Oracles case, as soon as updatebatch is called, the contents of .fields("ID") show "0" (zero), even though the contents of the database effectively show the triggered sequence value for the column.

      This boils down to 2 questions:

      Is there any way to get the last value issued by a sequence via an SQL call?

      OR

      Is there any way to get ADO to behave properly with Oracle with respect to retrieving the sequence value?

      I'd HATE to have to modify our code to pre-fetch sequence values prior to issuing an Addnew to the DB, call it laziness; I don't wanna go there, MANY MANY tables.
        • 1. Re: Oracle 9i, Sequences and ADO
          413073
          Anyone?

          The only potential resolution I can see, is to pre-fetch the sequences number once the ADO addnew method is called, and do away with the trigger.

          • 2. Re: Oracle 9i, Sequences and ADO
            417788
            I have the exact same problem with an application
            that works great with other DBMS's. I would appreciate
            it if you let me know what/if you have found any kind
            of solution. Thanks, Tony.
            • 3. Re: Oracle 9i, Sequences and ADO
              JustinCave
              A couple of options I can think of:

              1) You can get the last value of a sequence in Oracle by calling <<sequence name>>.currval

              2) You can requery the ResultSet, which will update the values.

              Justin
              Distributed Database Consulting, Inc.
              http://www.ddbcinc.com/askDDBC
              • 4. Re: Oracle 9i, Sequences and ADO
                430666
                Paul,

                I've exactly the same problem. Have you already found a solution?
                • 5. Re: Oracle 9i, Sequences and ADO
                  362610
                  Hi all,

                  I also have this problem, any ideas?

                  "Jan" - I found a lot of your posts on the internet - I was hopeful your solution would work for me but it does not :( I copied you code almost entirely into my VB6 app, but no joy :( Can you or anyone else help?

                  I open the connection with the cursorlocation to adServer, and use adOpenKeyset with the recordset, but after the rs.update the id column is null!!!!

                  I am using an Oracle 8i database - could that be the problem?

                  Thanks!
                  • 6. Re: Oracle 9i, Sequences and ADO
                    362610
                    ...Sorry, I should have added an URL to "Jans" post I was talking about:

                    http://www.talkaboutdatabases.com/group/comp.databases.oracle.server/messages/269136.html
                    • 7. Re: Oracle 9i, Sequences and ADO
                      430666
                      I'm afraid I can't help you.
                      I've tested my solution with oracle 9 & 10.

                      If it is not working with Oracle 8 you should take another approach.
                      Eg. making a stored function which does the insert and returns the new id.
                      • 8. Re: Oracle 9i, Sequences and ADO
                        362610
                        Ah well, it looks like a limitation of oracle 8i :(

                        Thanks anyway....
                        • 9. Re: Oracle 9i, Sequences and ADO
                          584281
                          Hi skinnepa,
                          I work with vb 6.0 and VB .NET.

                          The solution I'm using for this problem is:
                          1st) Create a Sequence for the specific field (E.g.
                          CREATE SEQUENCE XXX.XXID_SEQ
                          START WITH 1
                          MAXVALUE 999999999999999999999999999
                          MINVALUE 1
                          NOCYCLE
                          NOCACHE
                          NOORDER;)
                          2nd) Create a trigger associate to the specific table (in your case "cmptncs") that will be fired after the insert...Probably your problem is that ADO with the AddNew method firstly created the row. In any case the trigger fired after the insert should work. In the trigger (E.g.
                          CREATE OR REPLACE TRIGGER DLW_EVENT_MAIN_TRIGGER
                          BEFORE INSERT
                          ON DLW_EVENT_MAIN
                          REFERENCING NEW AS New OLD AS Old
                          FOR EACH ROW
                          DECLARE
                          tmpVar NUMBER;
                          BEGIN
                          tmpVar := 0;

                          SELECT DSA.XXID_SEQ.NEXTVAL INTO tmpVar FROM dual;
                          :NEW.XXID := tmpVar;

                          EXCEPTION
                          WHEN OTHERS THEN
                          -- Consider logging the error and then re-raise
                          RAISE;
                          END DLW_EVENT_MAIN_TRIGGER;
                          ) you can select the XXID_SEQ.NEXTVAL and place it into the new val...


                          Now it should works fine...