http://qs1969.pair.com?node_id=839885

benizi has asked for the wisdom of the Perl Monks concerning the following question:

I've been using DBI + SQL::Abstract for a lot of stuff, and one of the things I need to do now is generate concrete SQL scripts. For example, SQL::Abstract makes converting a hash into an insert extremely simple:

my $dbh = DBI->connect(@params); my $gen = SQL::Abstract->new; my ($insert, @bind) = $gen->insert(atable=>{a=>undef,b=>1,c=>'string'} +); my $sth = $dbh->prepare($insert); $sth->execute(@bind); __END__ $insert eq "INSERT INTO atable ( a, b, c) VALUES ( ?, ?, ? )" @bind contains undef, 1, "string"

But, now I have an occasion where I need to get parameter-free SQL to execute in a (My)SQL script. E.g., given the above example, I'd like to turn $insert and @bind back into:

INSERT INTO atable ( a, b, c) VALUES ( NULL, 1, 'string' )

Part of the problem is terminology. The only keywords I can think of { SQL, literal, abstract, bind, parameters, quoting } all get tons of results in the direction I've been using SQL::Abstract. (That is: ways to use tools like DBI that promote the use of placeholders, etc.)

I'm wondering whether there's a DBD driver that will do this. (e.g. change the connect string to something like 'DBI:fakesql:dialect=mysql', where 'fakesql' is the module I'm unable to find) Or is there another tool that I'm overlooking?

Replies are listed 'Best First'.
Re: Concrete SQL from SQL::Abstract?
by CountZero (Bishop) on May 13, 2010 at 19:32 UTC
    This will do what you want.
    use 5.012; use strict; use warnings; use SQL::Abstract; my $sql = SQL::Abstract->new; my $table ='atable'; my %data = (a=>q/NULL/,b=>1,c=>q/'string and string'/); my $stmt_and_val = $sql->generate('INSERT INTO', \$table, \%data); say $stmt_and_val;
    Output:
    INSERT INTO atable SET a = NULL, b = 1, c = 'string and string'
    And yes, unfortunately, you will have to take care of quoting the data yourself.

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

      And yes, unfortunately, you will have to take care of quoting the data yourself.

      ...that's half the point of my question, and being forced to use 'generate' defeats the benefit of using SQL::Abstract.

      Nonetheless, thanks. I'd glossed over the generate function in the first place, and the following is sufficient for simple inserts:

      my $dbh = DBI->Connect(@params); # initialized elsewhere my $sqlgen = SQL::Abstract->new; my %data = (a => undef, b => 1, c => q/'string and string'/); $_ = $dbh->quote($_) for values %data; print scalar $sqlgen->generate('insert into',\'atable',\%data); __END__ # prints: INSERT INTO atable SET a = NULL, b = '1', c = '\'string and string\''

      (Not sure whether the way it's quoting might cause trouble with, e.g., ZIP codes, where '08540' <> 8540...)

      Still interested in a broader solution.

        Dang. In addition to not noticing the slightly odd insert into X set field=value, field2=value2 syntax (as opposed to the 'insert' method's more standard insert into (fields) values (values)), I didn't realize there's weirdness with question marks:

        my %data = ( url => "http://foo/bar?baz", asdf => 1, thing => \'now()' + ); $_ = (ref) ? $_ : $dbh->quote($_) for values %data; print scalar $sqlgen->generate('insert into',\'atable',\%data); __END__ Use of uninitialized value in substitution iterator at /usr/lib/perl5/ +vendor_perl/5.10.0/SQL/Abstract.pm line 1290. INSERT INTO atable SET asdf = '1', thing = now(), url = 'http://foo/ba +rbaz'

        Seems this simply isn't the general use-case for 'generate'.

Re: Concrete SQL from SQL::Abstract?
by metaperl (Curate) on May 14, 2010 at 13:33 UTC
Re: Concrete SQL from SQL::Abstract?
by metaperl (Curate) on May 14, 2010 at 14:22 UTC

      Thanks for finding this. As with the other helpful suggestion, it's not really the intended use case (for SQL::DB, in this case). But the following hack works:

      my $dbh; # connected elsewhere my $sqlgen = SQL::Abstract->new; my $sqldb = SQL::DB->new; $$sqldb{sqldb_dbh} = $dbh; # <- this is why I called it a hack print $sqldb->query_as_string($sqlgen->insert(atable=>{bar=>1,baz=>und +ef,foo=>"a string's error"})); __END__ #prints INSERT INTO foo ( bar, baz, foo) VALUES ( '1', NULL, 'a string\'s erro +r' )
Re: Concrete SQL from SQL::Abstract?
by metaperl (Curate) on Aug 23, 2010 at 15:17 UTC

      Thanks. Hadn't logged on in a while (unfortunately).

      You should localize $_ there.

      local $_ = $sql;

      instead of:

      $_ = $sql;

      This has some quoting problems, but it's not your code's fault. Since SQL::Abstract doesn't use $dbh->quote_identifier, it can return invalid SQL. E.g. MySQL (at least) allows question marks in table names: mysql -Dtest -e 'create table `o rly?` (a int)'

      In the end, it didn't matter much anyway, as I was stuck doing most of my work in PHP. In that realm, nothing holds a candle to DBI