Bug #68625 for DBIx-Class: Scrambled field names on DBD::Oracle

Fri Jun 03 08:30:36 2011 RODRIGO [...] cpan.org - Ticket created

Subject:

Scrambled field names on DBD::Oracle

Some complex SQL columns in a DBIC-generated query get scrambled. Example: $where = { 'name||description||bl||username' => "%asdf%" }; $rs->search( $where )->all; The resulting statement and error: DBIx::Class::ResultSet::all(): DBI Exception: DBD::Oracle::db prepare_cached failed: ORA-00904: "SR_8C6ZTZCIQ8": identificador no válido (DBD ERROR: error possibly near <*> indicator at char 129 in 'SELECT me.id, me.name, me.description, me.active, me.ts, me.bl, me.username, me.ns FROM mytable me WHERE ( Nm||dscrptn||bl||<*>sr_8C6ZTZCIQ8 = :p1 ) ') Sometimes it may work... I couldn't nail which queries are bad and which aren't. Apparently the vowels are removed and the rest of the line gets scrambled. This problem only occurs with DBD::Oracle and DBIC 0.08192 (0.08127 works fine). Tried with different versions of modules (SQL::Abstract, DBD::Oracle, Perl 5.10.1 and 5.14, Linux, AIX), and it all indicates that DBIC is the culprit. It works fine with DBD::SQLite.

Fri Jun 03 08:38:53 2011 abraxxa [...] cpan.org - Correspondence added

That's because the newer version checks more strictly if you follow the syntax. To pass literal sql to DBIC you always have to pass it by reference: http://search.cpan.org/~frew/DBIx-Class-0.08192/lib/DBIx/Class/Manual/Cookbook.pod#Using_SQL_functions_on_the_left_hand_side_of_a_comparison Please report back if this fixes your issue.

Fri Jun 03 08:38:55 2011 The RT System itself - Status changed from 'new' to 'open'

Tue Jun 07 06:05:51 2011 ribasushi [...] leporine.io - Correspondence added

On Fri Jun 03 08:30:36 2011, RODRIGO wrote: Show quoted text

> Some complex SQL columns in a DBIC-generated query get scrambled. >

You did not provide us with a complete query (DBIC_TRACE=1) so it's hard to say what is the actual cause for your problem. However it is very likely that you are experiencing the result of the (fixed) bug reported in RT#68093. This should be released in a couple of days to cpan, but you are welcome to try our master branch to see if this solves your issue. Cheers

Wed Jun 15 02:56:02 2011 ribasushi [...] leporine.io - Correspondence added

Changing status to patched, by all indications the upcoming 0.08193 contains a fix for this issue. Feel free to reopen this if the problem persists after 0.08193 ships (hopefully in the next couple days).

Wed Jun 15 02:56:03 2011 ribasushi [...] leporine.io - Status changed from 'open' to 'patched'

Thu Jul 14 18:25:25 2011 ribasushi [...] leporine.io - Fixed in 0.08193 added

Thu Jul 14 18:25:25 2011 ribasushi [...] leporine.io - Correspondence added

Feel free to reopen this if the problem persists.

Thu Jul 14 18:25:26 2011 The RT System itself - Status changed from 'patched' to 'open'

Thu Jul 14 18:25:26 2011 ribasushi [...] leporine.io - Status changed from 'open' to 'resolved'

Fri Jul 15 04:19:04 2011 RODRIGO [...] cpan.org - Correspondence added

Ok, thanks for looking into this! Changing the syntax did solve it for me, although the older syntax was so much easier - and clearer. Finally we're staying on 0.08127 for now, we get lots of Oracle errors with sequences and triggers when using the latest DBIC... I haven't had the time to report them still, as they would probably go into a separate thread. cheers! -rodrigo

Fri Jul 15 04:19:07 2011 The RT System itself - Status changed from 'resolved' to 'open'

Fri Jul 15 05:10:36 2011 abraxxa [...] cpan.org - Correspondence added

Am Fr 15. Jul 2011, 04:19:04, RODRIGO schrieb: Show quoted text

> Ok, thanks for looking into this! > > Changing the syntax did solve it for me, although the older syntax was > so much easier - and > clearer.

There is no 'old syntax' as the syntax wasn't changed. Instead the newer versions follows the documented syntax whereas the old version generated working queries from wrong syntax which hid it. Show quoted text

> > Finally we're staying on 0.08127 for now, we get lots of Oracle errors > with sequences and > triggers when using the latest DBIC... I haven't had the time to > report them still, as they would > probably go into a separate thread.

The Oracle storage module uses INSERT INTO since some versions if your Oracle is sufficiently new so the sequence names don't matter any more. Which Oracle version are you running?

Fri Jul 15 08:18:04 2011 ribasushi [...] leporine.io - Correspondence added

On Fri Jul 15 04:19:04 2011, RODRIGO wrote: Show quoted text

> Ok, thanks for looking into this! > > Changing the syntax did solve it for me, although the older syntax was > so much easier - and > clearer. > > Finally we're staying on 0.08127 for now, we get lots of Oracle errors > with sequences and > triggers when using the latest DBIC... I haven't had the time to > report them still, as they would > probably go into a separate thread. >

Please do realize that unreported bugs are bound to remain unfixed :)

Tue Jul 19 07:55:12 2011 ribasushi [...] leporine.io - Correspondence added

On Fri Jul 15 04:19:04 2011, RODRIGO wrote: Show quoted text

> Ok, thanks for looking into this! > > Changing the syntax did solve it for me, although the older syntax was > so much easier - and > clearer. > > Finally we're staying on 0.08127 for now, we get lots of Oracle errors > with sequences and > triggers when using the latest DBIC... I haven't had the time to > report them still, as they would > probably go into a separate thread. >

Closing this particular bugreport, please please PLEASE file a new one with the remaining issues :) We *will* fix them.

Tue Jul 19 07:55:16 2011 ribasushi [...] leporine.io - Status changed from 'open' to 'resolved'