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

Thanks for a good hint & code.

I'm using DBD:DB2.
Maybe quatation (') rwapping data must be dropped, and
"CURRENT TIMESTAMP" keyword will be treated.

my @new_values = ();
my @place_holders =
map {
if (/^CURRENT TIMESTAMP$/) {
$_;
} else {
push @new_values => $_;
"?";
}
} @args
;

I've read DBI doc but I cann't understand prepare_cached.
Any difference between prepare and prepare_cached?

my $q = join ',' => map { '?' } @args;
my $sql = "$name VALUES ($q)";
if (!exists $sth{$sql}) {
$sth{$sql} = $dbh->prepare_cached($sql);
}
return $sth{$name}->execute(@args);


Regards,
Hirosi Taguti
h-taguchi@xxxxxxxxxxx


> -----Original Message-----
> From: Greg Sabino Mullane [mailto:greg@xxxxxxxxxxxx]
> Sent: Monday, August 22, 2005 10:26 AM
> To: dbi-users@xxxxxxxx
> Cc: Hirosi Taguti
> Subject: Re: Memoizing non-place-holding sql statements to
> place-holding
>
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > Is there a module already existing to memoize non-place-holding sql
> > statements to place-holding ones and suppress prepare to minimum?
>
> If I am understanding you correctly, there is no such module, although
> there are some modules out there that attempt to deconstruct SQL
> commands that might be adapted to the task. Far better to clean up
> the application however, and force it to use a normal prepare/execute
> scheme with placeholders.
>
> > I'm now transforming other peaple's scripts for a
> performance inprovement.
> > This system is so big, I cann't understand quickly, but I
> foud that all
> > SQL statements are made wothout place holders, and all
> INSERT statements
> > are processed by the next subrourine.
>
> > sub insert_sql {
> > my ($dbh, $sql) = @_;
> > my $sth = $dbh->prepare($sql) or die;
> > $sth->execute();
> > $sth->finish;
> >
>
> If you use prepare_cached() instead of prepare(), you may
> gain some advantage.
> If you are not using placeholders, you are probably better
> off using do()
> instead of the prepare/execute method. (also, the sth->finish
> above is not
> necessary for inserts).
>
> > I think this idea can be used to memoize each INSERT
> statement as one with
> > place holders, prepare only once for each SQL and execute
> "execute" method
> > for each call.
>
> That's not a bad hack if the code is really too bad to go
> back and change
> for the better. It really depends on which RDBMS you are using and how
> complex the SQL is, of course, but here's a first rough stab I whipped
> out which I think does what you want:
>
> sub insert_sql {
> my ($dbh,$sql) = @_;
> if ($sql !~
> /^\s*(INSERT\s+INTO\s+[^\(]+\s*\(\s*[^\)]+\s*\))\s+VALUES\s+\(
> (.+)\)\s*$/io) {
> my $line = (caller)[2];
> warn "insert_sql was unable to parse the statement from
> line $line: $sql\n";
> return $dbh->do($sql);
> }
> my ($name,$args) = ($1,$2);
> my @args = split /,/ => $args;
> if (!exists $sth{$name}) {
> my $q = join ',' => map { '?' } @args;
> $sth{$name} = $dbh->prepare_cached("$name VALUES ($q)");
> }
> return $sth{$name}->execute(@args);
> }
>



.

Relevant Pages

  • Re: Error:Invalid procedure call or argument
    ... > Number 0 everytiime I execute this code: ... > Private Sub Update() ... > On Error GoTo ErrHand ... > Following is an example of the completed SQL string. ...
    (microsoft.public.access.formscoding)
  • Re: Detach or change SQL data source for Word template, and other
    ... Sub MERGE ... .Execute Pause:=False ... SQL statement programmatically, from a security perspective you should ... Word data source. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Combo Box - How to populate from a table
    ... through the records in the recordset. ... Private Sub Form_Load ... oRS.Open sSQL, oConn, adOpenForwardOnly, adLockReadOnly, adCmdText ... 'and retrieves all rows in the table (same as the above SQL but the ...
    (microsoft.public.vb.controls)
  • Re: search by 1 of 3 combos
    ... I guess I should expand a bit on an example of what you can do with modifying SQL for a search combo... ... , mWhere as string ... Dim mRecordID As Long ... Private Sub cmd_search_Click ...
    (microsoft.public.access.formscoding)
  • Re: Use table in Replace function
    ... sub btnGo in my actual db. ... That will cause the SQL statement to be printed out to the ... >> (rst already declared). ... >> Dim rst As New ADODB.Recordset ...
    (microsoft.public.access.formscoding)