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

[liz]

While I'm (finally) getting to grips with using DBI placeholders, I realise that there may be one situation where placeholders are not the solution.

The situation: I'm writing an XML to RDBMS Perl layer that should allow my customer to use XML to update records in a database. For each type of information (e.g. address information), there are a number of expected nodes in the XML that correspond to columns in a table in the database. For example, a complete description of a record would be:

<address id="34567">
 <phone>123</phone>
 <street>Fred Ziffel Avenue 42</street>
 <zip>123456789</zip>
 <city>Hooterville</city>
</address>

Now, when the customer supplies the following XML:

<address id="34567" mode="update">
 <phone>456</phone>
 <!-- note the absence of other nodes -->
</address>

I want it to update just the "phone" column for that record in the database.

Now, at least in MySQL you can put the following in the SQL to indicate you don't want to change the value of a column. For example: the "street", "zip" and "city" fields.:

UPDATE address SET
 phone="456",
 street=street,
 zip=zip,
 city=city
WHERE ID="34567"

so in principle this should allow you to do a prepare once because the query would always be the same. Only those fields that you don't want changed, get their field name replaced rather than an actual value.

However, it seems to me that it is not possible to do this using placeholders, as you can only specify values with placeholders, rather than a name of the column.

Of course, I could always fetch the old values from the record in the database first, but I feel that sort of defeats the purpose. Or would it?

Anyway, my question: can I use placeholders in case I don't want to change the value of a column, without obtaining the old value from the record first?

Liz

Update:
Thanks to runrig and pg: it would seem that "Dynamic SQL" (never knew there was a phrase for it, as it is the way I was always used to doing it using the older Mysql.pm modules) is the way to go for me, as the number of different field combinations would provide me with a combinatorial explosion. I was hoping that I could use prepare() for this, but it seems that that just isn't possible.

Good News:
Jenda++ shows in Re: How to get a placeholder to keep the current column value? that there _is_ a way to do this with a pre-prepared statement handle, even in MySQL!

[gmax]

Fetching the old values or using column names for dummy updates sounds like a waste of resources to me.

Since I guess that you can get the XML nodes into a hash, I would go for the simpler solution, using a dynamically built SQL statement.

Here is a (tested) example of creating a query with placeholders using a well established idiom. (See DBI recipes for more on this subject.)

# assume this is the hash containing translated XML nodes
my %hash = ( 
    phone => '456',
    street => 'Malcom St.'
);


my @fields = keys %hash;
my $ID = "34567";

my $update_query =  qq{UPDATE address\n SET}
    . join(",\n", map { " $_ = ? "} @fields)
    . qq{\nWHERE id = ?};

print "$update_query\n";

my $sth = $dbh->prepare($update_query);

my $rows = $sth->execute(@hash{@fields}, $ID);

print "$rows rows affected\n";
__END__

The query produced by this code is

UPDATE address
SET phone = ? ,
 street = ?
WHERE id = ?

Provided that the keys in your hash have a corresponding column name, you can add or remove keys at will, and this kind of code will work smoothly.

Update
Be aware that this solution won't work when the value submitted for update is a formula. E.g. UPDATE wages SET salary = salary * 1.25 . If you pass "salary * 1.25" to a placeholder, you'll get a string, which MySQL tries to convert into a number, thus resulting in a value of "0" (zero). See the MySQL manual.
Remember that, since placeholders imply quoting, if you want to allow your users to use formulas you'll need to parse their input, check for allowed expressions, quote the quotable parts, and compose the query in a different way.

[pg]

There is this concept called dynamic SQL, which means the SQL statement is formed base on certain conditions or situation. In this case it is based on the presence of certain fields in your XML. The usual ones are called static SQL query, and its form keeps the same, and is not inferenced by what is going on.

Performance is affected by using dynamic SQL, but the solution is absolutely valid, and in some situation preferred.

