ColdFusion Muse

CF 10 Does Not Honor "Maxrows" on Sybase Stored Procedures

In working with CF 10 on a site that uses Sybase as a backend database server one of our tasks was to convert various "inline" queries over to stored procedures. In some cases these queries used the "maxrow" attribute to limit the number of rows returned to the driver. Personally I usually revert to LIMIT or TOP (or whatever the DB Server syntax provides) for that purpose. With maxrows the DB server works just as hard (my best guess) and the driver simply counts the number of rows sent to the buffer and limits it there. In other words, I have always suspected that maxrows limits the number of rows sent from the DB Server and not the number of rows actually produced by the query. Still there are situations where it hardly matters in either case - and maxrows has a purpose there I guess.

So when it came time to convert our queries over to stored procedures - and with a requirement to change as little as possible on the DB Server - we dutifully added the maxrow attribute to our cfprocresult tag like so:

<Cfprocresult maxrows="15" name="blah"/>
But to our suprise this had no effect. Sybase (or perhaps the Sybase db driver) simply failed ot honor the maxrows attribute. This might be one of those cases where only some drivers or DB servers are capable of implementing the attribute for a stored proc. For example the attribute dbvarname used on the cfprocparam tag is ignored by several DB servers (MSSQL among them). You must place your params in the correct order instead. Still, I wish an appropriate error message would be thrown rather than simply allowing the stored proc to execute and ignoring the attribute like a quirky basement dwelling uncle. Anyway I thought it was worth blogging this nuance of the Sybase driver for the 6 ColdFusion servers out there still connected to Sybase. :)

Comments
David Epler's Gravatar Are you using the DataDirect drivers that are in ColdFusion and have you tried using Sybase jConnect driver?

I have run into problems with stored procedures where it was not returning all the results (wasn't using maxrows to limit), but when changed the datasource over to use jConnect it returned all the results correctly. Now that was on CF 8.0.1 and Sybase ASE 15. Stil it might be worth trying.
# Posted By David Epler | 11/5/12 1:01 PM
Charlie Arehart's Gravatar Bummer indeed. I have two thoughts, for any who may need this to work.

First, I wonder if there is an underlying ConnectionString argument you could provide to enable this. It may well be that there was some argument that was being created (by CF), and now is not (which would be a bug, it would seem).

But second, if you can't find one, and Adobe doesn't soon fix this, then you (or readers) might want to consider that FusionReactor has a feature, in its JDBC monitoring feature (called "jdbc wrapping") to impose just such a query max records limit. It's there for other reasons (to stop knuckleheads asking for thousands or millions of rows), for those who want to impose such limitation. While not always appropriate, in this case it may be helpful.
# Posted By Charlie Arehart | 11/5/12 1:26 PM
Mark A Kruger's Gravatar @David,

Yes... data direct drivers. Result sets are returning correctly - just not trunctated as expected. I don't think different drivers are in the cards for this particular customer. It would require a separate testing and implementation regime.


@charlie,

Both good thoughts - I did not think about FR's wrapper as a possible solution - good idea!

-Mark
# Posted By Mark A Kruger | 11/5/12 3:25 PM



Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.