Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Re: dbi placeholders

by poj (Priest)
on Aug 05, 2014 at 17:39 UTC ( #1096324=note: print w/ replies, xml ) Need Help??


in reply to dbi placeholders

#!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);
poj


Comment on Re: dbi placeholders
Download Code
Replies are listed 'Best First'.
Re^2: dbi placeholders
by Tux (Monsignor) 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; }

    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; }

    Enjoy, Have FUN! H.Merijn
      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; }
      this is very interesting but get an error: 'statement has no result column' in the $sth2->bind_columns line

        Sorry, my bad. bind_columns doesn't work on inserts :(

        # Delete the bind_columns line for sth2 while ($sth1->fetch) { $sth2->execute (@rec{@fields}); }

        Enjoy, Have FUN! H.Merijn
Re^2: dbi placeholders
by fionbarr (Friar) on Aug 05, 2014 at 18:15 UTC
    just right! thanks

      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";
      poj

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1096324]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (8)
As of 2015-07-31 03:07 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (274 votes), past polls