Snitz Forums 2000 - (v3.4.03) BUG+FIX: text fields in upgraded SQL Dbs

Snitz Forums 2000

Username:	Password:
Save Password
Forgot your Password?

All Forums

Snitz Forums 2000 DEV-Group

DEV Bug Reports (Closed)

(v3.4.03) BUG+FIX: text fields in upgraded SQL Dbs

Forum Locked

Topic Locked

Printer Friendly

Author

Topic

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 06 October 2002 : 06:30:11

Just found out, during a discussion with Bozden, that the FORUM_REPLY.R_MESSAGE and the FORUM_TOPICS.T_MESSAGE remain as text fields in upgraded SQL Server DBs. In new SQL Server 3.4 DBs these fields are created as ntext fields, which I think is what was intended for them.

Snitz 3.4 Readme | Like the support? Support Snitz too

bax
Junior Member

141 Posts

Posted - 13 October 2002 : 15:12:38

Why the change? What would be the advantage of setting them as ntext? I started with a clean install of 3.4, and they are set as ntext initially. Must only be an upgrade thing.

Deleted
deleted

4116 Posts

Posted - 13 October 2002 : 18:05:56

Yes it is only a bug in upgrade. ntext can contain unicode which is a standart in Snitz for a bout a year (also the logical move towards globalization), whereas text cannot contain unicode.

The downside is it doubles the database space these fields need.

Stop the WAR!

work mule
Senior Member

USA
1358 Posts

Posted - 13 October 2002 : 18:59:58

I really don't want to start the discussion again

, but here's a related topic. It comes down to your own needs and preference and also the fact that after you install, you can change to suit your needs/preference. Put it in for those who'll need it. Like everything else, we can always modify after the install.

http://forum.snitz.com/forum/topic.asp?TOPIC_ID=35325

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 13 October 2002 : 20:01:32

Well I agree that those who don't need Unicode can change their column properties in the REPLY and TOPICS tables. My post was justified by the inconsistence between new and upgraded 3.4 forums. If new 3.4 forums have fields as ntext, so should upgraded forums have.

The decision to change the default table properties should be left to each user. Since Snitz is commited to have support for internationalization I don't see why these columns should be text instead of ntext.

Snitz 3.4 Readme | Like the support? Support Snitz too

Davio
Development Team Member

Jamaica
12217 Posts

Posted - 13 February 2003 : 11:20:16

Bozden have you made this change to the alpha versions yet?

Support Snitz Forums

Deleted
deleted

4116 Posts

Posted - 13 February 2003 : 12:29:11

Yes, it was fixed in v4b04 alpha 02, but I'm not sure of something:

Many people already upgraded using the script which uses TEXT (not NTEXT). I think I must add another ALTER TABLE command during v3.x => v4.x upgrade to be on the safe side. What do you think?

Stop the WAR!

Davio
Development Team Member

Jamaica
12217 Posts

Posted - 13 February 2003 : 12:38:25

Sounds good to me.

Support Snitz Forums

Deleted
deleted

4116 Posts

Posted - 13 February 2003 : 13:02:00

Implemented in v4b04 alpha 03, so that it is assured that TOPICS, A_TOPICS, REPLY, A_REPLY tables have NTEXT field type in SQL 7.

The setup/upgrade related code should be throughly tested in multiple environments...

Stop the WAR!

Davio
Development Team Member

Jamaica
12217 Posts

Posted - 07 March 2003 : 10:44:00

Bozden want to post the changes you made to fix this bug?

Support Snitz Forums

Deleted
deleted

4116 Posts

Posted - 15 March 2003 : 20:38:35

Let me "try" to pinpoint them. I cannot give exact line numbers because I worked them on v4b04, thus I'll rather give somehow longer code portions... Changes are marked with bold-red...

Setup.asp


[AROUND LINE 1450]
			SpecialSql5(SQL7) = "CREATE TABLE " & strTablePrefix & "A_TOPICS ( "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "CAT_ID int NOT NULL , "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "FORUM_ID int NOT NULL , "
	 		SpecialSql5(SQL7) = SpecialSql5(SQL7) & "TOPIC_ID int IDENTITY (1, 1) NOT NULL , "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "T_STATUS smallint NULL , "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "T_MAIL smallint NULL , "
	 		SpecialSql5(SQL7) = SpecialSql5(SQL7) & "T_SUBJECT nvarchar (100) NULL , "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "T_MESSAGE ntext NULL , "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "T_AUTHOR int NULL , "
	 		SpecialSql5(SQL7) = SpecialSql5(SQL7) & "T_REPLIES int NULL , "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "T_VIEW_COUNT int NULL , "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "T_LAST_POST varchar (14) NULL , "
	 		SpecialSql5(SQL7) = SpecialSql5(SQL7) & "T_DATE varchar (14) NULL, "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "T_LAST_POSTER int NULL, "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "T_IP varchar (15) NULL, " 
	 		SpecialSql5(SQL7) = SpecialSql5(SQL7) & "T_LAST_POST_AUTHOR int NULL ) "

