Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Unanswered: Oracle 9i, Sequences and ADO

    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 retrievingt 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.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    select last_number 
    from user_sequences 
    where sequence_name
    ='SEQ_CUSTOMER_ID';

    LAST_NUMBER
    -----------
           
    6040 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •