Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Re: Of Mysql, DBI and PK best practices

by ptum (Priest)
on Apr 17, 2007 at 14:36 UTC ( #610536=note: print w/ replies, xml ) Need Help??


in reply to Of Mysql, DBI and PK best practices

Personally, I tend to do two things differently:

  • First, I select a full list of primary keys and put it in a hash. Before I try to do an insert, I check to see if the primary key for that record exists in the hash. If it does, I do an update instead of an insert. If the primary key isn't in the hash, then I do an insert, and add it to the hash if the insert was successful. This seems to be a little cleaner and (potentially) halves my number of SQL executions.
  • Second, I tend to use the RaiseError option in my database connection so that my program doesn't die when DBI encounters an error. I examine $DBI::errstr to specifically identify the nature of the error (in Oracle, you get error codes) so that I cover my bases for the unexpected. There's no sense in attempting an update if the table doesn't exist or your SQL is malformed. I'm not a MySQL guy, but the use of eval and $@ seems less than precise -- you might react to what you think is a duplicate insert error but is really something much more severe.

Update: As noted by kyle above, however, if this code is executed by multiple users/processes, you could end up with a race condition, such that your inserts might still fail because some other guy just added that record since the time that you selected all the primary keys.


Comment on Re: Of Mysql, DBI and PK best practices
Replies are listed 'Best First'.
Re^2: Of Mysql, DBI and PK best practices
by dragonchild (Archbishop) on Apr 17, 2007 at 18:28 UTC
    Not unless you do your work within a transaction.

    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?

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://610536]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (9)
As of 2015-07-29 00:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (260 votes), past polls