Professional Web Applications Themes

Obtaining rowid on insert - MySQL

Given an insert into a simple table whose primary key is an autoincrement is there a reliable way to return the value in a 1-step process? I can grab it using a MAX( ) select afterwards but am wary of time conflicts with another update. I know of alternate methods using individual sequences, stored procedures etc but am pretty certain that there has to be a more elegant way of doing this. Thanks, - Craig Taylor...

  1. #1

    Default Obtaining rowid on insert

    Given an insert into a simple table whose primary key is an
    autoincrement is there a reliable way to return the value in a 1-step
    process? I can grab it using a MAX( ) select afterwards but am wary of
    time conflicts with another update.

    I know of alternate methods using individual sequences, stored
    procedures etc but am pretty certain that there has to be a more
    elegant way of doing this.

    Thanks,

    - Craig Taylor

    Craig Taylor Guest

  2. #2

    Default Re: Obtaining rowid on insert

    I found the mySql way of doing it - still feel there's a better (or
    should be a better) way

    For MySql 5 (at least, maybe in 4) use LAST_INSERT_ID() function or in
    php mysql_insert_id() function.

    - Craig Taylor


    Craig Taylor wrote:
    > Given an insert into a simple table whose primary key is an
    > autoincrement is there a reliable way to return the value in a 1-step
    > process? I can grab it using a MAX( ) select afterwards but am wary of
    > time conflicts with another update.
    >
    > I know of alternate methods using individual sequences, stored
    > procedures etc but am pretty certain that there has to be a more
    > elegant way of doing this.
    >
    > Thanks,
    >
    > - Craig Taylor
    Craig Taylor Guest

  3. #3

    Default Re: Obtaining rowid on insert

    If you're using php, you could get rid of the autoincrement thing
    altogether and instead use php to assign the index value based on some
    factors, like the time of the submission and the value being submitted
    for example. That way, php would already know the id.

    Craig Taylor wrote:
    > I found the mySql way of doing it - still feel there's a better (or
    > should be a better) way
    >
    > For MySql 5 (at least, maybe in 4) use LAST_INSERT_ID() function or in
    > php mysql_insert_id() function.
    >
    > - Craig Taylor
    >
    >
    > Craig Taylor wrote:
    > > Given an insert into a simple table whose primary key is an
    > > autoincrement is there a reliable way to return the value in a 1-step
    > > process? I can grab it using a MAX( ) select afterwards but am wary of
    > > time conflicts with another update.
    > >
    > > I know of alternate methods using individual sequences, stored
    > > procedures etc but am pretty certain that there has to be a more
    > > elegant way of doing this.
    > >
    > > Thanks,
    > >
    > > - Craig Taylor
    strawberry Guest

  4. #4

    Default Re: Obtaining rowid on insert

    Unfortunately, I have no gurantees that the time will be unique. In
    addition, my other data could (on a rare chance) be identical. The
    LAST_INSERT_ID() along with an auto-increment gurantees me a reliable
    index that I can pass back to the caller.

    (Granted, I doubt a timestamp down to the millisecond will be
    duplicated but I have seen stranger things happen because of race
    conditions in a database).

    - Craig Taylor

    strawberry wrote:
    > If you're using php, you could get rid of the autoincrement thing
    > altogether and instead use php to assign the index value based on some
    > factors, like the time of the submission and the value being submitted
    > for example. That way, php would already know the id.
    >
    > Craig Taylor wrote:
    > > I found the mySql way of doing it - still feel there's a better (or
    > > should be a better) way
    > >
    > > For MySql 5 (at least, maybe in 4) use LAST_INSERT_ID() function or in
    > > php mysql_insert_id() function.
    > >
    > > - Craig Taylor
    > >
    > >
    > > Craig Taylor wrote:
    > > > Given an insert into a simple table whose primary key is an
    > > > autoincrement is there a reliable way to return the value in a 1-step
    > > > process? I can grab it using a MAX( ) select afterwards but am wary of
    > > > time conflicts with another update.
    > > >
    > > > I know of alternate methods using individual sequences, stored
    > > > procedures etc but am pretty certain that there has to be a more
    > > > elegant way of doing this.
    > > >
    > > > Thanks,
    > > >
    > > > - Craig Taylor
    Craig Taylor Guest

Similar Threads

  1. Access (ROWID) Unique ID from Datagrid Column
    By Mona Syed in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: June 1st, 02:52 PM
  2. SQL - getting rowid into a temp table [368]
    By ART KAGEL, BLOOMBERG/ 65E 55TH in forum Informix
    Replies: 0
    Last Post: June 24th, 07:07 PM
  3. questions to ROWID
    By Nuno Souto in forum Oracle Server
    Replies: 0
    Last Post: December 13th, 01:17 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139