(Perl) Create query for DBI with SQL::Abstract

Question

I want to add new record to table1 on SQLite

use SQL::Abstract;
my %data = (
  id => \'max(id)', # it is doesn't work so which variant is right?
  record => 'Something'
);

my $sql = SQL::Abstract->new;

my ($stmt, @bind) = $sql->insert('table1', \%data);


...
my $sth = $dbh->prepare($stmt);

If I used DBIx::Class in Catalyst app I would written like so:

id => $c->model('Model')->get_column('id')->max()

and it will work fine. So how can I reach the same aim but using just SQL::Abstract which is used in DBIx::Class as well. Could someone fixed it? Thanks.

That seems really inefficient. Why not make the column auto-increment or use a counters table? — Lucas
– Lucas, Commented Apr 8, 2013 at 15:59
It's also subject to race conditions if you have more than 1 process doing inserts. — cjm
– cjm, Commented Apr 8, 2013 at 16:26
I just wanna understand how to use this module for making complicated queries. — edem
– edem, Commented Apr 8, 2013 at 16:52
Think backwards - what do you think the SQL should look like? Can you do this in one step or does it have to be two (finding the max id and then inserting new record)? How do you find the max id (is it a function call or a separate SQL)? It may just not be the best use case for this Perl module. — theglauber
– theglauber, Commented Apr 8, 2013 at 18:38
Caveat: i don't use this perl module, but maybe you're looking for the syntax for calling SQL functions: search.cpan.org/~frew/SQL-Abstract-1.73/lib/SQL/… — theglauber
– theglauber, Commented Apr 8, 2013 at 18:44

Miguel Prz · Accepted Answer · 2013-04-08 20:12:02Z

2

This is a piece of code. As you can see, first you need to get the max id+1 and then do the insert command. I have to notice you this is not safe, because in a multi-(user,process,thread) environment, a second process can execute the same code and get race conditions. But I assume you are just learning the SQL::Abstract api, and that problem doesn't matter

use DBI;
use SQL::Abstract;

#create table TEST(ID integer, NAME varchar);
my $dbh = DBI->connect('dbi:SQLite:dbname=test.db', '', '', {AutoCommit=>1});

my $sql = SQL::Abstract->new;

my($stmt, @bind) = $sql->select("TEST", [ 'max(ID)+1 as ID' ] );
my $sth = $dbh->prepare($stmt);
$sth->execute(@bind);

my ($id) = $sth->fetchrow_array // 1;

print "Select ID: $id", "\n";
$sth->finish;

($stmt, @bind) = $sql->insert("TEST", { ID=>$id, NAME=>"test-name"} );
$sth = $dbh->prepare($stmt);
$sth->execute(@bind);

$dbh->disconnect;

answered Apr 8, 2013 at 20:12

Miguel Prz

13.8k32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

edem Over a year ago

Thanks for your advice. You showed me a variant which consist of two steps but I would like do the same thing by one step. Something like this: id => { -in => \'select max(id) from table1' }

Collectives™ on Stack Overflow

(Perl) Create query for DBI with SQL::Abstract

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related