[CDBI] Missing TEMP data

[prev] [thread] [next] [Date index for 2006/01/13]

This is too weird.  I don't even know where to start on this one.
Three hours of sleep doesn't help, either.  Often posting is the only
way to see that I'm doing something really, eh,  dumb.  So....


I have a query that returns a list of "instructors" and a count() of
the number of classes they are teaching.  The count() is in a TEMP
column.  I'm using seq_sql for the query:

    package Person;

    __PACKAGE__->columns( TEMP => 'class_count' );
    __PACKAGE__->set_sql('count_classes', <<'' );
        SELECT      me.id, me.first_name, me.last_name,  COUNT(class.id) AS class_count
        FROM        %s
        WHERE       %s
        GROUP BY    me.id, me.first_name, me.last_name
        %s

In Catalyst every once in a while a few of the rows display the instructor's name,
but *blank* for the count -- not zero, but blank (or maybe NULL).
It's just an empty <td> in the html.

If I wrap my call to search() in DBI->trace() (which causes Cat to
restart) and reload in the browser the counts show up.  Then if I go
back and remove the trace the and reload, the counts still show.
Great.  Just great.  Can't figure out how to make it happen.

The trace shows this (Postgres, btw):

 T  <- prepare_cached('
        SELECT      me.id, me.first_name, me.last_name,  COUNT(class.id) AS class_count
        FROM        person me, class class, instructors instructor_jobs
        WHERE       ( class.class_time > ? AND class.review_mode = ?
                    AND class.workshop_group = ? )
                    AND instructor_jobs.class = class.id
                    AND me.id = instructor_jobs.person
        GROUP BY    me.id, me.first_name, me.last_name
         ORDER BY last_name, first_name

"instructors" is a link table between person and class.
Can count() ever be NULL there when a row returns?



The template is just passed the result of search() in "recs" (Sweet's
search) and the template only does:

    [% WHILE ( item = recs.next ) %]
        <tr>
            <td>[% item.first_name</td>
            <td>[% item.class_count</td>
        </tr>
    [% END %]

So in the template I see:

        <tr>
            <td>Joe</td>
            <td>3</td>
        </tr>
        <tr>
            <td>Fred</td>
            <td></td>
        <tr>


So, the SQL looks fine, and there's nothing odd about the template or
the CDBI search.  It's not a browser issue -- the counts are not there
in view source and I've seen it happen on multiple machines and
platforms.

The only thing I can think of is something odd is happening with the
TEMP column.  Hard to imagine it's a DBI issue.

Wise programmers, what would you do to debug this?  Would you suspect
something with TEMP?  And if so, can I add something to CDBI to help
debug?  The problem doesn't' show up very often.

7am and I need a drink.


        -- 
        Bill Moseley
moseley@xxxx.xxx


_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi