I need to access data in a table that has been encoded with the MySQL ENCODE function. I would like to be able to transparently access this data with Class::DBI such that it always uses DECODE on read and ENCODE on write.

Class::DBI is set up with standard SQL fragments, and not wishing to track down and override every one of these, or write custom SQL that mimics the standard functions, I am looking for the best place to intercept.

The low-level get and set functions operate at the Perl level, so don't seem appropriate (and I'm not sure of the ramifications of duplicating (EN|DE)CODE functionality in Perl).

The only other place I can think of is to override transform_sql to search the string for the appropriate column names and replace them with ENCODE or DECODE as required - however this would require testing if the SQL statement is a SELECT or INSERT/UPDATE, and on the whole seems like a lot of work.

Is there a simpler approach to this? Are there convenient hooks which I haven't thought of because they are not heavily advertised? Should I just write custom SQL fragments and be done with it (which reduces the whole concept of genericism)? Do the wise monks here have experience with this sort of technique that they could share with a humble neophyte?

Oh, before anyone suggests using an entirely different approach, this needs to coexist with lots of legacy code so changing the database design isn't going to happen.

Thanks in advance to all for any assistance offered.

In reply to Class::DBI and MySQL ENCODE/DECODE by Anonymous Monk

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!
  • 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, details, 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, summary, 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.