Re: Looking for elegant solution to dbi coding problem

[injunjoel]

Greetings all,
One suggestion would be to prepare the statement with ?'s and then re-use it for each line.
The following code is untested... you have been warned.

$dbh->{RaiseError} = 1;
my $sql = "INSERT INTO $table(".join(',',@fields).") VALUES(".join(','
+,(map{'?'}@fields)).")";
my $sth = $dbh->prepare($sql);

#line by line from your file
while(<INPUT>){
    #do your split and execute here.
    $sth->execute((split /\t/,$_));
}

The ?'s will handle the quoting for you, DBI is nice that way.
This assumes that its the same $table for each line, but it saves the overhead of preparing the query each time.

-InjunJoel

[Commander Salamander]

Aside: is there a way for me to reply to my own thread, or do I have to 'reply' to a specific response?

Thanks for the ideas everyone... I'm still getting the hang of perl, but I always learn a tremendous amount when I spend time on these forums. Comments on the first three replies:

1 That regexp took me a while to understand, as I wasn't familiar with "?:", but I finally managed to grok it :)

2 Cool, I didn't realize that I could use a negative limit in the split function to address this problem.

3 I see your point... it would be far more economical to go that route (in combination with the modification to split proposed in number 2)

Thanks again!



Retitled by holli from 'Followup'.

[graff]

If you're starting from a tab-delimited data file (and if there is a lot of data to be inserted), it's likely to be even more economical to use mysqlimport (command line interface to mysql's LOAD DATA statement). Or -- I haven't tried this yet -- maybe you can use LOAD DATA via DBI.pm? The point being that loading the data in bulk with tools built for this purpose will be incredibly faster than one-row-at-a-time inserts with DBI.

[Commander Salamander]

I first explored the idea of directly importing the data with the mysql INFILE command, but I was getting a large number of errors during the import (and MySQL is less than helpful when it comes to elucidating these errors). So, I elected to write a script that would assure that the data were properly formatted prior to insertion. However, I will explore the LOAD DATA function... I was not aware of it prior to your mention.

Thanks!

[planetscape]

You can reply to your own post the same basic way you reply to any other. Scroll down to just below your post, and look for the line beginning "comment on {post title}". (Or: click here)

For the convenience of people reading Newest Nodes or stumbling upon your post from other areas of the site, do please try to preserve information in the title of your reply so that some context is immediately apparent (see my title, above). Thanks!