Hello,
I have following situation in our system which use database:
I need to create unique number which is used for complete other tables. Let say I need in any time always incremented number which I can got in 1 single command.
I use C API to access MySQL.
I used for that table (ZUPINTID) following 2 comands:
UPDATE ZUPINTID SET INTID=INTID+1 WHERE ID = 1
SELECT MAX(ZUPINTID.INTID) FROM ZUPINTID

but meanwhile can someone also increment INTID and I receive the same numbers. Transactions didn't help.

- Is there any way how to get this number in one single question (select)? I use mysql_query and 1 command like this "UPDATE ZUPINTID SET INTID = LAST_INSERT_ID(INTID + 1); SELECT LAST_INSERT_ID();" says syntax error.
- would be possible in any way to use autoincremental field? But it seems to me I step into the same problem (for me looks UPDATE ZUPINTID SET INTID=INTID+1 WHERE ID = 1 something like autoincremental)
- I have read something about SELECT ... FOR UPDATE and locking until next update but I was not able to get some result from that - (from 2 mySQL fronts with the same SELECT.. FRO UPDATE I have reeived the same numbers).

I hope my questions are not too stupid. Thanks for your help in advance.

Now I found command mysql_insert_id() . I suppose it could help. Or not?
I found following description:
'Returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement. Use this function after you have performed an INSERT statement into a table that contains an AUTO_INCREMENT field. '
-> Is it really something like 1 transaction if I need insert command before? Connection remembers this value until next autoincrement field is updated? Is it thread safe (if i call it from more threads in 1 moment)?

Hi!

UPDATE ZUPINTID SET INTID=INTID+1 WHERE ID = 1
SELECT MAX(ZUPINTID.INTID) FROM ZUPINTID

but meanwhile can someone also increment INTID and I receive the same numbers. Transactions didn't help.


I am not too sure about what you exactly mean but the above two command lines give me a hint. You want to ADD new records in a table and you want to be sure that each record has unique INTID, is that right ?

I am not an expert on the issue but here is what I would do :

1. If INTID needs to be a number then I would define it as an AUTOINCREMENT and the numbering when adding would take care of itself

2. If INTID may for some reason not be an AUTOINCREMENT field I would use the SELECT MAX(ZUPINTID.INTID) FROM ZUPINTID command IMMEDIATELY after the INSERT record process AND THEN update INTID with the variable found.

HTH

Have Fun !
One line at a time ...

yes,
I need this unique ID but to increase and get it in 1 command in MySQL case is not so easy.
-I did it with C API with AUTOINCREENT and with above mentioned command mysql_insert_id(). I hope it works.
-But with ODBC connection I have no solution for thia problem. I can also use AUTOINCREMENT but how to find this record? If I say max than meanwhile someone can increase too? Maybe I will write some computer id (IP,...) into some other column and then ask with MAX and Where for this id. Maybe it will help.