[Catalyst] Catalyst Application : DBIx::Class : Oracle 8 : JOIN problem

Dear All,

I'm using Oracle 8i as a back-end database server for my Catalyst
application. When I try to use join parameter using DBIx::Class model,
it gives me syntax error.
It doesn't understand 'JOIN' keyword in the SQL generated by DBIx::Class.

for example - >

my $Info = $c->model('App::Log')->search({
id => $someid,
},{
join => 'foreignkey_id',
});

I did checked
"http://search.cpan.org/~frew/DBIx-Class-0.08115/lib/DBIx/Class/Storage/DBI/Oracle/WhereJoins.pm
<http://search.cpan.org/%7Efrew/DBIx-Class-0.08115/lib/DBIx/Class/Storage/DBI/Oracle/WhereJoins.pm>"
where it is mentioned that DBIx::Class automatically detects the
version and calls the above class to generate the JOIN in oracle 8i
fashion but somehow its not working.

Also I checked for join_type parameter which allows you to go for
'outer' or 'inner' join but it didn't work.

Could some body tell me how can I use join for oracle 8i using
DBIx::Class model in the Catalyst application?


Thanks and regards,
Rohan





_______________________________________________
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/

Hey Jorge,

Yes. The join parameter is the name of DBIx::Class relation to the
foreign table and I'm using the relation name not the foreign key
name. So that's not a problem.

Yes I'm using DBIC_TRACE and I see following output in my Catalyst app
console.

DBI Exception: DBD::Oracle::db prepare_cached failed: ORA-00933: SQL
command not properly ended (DBD ERROR: error possibly near <*>
indicator at char ....

SELECT me.logid, me.status, me.timedetails, employee.id
<http://employee.id>, employee.name <http://employee.name>,
employee.phone, employee.email FROM PGx_LOG me <*>JOIN employee
employee ON employee.userid = me.id <http://me.id> WHERE ( someotherid
= ? )

If I try to use the same query directly on sql prompt there also I get
the same error.

I checked for the Oracle 8i sql statements -

http://oreilly.com/catalog/orsqlpluspr2/chapter/ch01.html

http://www.dba-oracle.com/art_sql_iso_99.htm

This tells you that Oracle 8i does not support such syntax.
Also, the above link tells that the joins are differently handled by
for Oracle 8i.

Please let me know if I'm doing something wrong.

Regards,
Rohan


On Mon, May 30, 2011 at 1:00 PM, Jorge Gonzalez
wrote:

The argument to 'join' parameter must be the name of the
DBIx::Class relation to the foreign table, not the foreign key
name. In some cases (autogenerated schema with
DBix::Class::Schema::Loader) they will be the same, but in other
cases (i.e. when you build the schema by hand), this might not
necessarily be the case.

Have you checked this?

Have you executed your script with DBIC_TRACE=1 to see the exact
SQL which is being generated?

Can you post the exact error message that Oracle gives you? It's
quite clear that Oracle _does_ understand the JOIN syntax
since..... well, forever.

Cheers
J.


*/Jorge González Villalonga/*
Director Técnico

*/DAIKON Integración y Desarrollo S.L./*
Telf: (+34) 91 188 08 28
Fax: (+34) 91 632 65 42
*www.daikon.es <http://www.daikon.es>*


El 29/05/11 19:45, Rohan M escribió:
_______________________________________________
List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive:
http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/





_______________________________________________
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/