Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Re: Perl DBI - Bind variable when query is inserted as part of db column

by choroba (Archbishop)
on Feb 04, 2020 at 21:36 UTC ( #11112392=note: print w/replies, xml ) Need Help??


in reply to Perl DBI - Bind variable when query is inserted as part of db column

What do you mean by "inserting a query into another query"?

  1. Maybe you want to insert the results of the first query into the second one. This example shows how to do it with DBD::SQLite, some other databases can't have more than one prepared statement per db handle, so you'd need to connect more than once.
    #!/usr/bin/perl use warnings; use strict; use feature qw{ say }; use DBI; my $db = 'DBI'->connect('dbi:SQLite:;db=:memory:', "", ""); $db->do('CREATE TABLE t1 (id INT, name TEXT, quantity INT)'); $db->do('CREATE TABLE t2 (id INT, quantity INT)'); my $populate = $db->prepare( 'INSERT INTO t1 (id, name, quantity) VALUES (?, ?, ?)'); $populate->execute(@$_) for [ 1, 'John', 12 ], [ 2, 'Jane', 14 ], [ 3, 'Jack', 16 ], [ 5, 'Joe', 22 ], [ 6, 'Josh', 27 ]; my $insert = $db->prepare('INSERT INTO t2 (id, quantity) VALUES (?, ?) +'); my $query1 = $db->prepare( 'SELECT id, quantity FROM t1 WHERE name LIKE ? AND id > ?'); $query1->bind_param(1, 'J%'); $query1->bind_param(2, 2); $query1->execute; while (my @row = $query1->fetchrow_array) { $insert->execute(@row); } my $verify = $db->prepare('SELECT * FROM t2'); $verify->execute; my @row; say "@row" while @row = $verify->fetchrow_array;

  2. Or you want to create a "SELECT INTO" statement. SQLite doesn't support this syntax directly, but features a similar construct that you can use:
    #!/usr/bin/perl use warnings; use strict; use feature qw{ say }; use DBI; my $db = 'DBI'->connect('dbi:SQLite:;db=:memory:', "", ""); $db->do('CREATE TABLE t1 (id INT, name TEXT, quantity INT)'); $db->do('CREATE TABLE t2 (id INT, quantity INT, action INT)'); my $populate = $db->prepare( 'INSERT INTO t1 (id, name, quantity) VALUES (?, ?, ?)'); $populate->execute(@$_) for [ 1, 'John', 12 ], [ 2, 'Jane', 14 ], [ 3, 'Jack', 16 ], [ 5, 'Joe', 22 ], [ 6, 'Josh', 27 ]; my $insert = $db->prepare( 'INSERT INTO t2 (action, id, quantity)' . ' SELECT ?, id, quantity FROM t1 WHERE name LIKE ? AND id > ?'); $insert->bind_param(1, 100); $insert->bind_param(2, 'J%'); $insert->bind_param(3, 2); $insert->execute; my $verify = $db->prepare('SELECT * FROM t2'); $verify->execute; my @row; say "@row" while @row = $verify->fetchrow_array;

    map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (7)
As of 2020-06-05 20:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you really want to know if there is extraterrestrial life?



    Results (40 votes). Check out past polls.

    Notices?