Recordset not returned - SQLite Primary Key - why?

[CFsolar]

Problem: This query runs just fine on my development system:

SELECT ItemID,UpdateNo,ActivityDate,ActivityTime,ADescription
     FROM ActivityDB
     WHERE ItemID = #edit_ItemID#
     ORDER BY ActivityDate desc, activityTime desc, UpdateNo desc

On my production system the same query processes just fine...EXCEPT that the Recordset is "n/a".

With some experimentation, I discovered that if I left off the last ORDER BY column (i.e., leave off ", UpdateNo desc") it works just fine on both systems.

Very strange!!!  First time I've ever encountered a delta between the two systems.

Some details:

     UpdateNo is the Primary Key in the ActivityDB (set as integer, autoincrement, not null)

     I'm running SQLite on both systems

     The SQLite driver is identical in both systems

     I'm running SQLite 3.7.7.1 on both systems (just upgraded)

     I'm running CF9 Version                  9,0,1,274733 on both systems (though one is running the "developer" edition, the other running as "standard")

     Both systems are running Windows XP

     The Dev system is running Apache 2.2.17; the production system is running Apache 2.2.15 (I don't see anything in the change logs between these two versions that gives any hint that the operation of a query would change, but I'm not an expert in server stuff by any means.)

I would love to get suggestions as to where I should even begin to look to resolve this.

[CFsolar]

Small Update:  I regressed my development environment to Apache 2.2.15.  No change to the result...which is to say that the query still works on my dev system and not on my production system.

[Dan_Bracuk]

The first thing I would do is to see if the problem occurs if you run the query without using ColdFusion.

[CFsolar]

Great suggestion (duh, I should have thought of that!).

Did that on both systems and the query runs successfully on both.

So it would seem that it's a CF problem... agree?

[Adam Cameron.]

On my production system the same query processes just fine...EXCEPT that the Recordset is "n/a".

Can you clarify what you mean by "n/a"?

I can't see how it would make a difference, but you should not hard-code dynamic values into you SQL statement, you should pass them as a parameter.

So it would seem that it's a CF problem... agree?

Nope.  CF doesn't know or care about your SQL.  All it does is pass it to the DB driver, and wait for the DB driver to return something to it.

That said, it might not be a problem with the call, it might be a problem with how it handles what it gets back, I suppose.

Running with that theory... I wonder if the change to the ORDER BY statement is coincidental, perhaps it's just that it bubbles to the top of the recordset something that CF doesn't like.  Can you remove columns from you SELECT statement one by one, and see if at some point if starts returning data correctly?

Can you do a trace on the query and verify what the DB is receiving from the driver, and what it's sending back?  I have no idea how to do that in SQLite, sorry.

--

Adam

[CFsolar]

Adam,

Thanks for digging into this with me.  Sometimes you just need someone to poke holes in your thinking to find the solution!   I've solved the problem..

The variable between the two systems turned out to be a component I wrote to handle queries (so I could use cfscript in more places).  I did this prior to installing CF9.   As it turns out, I made some modifications to the component that handles queries on the development server and never updated the Production system, so it essentially had an "old" component.  For the benefit of other cfscripters who have written their own CFtag components (not that it's necessary anymore, but maybe there are some legacy ones out there) I'll explain...

The culprit was an "if" statement where the code parsed the SQLStmt that was passed to it and looked for sql operators, one of which was "update".  In the new code, the component only looked to parse the first 6 characters of the statement to find the word "update" and this worked fine. The old code (production system) used "findnocase()" to look for 'update' in the entire statement.  That's why the very last Order By column (aka "UpdateNo") was throwing it (the query component) for a loop.

Thanks again.

Case closed.