Grover McBroom
asked on
Changes in SQL Server with ColdFusion
We just updated to Windows Server 2012 r2, SQL Server 2013, and ColdFusion 11 from ColdFusion 8. Now our updates using CFQUERY don't seem to work using:
AND ONLINE = 1
Looks like we are now required to use:
AND ONLINE = 'True'
Looks like we need to change all our code to reflect that change using Dreamweaver, and gang change from one command, a bit precarious for me, but I'm in the process of doing so.
I'd just like some confirmation of this SQL change and any discussion about it, or what to avoid, what may or may not be correct and any suggestions anyone may have about this change using ColdFusion to update and insert date into the new SQL Server.
AND ONLINE = 1
Looks like we are now required to use:
AND ONLINE = 'True'
Looks like we need to change all our code to reflect that change using Dreamweaver, and gang change from one command, a bit precarious for me, but I'm in the process of doing so.
I'd just like some confirmation of this SQL change and any discussion about it, or what to avoid, what may or may not be correct and any suggestions anyone may have about this change using ColdFusion to update and insert date into the new SQL Server.
no such animal as SQL Server 2013, 2008, 2008R2, 2012 and 2014 are valid
ASKER
David > Yes, sorry, is SQL 2008 , not 2013..
Ryan Chong > It is of course, a BIT, not varchar. The confusing thing is that when looking at the column using SQL Management Studio, and SQL PANE, the column states "True", not 1 and "False" for 0. I thought there was some kind of screwy conversion from SQL 2000 to SQL 2008. Or perhaps that's just the way Microsoft decided to display the BIT for that field and if so, that leads to confusion.
When attempting to change the "False" in the "Online" column (set as a BIT), I get this error message:
Invalid value for cell (row 1, column 14).
The changed value in this cell was not recognized as valid.
.Net Framework Data Type: Boolean
Error Message: String was not recognized as a valid Boolean.
From my testing it seems that SQL, at some point changed showing the BIT data type from 0/1 to False/True. And to change that field with ColdFusion to True, one needs to update with:
Using ColdFusion's CFQUERY:
set online = 1
This works but also this works:
set online = 'True'
This probably worked all along, even with earlier versions but because of the way SQL is displaying that cell as "true" or "false", just leads to confusion as I see many other people experiencing the same confusion.
Is there a conversion going on with SQL?
I assume that SQL Server uses three valued logic (True, False, and NULL) as opposed to only True and False.
To keep overhead as low as possible, when using ColdFusion 11, should I use:
set online = 1 or set online = 'True'
I would assume it's more efficient to just use 1's and 0's when using CFQUERY.
Remember, the data was originally in SQL 2000 format, then RESTORED into SQL 2008 R2.
I'd like someone to actually and for certain verify this, and with an explanation, summary about the change in SQL, if what I wrote is correct. Otherwise I should close this.
Ryan Chong > It is of course, a BIT, not varchar. The confusing thing is that when looking at the column using SQL Management Studio, and SQL PANE, the column states "True", not 1 and "False" for 0. I thought there was some kind of screwy conversion from SQL 2000 to SQL 2008. Or perhaps that's just the way Microsoft decided to display the BIT for that field and if so, that leads to confusion.
When attempting to change the "False" in the "Online" column (set as a BIT), I get this error message:
Invalid value for cell (row 1, column 14).
The changed value in this cell was not recognized as valid.
.Net Framework Data Type: Boolean
Error Message: String was not recognized as a valid Boolean.
From my testing it seems that SQL, at some point changed showing the BIT data type from 0/1 to False/True. And to change that field with ColdFusion to True, one needs to update with:
Using ColdFusion's CFQUERY:
set online = 1
This works but also this works:
set online = 'True'
This probably worked all along, even with earlier versions but because of the way SQL is displaying that cell as "true" or "false", just leads to confusion as I see many other people experiencing the same confusion.
Is there a conversion going on with SQL?
I assume that SQL Server uses three valued logic (True, False, and NULL) as opposed to only True and False.
To keep overhead as low as possible, when using ColdFusion 11, should I use:
set online = 1 or set online = 'True'
I would assume it's more efficient to just use 1's and 0's when using CFQUERY.
Remember, the data was originally in SQL 2000 format, then RESTORED into SQL 2008 R2.
I'd like someone to actually and for certain verify this, and with an explanation, summary about the change in SQL, if what I wrote is correct. Otherwise I should close this.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for BinBroom's comment #a40959885
for the following reason:
No discussion with the concerns I have so guess I should close this.
Accepted answer: 0 points for BinBroom's comment #a40959885
for the following reason:
No discussion with the concerns I have so guess I should close this.
> our updates using CFQUERY don't seem to work using:
I'm a little confused. Ignoring how the value is displayed in SSMS, the original question said:
But later on you say
It sounds like you are now saying "set online = 1" DOES work ... or am I reading it wrong? If you can elaborate I'll test it with CF11 and SQL Server 2008. See if I can reproduce the results you are seeing.
Questions:
Under what conditions does it FAIL from CF?
- CF11
- SQL Server 2008
- Column: online / DataType: BIT
- Actual cfquery that fails?:
UPDATE SomeTable
SET online = 1
Under what conditions does it SUCCEED from CF?
- CF11
- SQL Server 2008
- Column: online / DataType: BIT
- Actual cfquery that succeeds?
UPDATE SomeTable
SET online = 'True'
I'm a little confused. Ignoring how the value is displayed in SSMS, the original question said:
Now our updates using CFQUERY don't seem to work using:
AND ONLINE = 1
....
But later on you say
From my testing it seems that SQL, at some point changed showing the BIT data type from 0/1 to False/True. And to change that field with ColdFusion to True, one needs to update with:
Using ColdFusion's CFQUERY:
set online = 1
This works but also this works:
set online = 'True'
It sounds like you are now saying "set online = 1" DOES work ... or am I reading it wrong? If you can elaborate I'll test it with CF11 and SQL Server 2008. See if I can reproduce the results you are seeing.
Questions:
Under what conditions does it FAIL from CF?
- CF11
- SQL Server 2008
- Column: online / DataType: BIT
- Actual cfquery that fails?:
UPDATE SomeTable
SET online = 1
Under what conditions does it SUCCEED from CF?
- CF11
- SQL Server 2008
- Column: online / DataType: BIT
- Actual cfquery that succeeds?
UPDATE SomeTable
SET online = 'True'
Truthfully I'm not sure if you're asking about SSMS or CFQUERY.... In terms of bit fields, yes, using 'true' probably worked all along:
.. and yes, supposedly there were changes to SSMS with regards to how IT treats bit fields, but ... again, that's just the IDE's behavior. It's totally unrelated to CF and cfquery. Like any IDE, SSMS is biased towards human friendly presentation over accuracy. Dates are a good example. They're stored as numbers, but SSMS presents them as human friendly strings.
The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
.. and yes, supposedly there were changes to SSMS with regards to how IT treats bit fields, but ... again, that's just the IDE's behavior. It's totally unrelated to CF and cfquery. Like any IDE, SSMS is biased towards human friendly presentation over accuracy. Dates are a good example. They're stored as numbers, but SSMS presents them as human friendly strings.
When attempting to change the "False" in the "Online" column (set as a BIT), I get this error message:
Invalid value for cell (row 1, column 14).
The changed value in this cell was not recognized as valid.
.Net Framework Data Type: Boolean
Error Message: String was not recognized as a valid Boolean.
Or perhaps that's just the way Microsoft decided to display the BIT for that field and if so, that leads to confusion.
As far as I can remember, if you directly opened a table by viewing its data in SQL Management Studio, for the Bit field, it will displayed as True/False/NULL instead.
>>I assume that SQL Server uses three valued logic (True, False, and NULL) as opposed to only True and False.
that's depends on your field setting whether to allow NULL value or not, but basically a Bit field will only accept 0 or 1, which equivalent to False and True.
I think there's still room for discussion, so I object to close this question at this moment.
ASKER
We just updated from SQL 2000 to SQL 2008. My confusion is that in SQL 2000 (and using CF8), when viewing the data in the older SQL Enterprise Manager, a 'bit' is shown in the table as 0 or 1, not True or False as is currently shown. That's what I was use to and expected. That caused part of the confusion on my part.
The other part of the confusion is that with the older system (SQL 2000 and CF8) when using CashedWithin on any cfquery with and the "Maximum number of cached queries" set higher than 1, the query was held in cache. That's what I expected after our software updates.
That does not seem to be the way it works in CF11 and SQL 2008. I now cannot go into CF Administrator and change the "Maximum number of cached queries" to "1" as before to flush the cache. I just tested it pretty well. If a cfquery is using CashedWithin, the only way I have found so far to remove the cache is to edit the code and removed the CashedWithin statement.
This scenario caused me to believe that the way data type 'bit" is manipulated by Coldfusion and SQL, had changed. It appears it makes no difference if it's 1 or True, 0 or False. It's just that SQL changed the way the bit is displayed, and one CAN NOT change, as an example, "True" to a "1" using the SSMS interface. Again, that led to my confusion.
As far as removing the Cache on any particular query, I will assume there is a way around this other than editing the actual cfquery, and hopefully ColdFusion made an improvement here I cannot see or at least have not found yet. So, that's the problem, originally posed incorrectly because of my confusion about the changes and the way CF works.
I hope someone will point out the best and easiest way to remove a single or multiple cached queries, either in CF Admin or elsewhere, just toggle off/on so that the data is then pulled from the SQL table, then cache turned back on. We have always needed to reset that cached query when we update our data. I unchecked every single option in CF Admin under "Server Settings > Caching" or set numbers to 1 or 0. But none of that helped until I either removed the CachedWithin code or set the CachedWithin time settings.
When we write a new story, the new or updated SQL row includes data types: INT (ID -PK), BIT, TEXT, VARCHAR, DATETIME, etc. We understand the CachedWithin statement in the cfquery holds data in memory and does not query the database until the Cache is released. I see there are several new attributes for Cache and I've been reading about them but most are confusing so hope someone will give me a quick answer that will do the trick for the time being, then I'll attempt to educate myself in some depth later.
So, should I create another question or is there an easy answer that works to this problem about toggling cache off/on for either a single query or system wide?
The other part of the confusion is that with the older system (SQL 2000 and CF8) when using CashedWithin on any cfquery with and the "Maximum number of cached queries" set higher than 1, the query was held in cache. That's what I expected after our software updates.
That does not seem to be the way it works in CF11 and SQL 2008. I now cannot go into CF Administrator and change the "Maximum number of cached queries" to "1" as before to flush the cache. I just tested it pretty well. If a cfquery is using CashedWithin, the only way I have found so far to remove the cache is to edit the code and removed the CashedWithin statement.
This scenario caused me to believe that the way data type 'bit" is manipulated by Coldfusion and SQL, had changed. It appears it makes no difference if it's 1 or True, 0 or False. It's just that SQL changed the way the bit is displayed, and one CAN NOT change, as an example, "True" to a "1" using the SSMS interface. Again, that led to my confusion.
As far as removing the Cache on any particular query, I will assume there is a way around this other than editing the actual cfquery, and hopefully ColdFusion made an improvement here I cannot see or at least have not found yet. So, that's the problem, originally posed incorrectly because of my confusion about the changes and the way CF works.
I hope someone will point out the best and easiest way to remove a single or multiple cached queries, either in CF Admin or elsewhere, just toggle off/on so that the data is then pulled from the SQL table, then cache turned back on. We have always needed to reset that cached query when we update our data. I unchecked every single option in CF Admin under "Server Settings > Caching" or set numbers to 1 or 0. But none of that helped until I either removed the CachedWithin code or set the CachedWithin time settings.
When we write a new story, the new or updated SQL row includes data types: INT (ID -PK), BIT, TEXT, VARCHAR, DATETIME, etc. We understand the CachedWithin statement in the cfquery holds data in memory and does not query the database until the Cache is released. I see there are several new attributes for Cache and I've been reading about them but most are confusing so hope someone will give me a quick answer that will do the trick for the time being, then I'll attempt to educate myself in some depth later.
So, should I create another question or is there an easy answer that works to this problem about toggling cache off/on for either a single query or system wide?
so are you suspecting that the use of "CachedWithin" will change the behaviour of how your Update SQL statement should look like?
can you do a simple test, which doing a simple Update SQL statement using cfquery with same connection setting and without CachedWithin, and see if you can execute and updated the bit field without any problem?
can you do a simple test, which doing a simple Update SQL statement using cfquery with same connection setting and without CachedWithin, and see if you can execute and updated the bit field without any problem?
ASKER
I cannot seem to clear the template cache. No matter what I do, the same data remains. Nothing clears, no matter what I do in CF Administrator's Server Settings > Caching settings. I have tried EVERY option. In ColdFusion 8 I was able to set "Maximum number of cached queries" from, sayh 200 to 1, which would clear the Cache. Now, in ColdFusion 11, this does not work at all.
See the attached screen shot of CF Admin.
My query will only be cleared when I remove the "cachedWithin" statement on the query.
<cfquery name="StoryInfo" datasource="qnews" cachedWithin="#CreateTimeS
select top 50 *
from storybook
where online = 1
order by id desc
</cfquery>
With the new version CF8 to CF11, I had hoped I'd be able to place a link for each page I wanted to remove the cache and simply link to it to remove that Cache.
Or honestly, I'd simply settle for removing all cache system wide when we add a new story and want it to be seen, and just as quickly as I did in CF8. But this new version of CF11 seems beyond me. I am not a programmer but subbing for the one who died. I've read so many examples on the net but they assume I know more than I do which is quite frustrating.
The DOCS from Adobe seem written in nearly another language. So, all I've been able to do is experiment. I mean, all I want to do is remove the cache system wide so my changes will take effect and be seen, even if it's only one addition to one table. Having to edit the cfquery seems pretty stupid, no doubt my confusion and lack of understanding.
I'm waiting until hopefully someone will read this and see my obvious confusion and show me how dumb I was and tell me what I need to do so I can get on with my life.
Still waiting.
cf-admin-caching-screen.gif
See the attached screen shot of CF Admin.
My query will only be cleared when I remove the "cachedWithin" statement on the query.
<cfquery name="StoryInfo" datasource="qnews" cachedWithin="#CreateTimeS
select top 50 *
from storybook
where online = 1
order by id desc
</cfquery>
With the new version CF8 to CF11, I had hoped I'd be able to place a link for each page I wanted to remove the cache and simply link to it to remove that Cache.
Or honestly, I'd simply settle for removing all cache system wide when we add a new story and want it to be seen, and just as quickly as I did in CF8. But this new version of CF11 seems beyond me. I am not a programmer but subbing for the one who died. I've read so many examples on the net but they assume I know more than I do which is quite frustrating.
The DOCS from Adobe seem written in nearly another language. So, all I've been able to do is experiment. I mean, all I want to do is remove the cache system wide so my changes will take effect and be seen, even if it's only one addition to one table. Having to edit the cfquery seems pretty stupid, no doubt my confusion and lack of understanding.
I'm waiting until hopefully someone will read this and see my obvious confusion and show me how dumb I was and tell me what I need to do so I can get on with my life.
Still waiting.
cf-admin-caching-screen.gif
ASKER
In reference to: "Administrative Comment 2015-09-03 at 22:24:05ID: 40961959"
"You should only close your question if there's a solution or the issue is no longer relevant. Please do not feel pressured to close your question unnecessarily."
Leaving this open did not seem to help. I have the same basic problem but with no suggestions that solve the problem. So, to keep from receiving additional Administrative comments, I'll simply leave this open but create another, more specific question where hopefully someone will be able to help with our problem.
"You should only close your question if there's a solution or the issue is no longer relevant. Please do not feel pressured to close your question unnecessarily."
Leaving this open did not seem to help. I have the same basic problem but with no suggestions that solve the problem. So, to keep from receiving additional Administrative comments, I'll simply leave this open but create another, more specific question where hopefully someone will be able to help with our problem.
ok, seems you getting some new insights by yourself in your another post by adding:
<cfobjectcache action = "clear">
to your page.
https://www.experts-exchange.com/questions/28712592/URGENT-ColdFusion-11-Cache-not-working-as-expected.html
so, by doing this, would your original issue getting resolved?
<cfobjectcache action = "clear">
to your page.
https://www.experts-exchange.com/questions/28712592/URGENT-ColdFusion-11-Cache-not-working-as-expected.html
so, by doing this, would your original issue getting resolved?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Yes but, just like last time I tried to close, not sure how I should close this.
ok glad that you found the solution....
do you mean to close this question? I suggest to PAQ as 0 pts and accept comment: ID: 40970427 as the answer instead.
ok glad that you found the solution....
do you mean to close this question? I suggest to PAQ as 0 pts and accept comment: ID: 40970427 as the answer instead.
ASKER
Right. PAQ sounds good to me and i'll do it. But first, please tell me ...
What does PAQ mean?
What does PAQ mean?
PAQ = previous asked question... emm , I mean to close this question..
check this out on how to do it cheers
http://support.experts-exchange.com/customer/portal/articles/626549
check this out on how to do it cheers
http://support.experts-exchange.com/customer/portal/articles/626549
ASKER
Found an acceptable solution.
if it's a Bit/Int, you should use AND ONLINE = 1
if it's a varchar, you should use AND ONLINE = 'True'
it's not dependent on ColdFusion to change the SQL syntax.