[Dbix-class] Help with Arbitrary SQL through a custom ResultSource

[Matt S Trout]

On Mon, Mar 10, 2008 at 12:36:22PM -0700, Steve Kirkup wrote:
> I followed the cookbook for writing a custom ResultSource for executing 
> custom sql.
> 
> However when I run the code, it I get SQL errors, it looks like it 
> concats my customer after the FROM keyword.
> 
> My code,
> 
>    my $new_source = $source->new( $source );
>       $new_source->source_name( $source_name );
>       $new_source->name( \'some stuff' );
>   
>    $oracle->register_source( $source_name => $new_source );
> 
>    return [ $oracle->resultset( $source_name )->search( {},
>        { bind => [ $cmd_options->{'start_date'}->mdy(q{/}),
>                    $cmd_options->{'end_date'  }->mdy(q{/})  ] }
>    )];
> 
> 
> The error I get is,
> 
> DBIx::Class::ResultSet::search(): DBI Exception: DBD::Oracle::db 
> prepare_cached failed: ORA-00903: invalid table name (DBD ERROR: error 
> possibly near <*> indicator at char 235 in 'SELECT me.currency_tax, 
> me.royalty, me.product_info, me.quantity, me.invoice_id, me.tax, 
> me.currency_price, me.item_id, me.created, me.amount, 
> me.currency_amount, me.media_id, me.product_id, me.customer, me.price, 
> me.promotion_id FROM <*>some stuff
> 
> I would just like to run my SQL as a standalone rather than try and 
> force intergration with SQL::Abstract, at least in this case.  Could 
> someone help me out?

The custom resultsource trick expects you to provide a full (SELECT ...)
subquery. Do that and it'll work.

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/