| Author |
 Topic  |
|
|
Deleted
deleted
4116 Posts |
 Posted - 26 January 2003 : 20:51:46
|
During my tests towards v4b04, I keep seeing multiple records (different cases) of the same variable. This is both true with Access 2000 and SQl Server 2000 tests (no conversion). Examples are:
ID C_VARIABLE C_VALUE
50 STRVISITEDLINKCOLOR blue
51 STRVISITEDTEXTDECORATION none
558 strVisitedLinkColor blue
559 strVisitedTextDecoration none
The more I use the database through setup/upgrade/config process, the more show these duplicates up.
I first thought that is v4 related, but checked one other v3.4.03 site and saw the same issue there.
As you know these are input to the database through Function SetConfigValue(bUpdate, fVariable, fValue) and the related SQL commands in this function are:
strSql = "SELECT C_VARIABLE FROM " & strTablePrefix & "CONFIG_NEW " &_
" WHERE C_VARIABLE = '" & fVariable & "' "
...
my_conn.execute ("INSERT INTO " & strTablePrefix & "CONFIG_NEW (C_VALUE,C_VARIABLE) " &_
"VALUES ('" & fValue & "' , '" & fVariable & "')"),,adCmdText + adExecuteNoRecords
...
my_conn.execute ("UPDATE " & strTablePrefix & "CONFIG_NEW SET C_VALUE = '" & fValue & "' " &_
"WHERE C_VARIABLE = '" & fVariable &"'"),,adCmdText + adExecuteNoRecords
The fVariable is used as is, meaning the value it handles is case sensitive. However, there are different types of calling this function:
For example, in inc_create_forum_config_values.asp (nw creation):
strDummy = SetConfigValue(1,"STRVERSION", strNewVersion)
In setup.asp (upgrades):
strDummy = SetConfigValue(1,"strVersion", strNewVersion) '## make sure the string is there
Also in some of the files they are called as (this is from admin_config_datetime.asp):
for each key in Request.Form
if left(key,3) = "str" or left(key,3) = "int" then
strDummy = SetConfigValue(1, key, ChkString(Request.Form(key),"SQLstring"))
end if
next
This last one depends on the case of the form elements.
I'm not sure on the exact effects of this design. Eventually the strMyVariable format is used, not the original STRMYVARIABLE format, but the database size and the process that takes them into application variables eventually doubles. They don't get mixed in runtime, because application variable keys are not case sensitive AND the latest records in the table get used. What I'm sure is, that this is not the intended behaviour.
I suggest using uppercase ASCII values in that table (for C_VARIABLE field) and to ensure it, we simply can add UCASE into the SetConfigValue function. We may also need an external tool to clean-up/correct the table. Alternatively we can include that process into upgrade part of v4 setup.
Do you have the same issue? What do you think?
|
 Stop the WAR! |
