Will Hawes (wd...@gmail.com)

2008/9/25 Kai Andresen <kai.andresen@gmail.com>:
> Hi all,
>
> I need to call an Oracle stored procedure, and a function, where can I find
> som reference material on that?

http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Using_database_functions_or_stored_procedures

The 0.08010 docs in DBIx::Class::ResultSet contain an example for the
"having" attribute that shows parameter values being passed using a
hashref:

having => { 'count(employee)' => { '>=', 100 } }

Meanwhile, in the test suite there are a couple of tests using
"having" (76joins.t in 0.08010 and 77prefetch.t in 0.08 trunk), but
these both pass a scalar ref instead, like this:

$rs = $rs->search( undef, {  having =>{ 'count(*)'=> \'> 2' }});

DBIC_TRACE shows the following SQL is generated for the above test:

SELECT me.artistid, me.name FROM artist me LEFT JOIN cd cds ON (
cds.artist = me.artistid ) GROUP BY me.name HAVING ( ( ( MAX(cds.cdid)
< 5 ) AND ( count(*) > 2 ) ) ):

And here's what happens if the test is altered to use the hashref syntax:

$rs = $rs->search( undef, { having =>{ 'count(*)'=> { '>', 2' }}});

SELECT me.artistid, me.name FROM artist me LEFT JOIN cd cds ON (
cds.artist = me.artistid ) GROUP BY me.name HAVING ( ( ( MAX(cds.cdid)
< 5 ) AND ( count(*) > ? ) ) ): '2'

Both look fine to me and I'm not getting any errors, but I'm seeing
the scalarref syntax return rows as expected, while the hashref syntax
returns zero rows irrespective of what's in the database.

I've observed this on two machines, one running DBD::SQLite 1.14 and
DBI 1.607 and the other DBD::SQLite 1.13 and DBI 1.57.

I don't have a database server handy to test against other drivers
atm, but since DBIx::Class::Storage::DBI calls $sth->bind_param when
the hashref syntax is used I'm guessing the DBIC docs are correct and
that this may be a DBD problem of some sort.

I've not found reference to anything similar in the list archives -
can anyone else confirm this behaviour before I go any further?

Having reviewed the InflateColumn and InflateColumn::DateTime source
and rewritten my code from scratch, neither am I, as it now works
perfectly.

I wasn't trying to do anything wacky - I just have a single nullable
column defined as "datetime" and am inflating/deflating that. I'd used
InflateColumn as a temporary workaround and unfortunately the original
code never made it into my repository, so I can't even check back to
see what I'd done wrong. Slap.

Clearly one to file under "user error", sorry list.

The InflateColumn::DateTime helper currently throws "Invalid date
format:  " for rows that contain NULL, even if the column info has
is_nullable => 1. The docs show that either default_value =>
'0000-00-00' or datetime_undef_if_invalid => 1 could be used to work
around this, but bypassing inflation completely based on the presence
of is_nullable => 1 would be trivial and probably more sensible. Happy
to patch if others are in agreement, what do you think?

And if in doubt, just use some warn statements or $c->log->debug calls
in your view class, along with Data::Dumper, to see what is being
passed to the template.