cfqueryparam altering query??

[WolfShade]

Hello, everyone.

Got a weird issue (IMHO)..

I have a medium sized SELECT query that takes four dynamic values - three for the WHERE clause, one for the ORDER BY.

If I run the query within a CFC method without using CFQUERYPARAM, it runs exactly as expected.

If I use CFQUERYPARAM on any/all of the WHERE clause, it ignores the ORDER BY and just returns data as it is.

What is happening???

Thanks,

^_^

[Adam Cameron.]

Impossible to comment sensibly without seeing the code.

--

Adam

[12Robots]

You can't use cfqueryparam with the value going into the ORDER BY clause. cfqueryparam is only for statements in the WHERE clause.

You'll need to find another way to sanitize the input for ORDER BY.

Jason

[WolfShade]

@Adam: Unfortunately, I cannot copy/paste code - dev system is isolated from internet, no cd burning permissions, flash drives are proscribed.

@Jason: CFQUERYPARAMS are only going on the WHERE clauses, I hadn't got to the ORDER BY sanitization, yet.  Was going to work on that after I get this issue resolved.

Thank you, both, for your help.

^_^

[12Robots]

And you cannot retype a sample of the code?

Jason

[WolfShade]

The query is about 12 lines long and goes all the way across the screen.

Basically

SELECT a.colA, a.colB, a.colC, b.colC, b.colD, c.colA, c.colC, c.colD, d.colA, d.colF

FROM tableA a LEFT OUTER JOIN tableB b on b.id = a.id

   LEFT OUTER JOIN tableC c on c.id = a.id

   LEFT OUTER JOIN tableD d on d.tid = c.tid

   LEFT OUTER JOIN tableE e on e.org = b.org

WHERE d.tid = [training id] AND c.auth = [authorization id] AND b.year = [4 digit year]

ORDER BY #by#

by can be last name, date completed, or organization.

[12Robots]

You said it did not work after you added the cfqueryparams.  I don't see any query params there.  Show us the version that doesn't work. Not the one that does.

Jason

[WolfShade]

Sorry.. I used a shorthand without explaining.. anything in between [ and ] is CFQUERYPARAM.

WHERE d.tid = <cfqueryparam value="#tid#" cfsqltype="CF_SQL_INTEGER"> AND c.auth = <cfqueryparam value="#aid#" cfsqltype="CF_SQL_INTEGER"> AND b.year = <cfqueryparam value="#year#" cfsqltype="CF_SQL_INTEGER">

This is the version that does not work; ORDER BY is ignored.  If I use:

WHERE d.tid = #tid# AND c.auth = #aid# AND b.year = #year#

.. it will use the ORDER BY that I write. 

^_^

[Dan_Bracuk · Accepted solution]

If this:

SELECT a.colA, a.colB, a.colC, b.colC, b.colD, c.colA, c.colC, c.colD, d.colA, d.colF

FROM tableA a LEFT OUTER JOIN tableB b on b.id = a.id

LEFT OUTER JOIN tableC c on c.id = a.id

LEFT OUTER JOIN tableD d on d.tid = c.tid

LEFT OUTER JOIN tableE e on e.org = b.org

WHERE d.tid = [training id] AND c.auth = [authorization id] AND b.year = [4 digit year]

ORDER BY #by#

resembles your actual query, you have a logic problem.  You are specifying left joins to tables b,c, and d and then using those tables in your where clause.  This changes your outer join to an inner join.  You fix it by moving those filters to your from clause, like this:

from tablea a left join tableb b on b.id = a.id and b.year = [4 digit year]

[WolfShade]

AH!  Thank you, Dan!  Moving the parts of the WHERE clause not associated with the original table to the associated JOINS fixed it.

It's sorting, now, as desired.

Thank you!!

^_^

[Adam Cameron.]

HUH?

Nothing Dan suggested - whilst correct - would have any impact on the ordering (or otherwise) of the recordset.

Nor would there be any behavioural change between "with params" and "without params".

What else did you change?

Or what else was going on that you didn't tell us?

--

Adam

[WolfShade]

Nothing else was changed, nothing else was going on.  What are you implying?

[Adam Cameron.]

The problem you said you were having was that the ordering was "misbehaving" (for lack of a better term, given we never worked out WTH was going on).

What Dan observed would not have impacted the ordering of the recordset at all.  It might have caused different records to be returned, but it would not change their ordering.

So simply applying what Dan suggested could not possibly have fixed your problem unless:

* you also changed something else;

* you were not giving us all the relevant information from the outset, and theer were other contributing factors you didn't mention.

I wasn't "implying" anything... I think that's pretty much what I said in my previous post.

--

Adam

[WolfShade]

All I know is that I was getting data that was jumbled and definitely not in the order by which I was requesting, regardless of by name, date, or organization. 

I did not pay attention to the number of records being returned.. didn't even occurr to me to look, as soon as I noticed that the data was't being sorted, so different records or not, IDK. 

As soon as I moved the WHERE clauses that were not associated with the first table into their respective JOIN, the data starts sorting by requested parameter.

I'm not a db guru - if what I described is not logical to you, I don't know.  Nothing else was changed, nothing else was going on.

^_^

[Adam Cameron.]

OK.  Weird.

Still: it's sorted (I mean that in the sense of "fixed" ;-), so that's all good.

--

Adam

[Adam Cameron.]

And you cannot retype a sample of the code?

I was about to say much the same thing.   Even if it's truly secret squirrel stuff, you could type out something similar.

The fact remains the same: if we can't see code, we can't help.  The answer will always be the same, every time you ask a question.  Help us to help you.

That said: I suspect Jason's guesswork is pointing you in the right direction.

This might help further:

http://adamcameroncoldfusion.blogspot.co.uk/2012/07/what-one-can-and-cannot-do-with.html

--

Adam

[WolfShade]

I was panicky, for a bit.  I had never encountered something like that, before.  But, then, I don't use JOINs, very often.

Looking at some of my co-worker's code, putting a CFQUERY inside a loop seems to be SOP; but I've never liked that.  So I'm trying to break that and use JOINs wherever possible.

Thanks, everyone, for your time.  I do appreciate it.

^_^

[Adam Cameron.]

Looking at some of my co-worker's code, putting a CFQUERY inside a loop seems to be SOP; but I've never liked that.

Only if SOP in this case means "Sh!tty Operating Procedure" 😉

You should aim to hit the DB only when necessary.  As many times as necessary, sure, but make sure the trip is necessary first.

It is seldom (if ever) necessary to loop over something and then hit the DB each iteration, using values from the iteration to pass to the DB.  What you're doing tin rying to make it a single hit is the correct way to go about things, if poss.

This - of course - is a sweeping generalisation, but it's gonna be right far more often than it's wrong.

--

Adam

[WolfShade]

Adam Cameron. wrote:

This - of course - is a sweeping generalisation, but it's gonna be right far more often than it's wrong.

I completely agree.  Highly inefficient, hogs bandwidth, etc.  And just plain lazy, if you ask me.  I wish they would let us create SPs, but they don't use SPs.  😕

^_^