Problem with update statement with DBD::mysql

[vendion]

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 $@;

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.

[aquarium]

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.

[mjscott2702]

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.

[sundialsvc4]

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.

[mertserger]

This may be a silly question but did you "commit" your change to the database? If not then you'd lose your update.

[mjscott2702]

Good point - remember that you can also set AutoCommit on a database handle, if you don't want to manually commit each transaction.