how can I turn escape processing off in JDBC-based ActiveRecord?

[Stephyn Butcher]

Dear list,

I'm having a bit of a problem and it took me a while to narrow it down. I'm working with a database that has a prescribed naming convention for the columns. Unfortunately, this naming convention includes the use of curly braces in the names of columns. So, for example, I might have a column called COLUMN[{_01}]. Inside the MySQL client, this presents no problems. The statement:

SELECT `COLUMN[{_01}]` FROM TABLE;

works just fine and as it did when I used it with ActiveRecord via Ruby.

However, when I switched to JRuby, I got a nasty surprise:

Unknown column Column[] in statement 'SELECT `COLUMN[{_01}]` FROM TABLE'

which seemed kind of odd to me because, well, COLUMN[] does not appear in the statement. That's when I learned about SQL escape sequences and that JDBC processes SQL escape sequences by default which means that the jdbcmysql adapter is processing escape sequences by default. Using JDBC directly, I can fix my problem as follows:

  stmt = conn.createStatement

  stmt.setEscapeProcessing( false)

  rs = stmt.executeQuery("SELECT `COLUMN[{_01}]` FROM TABLE")

What I would like to hear is that there is an escape_processing parameter to setting up ActiveRecord as in:

development:

adapter: jdbcmysql

escape_processing: false

database: testapp_development

username: root

password: root

However, I doubt this is the case. I did a bit of digging in the gems and I just couldn't identify where I could put the expression:

  stmt.setEscapeProcessing( false)

in the code. Perhaps a list reader could be of some assistance? Is there an alternative.

Cheers,

Steve

[Hassan Schroeder-2]

On Mon, Nov 2, 2009 at 12:13 PM, Stephyn Butcher <stephynb@...> wrote:

> The statement:
> SELECT `COLUMN[{_01}]` FROM TABLE;
> works just fine and as it did when I used it with ActiveRecord via Ruby.
> However, when I switched to JRuby, I got a nasty surprise:
> Unknown column Column[] in statement 'SELECT `COLUMN[{_01}]` FROM TABLE'
> which seemed kind of odd to me because, well, COLUMN[] does not appear in
> the statement. That's when I learned about SQL escape sequences and that
> JDBC processes SQL escape sequences by default which means that the
> jdbcmysql adapter is processing escape sequences by default.

> What I would like to hear is that there is an escape_processing parameter to
> setting up ActiveRecord as in:
> escape_processing: false

> However, I doubt this is the case. I did a bit of digging in the gems and I
> just couldn't identify where I could put the expression:
>   stmt.setEscapeProcessing( false)
> in the code. Perhaps a list reader could be of some assistance? Is there an
> alternative.

WAG: Set this property: processEscapeCodesForPrepStmts
<http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html>
:: in your connection string in database.yml?

Adjust per the version of Connector/J you're actually running :-)

HTH,
--
Hassan Schroeder ------------------------ hassan.schroeder@...
twitter: @hassan

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email