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 | [EMAIL PROTECTED] ])
+
+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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
