Fill in SQL from Bind Values

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

I'm submitting a REST query to Yahoo and they don't care about bind values and place holders. I used SQL::Abstract to make my query to them but is there a defacto non hackery way to fill in the bind values to the placeholders to get the full SQL statement? As far as I can tell it isn't in the SQL::Abstract documentation.

Here is my code:

     use SQL::Abstract;

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

     #  get query string
     my ( $stmt, @bind ) = $sql->select(
          'yahoo.finance.historicaldata',
          [ '*' ],
          [
               -and => [ 
                    symbol    => $arg{'symbols'},
                    endDate   => $arg{'end_dt'}->ymd('-'),
                    startDate => $arg{'start_dt'}->ymd('-'),
               ],
           ],
     );

### outputs
# $stmt = 'SELECT * FROM yahoo.finance.historicaldata WHERE ( ( ( symb
+ol = ? OR symbol = ? ) AND endDate = ? AND startDate = ? ) )';
#\@bind = [
#          'VZ',
#          'T',
#          '2013-10-19',
#          '2013-10-29'
#        ];
#

### wanted
# $stmt = 'SELECT * FROM yahoo.finance.historicaldata WHERE ( ( ( symb
+ol = VZ OR symbol = T ) AND endDate = 2013-10-29 AND startDate = 2013
+-10-19 ) )';
[download]

Comment on Fill in SQL from Bind Values Download Code

Replies are listed 'Best First'.
Re: Fill in SQL from Bind Values by rminner (Chaplain) on Oct 29, 2013 at 06:22 UTC
DBI-trace() might be what you are looking for. See also: http://search.cpan.org/~timb/DBI-1.630/DBI.pm#TRACING http://oreilly.com/catalog/perldbi/chapter/ch04.html - search for "Tracing DBI Execution " http://www.felixgers.de/teaching/perl/perl_DBI.html - see the debugging section Update: The following perlmonks thread might also be of interest to you: Pretty printing the exact query (with bind variables) executed by DBI? It seems the parameters are actually never really inserted into the statement, but passed separately to the DBD. So if you really want the statement with the parameters inserted, you will have to do it manually (see also the solutions by awohld and hdb in this thread).	[reply]
Re: Fill in SQL from Bind Values by awohld (Hermit) on Oct 29, 2013 at 05:26 UTC
This is what I came up with... as elegant as I could think but still feels hackery. But shouldn't there be a way to just get the straight SQL without the bind and placeholder values? `my @sql_char_list = map { my $char = $_; my $wanted = $char eq '?' ? shift @bind : $char; $wanted; } split '', $stmt; $stmt = join "", @sql_char_list; die Dumper $stmt;` [download]	[reply] [d/l]
Re^2: Fill in SQL from Bind Values by hdb (Monsignor) on Oct 29, 2013 at 09:00 UTC
Not sure you should be doing it this way, but you can distill your code into: `$stmt =~ s/\?/shift @bind/ge;` [download] or with quotes `$stmt =~ s/\?/"'".(shift @bind)."'"/ge;`	[reply] [d/l] [select]