http://coldfusion.com logo
Join Slack
Powered by
Has anyone ever seen a case where a computed colum...
# sql
b
Has anyone ever seen a case where a computed column in MS Sql looses it's value when cfoutput. It should be a 2, but the dump and output is showing as 0.
a
Like if you go 
SELECT myComputedColumn FROM mytable WHERE id = ?
in a DB client, you get 
2
, but if you run that through 
<cfquery>
(etc), it comes back as 
0
?
b
yes
a
wow
Weird.
b
I thought it was maybe perms, but I'm running as the same user.
The formula is just a CASE
a
And you can replicate this with a query that is that simple - as the one I suggested? There's nothing else that could be interfering? Can you pare the SQL statements down to be as simple as possible and still exercises the relevant bits?
b
Doing some additional testing now, I'm now doing a dump right after the cfquery and it shows incorrect. Trimming that sql statement now as well.
👍 1
So this is totally bizarre, I created a new datasource, stripped the query down to no joins and just the 3 columsn in use for the computed column. 2 new cfquery on the existing page, with the stripped down sql. stil l get incorrect results still for all 3 of the queries. Moving my 2 stripped down queries to a new template, wham, correct results.
Copy code
###########################################
EXISTING TEMPLATE
###########################################
rootdsn
------------------------------------------
SQL:
select units, qtyReserved, inStockQty
from stock s
where autoid = 114957
units	qtyReserved	inStockQty
1	1	0

testdsn
----------------------------------
SQL:
select units, qtyReserved, inStockQty
from stock s
where autoid = 114957
units	qtyReserved	inStockQty
1	1	0

###########################################
NEW TEMPLATE
###########################################
rootdsn
SQL:
select units, qtyReserved, inStockQty
from stock s
where autoid = 114957
units	qtyReserved	inStockQty
1	0	1

testdsn
SQL:
select units, qtyReserved, inStockQty
from stock s
where autoid = 114957
units	qtyReserved	inStockQty
1	0	1
a
And like literally it's 
file1.cfm
and 
file2.cfm
? Template cache need clearing?
And files are adjacent, so same 
Application.cfc
etc?
b
yes in the same app. I know in the past I've seen some odd column stuff in the old days. Like if you added a column to the DB and were using 
select *
.
Let me try the template cache
a
know in the past I've seen some odd column stuff in the old days. Like if you added a column to the DB and were using 
select *
.
Oh yes... that rings a bell. CF had a bug in how it cached its SQL statements or something. That's way old skool.
b
cleared all the caches from the admin both web and server, till the same. Through this, I even updated jdbc versions for ms sql.
only thing I haven't done is blow away the commandbox instance and restart in full
r
That same query isn't cached elsewhere in the application?
b
Nope, and I also renamed the orginal query.
r
IIRC, the name really doesn't matter. The query itself is used to determine whether or not to use the cache.
b
I took the orig sql and put it into 2 new cfquery (on the same template) and stripped it of all the joins and extra columns and still the same odd result for those cols.
even the new test DSN, so my thought is it can't be a jdbc driver thing.
a
This is some weird shit.
b
Is there caching to clear in app, aside from what can be done in the admin?
a
not that I'm aware of. I am very rusty with CF administration though I'd go and actively delete everything in 
cfclasses
though. Also can't vouch for anything commandbox might be doing to be "helpful", but I doubt it's that.
And I'd cycle the CF service
b
OK, so fresh docker and all that and still happens. When all else fails go back to the basics. I ran the template and hit refresh in sql studio as fast as I can. I can see the values toggling. This is some really old template of a template of a template code and there must be something else up and down stream updating the values. However, nothing in the sql dump in Lucee is revealing anything. There are a number of stored procs called, but not after this query, so how the values get rolled back is the mystery to solve and this isn't within a transaction (that I'm seeing right now). So long and short, I really appreciate your efforts and I apologies for the time grab. I hope you have a great weekend.
a
That's all right dude. If you get to the bottom of it... report back? Just out of interest, I mean.
b
Thought I would follow up. This turned out to be a trigger on the Stock table that was "fixing" a qty in the background to the proper value.
r
One of the reasons not to use triggers, although they can come in handy. Thanks for the follow up.
2 Views
Open in Slack
PreviousNext
Powered by