Re: Memoizing non-place-holding sql statements to place-holding

Greg Sabino Mullane wrote:

there are some modules out there that attempt to deconstruct SQL
commands that might be adapted to the task

As, for example SQL::Statement which would catch a few things your hack wouldn't - e.g. embedded escaped quotes in values, or INSERT statements that don't specify the column names (which are valid even if unwise).

#!/usr/bin/perl -w
use strict;
use SQL::Statement;
my $sql = q{ INSERT INTO MyTbl (col1,col2) VALUES (7,'o''brian') };
my ($pholder_sql,@vals) = make_pholders( $sql );
print "$pholder_sql [@vals]\n";

# output : INSERT INTO MYTBL (COL1,COL2) VALUES (?,?) [7 o'brian]

sub make_pholders {
   my($sql) = @_;
   my $stmt = SQL::Statement->new( $sql );
   my($table)   = map {$_->name} $stmt->tables;
   my($columns) = join ',',map {$_->name} $stmt->columns;
   my(@values)  = $stmt->row_values;
   my $pholder  = join ',',('?')x@values;
   my $pholder_sql = "INSERT INTO $table ($columns) VALUES ($pholder)";
   return $pholder_sql,@values;
}
__END__

--
Jeff


.

Relevant Pages

  • Re: Weird printing behaviour
    ... File>Print and the toolbar button are different commands. ... specified in your printer driver. ... The File>Print command allows you to specify the options you want. ...
    (microsoft.public.mac.office)
  • Re: SVG in LaTeX einbinden?
    ... Es besteht aber die Möglichkeit, ... | specify the rules for converting from in_format to out_format These ... | rules may be used to translate directly between formats. ... | Commands which specify only ...
    (de.comp.text.tex)
  • Re: [SLE] Useradd without the default secondary groups
    ... >> from any secondary group they should not be in. ... >> you to specify which groups the user is in. ... I just try to find a way to do it with the commands ... Please read the FAQs: suse-linux-e-faq@suse.com ...
    (SuSE)
  • Re: ALTER Table/UPDATE Syntax
    ... AnnMarie wrote: ... I've used the code below, which works when I specify the actual table name and number for days, but states it can't find the table when I use a variable. ... For human replies, replace the queue with a tea ...
    (microsoft.public.access.modulesdaovba)
  • Re: Using Sockets
    ... > One more thing......Can I specify a timeout value for the send and connect ...
    (microsoft.public.vc.mfc)