DB update query from Linux using a Hash array

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

I spent a long time writing a client and server program on my windows machine that monitors program usage, user statistics, logged in status of machines here where I work. Everything had run smoothly on my windows machine until it crashed and I moved my server code over to my gentoo linux machine. Now I'm running into a lot of issues with code that had been working before and nothing had changed. My current problem is the following: The server gathers all software numbers for a monitored software list and totals them up into a total value for the software hash. When I do a foreach loop against the keys in the hash to then update the software totals in the DB nothing is being updated. I've narrowed the issue down to the software name that isn't working. Here is the loop

foreach $key1(keys(%prog_numbers))
{
$update = $ldb->prepare("UPDATE LOW_PRIORITY $ldbtable SET $mtime=$pro
+g_numbers{$key1} WHERE software=\"$key1\"");
$showfail = $update->execute;
$update->finish();
[download]

All the other variables are working fine and if I change $key1 to say "firefox.exe" it works, but for some reason it just doesn't want to update using the keys. I've tried putting all the keys into normal arrays and it still won't update due to the WHERE part of the query. The query itself runs but $showfail equals 0 and the my debug file simply says "UPDATE FAILED" with no explanation. I've also tried other loops like for, nested foreach, etc and still no go. Thank you for your time. Seth

Comment on DB update query from Linux using a Hash array Select or Download Code

Replies are listed 'Best First'.
Re: DB update query from Linux using a Hash array by pc88mxer (Vicar) on Mar 24, 2008 at 23:48 UTC
I would first verify that the values being assigned to `$key1` are what you think they are. If, for instance, they have spaces in them (at the beginning or end), then your UPDATE statement isn't going to work. Another way to test this is to change your UPDATE statement into an INSERT statement (just change the WHERE to a comma.) If the INSERT statement works, that would indicate the UPDATE statement is not working because it is not satisfying the WHERE clause. Finally, I would suggest you read up on using place-holders. They will save you a lot of trouble with constructing SQL statements. Re-written to use place-holders, your loop would become: `my $update = $ldb->prepare("UPDATE LOW_PRIORITY $ldbtable SET $mtime=? + WHERE software=?"); foreach my $key1 ( keys %prog_numbers) ) { $update->execute($prog_numbers{$key1}, $key1); }` [download] This is also a bit more efficient as the SQL statement is only prepared once.	[reply] [d/l] [select]
Re: DB update query from Linux using a Hash array by jfraire (Beadle) on Mar 25, 2008 at 00:56 UTC
By enabling DBI's tracing capabilities, extensive info is logged about the communication between your script and the database. You will see the statements received by the database server after any variable interpolations or placeholder substitutions. DBI offers 5 levels of tracing, from 1 to 5. Use 0 to disable tracing; 2 gives you a good result for debuging. You use it by setting the environment variable DBI_TRACE or by using the method trace on either your hanlde or as a class method. See DBI 's documentation for a thorough discussion. I hope this helps, Julio	[reply]
Re^2: DB update query from Linux using a Hash array by sethwalsh (Initiate) on Mar 25, 2008 at 16:04 UTC
Thanks everyone for the hints I'll give them all a try now.	[reply]
Re: DB update query from Linux using a Hash array by igelkott (Priest) on Mar 25, 2008 at 20:18 UTC
... SET $mtime= ... Does $mtime hold the name of the column you wish to update or is the column actually named "mtime"?	[reply]
Re: DB update query from Linux using a Hash array by sethwalsh (Initiate) on Mar 26, 2008 at 22:50 UTC
I wanted to say thanks for the ideas and help, learned some new stuff about DBI and tracing. I located the problem, because I had created the text files that my server program uses on my Windows machine then just ported them over to my Gentoo box they had a hidden ^M character at the end of every line that Windows puts in but Linux doesn't see so that is why all the matching failed and the updated failed since the program name in the Hash would never match the program name in the DB. Thanks again, Seth	[reply]