[Dbix-class] Get generated SQL

[Mark Lawrence]

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.

Mark.
-- 
Mark Lawrence