Beefy Boxes and Bandwidth Generously Provided by pair Networks BBQ
Think about Loose Coupling
 
PerlMonks  

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

by arturo (Vicar)
on Oct 26, 2003 at 15:29 UTC ( #302209=note: print w/ replies, xml ) Need Help??


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

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!

If not P, what? Q maybe?
"Sidney Morgenbesser"


Comment on Re: How to get a placeholder to keep the current column value?
Select or Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://302209]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (6)
As of 2014-04-18 03:51 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (461 votes), past polls