Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    1

    Question Unanswered: Migrating from Oracle to MySQL

    I have to migrate to MySQL. In Oracle i had Sequnces to get incrementing numbers to use for unique keys. I know in MySQL there is AUTO_INCREMENT, but thats not what i need, cause i need the number befor i actualy insert the data to the table. Is there somthing simular?
    Thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Edison, NJ / Oakland, NJ (Work)
    Posts
    32

    Re: Migrating from Oracle to MySQL

    Originally posted by badom
    I have to migrate to MySQL. In Oracle i had Sequnces to get incrementing numbers to use for unique keys. I know in MySQL there is AUTO_INCREMENT, but thats not what i need, cause i need the number befor i actualy insert the data to the table. Is there somthing simular?
    Thanks
    Ok, so you need the next "ID" before doing something, then you insert the new data. Is the system multi-user, and if so isn't it possible for another use to "steal" the ID from a stalled/held up process. I'm not a DB professional, but you could create a single field table with one value, NextID and you could query it, then increment it by one at the same time, then whenever another process gets a number it's different.

    Let me know if you still need help on this, it has been a long time.
    Last edited by smithhayward; 04-04-03 at 14:39.
    -----------------------------------
    Smith Hayward
    -----------------------------------

  3. #3
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Migrating from Oracle to MySQL

    Originally posted by smithhayward
    Ok, so you need the next "ID" before doing something, then you insert the new data. Is the system multi-user, and if so isn't it possible for another use to "steal" the ID from a stalled/held up process. I'm not a DB professional, but you could create a single field table with one value, NextID and you could query it, then increment it by one at the same time, then whenever another process gets a number it's different.

    Let me know if you still need help on this, it has been a long time.
    Assuming you are using InnoDb, you could have something like:

    CREATE TABLE sequences
    ( SEQ_NAME varchar(18) NOT NULL,
    SEQ_COUNTER int(11) NOT NULL,
    PRIMARY KEY (SEQ_NAME)) TYPE=InnoDB


    And to get the next sequence number and make sure it will get attributed to you, you could have something like:

    BEGIN TRANSACTION;
    SELECT
    * FROM sequences WHERE SEQ_NAME = 'MyTable' FOR UPDATE;

  4. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Migrating from Oracle to MySQL

    Originally posted by smithhayward
    Ok, so you need the next "ID" before doing something, then you insert the new data. Is the system multi-user, and if so isn't it possible for another use to "steal" the ID from a stalled/held up process. I'm not a DB professional, but you could create a single field table with one value, NextID and you could query it, then increment it by one at the same time, then whenever another process gets a number it's different.

    Let me know if you still need help on this, it has been a long time.
    Sorry... Previous post got posted by mistake... Here we are again!

    Assuming you are using InnoDb, you could have something like:

    CREATE TABLE sequences
    ( SEQ_NAME varchar(18) NOT NULL,
    SEQ_COUNTER int(11) NOT NULL,
    PRIMARY KEY (SEQ_NAME)) TYPE=InnoDB


    And to get the next sequence number and make sure it will get attributed to you, you could have something like:

    BEGIN TRANSACTION;

    /* Get the value of the next id AND LOCK IT */
    SELECT
    SEQ_COUNTER
    FROM
    sequences
    WHERE
    SEQ_NAME = 'MyTable' FOR UPDATE;


    UPDATE
    sequences
    SET
    SEQ_COUNTER = SEQ_COUNTER + 1
    WHERE
    SEQ_NAME = 'MyTable';

    COMMIT TRANSACTION;



    Hope this helps.

Posting Permissions

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