Dynamic SQL is usually considered as a ++ for a database, as it increase flexibility and power, although you have to use it with caution, but what not. A good example where dynamic SQL is preferred is your search screens, as people might skip certain optional search criteria.

[runrig]

I would not have one fixed sql statement in this case. I would just build it dynamically on each record. Perhaps something like the prepare_cached examples in the DBI docs (it would be easy enough to tweak the insert or select example to do an update), although I would use prepare and not prepare_cached, and just update the columns that need to be updated. You could use placeholders, or use the quote method to paste the values directly into the sql statement.

If there are really only 4 fields that could be updated, and there are alot of records to update, then using prepare_cached there would only be at most 2**4 - 1 = 15 different statements cached, which is not bad. If this is only an example, and there are really many more fields, then prepare_cached would be a very bad option.

[edan]

I realize that you already seem to have decided to dynamically generate the queries (which seems like the right solution to me, too), but I still want to make a point here for future reference...

Of course, I could always fetch the old values from the record in the database first, but I feel that sort of defeats the purpose. Or would it?

This is a bad idea, unless you plan to implement a lock on the rows (or tables) in question between the select and subsequent update, since someone else could update the row in between your 2 operations, and then you'll stomp on their update.

[Jenda]

What about

UPDATE address SET
 phone = COALESCE(?, phone),
 street = COALESCE(?, street),
 zip = COALESCE(?, zip),
 city = COALESCE(?, city)
WHERE ID=?

This way the SQL is static, can be prepared and if you pass an undef for some field it'll keep the old value. Of course this would not work if you did want to be able to set some fields to NULL, but otherwise this seems to be a nice solution.

P.S.: I have never worked with MySQL so I'm not sure there is a COALESCE() function there. It's supposed to return the first non-NULL parameter.

Edit by castaway: Closed small tag in signature

[Anonymous Monk]

Clever. It will work, and it will do what the OP wanted. But it is not a clean solution.

this would not work if you did want to be able to set some fields to NULL

Right. And this is a good reason not to use this feature.

A second good reason to avoid this idiom is that it makes debugging more difficult. If all updates look the same, it will be harder to spot the offending one in a log file whan you're hunting for bugs.

A further good reason not to go for this solution is that you are straining the DBMS to encourage the programmer's laziness, and I am not talking of the Laziness that makes good coders.

It isn't difficult to create a query using only the fields that are needed. Why looking for trouble?

[Jenda]

Whether the fact that you can't reset the columns to NULL means you can't use this solution or not depends on the situation. What if all the columns in question disallow NULLs? I would not consider this a general reason to frown upon this solution. It's just something you have to keep in mind. How often do you actually want to set something to NULL?

I do not understand the second reason. Sounds like you thought that building dynamic SQL including the values would be the best solution then. And I'm actually not sure what log file do you mean. Could you explain?

Without benchmarking I would not dare to say whether the additional burden of "updating" some fields by the old values is bigger that the burden of either recompiling the SQL each time or keeping an exponential number of prepared statements. And I bet the results would differ a lot depending on the indexes and triggers on the table and the size of those columns.

Sure it's not dificult (in Perl) to create a query containing just the columns you want to update. The problem is that this way you either force the SQL server to parse and compile the statement each time or you have to cache a potentialy big number of different statements, one for each combination of updated columns. Both are straining the DBMS and the second one heavily complicates the code.

And there is actually one more reason why this IS quite often the one good solution is that quite often you want to keep all SQL inside stored procedures. That way you may rename and/or split tables without having to search for SQL in all programs and pages that might access the database. Plus the DBAs can see/modify/optimize all SQL that's gonna get executed against the database. Neither Monster.com nor VIPeR actually allow any inline SQL, all database access has to go via stored procedures.

And neither constructing and executing some ad-hoc SQL inside the stored procedures nor having an explosion of IFs seem like a very good solution to me. (The first would not be accepted by the Monster DBAs anyway.)

