dbi placeholders

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

hi I need to read a table with 94 columns and write back to another table. I am using fetchrow_array to read and the write is:

my $sql = qq(INSERT INTO table VALUES (
            $array[0], $array[1], $array[2], $array[3], $array[4],$arr
+ay[5],
            $array[6], $array[7], $array[8], $array[9],$array[10],$arr
+ay[11],$array[12],
            $array[13],$array[14],$array[15],$array[16],$array[17],$ar
+ray[18],
            $array[19],$array[20],$array[21],$array[22],$array[23],$ar
+ray[24],
            $array[25],$array[26],$array[27],$array[28],$array[29],$ar
+ray[30],
            $array[31],$array[32],$array[33],$array[34],$array[35],$ar
+ray[36],
            $array[37],$array[38],$array[39],$array[40],$array[41],$ar
+ray[42],$array[43],
            $array[44],$array[45],$array[46],$array[47],$array[48],$ar
+ray[49],$array[50],
            $array[51],$array[52],$array[53],$array[54],$array[55],$ar
+ray[56],$array[57],
            $array[58],$array[59],$array[60],$array[61],$array[62],$ar
+ray[63],$array[64],
            $array[65],$array[66],$array[67],$array[68],$array[69],$ar
+ray[70],
            $array[71],$array[72],$array[73],$array[74],$array[75],$ar
+ray[76],
            $array[77],$array[78],$array[79],$array[80],$array[81],$ar
+ray[82],
            $array[83],$array[84],$array[85],$array[86],$array[87],$ar
+ray[88],
            $array[89],$array[90],$array[91],$array[92],$array[93] ));


my $sth = $dbh_1->prepare($sql) or die "fail to prepare sql statement.
+...$!\n";

$sth->execute() or die "cannot execute...$!";
[download]

I know this is extremely inefficient but I can't get it right using placeholders

Comment on dbi placeholders Download Code

Replies are listed 'Best First'.
Re: dbi placeholders by poj (Abbot) on Aug 05, 2014 at 17:39 UTC
`#!perl use strict; my @array = (0..4); my $ph = '?'.(',?' x $#array); my $sql = qq!INSERT INTO table VALUES ($ph)!; my $sth = $dbh_1->prepare($sql); $sth->execute(@array);` [download] poj	[reply] [d/l]
Re^2: dbi placeholders by Tux (Canon) on Aug 06, 2014 at 06:04 UTC
Still rather inefficient. Why not use bind_columns? `my %foo; # Store here when fetching fields my $sth1 = $dbh->prepare ("select * from foo"); $sth1->execute; # added this line after [poj] spotting it was missing # store foo.blah into $foo{blah} $sth1->bind_columns (\@foo{@{$sth1->{NAME_lc}}}); my $sth2 = $dbh->prepare ("insert into bar (ape, monkey) values (?, ?) +"); # foo.morg => bar.ape, foo.jume => bar.monkey $sth2->bind_columns (\@foo{qw( morg jume )}); while ($sth1->fetch) { $sth2->execute; }` [download] If both tables have the same fields `my %rec; my $sth1 = $dbh->prepare ("select * from foo"); my @fields = @{$sth1->{NAME_lc}}; $sth1->bind_columns (\@rec{@{$sth1->{NAME_lc}}}); my $sth2 = do { local $" = ", "; $dbh->prepare ("insert into bar (@fields) values (@{[('?')x@fields +]})"); }; $sth2->bind_columns (\@rec{@fields}); while ($sth1->fetch) { $sth2->execute; }` [download] Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]
Re^3: dbi placeholders by fionbarr (Friar) on Aug 06, 2014 at 13:56 UTC
`my %rec; my $sth1 = $dbh_0->prepare ("select * from production.computersystem") +; my @fields = @{$sth1->{NAME_lc}}; $sth1->bind_columns (\@rec{@{$sth1->{NAME_lc}}}); my $sth2 = do { local $" = ", "; my $sql = "insert into barrycomputersystem (@fields) values (@{ +[('?')x@fields]})"; $dbh_1->prepare ($sql); }; $sth2->bind_columns (\@rec{@fields}); while ($sth1->fetch) { $sth2->execute; }` [download] this is very interesting but get an error: 'statement has no result column' in the $sth2->bind_columns line	[reply] [d/l]
Re^4: dbi placeholders by Tux (Canon) on Aug 06, 2014 at 14:25 UTC
Re^5: dbi placeholders by fionbarr (Friar) on Aug 06, 2014 at 14:38 UTC
Some notes below your chosen depth have not been shown here
Re^2: dbi placeholders by fionbarr (Friar) on Aug 05, 2014 at 18:15 UTC
just right! thanks	[reply]
Re^3: dbi placeholders by poj (Abbot) on Aug 05, 2014 at 18:59 UTC
Or maybe you can just use `my $sql = 'INSERT INTO table2 SELECT * FROM table1'; my $count = $dbh->do($sql); print "$count records inserted from table1 into table2\n";` [download] poj	[reply] [d/l]