Re: DBI and mysql query formatting

[afoken]

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

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