[Phoenix-pm] PERL DBI

[Scott Walters]

Hi Peter,

Use placeholders:

    my $question_marks = join ', ', '?' x @column_names;  # one ? for each value
    my $sth = $dbh->prepare(qq{insert into foo (@column_names) values ($question_marks)}) or die $dbh->errstr;
    while(my @row = $old_database->fetchrow_array) {  # read from old database
         $sth->execute(@row); # insert into new database
    }

If you use placeholders, you never need to quote or escape data, and you don't suffer
from SQL command length limitations, and the database doesn't have to parse potentially
megs of data just to parse the SQL command, and it doesn't have to parse the SQL more
than once at all -- you prepare it once and then execute it as many times as you like
for a huge speed increase.  If you're loading a lot of data, you'll also want to
drop indexes first and the rebuild them later.  This is a huge time savings over
incrementally updating them for millions of records.

I might have missed the point of your question -- if so, please clarify, and I aplogize.

Cheers,
-scott

On  0, "Loo, Peter # PHX" <Peter.Loo at source.wolterskluwer.com> wrote:
> 
>    Hi,
>    
>    
>            $columnsToAssign = $columnsToAssign . ", \"" . @$d_arrayref[0]
>    . "\"";
>            }
>          else {
>            $columnsToAssign = $columnsToAssign . ", " . @$d_arrayref[0];
>            }
>    
>    Is there an easier way?
>    
>    
>    
>    Thanks.
>    
>    
>    
>    Peter Loo
>    
>