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

I am having some problems with getting a mysql update statement with DBD::mysql. My SQL statemen is as follows:

  eval { $dbh->do("UPDATE computer SET didDiagnostic = 1 WHERE CustId 
+= *000-000002*") };
  print "Update failed: $@\n" if $@;

[download]

From what I can tell the if $@ statement is not being exectued so I don't think it is failing but the entry in the database is never updated. I know my SQL syntax is correct becuase I can copy and paste it into mysql console prompt and it work. I can provide more information as needed, a code sniplet or a mysql dump.

Replies are listed 'Best First'.
Re: Problem with update statement with DBD::mysql
by aquarium (Curate) on Nov 09, 2010 at 03:25 UTC
    arguably better to "prepare" before "execute". anyway, the problem is probably not quoting the value you want to set for didDiagnostic column. i remember having such problem with a database when using a stored procedure vs executing at console, with the console being a bit smarter(?) to quote the value automatically terminated by whitespace. you could also turn on dbd/mysql debugging to see what happens.
    the hardest line to type correctly is: stty erase ^H
[reply]
Re: Problem with update statement with DBD::mysql
by mjscott2702 (Pilgrim) on Nov 09, 2010 at 08:23 UTC
    As well as possible quoting issues, as mentioned by aquarium (I usually quote using a qq or q around the entire SQL statement, and DBI also provides its own quote method), make sure that RaiseError is set to 1 on the database handle, otherwise $@ might not contain anything useful.
[reply]
Re: Problem with update statement with DBD::mysql
by locked_user sundialsvc4 (Abbot) on Nov 09, 2010 at 13:33 UTC

    I think that you should be checking the value of DBI::err or the equivalent property of a handle.

    Sounds funny but, what I usually do is to “try something that I know is right,” and then “something that I know is wrong,” and observe what actually happens in both cases.

    In addition, I strongly suggest that you use query parameters, and not simply as a defense against SQL injection.   You prepare the query once, and execute it as many times as necessary, binding a different value to the parameter each time.   (Both execute and do have arguments expressly designed for this.)

    Take explicit control of your database transaction behavior.   Issue begin_work and commit/rollback calls explicitly.   Observe what the application is actually doing in response to the source-code you have written.

    I have become a big fan of the various Test suites, and of the notion of creating regression tests for every module that I write, as I write them.   The modest additional effort is worth every single penny when debugging, and it greatly reduces the amount of “debugging” that you actually have to do.   Just last week, I “whipped out a little module,” briefly thought to myself that it really didn’t require a test suite ... wrote one anyway, and heh ... every one of the methods that I had just written had a subtle bug in it.

Re: Problem with update statement with DBD::mysql
by mertserger (Curate) on Nov 09, 2010 at 10:20 UTC
    This may be a silly question but did you "commit" your change to the database? If not then you'd lose your update.
[reply]
      Good point - remember that you can also set AutoCommit on a database handle, if you don't want to manually commit each transaction.
[reply]

Back to Seekers of Perl Wisdom