Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Inserting into several linked MySQL tables.

by techcode (Hermit)
on Sep 27, 2005 at 16:07 UTC ( [id://495428]=perlquestion: print w/replies, xml ) Need Help??

techcode has asked for the wisdom of the Perl Monks concerning the following question:

Fellow Monks,

Classic chicken-egg problem. :)

I have two tables

order:

  • id - primary key
  • .. few other fields
order_products:
  • id - primary key
  • order_id - should be linked to other table
I obviously need first to insert data into order table, so that I could get id (auto increment) that MySQL gave to it. So that I could insert few more records into order_products table and use that id.

How do I tackle that? Transactions? I believe that MySQL server used doesn't support it (older version). Can I use locking mechanism described at 111666 even if transactions aren't supported?

Have you tried freelancing? Check out Scriptlance - I work there.
  • Comment on Inserting into several linked MySQL tables.

Replies are listed 'Best First'.
Re: Inserting into several linked MySQL tables.
by mpeters (Chaplain) on Sep 27, 2005 at 16:47 UTC
    Which version of MySQL are you using? Transactions have been around for a while. What I think you need more than anything though is a foreign key constraint. Check out the InnoDB storage engine for MySQL.

    -- More people are killed every year by pigs than by sharks, which shows you how good we are at evaluating risk. -- Bruce Schneier
Re: Inserting into several linked MySQL tables.
by dragonchild (Archbishop) on Sep 27, 2005 at 17:21 UTC
    If all tables are using the InnoDB engine, then you can certainly use transactions. The way to do this in DBD::mysql is to turn AutoCommit off, then issue your statements. You can optionally send an explicit "BEGIN WORK" statement, but you don't have to. (I do, but that's cause I'm paranoid.) When you're ready to commit, issue a $dbh->commit() to commit or $dbh->rollback(), if you're rolling back. (Those functions are documented in the DBI documentation.)

    If any of your tables are non-InnoDB, then you would have to issue a LOCK TABLES statement, pairing that with the appropriate UNLOCK TABLES at the appropriate time. The big issue with this is that you don't have any method to rollback if you run into a problem. The other problem is that you have a lock on those tables, which means all other attempts to modify the tables (and possibly even read, depending on the lock) are blocked until such time as you release the lock. Because of this, you can enter a race condition where process 1 locks tables A and B and process 2 locks tables B and A. If they run at the same time, process 1 locks table A, then tries to get a lock on table B. But, process 2 already locked table B and is trying to get a lock on table A. This is called deadlock, which is bad.

    As for the process by which you do this, you would use the $dbh->{mysql_insertid} to find the last inserted id. (This is documented in the DBD::mysql documentation.) This is a step up from, say Oracle or Sybase, where you have to explicitly issue a SELECT statement to retrieve this information.


    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
      Actually DBD::Sybase will honor the $dbh->last_insert_id() method in a lot of cases.

      Michael

Re: Inserting into several linked MySQL tables.
by eric256 (Parson) on Sep 27, 2005 at 16:55 UTC

    I don't understand what the problem is. Set id to autoincrement. Insert that record. The use last_insert_id (i think thats it) to get the ID of the record you just inserted and use it when adding order_products. No chicken and egg here. Maybe I missed where you think the delima is.


    ___________
    Eric Hodges
Re: Inserting into several linked MySQL tables.
by davidrw (Prior) on Sep 27, 2005 at 16:28 UTC
    Without transactions, I would be sure to run audit querys afterwards to check for broken RI (e.g. order_products rows where the order_id does not exist in order table), and also to see if there's any "product-less" order table rows (though maybe that's legal.. depending on your use you can delete them or know that they represent an error).
Re: Inserting into several linked MySQL tables.
by tinita (Parson) on Sep 27, 2005 at 19:01 UTC
    I believe that MySQL server used doesn't support it (older version).
    well, better check: Feature Comparison

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://495428]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others surveying the Monastery: (3)
As of 2024-04-17 22:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found