Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

Re: RFC : AJAX + DBI = DBIx::LiveGrid

by mje (Curate)
on Sep 16, 2005 at 13:43 UTC ( #492608=note: print w/replies, xml ) Need Help??

in reply to RFC : AJAX + DBI = DBIx::LiveGrid

Thanks for this.

I had a few problems using DBD::ODBC and MS SQL Server. With a table of 1000 rows:

What I found is the data grid displayed the first 70 rows repeatedly as you scrolled. I tracked this down to the SQL being run and then found SQL::Abstract::Limit says $order must be specified when $rows is specified. DBIx::LiveGrid only sets $order if you click on a column heading. I verified this with SQL::Abstract::Limit which produces the following incorrect SQL when $order is not specified:

SELECT * FROM ( SELECT TOP 10 * FROM ( SELECT TOP 30 f1, f2 FROM bench_char ) AS foo ) AS bar
and the following correct SQL when $order is specified:
SELECT * FROM ( SELECT TOP 10 * FROM ( SELECT TOP 30 f1, f2 FROM bench_char ORDER BY f1 ASC ) AS foo ORDER BY f1 DESC ) AS bar ORDER BY f1 ASC

I did a rough change to DBIx::LiveGrid to work around this:

sub query_database { my($self,$dbh,$table_name,$fields,$where,$order)=@_; my $rows = $self->get('ajax_page_size'); my $offset = $self->get('ajax_offset'); my $sort_dir = $self->get('ajax_sort_dir'); my $sort_col = $self->get('ajax_sort_col'); $sort_col = $self->clean_param('sort_col',$sort_col); my @porder = (); if ($sort_col) { @porder = ("$sort_col $sort_dir"); $order = \@porder; } require SQL::Abstract::Limit; my $abstract = SQL::Abstract::Limit->new( limit_dialect => $dbh ); my( $stmt, @bind ) = $abstract->select( $table_name , $fields , $where , $order , $rows , $offset );
and then changed livegrid.cgi:
my $dbh = DBI->connect("dbi:ODBC:test", "Martin_Evans", "easyso +ft", {RaiseError=>1,PrintError=>0}); my $table_name = "bench_char"; my @fields = qw/f1 f2/; my @order = qw/f1/; DBIx::LiveGrid->run( undef, $dbh, $table_name, \@fields, undef, \@orde +r );

I hope this helps.

BTW, I think there is a bug in IE 6.0.2800.1106 which means the row data is not updated every time when scrolling past the rowset points (70).

Replies are listed 'Best First'.
Re^2: RFC : AJAX + DBI = DBIx::LiveGrid
by jZed (Prior) on Sep 16, 2005 at 14:49 UTC
    Thanks for spotting this and finding the right place to fix. I'll add a default sort-order on the first column but allow programmers to over-ride it.

    The Rico site forum has a lot of bug reports and fixes for browser scrolling bugs, I'd imagine they've seen the one you mention, but you might want to post it over there.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://492608]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (4)
As of 2022-05-25 06:18 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (84 votes). Check out past polls.