|
|
Deleted
deleted
4116 Posts |
Posted - 27 January 2003 : 06:08:46
|
| Any idea on this? |
Stop the WAR! |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 27 January 2003 : 07:39:54
|
Now that you talk about it, I just checked an upgraded 3.4 version and verified a very similar situation, though not quite like it. Here is what that database has for rank variables:
ID C_VARIABLE C_VALUE
91 INTRRANKLEVEL5 1000
90 INTRRANKLEVEL4 500
89 INTRRANKLEVEL3 250
88 INTRRANKLEVEL2 50
87 INTRRANKLEVEL1 10
86 INTRRANKLEVEL0 0
142 intRankLevel5 1000
141 intRankLevel4 500
140 intRankLevel3 250
139 intRankLevel2 50
138 intRankLevel1 10
137 intRankLevel0 0
Probably there was change of the name between versions that can explain this, but there is also the case difference between the two sets. I agree with your proposal to use always upper case, it can alleviate the problem and it can be used as a standard for mod writers.
Maybe we could have a function that would not allow duplicates for these variables, through a controlled insertion process, that everybody, even mod writers could use. I have experienced similar situations with mod defined variables that, though using the same case and names were repeated each time the DBS was run, which was very unpleasant... This was a test site to help a user with a specific upgrade path, and yes a DBS was used. I know you were not addressing DBS files here, but I guess they needed to be considered as well. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Deleted
deleted
4116 Posts |
Posted - 27 January 2003 : 08:39:51
|
SetConfigValue is for this purpose. It checks the existance and inserts/replaces/does nothing depending on the first parameter.
The problem is the case difference that prevents the detection.
Your problem is caused by a bug in v3.3.x I think. I didn't search it here but v3.3.05 has in setup.asp upgrade procedure:
strDummy = SetConfigValue(0,"INTRRANKLEVEL0", rs("C_INTRANKLEVEL0"))
strDummy = SetConfigValue(0,"INTRRANKLEVEL1", rs("C_INTRANKLEVEL1"))
strDummy = SetConfigValue(0,"INTRRANKLEVEL2", rs("C_INTRANKLEVEL2"))
strDummy = SetConfigValue(0,"INTRRANKLEVEL3", rs("C_INTRANKLEVEL3"))
strDummy = SetConfigValue(0,"INTRRANKLEVEL4", rs("C_INTRANKLEVEL4"))
strDummy = SetConfigValue(0,"INTRRANKLEVEL5", rs("C_INTRANKLEVEL5"))
These seem to be corrected in v3.4...
|
Stop the WAR! |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Deleted
deleted
4116 Posts |
Posted - 27 January 2003 : 10:24:52
|
Can someone from the dev-team confirm the bug (if it is). Here is my (simple) suggested change in inc_func_admin.asp (replace the function):
Function SetConfigValue(bUpdate, fVariable, fValue)
' bUpdate = 1 : if it exists then overwrite with new values
' bUpdate = 0 : if it exists then leave unchanged
Dim strSql, strVariable
strVariable = ucase(fVariable)
strSql = "SELECT C_VARIABLE FROM " & strTablePrefix & "CONFIG_NEW " &_
" WHERE C_VARIABLE = '" & strVariable & "' "
Set rs = Server.CreateObject("ADODB.Recordset")
rs.open strSql, my_Conn
if (rs.EOF or rs.BOF) then '## New config-value
SetConfigValue = "added"
my_conn.execute ("INSERT INTO " & strTablePrefix & "CONFIG_NEW (C_VALUE,C_VARIABLE) VALUES ('" & fValue & "' , '" & strVariable & "')"),,adCmdText + adExecuteNoRecords
else
if bUpdate <> 0 then
SetConfigValue = "updated"
my_conn.execute ("UPDATE " & strTablePrefix & "CONFIG_NEW SET C_VALUE = '" & fValue & "' WHERE C_VARIABLE = '" & strVariable &"'"),,adCmdText + adExecuteNoRecords
else ' not changed
SetConfigValue = "unchanged"
end if
end if
rs.close
set rs = nothing
end function
This may cause problems on existing forum configuration values. Don't try on working systems.
|
Stop the WAR! |
|
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 27 January 2003 : 11:23:57
|
The same function is also in setup.asp
|
|
|
|
Deleted
deleted
4116 Posts |
Posted - 27 January 2003 : 11:42:45
|
You are right, I had that in mind but had to leave before posting this  ... |
Stop the WAR! |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 27 January 2003 : 12:04:36
|
I tried several ways to reproduce the scenario but no success.
However, bozden's suggestion won't do any harm. A little modification though to bozden's suggestion
strSql = "SELECT C_VARIABLE FROM " & strTablePrefix & "CONFIG_NEW " &_
" WHERE C_VARIABLE = '" & strVariable & "' "
strSql = "SELECT C_VARIABLE FROM " & strTablePrefix & "CONFIG_NEW " &_
" WHERE UCASE(C_VARIABLE) = '" & strVariable & "' "
|
|
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 27 January 2003 : 12:41:08
|
This is not a bug, but something with your database settings, the following queeries would all return the same reult
SELECT C_VARIABLE FROM SnitzUser.FORUM_CONFIG_NEW WHERE (C_VARIABLE = 'strversion')
SELECT C_VARIABLE FROM SnitzUser.FORUM_CONFIG_NEW WHERE (C_VARIABLE = 'STRVERSION')
SELECT C_VARIABLE FROM SnitzUser.FORUM_CONFIG_NEW WHERE (C_VARIABLE = 'StrVersion')
it is not case sensitive, they would all return the same record from the db |
|
|
|
Deleted
deleted
4116 Posts |
Posted - 28 January 2003 : 06:18:39
|
quote:
Originally posted by HuwR
This is not a bug, but something with your database settings, the following queeries would all return the same reult
SELECT C_VARIABLE FROM SnitzUser.FORUM_CONFIG_NEW WHERE (C_VARIABLE = 'strversion')
SELECT C_VARIABLE FROM SnitzUser.FORUM_CONFIG_NEW WHERE (C_VARIABLE = 'STRVERSION')
SELECT C_VARIABLE FROM SnitzUser.FORUM_CONFIG_NEW WHERE (C_VARIABLE = 'StrVersion')
it is not case sensitive, they would all return the same record from the db
It seems to be an issue rather than a bug, but what I see is, it is consistent in all my databases, Access or SQL Server, only setup or upgraded or DTS'ed...
I think it is related to my locale, which is Turkey/Turkic/Turkish. I use default server collation (SQL Server) as Turkish_CI_AS (Case Insensitive & Accent Sensitive) and all databases use the server default... In access I use Turkish sort order in Tools/Options=>General tab. Without these, I cannot get correct sort order with Turkish data which is default here.
In Turkish, ucase("i") is "Ý" and lcase("I") is "ý". This is one of the most important issues we have around here. As far as I can see ONLY those variables with (English) I or i in their names got effected.
What callation order do you use by default on SQL Server? I'll create one database with the same and test on it. As you may know I cannot change the server default collation easily, but I can create new databases with different collation orders.
Although I admit that it is not a general bug/issue, it is a PERSISTENT BUG for Turkish users. It is similar to the issue I mentioned a couple of times with table/field names and related SQL code in ASP: rs("TOPIC_ID") is not the same as rs("topic_id") here.
It may also rise problems with other collation orders. If there is no objection, I'd prefer to implement the above idea (with GB's addition ) in v4...
I'm afraid we will encounter much more issues like this one with Snitz being used international...
|
Stop the WAR! |
|
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 28 January 2003 : 06:54:35
|
quote:
Originally posted by GauravBhabu
strSql = "SELECT C_VARIABLE FROM " & strTablePrefix & "CONFIG_NEW " &_
" WHERE UCASE(C_VARIABLE) = '" & strVariable & "' "
I wouldn't use a UCASE in the SQL statement. It would only work for access.
You need to use UPPER in MS SQL Server.
Using functions in an SQL statement can lead to problems with different database platforms meaning you would probably need to code different versions of the SQL statement for different databases. |
|
|
|
Deleted
deleted
4116 Posts |
Posted - 28 January 2003 : 07:46:11
|
This is from http://www.microsoft.com/globaldev/getwr/steps/wrg_localizability.mspx
quote:
The product needs to be globalized: As localized versions are based on the US product, the bugs appearing in the US product will appear in the localized product too. As "world ready" features are especially important for the international market (the market where localized products sell), the US product needs to be "globalized". "Globalized" means that the product does not make any assumption based on a single language or locale, and have full support for different fonts, locales, sorting orders, time/date/address formats, etc. Once the product is globalized and localizable it is considered "world-ready."
|
Stop the WAR! |
|
|
| |
Topic |
|
|
|
Topic Locked
