Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Perl DBI MS SQL Question

by peppiv (Curate)
on Jul 12, 2002 at 15:20 UTC ( #181304=perlquestion: print w/replies, xml ) Need Help??

peppiv has asked for the wisdom of the Perl Monks concerning the following question:

Let me tell ya, TGIF!!! I'm burnt!!!

Here's my problem.
I'm running Perl 5.6 on a Linux/Apache Box talkin' to a W2K Box and SQL Server. I'm using FreeTDS as the go between and all things work well. That is until I realize I don't know MS SQL Server commands.

I have a program that connects to a MySQL DB and I use the LIMIT command that allows me to call the rows I want returned. You know, LIMIT 0,10 -- returns rows 1-10. I don't know how to do this in MS SQL. I know how to use SELECT TOP 10, but I want to return results in groups of ten. With LIMIT I can return 1-10, then 10-20 and so forth. Kinda speeds up the screen write in case someone chooses a search criteria that returns mega results. Does anyone know a Perl workaround?

Also, more of a DBI question - in MySQL I can Perl with
if ($sth->rows == 2)
And this lets me know if I've returned two rows or not. In the MS SQL it only tell me that if I have a return it equals -1. Does anyone know how to see how many rows return true?

Supa Thanx


Replies are listed 'Best First'.
Re: Perl DBI MS SQL Question
by mpeppler (Vicar) on Jul 12, 2002 at 16:28 UTC
    Note - I've never used MS-SQL, so what follows may be slightly off, but as MS-SQL is based on the Sybase code line it should still be relevant

    Getting "pages" of result from an RDBMS is a popular question, and one that is essentialy a SQL question, really.
    For MS-SQL and/or Sybase there is no direct way of only retrieving rows x,y from a result set. However, you can trick the server into doing it for you. For example, with Sybase I can write the following:

    set rowcount $last_row select, id=identity(10) into #tmp from the_table tb where some condition order by something or other set rowcount 0 select, other columns from the_table tb , #tmp t where = and between $first_row and $last_row
    This can be run as a single DBI prepare() call, and the second query will return the rows from the result set that are positioned between $first_row and $last_row.

    The caveats with this technique is that as you page forward (or back) the results that you display may change if the underlying data changes. The initial set rowcount $last_row will limit the number of rows that are pulled from the base table and stuffed into the temporary table - usefull if the base table has a lot of rows that match the query.

    An alternative solution is to fetch a large subset (say the first 10 pages worth) of primary keys and cache them locally. Then as the user pages forward (or back) issue a query with an IN clause with the primary keys for that set of rows.

    As for the rows() not returning the correct (in your opinion :-) data - well that's documented. MS-SQL, Oracle, Sybase (and others) can't provide you with the count of the number of rows affected by a SELECT query until all the rows have been fetched. This is because the server doesn't know how many rows it has processed until it has processed all the rows (and finding out would take more time, and might not be correct in a multiuser environment anyway)


    edited: to fix typo - thanks Abigail-II!

      Just a small nit, the where clause in the second select has a typo, it should read:
      where =


Re: Perl DBI MS SQL Question
by cfreak (Chaplain) on Jul 12, 2002 at 16:20 UTC

    Show some code!

    It seems to me that LIMIT should work. I've never messed with MS SQL but when I did some stuff in Access a while back I seem to recall that LIMIT worked on that.

    As far as rows returning a negative number I would venture to guess that MS SQL doesn't support something needed to make that function work so it returns -1. You should read the DBI man page for that info.

    But again, show us some code and we will be able to help you much more effectively.

    Lobster Aliens Are attacking the world!
      Yes, of course. My apologies. But again, LIMIT does not work in MSSQL - command not found.

      sub DoSearchActive { my $dbh = DBI->connect('DBI:mysql:new_db','username','password'); my $limit_3 = param("limit_3"); my $limit = param("limit"); $limit = ($limit_3 + $limit); my $spread = ($limit + 1); my $spread_2 = ($limit + 10); my $types = $dbh->selectrow_array('SELECT COUNT(status) FROM appl +icant WHERE status = "Active"'); if ($spread_2 >= $types){$spread_2 = $types} if ($spread >= $types){$spread = $types} print qq(<p style="margin-left: 0; margin-top: 20"><font size="2" + face="Arial"><b>There are $types applications listed as 'Active'.<br +>Results $spread - $spread_2 shown.</b></font></p>); print qq($table); print qq($table_headers); my $sth = $dbh->prepare('SELECT * FROM applicant WHERE status = " +Active" ORDER BY date_col DESC LIMIT ?,10'); $sth->bind_param(1, $limit, DBI::SQL_INTEGER); $sth->execute($limit) or die $sth->errstr; while (my @result = $sth->fetchrow_array()) { if ($result[21] eq '' || $result[21] eq '0000-00-00') {$shade += $default} else {$shade = $default2} if ($result[32] eq 'checked') {$fav = $something} else {$fav = ''} print qq(<tr><td><form method="POST" action="applicant_trackin"><input type="radio" name="selection" value="all"><input type="h +idden" name="id" value="$result[0]"><br><input type="submit" value="G +O"></form></td><td bgcolor="$shade"><font size=2 face="Arial">$result +[20]</font></td><td bgcolor="$shade"><font size="2" face="Arial">$res +ult[15]</font></td><td bgcolor="$shade"><font size="2" face="Arial">$ +result[16]</font></td><td bgcolor="$shade"><font size="2" face="Arial +">$result[1], $result[2]</font></td><td bgcolor="$shade"><font size=" +2" face="Arial">$result[28]<br>$result[6]</font></td><td bgcolor="$sh +ade"><font size="2" face="Arial">$result[11]-$result[12]-$result[13]< +/font></td><td bgcolor="$shade"><font size="2" face="Arial">@result[1 +7, 18, 19]</font></td><td bgcolor="$shade"><font size="2" face="Arial +">$result[61]<br>$result[39]<br>$fav</font></td><td><font size="2" fa +ce="Arial">$result[31]$result[62]$result[63]$result[64]$result[65]</f +ont></td></tr>\n); } print qq(<tr><td colspan="10"><center><form method="POST" acti +on=""><input type="hidden" name="limit_3" value= +"10"><input type="hidden" name="limit" value="$limit"><input type="su +bmit" name="action11" value="Next"></form></center></td></tr>); print qq(</table>); if ($sth->rows == 0) { print qq(<br>There are no employees in the Active folder.\n\n +); } $sth->finish(); $dbh->disconnect; &DoSomethingElse; }

      This allowed me to return results 1-10, then with the click of a button return 11-20, and so forth. jeffa actually helped with the incremental code. Thanks jeffa++

Re: Perl DBI MS SQL Question
by perrin (Chancellor) on Jul 12, 2002 at 17:32 UTC
    There is a slide here written by Tim Bunce with his advice on how to handle paged results with DBI.
Re: Perl DBI MS SQL Question
by sedhed (Scribe) on Jul 12, 2002 at 22:33 UTC

    Regarding the first problem (limiting results to page-size subsets with MS SQL), I use a nested select that I picked up from a DBA much craftier than myself, and I use it in code like the following (using an arbitrary 'parts' table with fields 'partno' and 'custno'):

    my $perpage = '25'; my $curpage = $q->param('curpage') || 1; # Assumes $q is a CGI object my $custno = $q->param('custno'); my $offset = $curpage*$perpage; my $orderby = 'partno'; my $totalcount = @{ $dbh->selectrow_arrayref( qq{SELECT count(*) FROM parts WHERE custno = '$custno'} ) }[0]; # Next four lines limit the results of the LAST page's data, in case o +f a short page. my $reccount = $perpage; if (($totalcount - $offset) < 0) { $reccount = $totalcount-$offset+$perpage; } # Now build the select (read inside out) (my $select = <<ENDOFSQL) =~ s/^\s*#.*\n//gm; SELECT * FROM ( SELECT top $reccount * FROM ( SELECT top $offset * FROM parts WHERE custno = '$custno' ORDER BY $orderby DESC ) top_data ORDER BY $orderby ASC ) page_data ORDER BY $orderby DESC ENDOFSQL my $dataref = $dbh->selectall_arrayref($select,{Columns=>{}}) || die ' +Whoops!';

    The main select gets everything past the $offset, it's then wrapped in a select that flips the order and gets just the TOP $reccount of the first set, in turn that's wrapped in a last outer select which just flips the order back right-side-up.

    Anyone else use something like this?

    I know I'm wasting a few cycles doing the $totalcount lookup, but I also use that number to generate the page navigation links. In fact, I might ask for critique of my navLinks() sub here at some point, I think it's kinda nifty.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://181304]
Approved by Snuggle
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (5)
As of 2021-05-11 01:19 GMT
Find Nodes?
    Voting Booth?
    Perl 7 will be out ...

    Results (111 votes). Check out past polls.