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
-- :OLD.XXXX refers to the value in the record being updated
IF :NEW.foo IS NULL THEN
:NEW.foo := :OLD.foo
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!
If not P, what? Q maybe?
Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
Read Where should I post X? if you're not absolutely sure you're posting in the right place.
Please read these before you post! —
Posts may use any of the Perl Monks Approved HTML tags:
You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
- a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
Link using PerlMonks shortcuts! What shortcuts can I use for linking?
See Writeup Formatting Tips and other pages linked from there for more info.
| & || & |
| < || < |
| > || > |
| [ || [ |
| ] || ] ||