Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

Comment on

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


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 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!

In reply to How to get a placeholder to keep the current column value? by liz

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!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • 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
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            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?

    What's my password?
    Create A New User
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others musing on the Monastery: (5)
    As of 2018-03-18 23:44 GMT
    Find Nodes?
      Voting Booth?
      When I think of a mole I think of:

      Results (231 votes). Check out past polls.