Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

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.

Replies are listed 'Best First'.
Re: Class::DBI and MySQL ENCODE/DECODE
by perrin (Chancellor) on Dec 21, 2005 at 17:35 UTC
    Overriding the SQL fragments is pretty easy. If you look in at the top of the Class::DBI code, you'll see them.
[reply]

      Well yes, but ...

      SELECT %s
      FROM __TABLE__
      WHERE __IDENTIFIER__

      or

      UPDATE __TABLE__
      SET %s
      WHERE __IDENTIFIER__

      One would need to define custom SQL for the fields that need to be encoded or decoded, or else encode or decode everything.

      So it seems that the string being passed in must contain the ENCODE/DECODE only for the fields that need it, or else modify the query after it has been built.

      What I am trying to identify is where to put it to avoid duplication of code and/or special casing.

[reply]
        Didn't you just say you want encode/decode everything for this table? If you override these in one class/table, it will not affect any others.
[reply]

Back to Seekers of Perl Wisdom