vfpmcp .AT. mbsoftwaresolutions .D.OT 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 th=
e
> 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 k=
ey,=20
as in:
CREATE TABLE test (iid primary key not null auto_increment, clast=20
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=20
connection, so as long as your connection doesn't do any other inserting=20
first, it will always be correct and you can use that return value to use=
=20
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=20
order numbers that must be unique but is not the primary key. =20
lock tables <database>.<table> write
select max(iordnum) as iordnum from <database>.<table>
insert into <database>.<table> (iordnum) values (iordnum + 1)
unlock tables
--=20
Paul McNett
©2003 Paul McNett |
