FAQ
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/catalyst/attachments/20110529/eda81c22/attachment.htm

Search Discussions

  • Jorge Gonzalez at May 30, 2011 at 7:30 am
    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
    <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/
    -------------- next part --------------
    Skipped content of type multipart/related
  • Rohan M at May 30, 2011 at 9:54 am
    Skipped content of type multipart/alternative-------------- next part --------------
    A non-text attachment was scrubbed...
    Name: not available
    Type: image/png
    Size: 5609 bytes
    Desc: not available
    Url : http://lists.scsys.co.uk/pipermail/catalyst/attachments/20110530/4a14d260/attachment.png
  • Marc Logghe at May 30, 2011 at 10:19 am
    Skipped content of type multipart/alternative-------------- next part --------------
    A non-text attachment was scrubbed...
    Name: not available
    Type: image/png
    Size: 5609 bytes
    Desc: not available
    Url : http://lists.scsys.co.uk/pipermail/catalyst/attachments/20110530/dcb6a24e/attachment.png
  • Rohan M at May 30, 2011 at 1:18 pm
    Skipped content of type multipart/alternative-------------- next part --------------
    A non-text attachment was scrubbed...
    Name: not available
    Type: image/png
    Size: 5609 bytes
    Desc: not available
    Url : http://lists.scsys.co.uk/pipermail/catalyst/attachments/20110530/35a043de/attachment.png
  • Jorge Gonzalez at May 30, 2011 at 10:32 am
    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
    <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.
    "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>"
    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ó:
    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/
    _______________________________________________
    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/
    -------------- next part --------------
    Skipped content of type multipart/related

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupcatalyst @
categoriescatalyst, perl
postedMay 29, '11 at 5:45p
activeMay 30, '11 at 1:18p
posts6
users3
websitecatalystframework.org
irc#catalyst

People

Translate

site design / logo © 2021 Grokbase