Re: dbi placeholders

Replies are listed 'Best First'.
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
Sorry, my bad. `bind_columns` doesn't work on inserts :( `# Delete the bind_columns line for sth2 while ($sth1->fetch) { $sth2->execute (@rec{@fields}); }` [download] Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]
Re^5: dbi placeholders by fionbarr (Friar) on Aug 06, 2014 at 14:38 UTC
Re^6: dbi placeholders by poj (Abbot) on Aug 06, 2014 at 17:48 UTC
Re^6: dbi placeholders by marto (Cardinal) on Aug 06, 2014 at 14:43 UTC
Some notes below your chosen depth have not been shown here
Re^6: dbi placeholders by Tux (Canon) on Aug 06, 2014 at 14:47 UTC
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]