...
[AROUND LINE 1520]

	 		SpecialSql5(SQL7) = "CREATE TABLE " & strTablePrefix & "A_REPLY ( "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "CAT_ID int NOT NULL , "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "FORUM_ID int NOT NULL , "
	 		SpecialSql5(SQL7) = SpecialSql5(SQL7) & "TOPIC_ID int NOT NULL , "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "REPLY_ID int IDENTITY (1, 1) NOT NULL , "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "R_STATUS smallint NULL , "
	 		SpecialSql5(SQL7) = SpecialSql5(SQL7) & "R_MAIL smallint NULL , "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "R_AUTHOR int NULL , "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "R_MESSAGE ntext NULL , "
	 		SpecialSql5(SQL7) = SpecialSql5(SQL7) & "R_DATE varchar (14) NULL , "
 			SpecialSql5(SQL7) = SpecialSql5(SQL7) & "R_IP varchar (15) NULL ) "

...
[AROUND LINE 1820]

				SpecialSql6(SQL7) = "CREATE TABLE " & strTablePrefix & "A_TOPICS ( "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "CAT_ID int NOT NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "FORUM_ID int NOT NULL , "
	 			SpecialSql6(SQL7) = SpecialSql6(SQL7) & "TOPIC_ID int NOT NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "T_STATUS smallint NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "T_MAIL smallint NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "T_SUBJECT nvarchar (100) NULL , "
	 			SpecialSql6(SQL7) = SpecialSql6(SQL7) & "T_MESSAGE ntext NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "T_AUTHOR int NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "T_REPLIES int NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "T_VIEW_COUNT int NULL , "
	 			SpecialSql6(SQL7) = SpecialSql6(SQL7) & "T_LAST_POST varchar (14) NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "T_DATE varchar (14) NULL, "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "T_LAST_POSTER int NULL, "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "T_IP varchar (15) NULL, " 
	 			SpecialSql6(SQL7) = SpecialSql6(SQL7) & "T_LAST_POST_AUTHOR int NULL ) "

...
[AROUND LINE 1900]
		 		SpecialSql6(SQL7) = "CREATE TABLE " & strTablePrefix & "A_REPLY ( "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "CAT_ID int NOT NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "FORUM_ID int NOT NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "TOPIC_ID int NOT NULL , "
	 			SpecialSql6(SQL7) = SpecialSql6(SQL7) & "REPLY_ID int NOT NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "R_STATUS smallint NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "R_MAIL smallint NULL , "
	 			SpecialSql6(SQL7) = SpecialSql6(SQL7) & "R_AUTHOR int NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "R_MESSAGE ntext NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "R_DATE varchar (14) NULL , "
 				SpecialSql6(SQL7) = SpecialSql6(SQL7) & "R_IP varchar (15) NULL ) "


