Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
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
Re^2: dbi placeholders
by fionbarr (Pilgrim) 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
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

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 imbibing at the Monastery: (8)
As of 2014-11-25 23:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (160 votes), past polls