[Dbix-class] Get generated SQL

[Matt Lawrence]

Mark Lawrence wrote:
> On Tue Mar 11, 2008 at 06:12:37PM +0000, Matt Lawrence wrote:
>
>   
>> +=head2 as_static_sql
>> +
>> +    $sql = $cursor->as_static_sql([ \%datatypes | \@datatypes ])
>> +
>> +Returns the SQL for the cursor as complete SQL, with placeholders replaced with
>> +relevant values, quoted by DBI's L<quote|DBI/quote> method.
>> +
>> +If a reference is passed as an argument it is used to ascertain the data type
>> +to pass to L<quote()|DBI/quote>. Array refs are passed in order for the
>> +positional parameters, hashrefs are mapped to columns by name.
>> +
>> +Warnings are issued if there is a mismatch between the number of placeholders
>> +and the number of bind values.
>> +
>> +Passing this SQL to a database is not recommended, certainly not
>> +programmatically. It should be used for informational or debugging purposes
>> +only.
>> +
>> +=cut
>> +
>> +sub as_static_sql {
>> +    my ($self, $datatypes) = @_;
>> +    my $storage = $self->{storage};
>> +
>> +    my ($sql, $bind) = $self->_as_sql;
>> +
>> +    if (ref $datatypes eq 'HASH') {
>> +        $datatypes = [ map { $datatypes->{$_->[0]} } @$bind ];
>> +    }
>> +    elsif (ref $datatypes ne 'ARRAY') {
>> +        $datatypes = [];
>> +    }
>> +
>> +    # Store quoted versions of the values
>> +    my @bind_vals = map {
>> +        $storage->dbh->quote($_->[1], shift @$datatypes)
>> +    } @$bind;
>> +
>> +    # Replace placeholders in the SQL string directly
>> +    # Are there any other possible placeholders other than '?'
>> +    $sql =~ s/(\?)/shift @bind_vals || $1/ge;
>> +
>> +    # Check for a mismatch in the number of placeholders
>> +    if (my $extra = $sql =~ y/\?/\?/) {
>> +        warn "$extra extra placeholder", $extra==1?'':'s', " in SQL";
>> +    }
>> +    elsif (@bind_vals) {
>> +        warn @bind_vals." extra bind parameter", @bind_vals==1?'':'s';
>> +    }
>> +
>> +    return $sql;
>> +}
>>     
>
> You don't deal with binary (non-printable) data. Assuming this is method
> is for debugging purposes its usefulness is limited to pure text and
> won't work for mixed text/binary queries.
>   

Specifying the data type for the bind columns should allow the driver to 
properly escape binary data where appropriate, albeit non-automatically.

I guess I could add a failsafe to trap unprintable values still present 
after quote() and replace them with dummy values as in your previous 
example.

    # Store quoted versions of the values
    my @bind_vals = map {
        $_ = $storage->dbh->quote($_->[1], shift @$datatypes);
        $_ = $storage->dbh->quote('*BINARY DATA*') if /[^[:print:]\n\t]/;
        $_;
    } @$bind;

Are there any circumstances where unprintable values would be wanted anyway?

Matt