Edit by castaway: Closed small tag in signature

[giulienk]

It seems that MySQL's COALESCE allows to input NULL values as if all the values are NULL, NULL will be returned.

I quote from MySQL Manual:

COALESCE(list)
    Returns first non-NULL element in list:

mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL

UPDATE: ops, i wasn't careful enough to check the solution offered. In that case NULL won't be returned so Jenda's assumption is right.

[liz]

Jenda++!

This is what I was looking for! And according to the MySQL documentation it is supported by MySQL!

It would seem to me that gmax needs to add another idiom to DBI Recipes! ;-)

Liz

[Anonymous Monk]

Anyway, my question: can I use placeholders in case I don't want to change the value of a column, without obtaining the old value from the record first?

No. The purpose of placeholders is to prevent injection of sql code (basically save you the trouble of calling quote).

[arturo]

The way I address this sort of problem is to not address it, really, and it involves core elements of application design; first and foremost, I use an OO approach. What i do is set up an object that serves as a repository for the objects in the database. The repository handles all the SQL, and, if you want to minimize DB accesses, make the repository persistent and have it cache the objects (this works best, of course, if your primary means of fetching the objects is via their primary keys). To update an object, you check a copy out of the repository, modify it as needed (of course, you might need to make sure that it's hard to change certain values, e.g. primary keys) , then call $repository->update($object);, where the update call basically looks like what you have. , where foo, bar, baz designate all the updateable fields. If you've pre-cached the object, there's no need to call a new "fetch" routine to figure out what the old values are. Of course, that might not be appropriate.

I've played with trying to build the SQL dynamically (by checking to see which fields on the object are different from the one stored in the repository, and generating an SQL UPDATE statement on the fly), but that can be error-prone and time-consuming.

Another option eschews database independence -- and yes, I'm well aware that this option is probably of no solace to you, unless Postgres is an option. If you have a DBMS that supports triggers, you can just issue all the SQL as-is, and write your triggers to refuse to overwrite null fields; in PL/SQL (Oracle), such a trigger would look something like this:

  -- :NEW.XXXX refers to the value of the XXXX field specified in the 
+update statement
   -- :OLD.XXXX refers to the value in the record being updated
  IF :NEW.foo IS NULL THEN
    :NEW.foo := :OLD.foo
  END IF;

This does have the drawback, as I mentioned, of being non-portable (but I have sinned, I do use it on occasion). If your application's ecological niche makes the precaching repository approach feasible, I'd suggest that, otherwise I believe that the dynamic SQL is the best approach. Good luck!

[BUU]

This is a tad late and perhaps superfluous, but why are you bothering with all this dynamic sql and so on? When you execute an update in mysql it only changes the columns you specify, yes? So just leave all the other fields blank and let the database handle it. Eg, assuming you have a hash with the key being the column name and the value being the, uh, value, then just do something like:

my $sql='UPDATE table SET '.join',',map {"$_=".quote($hash{$_})} keys 
+%hash;

Then, in your example, you just end up with sql looking like:

UPDATE table SET phone='456'

Obviously you need a where statement and so on, but thats simple. This way you only touch the fields you want to change and you don't need to worry about restoring the old values and fun stuff like that. The only problem I see is if they wanted to submit say, an empty field and not update the value which would probably require something slightly more complicated then a nice map, but perhaps you could chain maps:

map {"$_=".quote($hash{$_})} grep defined $hash{$_},keys %hash;

[castaway]

Pardon me for wondering, but, isn't this dynamic SQL, that you're proposing? ;)

Dynamic = produced on the fly, not static.

C.

[BUU]

Well, ok, perhaps I missused the term or over used it or something of that nature. I really meant however, that it seemed much simpler to just touch the fields you want and ignore the rest, letting the database do it for you rather then trying to find all the old values and so on yourself. Dynamic sql just kind of slipped in and out there =].