Re: Update mysql row with a hash

If your database has any kind of data type contraints which its 99% likely it does, then your query will fail if your perl statement happens to try to stick a string into a date field, or a number into a bool. This is because hashes do not have a standard internal order, the returned order of keys or values is random**, unless you sort them. Even then, you can only ever be sure you'll get them in the same order if no keys are added or deleted.

What does $stmt actaully look like if you print it out? If you then copy that into your DB query window and execute it, what error(s) do you get? That'll likely reveal the problem.

Update:
Actually, upon re-reading your question, I think you may be having problems due to mis-understanding SQL.
The usual syntax for an UPDATE cmd is:

UPDATE "table_name"
SET column_1 = [value1], column_2 = [value2]
WHERE {condition}
[download]

I don't see how you can simplify that down to an "Update table " . keys %hash . " VALUES " . values $hash kind of structure.

Update:Clarified post thanks to afoken

** But will return the same order for the same hash if compared within the same Perl 'run' AND the hash has not been modified, thanks afoken for correcting me.

Comment on Re: Update mysql row with a hash Select or Download Code

Replies are listed 'Best First'.
Re^2: Update mysql row with a hash by afoken (Chancellor) on Feb 11, 2010 at 19:34 UTC
This is because hashes do not have a standard internal order, the returned order of keys or values is random, unless you sort them. Correct, but `keys`, `values`, and `each` use the same order for the same hash. From perlfunc: Entries are returned in an apparently random order. The actual random order is subject to change in future versions of perl, but it is guaranteed to be in the same order as either the keys or values function would produce on the same (unmodified) hash. Since Perl 5.8.2 the ordering can be different even between different runs of Perl for security reasons [...]. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^2: Update mysql row with a hash
by afoken (Chancellor) on Feb 11, 2010 at 19:34 UTC

This is because hashes do not have a standard internal order, the returned order of keys or values is random, unless you sort them.

Correct, but keys, values, and each use the same order for the same hash. From perlfunc:

Entries are returned in an apparently random order. The actual random order is subject to change in future versions of perl, but it is guaranteed to be in the same order as either the keys or values function would produce on the same (unmodified) hash. Since Perl 5.8.2 the ordering can be different even between different runs of Perl for security reasons [...].

Alexander

--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)

[reply]
[d/l]
[select]