Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
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"


In reply to Re: How to get a placeholder to keep the current column value? by arturo
in thread How to get a placeholder to keep the current column value? by liz

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • 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:
    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
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others having an uproarious good time at the Monastery: (12)
    As of 2014-12-26 17:44 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      Is guessing a good strategy for surviving in the IT business?





      Results (174 votes), past polls