DBI and mysql query formatting

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

Hello monks. I am relatively new to the DBI module and i ve come up to an obstacle so i need your wisdom :) There is a case, where i need to prepare a mysql query, for inserting data to a table, which contains some dozens of columns. I use bind_params for the preparation, but the thing is that sometimes the query itself must change and use some mysql native function like 'NOW()'. For better clearance of code and for better maintenance, i 've decided to go with a hash, using the mysql column names as its keys, and the values as its elements, so the code is in a smaller scale like this:

my %test = (
    "`test`.`id`"         => "NULL",
    "`test`.`hash`"       => "MD5()",
    "`test`.`power`"      => "HASH()",
    "`test`.`allegiance`" => "?",
);

my $str = join(", ", map { "$_" } keys %test);

my $val = join(", ", values %test);

my $query = "INSERT INTO `test` ($str) VALUES ($val)";
[download]

from what i've seen till now, it works, but i don't know if it is from some divine intervention or something else, because i am not really sure, if $val will always have the order of the values in the corresponding order of the keys in the $str. Moreover, since this is the best i can think of, and i am sure someone has come with something better, can you suggest anything else for formatting an sql insertion query for a table of 60+ columns? What i really care about is the ease of read for the code. Thank you for your time :)

Comment on DBI and mysql query formatting Download Code

Replies are listed 'Best First'.
Re: DBI and mysql query formatting by tangent (Parson) on Dec 05, 2013 at 15:17 UTC
I can't comment on the SQL but this will ensure you always have the same order: `my @keys = keys %test; my $str = join(", ", @keys); my $val = join(", ", map { $test{$_} } @keys);` [download]	[reply] [d/l]
Re^2: DBI and mysql query formatting by kcott (Archbishop) on Dec 06, 2013 at 11:00 UTC
"... this will ensure you always have the same order: `my @keys = keys %test;`" Actually, that's only true for versions of Perl prior to 5.18.0. From perl5180delta: Hash overhaul: "By default, two distinct hash variables with identical keys and values may now provide their contents in a different order where it was previously identical." I have v5.18.1 - here's a few example runs: `$ perl -Mstrict -Mwarnings -E 'my %x = map { $_ => 1 } "A" .. "E"; my +@y = keys %x; say "@y"' A D E C B $ perl -Mstrict -Mwarnings -E 'my %x = map { $_ => 1 } "A" .. "E"; my +@y = keys %x; say "@y"' D E C B A $ perl -Mstrict -Mwarnings -E 'my %x = map { $_ => 1 } "A" .. "E"; my +@y = keys %x; say "@y"' D E B A C` [download] Either a sort on the keys, or even a hard-coded list, would probably provide a more robust solution. -- Ken	[reply] [d/l] [select]
Re^3: DBI and mysql query formatting by tangent (Parson) on Dec 06, 2013 at 11:20 UTC
Yes, I agree. But the goal of the OP is to have the same order for the $str and $val, not for every run.	[reply]
Re^4: DBI and mysql query formatting by afoken (Chancellor) on Dec 08, 2013 at 14:37 UTC
Re^5: DBI and mysql query formatting by tangent (Parson) on Dec 08, 2013 at 18:38 UTC
Re: DBI and mysql query formatting by afoken (Chancellor) on Dec 08, 2013 at 15:12 UTC
A few points, not related to the keys/values question: the `map` and the quotes around `$_` are redundant in `join(", ", map { "$_" } keys %test)`. Just use `join(', ',keys %test)`. Abusing backticks as quotes for identifiers is MySQL specific, remove them. You don't need them for identifiers that are not reserved names or contain special characters. And you should not use identifiers that are reserved words or contain funny characters. If you insinst on "funny" table and column names, use DBI's quote_identifier method, that works with all databases supported by DBI. Use placeholders. You don't want to discuss with Bobby's mom why your database suddenly looks like ground zero. And you surely don't want to discuss that with your boss. There is absolutely no excuse for not using placeholders. See Re: Counting rows Sqlite, Re^2: Massive Memory Leak, Re^5: Variable interpolation in a file to be read in for some more information. For bulk inserts, consider using the native tools that came with the database. They are optimized for speed, so they are usually much faster than any code you can write in Perl. For just a few inserts into the same table, prepare a single `INSERT` statement (using placeholders) and execute it repeatedly. This way, the database and the DBI database driver can optimize as much as possible. Read the database driver's manual page (DBD::mysql in your case) to find some optimizing hints: Some databases prefer to have bulk inserts in transactions, others suffer from using transactions in bulk inserts. Placeholders example: `my $names=join(',',keys %test); my $placeholders=join(',',('?' x scalar keys %test)); my $sth=$dbh->prepare("INSERT into test ($names) VALUES ($placeholders +)"); # no error check, assuming RaiseError => 1 in DBI->connect() $sth->execute(values %test); # again, no error check, DBI will die on +errors $sth->finish(); # no error check, DBI will handle that` [download] Note that DBI automatically translates `undef` in values to NULL in the database, so you don't have to care for that. And of course, you don't have to care for correctly quoting values. 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]