Highlighted

mysql query returns different number of records from coldfusion and navicat

Community Beginner ,
Jul 17, 2014

Copy link to clipboard

Copied

Hi

I'm hoping that someone can suggest a basic strategy to debug this.

I have a fairly large and complicated query that is executed by a function in a cfc.

It returns (for example) 100 rows.

If I use cfdump and then copy and paste the SQL of the query (with the variables, of course) into Navicat and execute exactly the same query on the same mySQL database, it returns 130 rows.

Same SQL string, same database, same data - the only difference is that in one instance Navicat submits the query and in the other, Coldfusion does.

Has anyone ever had anything like this happen before?

Correct answer by Endboss_ZA | Community Beginner

Ok I found my own bug. Of *course* the sql queries were not identical.. they could not possibly have been. My mistake was thinking that they were.

The problem was part of the WHERE clause:

AND orderid in (500,503,505)

In the coldfusion code this was

AND orderid in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#lstOrderID#">)

which of course rendered in mySQL as AND orderid in ('500,503,505')

This was not immediately apparent as the cfdump returns this as AND orderid in (?) with the variable in the array below.

Views

130

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

mysql query returns different number of records from coldfusion and navicat

Community Beginner ,
Jul 17, 2014

Copy link to clipboard

Copied

Hi

I'm hoping that someone can suggest a basic strategy to debug this.

I have a fairly large and complicated query that is executed by a function in a cfc.

It returns (for example) 100 rows.

If I use cfdump and then copy and paste the SQL of the query (with the variables, of course) into Navicat and execute exactly the same query on the same mySQL database, it returns 130 rows.

Same SQL string, same database, same data - the only difference is that in one instance Navicat submits the query and in the other, Coldfusion does.

Has anyone ever had anything like this happen before?

Correct answer by Endboss_ZA | Community Beginner

Ok I found my own bug. Of *course* the sql queries were not identical.. they could not possibly have been. My mistake was thinking that they were.

The problem was part of the WHERE clause:

AND orderid in (500,503,505)

In the coldfusion code this was

AND orderid in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#lstOrderID#">)

which of course rendered in mySQL as AND orderid in ('500,503,505')

This was not immediately apparent as the cfdump returns this as AND orderid in (?) with the variable in the array below.

Views

131

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jul 17, 2014 0
Community Beginner ,
Jul 17, 2014

Copy link to clipboard

Copied

Ok I found my own bug. Of *course* the sql queries were not identical.. they could not possibly have been. My mistake was thinking that they were.

The problem was part of the WHERE clause:

AND orderid in (500,503,505)

In the coldfusion code this was

AND orderid in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#lstOrderID#">)

which of course rendered in mySQL as AND orderid in ('500,503,505')

This was not immediately apparent as the cfdump returns this as AND orderid in (?) with the variable in the array below.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 17, 2014 0