PERL | DBI | Batch Upload/Insert – Row wise vs Column Wise binding.

Today’s topic is Bitch uploading. Oops – Batch Uploading. Using Perl DBI. Into SQL Server. It’s hard to believe that anything perl is poorly documented. Well, atleast non-intuitively documented. How else am I supposed to find out how to bind row-wise instead of column-wise to a prepared statement ? You would think that `perldoc DBI` would help, but it has but one nondescript line about row-wise binding.

Anyway, since I am documenting anything that people might find useful in their lives, here’s what I have found about row-wise binding in DBI as opposed to column-wise binding ( which is easy, well – better documented ). I show you examples of both.

To bind column-wise, use this :

$sth = $dbh->prepare(“insert into database..table values (?, ?, ?)”);
@array1 = ( 1, 2, 3 ) # array that contains the values of column1 across all the rows.
@array2 = ( 1, 2, 3 ) # array that contains the values of column2 across all the rows.
@array3 = ( 1, 2, 3 ) # array that contains the values of column3 across all the rows.

$sth->execute_array({}, \@array1, \@array2, \@array3); # {} is on purpose, not a typo. Read the perldoc for more.

—————————————–

To bind row-wise, use this :

$sth = $dbh->prepare(“insert into database..table values ( ?, ?, ?)”);
$sth->execute_array({ArrayTupleFetch => sub { return_a_referecnce_to_an_array(a row)_with_three_columns }});

what the second statement does is that it calls the subroutine repeatedly over and over ( by itself, without you specifying a loop ) till it returns an undef.

e.g:

$sth->execute_array({ArrayTupleFetch => sub { return shift @array_of_row_references }} );

so, when called repeatedly, the code will keep shifting an array ( thereby returning its first element ) till it runs out, in which case it will return undef.

Have fun with bitc..err..batch uploading !

—-

All this info is courtesy the Author/Architect of the perl DBI – > Tim Bunce ( or something to that effect, apologies to him – a great guy, ubiquitous with his help ).

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to PERL | DBI | Batch Upload/Insert – Row wise vs Column Wise binding.

  1. cmillard says:

    Thanks! Tim really could clean up the documentation on this point, so I went looking for it and found you. :)

  2. mohomed says:

    This is awesome. Thank you very much.

  3. Anonymous says:

    excellent post. i want to thank you for this informative read, i really appreciate sharing this great post. keep up your work.What an ideal web site.post by s3 phone cover.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s