Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

I get an error using a DBIx::Class model in Catalyst but not when it is standalone

by ivanwillsau (Novice)
on Mar 07, 2011 at 22:04 UTC ( #891919=perlquestion: print w/ replies, xml ) Need Help??
ivanwillsau has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I have a Catalyst project that talks to an Oracle database and tries to read a column from a table that is a CLOB data type. When I run the query in Catalyst I get an error ORA-24812 (which is about character code conversion). I then run the same query in a test script that just loads the Catalyst model directly runs the same query and get no error.

The databse is an Oracle 10g database which has recently been changed from US7ASCII to AL32UTF8 character encoding, and it belongs to another project so I have no ability to change it in any way.

I ran both queries with dbi tracing turned on to level 4 (see diff below), the only (possible?) differences that I could see where the ptype of one bind params was different and a csid in the working code has 1->0->1 and the filing code has 873->0->873. I think the problem is due to Catalyst setting some character encoding setting somewhere but I can't work out what is set or how to unset it (tried using utf8 and setting NLS_LANG et al Oracle environment variables.)

--- test.trace 2011-03-08 08:41:27.000000000 +1100 +++ cat.trace 2011-03-08 08:41:28.000000000 +1100 @@ -1,6 +1,6 @@ - DBI::db=HASH(0x???????) trace level set to 0x0/4 (DBI @ 0x0/0) in + DBI 1.609-ithread (pid 4136) + DBI 1.609-ithread default trace level set to 0x0/4 (pid 4122) at +Inbox.pm line 386 via Inbox.pm line 346 -> prepare_cached for DBD::Oracle::db (DBI::db=HASH(0x???????)~0x +??????? 'SELECT me.id, me.part_ref, me.image_id, me.text_id, me.binar +y_id, me.name, me.bin_mimetype, md_mms_repos_text.text, md_mms_repos_ +text.text_mime FROM md_mms_repos_part me LEFT JOIN md_mms_repos_text +md_mms_repos_text ON md_mms_repos_text.text_id = me.text_id WHERE ( m +d_mms_repos_text.text_id = ? )' HASH(0x???????) 3) thr#??????? -1 -> prepare for DBD::Oracle::db (DBI::db=HASH(0x???????)~INNER 'SE +LECT me.id, me.part_ref, me.image_id, me.text_id, me.binary_id, me.na +me, me.bin_mimetype, md_mms_repos_text.text, md_mms_repos_text.text_m +ime FROM md_mms_repos_part me LEFT JOIN md_mms_repos_text md_mms_repo +s_text ON md_mms_repos_text.text_id = me.text_id WHERE ( md_mms_repos +_text.text_id = ? )' HASH(0x???????)) thr#??????? +1 -> prepare for DBD::Oracle::db (DBI::db=HASH(0x???????)~INNER 'SE +LECT me.id, me.part_ref, me.image_id, me.text_id, me.binary_id, me.na +me, me.bin_mimetype, md_mms_repos_text.text, md_mms_repos_text.text_m +ime FROM md_mms_repos_part me LEFT JOIN md_mms_repos_text md_mms_repo +s_text ON md_mms_repos_text.text_id = me.text_id WHERE ( md_mms_repos +_text.text_id = ? )' HASH(0x???????) thr#??????? dbd_preparse scanned 1 distinct placeholders dbd_st_prepare'd sql SELECT (pl1, auto_lob1, check_sql1) dbd_describe SELECT (EXPLICIT, lb 2000000)... @@ -50,10 +50,10 @@ <- prepare_cached= DBI::st=HASH(0x???????) at /usr/lib/perl5/vend +or_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 1777 via at /usr/lib/pe +rl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 516 -> bind_param for DBD::Oracle::st (DBI::st=HASH(0x???????)~0x???? +??? 1 '152756' HASH(0x???????) thr#??????? dbd_bind_ph(): bind :p1 <== '152756' (type 0 (DEFAULT (varchar)), att +ribs: HASH(0x???????) -dbd_rebind_ph_char() (1): bind :p1 <== '152756' (size 6/8/0, ptype 4( +VARCHAR), otype 1 ) +dbd_rebind_ph_char() (1): bind :p1 <== '152756' (size 6/8/0, ptype 5( +VARCHAR), otype 1 ) dbd_rebind_ph_char() (2): bind :p1 <== ''15275' (size 6/8, otype 1(VA +RCHAR), indp 0, at_exec 1) bind :p1 as ftype 1 (VARCHAR) -dbd_rebind_ph(): bind :p1 <== '152756' (in, not-utf8, csid 1->0->1, f +type 1 (VARCHAR), csform 0->0, maxlen 8, maxdata_size 0) +dbd_rebind_ph(): bind :p1 <== '152756' (in, not-utf8, csid 873->0->87 +3, ftype 1 (VARCHAR), csform 0->0, maxlen 8, maxdata_size 0) <- bind_param= 1 at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/Class/S +torage/DBI.pm line 1123 via at /usr/lib/perl5/vendor_perl/5.8.8/DBIx +/Class/Storage/DBI/Oracle/Generic.pm line 111 -> execute for DBD::Oracle::st (DBI::st=HASH(0x???????)~0x??????? +) thr#??????? dbd_st_execute SELECT (out0, lob0)... @@ -72,7 +72,9 @@ field #6 with rc=1405(NULL) field #7 with rc=1405(NULL) field #8 with rc=0(OK) - OCILobRead field 8 of 9 SUCCESS: csform 1, LOBlen 11c, LongRe +adLen 2000000c, BufLen 44b, Got 11c + OCILobRead field 8 of 9 ERROR: csform 1, LOBlen 11c, LongRead +Len 2000000c, BufLen 44b, Got 0c + OCIErrorGet after OCILobRead (er1:ok): -1, 24812: Error while try +ing to retrieve text for error ORA-24812 + field #9 with rc=0(OK) - <- fetchrow_array= ( '182057' '1' undef '152756' undef undef unde +f 'Hey Buffy -' 'text/plain' ) [9 items] row1 at /usr/lib/perl5/vendor_perl/5.8.8/D +BIx/Class/Storage/DBI/Cursor.pm line 89 via at /usr/lib/perl5/vendor +_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 516 + !! ERROR: '24812' 'Error while trying to retrieve text for error +ORA-24812 (DBD ERROR: OCILobRead)' (err#1) + <- fetchrow_array= ( ) [0 items] row1 at /usr/lib/perl5/vendor_pe +rl/5.8.8/DBIx/Class/Storage/DBI/Cursor.pm line 89 via at /usr/lib/pe +rl5/vendor_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 531

Any suggestions on what might be going on would be appreciated

Thanks,
Ivan

Comment on I get an error using a DBIx::Class model in Catalyst but not when it is standalone
Download Code
Re: I get an error using a DBIx::Class model in Catalyst but not when it is standalone
by Khen1950fx (Canon) on Mar 08, 2011 at 01:49 UTC

      Tried install Catalyst::Plugin::Unicode::Encoding unfortunately it fails it's tests and wont install :-(, I will continue trying to get installed to see if it helps.

      As far as validation is concerned everything is valid, the problem occurs when the DBI tries to read the CLOB data, presumably it asks Oracle for the data in one format and Oracle fails to convert the data to that format.

      I tried reinstalling Catalyst::Plugin::Unicode::Encoding. It passed all tests on 5.8.8, 5.10.1, and 5.12.2, so your problem probably is your Catalyst installation. It's missing some dependencies. Run cpandeps on Catalyst and on Catalyst::Plugin::Unicode::Encoding, and you'll see what I mean. Make sure you have all the dependencies and update them. Then try installing Catalyst::Plugin::Unicode::Encoding again.

        If you look at the cpandeps site you will see that there are two failed tests results and Catalyst::Plugin::Unicode::Encoding has about 33% success of passing. The test failure that I get is the same as the two other failures listed there. I have submitted the test report via CPAN::Reporter so that should soon be 3 failures. All other dependencies are met.

      I finally got Catalyst::Plugin::Unicode::Encoding installed but it didn't effect the error, I still get the Oracle error.

        I'm getting this error too, but from non-Catalyst code running under mod_perl2. I also cannot reproduce the error using a standalone script that uses the same DBIx::Class model.

        I'm digging through the mod_perl environment now to try to figure out what's different.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://891919]
Approved by herveus
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (14)
As of 2014-07-25 19:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (174 votes), past polls