Hey Peter, thanks for the reply. Yes I am using the
SQL_CALC_FOUND_ROWS in the first query. My queries have not changed in
years... this was working fine on the old BD Free server. Here's a
simple test.cfm that I put together, which is a stripped down version
of my main query (without any of the joins and where clauses):
<cftransaction>
<cfquery name="qryCustomers" datasource="#Application.dsn#">
SELECT SQL_CALC_FOUND_ROWS *
FROM tbl_customers
LIMIT 0, 50;
</cfquery>
<cfquery name="qryTotalCustomers" datasource="#Application.dsn#">
SELECT FOUND_ROWS() AS totalRows
</cfquery>
</cftransaction>
<cfoutput>#qryTotalCustomers.totalRows#</cfoutput>
This returns a bogus '22' but my table has over 4000 records. I
haven't been able to figure out the significance of the '22'.
Tom
On Dec 1, 4:30 pm, "Peter J. Farrell" <[email protected]> wrote:
> It's all in the DB. Are you using this in the first query?
>
> <cfquery name="qryRead" datasource="#variables.instance.dsn#">
> SELECT SQL_CALC_FOUND_ROWS t.*
> FROM table AS t
> LIMIT 10, 10
> </cfquery>
>
> The key is the SQL_CALC_FOUND_ROWS in the first otherwise the second
> query breaks:
>
> <cfquery name="qryFound" datasource="#variables.instance.dsn#">
> SELECT FOUND_ROWS() AS total
> </cfquery>
>
> SEE:http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#fun...
>
> Tom said the following on 12/01/2011 03:23 PM:
>
>
>
> > I should add that the FOUND_ROWS() function requires some information
> > to persist from the previous query. I am not sure if this is the
> > responsibility of the database or the cf server.
>
> > On Dec 1, 4:19 pm, Tom<[email protected]> wrote:
> >> Hey guys thanks for the help. Much appreciated. Hoping to crack this
> >> before I have to go home :)
>
> >> I just downloaded everything today - latest versions. MySQL 5.5. I
> >> verified that the function is valid and not deprecated
> >> (seehttp://dev.mysql.com/doc/refman/5.5/en/information-functions.html#fun...).
> >> Also I ran the query successfully directly on the database.
>
> >> Yeah the db and tomcat/openbd server are on the same machine.
>
> >> Driver could be the culprit.. how would I go about troubleshooting?
>
> >> On Dec 1, 4:05 pm, Jason King<[email protected]> wrote:
>
> >>> Yea I would check versions. What version of mySQL are you using? Have you
> >>> checked to see if that function is deprecated? There may be a new function
> >>> in it's place.
> >>> Is the database on the same server? Or is it a remote server?
>
> --
> Peter J. Farrell
> [email protected]
> [email protected]http://blog.maestropublishing.com
> Identi.ca / Twitter: @maestrofjp
--
online documentation: http://openbd.org/manual/
google+ hints/tips: https://plus.google.com/115990347459711259462
http://groups.google.com/group/openbd?hl=en
