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

Mon, 30 May 2011 03:33:52 -0700

Hi Rohan,

seems that I was pretty wrong regarding Oracle ANSI JOIN syntax, everyone please accept my apologies. I'll double check before posting next time. Even things that seem improbable. :-)

Sorry again. This time I also learnt something.
Regards
J.


El 30/05/11 11:54, Rohan M escribió:
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, employee.name, employee.phone, employee.email FROM PGx_LOG me <*>JOIN employee employee ON employee.userid = 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 -


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 <[email protected]> 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

El 29/05/11 19:45, Rohan M escribió:
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"  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: [email protected]
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/[email protected]/
Dev site: http://dev.catalyst.perl.org/

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





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



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

Reply via email to