Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

Re: dbi placeholders

by poj (Prior)
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);

Replies are listed 'Best First'.
Re^2: dbi placeholders
by Tux (Abbot) 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";

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1096324]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (4)
As of 2017-08-23 21:33 GMT
Find Nodes?
    Voting Booth?
    Who is your favorite scientist and why?

    Results (359 votes). Check out past polls.