Equals-Operator on RAW Columns

From: Arno Huetter <arno.huetter_at_aon.at>
Date: 1 May 2004 02:09:33 -0700
Message-ID: <afd36318.0405010109.152751bb_at_posting.google.com>

Hi there,

I try to execute a DB-Update using
ADO.NET CommandBuilder (MSORA Data Provider) on Oracle 8.1.6 (unfortunately, ODP.NET seems to require Oracle 9i or above). One of ADO.NET's
DataTable columns is of type Byte-Array, and refers to a RAW column on the Oracle-DB. The CommandBuilder generates Update-Statements of this kind:

Update table set col = :col_new_value where col = :col_old_value;

Ok, the parameters are actually called p0...pn. The problem is that the Byte-Array will be expanded to an expression like
'0f0f0f0f0f0f0f0f0f0f0f0f0f0f0f0f', so the statement will expand to:

Update table set col = 'ffffffffffffffffffffffffffffffff' where col =

'0f0f0f0f0f0f0f0f0f0f0f0f0f0f0f0f';

The old RAW-value in the database is
'0F0F0F0F0F0F0F0F0F0F0F0F0F0F0F0F'. But the update fails due to
missing capitalization - the equals-operator only seems to work on capitalized hex values.

This one would work fine:

Update table set col = 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'

where col = '0F0F0F0F0F0F0F0F0F0F0F0F0F0F0F0F';

Due to the nature of ADO.NET CommandBuilder I cannot really influence the way it generates its Update-statements. I cannot hardcode SQL either (e.g. using HEXTORAW), as this legacy application is continuously based on CommandBuilders.

Any ideas how to circumvent this behaviour? Is there a way to convince Oracle to accept lowercase hex strings on equals comparisons as well?

Thanks a lot in advance!

Kind regards,
Arno Huetter Received on Sat May 01 2004 - 11:09:33 CEST