I thought that the auto_increment feature only works with int type fields..
Bob Lee
----- Original Message -----
From: "Paul McNett" <p /at/ ulmcnett .D.O.T com>
To: <profoxtech@leafe.com>
Sent: Thursday, June 12, 2003 8:18 PM
Subject: Re: [NF] What's the equivalent of VFP's NEWID() function in MySQL?
> vfpmcp /at/ mbsoftwaresolutions .D.O.T com writes:
>
> > How do I get the same functionality for generating unique keys with a
> > MySQL back end (specifically, in the scenario where you need to know the
> > parent key so that you can write the corresponding key in the child
> > tables for linking records)?
>
> You need to have your MySQL tables set up to auto-increment the primary
key,
> as in:
>
> CREATE TABLE test (iid primary key not null auto_increment, clast
> varchar(20))
>
> Then, when you insert a record, as in:
>
> insert into test (clast) values ("mcnett")
> -or-
> insert into test (iid, clast) values (0, "mcnett")
> -or-
> insert into test (iid, clast) values (NULL, "mcnett")
>
> the primary key will get a unique value.
>
> To get that value back, you just do a:
>
> select last_insert_id() as iid
>
> The last_insert_id() will always return the last id generated for that
> connection, so as long as your connection doesn't do any other inserting
> first, it will always be correct and you can use that return value to use
> in child records and so forth.
>
>
> > I saw a function to determine the next key in a MySQL database, but my
> > concern is that the operation gets that key (but only gets...doesn't
> > increment), but by the time my user finally saves the record, that key
> > value is not accurate (unless there's some way to lock the record, but I
> > don't think so in MySQL)?
>
> You can lock a table in MySQL, which is how I deal with situations like
> order numbers that must be unique but is not the primary key.
>
> lock tables <database>.<table> write
> select max(iordnum) as iordnum from <database>.<table>
> insert into <database>.<table> (iordnum) values (iordnum + 1)
> unlock tables
>
>
>
> --
> Paul McNett
>
[excessive quoting removed by server]
©2003 Bob Lee |
