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:
3 Comments
-
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. -
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. -
@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
Sites to Watch
Aggregators
RSS
