in reply to Using prepared_cache with placholders

As you can see here, I'm creating a separate SQL statement for each command and then creating a hash of definition->SQL.

Why? The SQL never changes, so you're just using up memory for nothing.

This is sloooow compared to working with a HOH, which I am currently trying to switch from.

Not surprised. You're using Switch, creating redundant SQL, keeping the file in memory, and doing redundant round trip to database.

You need to start from scratch.

Is there a way I can leverage placeholders and prepared cache if that many values change on each statement?

Yes, absolutely. Besides preventing SQL injection, placeholders are there to speed things up.

  • Comment on Re: Using prepared_cache with placholders

Replies are listed 'Best First'.
Re^2: Using prepared_cache with placholders
by herda05 (Acolyte) on Apr 03, 2010 at 22:31 UTC
    Why? The SQL never changes, so you're just using up memory for nothing.

    Sorry, should have posted the error. I'm guessing that according to SQLite, the SQL does change. My prepared_cached statement goes from:

    UPDATE twsJobs SET command = ? WHERE jobId = ?";

    to:

    UPDATE twsJobs SET ? = ? WHERE jobId = ?";

    and the error I receive is:

    DBD::SQLite::db prepare_cached failed: near "?": syntax error at...

    Evidently, by changing the column name SQLite feels I'm altering the SQL and therefore I get the error with placeholders. By placing the column name in place of the 1st ?, the SQL runs fine. If I can't have a placeholder for the column names, I need to generate the SQL statement each time. I'm hoping some monk may be able to give me insight into how to have a placeholder for the column name.

[reply]
[d/l]
[select]

      DBD::SQLite::db prepare_cached failed: near "?": syntax error at...

      You're getting the error because placeholders can only replace values, not field names, table names, SQL clauses, etc. 

      my $sth = $dbh->prepare("
   UPDATE twsJobs
      SET ? = ?
    WHERE jobId = ?
");
$dbh->execute($field_name, ..., ...);

      [download]
      should be 
      my $q_field = $dbh->quote_identifier($field_name);
my $sth = $dbh->prepare("
   UPDATE twsJobs
      SET $q_field = ?
    WHERE jobId = ?
");
$dbh->execute(..., ...);

      [download]
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom