Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    20

    Unanswered: Problem with creating unique numbers for complete database

    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.

  2. #2
    Join Date
    Dec 2003
    Posts
    20
    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)?
    Last edited by Sladky; 09-09-04 at 05:27.

  3. #3
    Join Date
    Sep 2004
    Posts
    39
    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 ...


  4. #4
    Join Date
    Dec 2003
    Posts
    20
    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.
    Last edited by Sladky; 09-13-04 at 04:47.

Posting Permissions

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