...
[TO BE ADDED INTO NEW UPGRADE CODE]
			'## Change SQL 7 T_MESSAGE, T_SUBJECT and R_MESSAGE fields to unicode versions correcting an old bug
			if (strDBType = "sqlserver") and (strSQL_Server = "SQL7") then
				Dim SpecialSQL12(4)
				'## Change T_MESSAGE to a NTEXT in A_TOPICS table and TOPICS table
				strOkMessage = fLangN(sLngSetup00520, "T_MESSAGE")

		 		SpecialSQL12(SQL7) = "ALTER TABLE " & strTablePrefix & "A_TOPICS ALTER COLUMN T_MESSAGE NTEXT NULL "
				call SpecialUpdates(SpecialSQL12, strOkMessage)
				Response.Flush

		 		SpecialSQL12(SQL7) = "ALTER TABLE " & strTablePrefix & "TOPICS ALTER COLUMN T_MESSAGE NTEXT NULL "
				call SpecialUpdates(SpecialSQL12, strOkMessage)
				Response.Flush

				'## Change T_SUBJECT to a NVARCHAR in A_TOPICS table and TOPICS table
				strOkMessage = fLangN(sLngSetup00520, "T_SUBJECT")

		 		SpecialSQL12(SQL7) = "ALTER TABLE " & strTablePrefix & "A_TOPICS ALTER COLUMN T_SUBJECT NVARCHAR (100) NULL "
				call SpecialUpdates(SpecialSQL12, strOkMessage)
				Response.Flush

		 		SpecialSQL12(SQL7) = "ALTER TABLE " & strTablePrefix & "TOPICS ALTER COLUMN T_SUBJECT NVARCHAR (100) NULL "
				call SpecialUpdates(SpecialSQL12, strOkMessage)
				Response.Flush

				'## Change R_MESSAGE to a NTEXT in A_REPLY table and REPLY table
				strOkMessage = fLangN(sLngSetup00520, "R_MESSAGE")

		 		SpecialSQL12(SQL7) = "ALTER TABLE " & strTablePrefix & "A_REPLY ALTER COLUMN R_MESSAGE NTEXT NULL "
				call SpecialUpdates(SpecialSQL12, strOkMessage)
				Response.Flush

		 		SpecialSQL12(SQL7) = "ALTER TABLE " & strTablePrefix & "REPLY ALTER COLUMN R_MESSAGE NTEXT NULL "
				call SpecialUpdates(SpecialSQL12, strOkMessage)
				Response.Flush
			end if

In the last portion of the above code, just replace the v4 specific language elements of type


	strOkMessage = fLangN(sLngSetup00520, "T_MESSAGE")

with


	strOkMessage = "T_MESSAGE Field has been changed"

As you see, the problem is caused by a buggy version of inc_create_forum_mssql.asp (I cheched v3.3.05 and it has this bug), where the TOPICS and REPLY tables are created with non-unicode types. If people used that version to create the tables, the effects continue to exist...

This is all I have in v4b04 alpha code, but (read below)...

On the other hand, I fail to understand the use of NVARCHAR on some text fields like (code portion from latest inc_create_forum_mssql.asp):


	strSql = "CREATE TABLE " & strTablePrefix & "REPLY ( "
	strSql = strSql & "CAT_ID int NOT NULL , "
	strSql = strSql & "FORUM_ID int NOT NULL , "
	strSql = strSql & "TOPIC_ID int NOT NULL , "
	strSql = strSql & "REPLY_ID int IDENTITY (1, 1) NOT NULL , "
	strSql = strSql & "R_MAIL smallint NULL , "
	strSql = strSql & "R_AUTHOR int NULL , "
	strSql = strSql & "R_MESSAGE " & strN & "text NULL , "
	strSql = strSql & "R_DATE " & strN & "varchar (14) NULL , "
	strSql = strSql & "R_IP " & strN & "varchar (15) NULL, "
	strSql = strSql & "R_STATUS smallint NULL CONSTRAINT " & strTablePrefix & "SnitzC1017 DEFAULT 0 , "
	strSql = strSql & "R_LAST_EDIT " & strN & "varchar (14) NULL , " 
	strSql = strSql & "R_LAST_EDITBY int NULL , "
	strSql = strSql & "R_SIG smallint NULL ) "

These fields are numeric only and there is no need for them to be nvarchar even in an international environment (in our current knowledge of implementations). Therefore I did NOT took any actions to correct similar issues in these fields.

BUT, there can be a risk caused by incompatibilities in TOPICS and A_TOPICS, REPLY and A_REPLY structures. Perhaps worth to examine further...

Stop the WAR!

RichardKinser
Snitz Forums Admin

USA
16655 Posts

Posted - 15 March 2003 : 20:44:34

the date fields and the IP fields are not number fields per se. They are really text fields that contain numbers.

Deleted
deleted

4116 Posts

Posted - 15 March 2003 : 21:04:59

quote:
Originally posted by RichardKinser

the date fields and the IP fields are not number fields per se. They are really text fields that contain numbers.

Yes, my phrasing is not quite correct (meant "digits"), but the fact is the same. They sure are from the ASCII set and there is no need for them to be unicode IMO. But having them already unicode, the problem is: We may need to make these fields also part of the upgrade process.

Stop the WAR!

Davio
Development Team Member

Jamaica
12217 Posts

Posted - 15 March 2003 : 22:15:38

Thanks Bozden.

Fixed in 3.4.04.

Support Snitz Forums

Topic

Forum Locked

Topic Locked

Printer Friendly

Jump To:

Snitz Forums 2000

This page was generated in 0.2 seconds.