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.

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.

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]

I've written this example script to illustrate what I'm attempting to do:

#my @col = ("machine","runas");
#my @val = ("bar","faa");
my $col = "machine";
my $val = "bar";
my $dbName = "fooDB.db";

#Create DB connection
my $dbh = DBI->connect(          # connect to your database, create if
    "dbi:SQLite:dbname=$dbName", # DSN: dbi, driver, database file
    "",                          # no user
    "",                          # no password
   { RaiseError => 1 },         # complain if something goes wrong
) or die $DBI::errstr;

#Create Table
$dbh->do('create table schedules
        (schedId INTEGER PRIMARY KEY,
        schedName TEXT,
        machine TEXT,
        runas TEXT,
        owner TEXT)' );

#Insert 1 row
my $sth = $dbh->prepare_cached("INSERT into schedules (schedName) valu
+es ('foo')")
        or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute()
        or die "Couldn't execute statement: " . $sth->errstr;

#Get row ID
my $rowId = $dbh->func('last_insert_rowid');

#Do an update with mutiple placeholders
my $sth1 = $dbh->prepare_cached("UPDATE schedules SET ? = ? WHERE sche
+dId = ?")
        or die "Couldn't prepare statement: " . $dbh->errstr;

#Try with bind_param_array
#$sth1->bind_param_array(1, @col);
#$sth1->bind_param_array(2, @val);
#$sth1->bind_param_array(3, $rowId); # scalar will be reused for each 
+row
#$sth1->execute_array()

$sth1->execute($col,$val,$rowId)
        or die "Couldn't execute statement: " . $sth->errstr;
[download]

No matter how I use this, with bind_param_array or with straight scalars, I get the same error:

DBD::SQLite::db prepare_cached failed: near "?": syntax error at ./foo
+.pl line 37.
DBD::SQLite::db prepare_cached failed: near "?": syntax error at ./foo
+.pl line 37.
[download]

You cannot have a fieldname represented by a "?" placeholder (unless you use some trickery as Anonymous Monk said). The prepare method of DBI asks the database to look at the SQL before it is executed and already prepare an execution plan.

Not knowing the fields to use at the moment of preparing the statement handle will not allow the database to prepare a plan, hence the error.

One more comment: having both RaiseError on and using die statements is overkill.

From the docs:

The RaiseError attribute can be used to force errors to raise exceptions rather than simply return error codes in the normal way. It is "off" by default. When set "on", any method which results in an error will cause the DBI to effectively do a die("$class $method failed: $DBI::errstr"), where $class is the driver class and $method is the name of the method that failed.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Re: How to get a placeholder to keep the current column value?