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

Hello, I was wondering if one of you could advise me on the most elegant solution to the following issue with a small script that it is inputting lines into a MySQL table.

I'm currently feeding each line in with this code: 

my $sql = "INSERT INTO $table (" .
    join(',' , @fields) .
    ") VALUES (" .
    join(',' , map{$dbh->quote("$_")} split("\t",$line)) .
    ")";

[download]


Each line contains a tab-delimited separation of the six fields that I'm inputting. The problem is that when the final field is blank, the split function ignores the empty space to the right of the final "\t", and thus I get an error in MySQL when executing the statement. I hope I'm making sense here.

Anyway, I can think of some cumbersome ways to preprocess the lines, but I have this feeling that there is a far more elegant solution to this that I haven't stumbled upon. Any advice would be much appreciated.

Thanks!

Replies are listed 'Best First'.
Re: Looking for elegant solution to dbi coding problem
by injunjoel (Priest) on Jul 15, 2005 at 22:48 UTC
    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/,$_));
}

    [download]
    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
    "I do not feel obliged to believe that the same God who endowed us with sense, reason and intellect has intended us to forego their use." -Galileo
[reply]
[d/l]
      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'.

[reply]
        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.
[reply]

        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!

        planetscape
[reply]
Re: Looking for elegant solution to dbi coding problem
by ikegami (Patriarch) on Jul 15, 2005 at 22:44 UTC

    Insted of
    map { ... } split("\t", $line)
    use
    map { ... } $line =~ /(.*?)(?:\t|$)/g

    >perl -le "@a = split(/\t/, """a\t"""); print scalar @a"
1

>perl -le "@a= """a\t""" =~ /(.*?)(?:\t|$)/g; print scalar @a"
2

    [download]
[reply]
[d/l]
[select]

      Or just the simpler split(/\t/,$line,-1)

      perldoc -f split

      duff

[reply]
[d/l]

Back to Seekers of Perl Wisdom