| Author |
Topic |
|
Nick
Posting Yak Master
155 Posts |
 Posted - 2002-03-21 : 12:38:27
|
| What data type is best for use in a forum message? For instance, what is this message being stored in right now? I need to use something that can possibly be larger than 8000 bytes.What does Snitz use? |
|
|
Jay99
468 Posts |
Posted - 2002-03-21 : 12:57:40
|
Dunno 'bout Snitz implementation, but you should probably ...create table message( messsage varchar(7500), chunknumber tinyint) ... then take the message and break it into 7500(or whatever) character chunks and order them. Your other option is to use a TEXT datatype, but for a web based app, that get kinda hairy (but can be done).BTW: remember SQL Server uses 8K pages so when you are dealing with large varchars you might want to pay attention to your row size so it/they fit neatly on the pages . . .Jay<O> |
 |
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2002-03-21 : 13:00:40
|
Yeah, I'm thinking I don't want to use TEXT because it is for a web based app. Would I split everything in a Stored Procedure, or should I do it in the web page? This seems like it would be a pretty common problem. Has anyone else run into it and come up with a clean solution?quote:
Dunno 'bout Snitz implementation, but you should probably ...create table message( messsage varchar(7500), chunknumber tinyint) ... then take the message and break it into 7500(or whatever) character chunks and order them. Your other option is to use a TEXT datatype, but for a web based app, that get kinda hairy (but can be done).BTW: remember SQL Server uses 8K pages so when you are dealing with large varchars you might want to pay attention to your row size so it/they fit neatly on the pages . . .Jay<O>
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-21 : 13:06:21
|
| Snitz DOES use text data (memo if you have an Access database). If you're gonna be using ADO to retrieve it, then text is not really that problematic. It's trying to use T-SQL or stored procedures to manipulate it that's troublesome. |
|
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2002-03-21 : 13:08:17
|
| Well, I really need to use a SP... Is it the best solution to just split the message? It seems a bit ugly... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-21 : 13:14:54
|
| Well, answer this: how does the text data go into the table? When you retrieve it using the SP, what do you do with it? Do you need to manipulate it or search it?While using varchar will alleviate the usual text data problems, splitting it into multiple rows introduces a bunch of new problems. It's hard to say which is better without more background on how the data is handled. |
|
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2002-03-21 : 13:18:57
|
| What I'm doing with it is very easy... We want to allow "management" type people to post small messages to sales people located around the country. We are basically pulling out the subject on one screen, and allowing the user to click on it. Then we are just going to display the entire message. We do not need a search capability, and we only need to manipulate the data if the Management would like to edit the message...I'm assuming there is no work around for using TEXT with SP's? |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-21 : 13:22:27
|
quote:
I'm assuming there is no work around for using TEXT with SP's?
Don't get the wrong idea here . . . you can work with TEXT datatype in store procs. It just not quite as simple as select blah from table47 . . .I suggest you read up on working with TEXT (lookup READTEXT, WRITETEXT, TEXTPTR etc) and make some prototypes both ways. See what works best for you . . .Jay<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-21 : 13:30:42
|
| You CAN manipulate text data in T-SQL, it's just UNBELIEVEABLY difficult. Nothing like varchar. Think about editing a Word document without having the find/replace facility, or using the mouse, and you'll get the idea!SNIPED AGAIN! Oh well.If these are supposed to be short notes, can you sell the end users on an upper limit? 8000 characters is A LOT, it's almost 2 single-spaced typed pages. I imagine 2000 characters could well handle 99.99% of what people will actually type.If you do have a couple of Tolstoy's working at your company, you can still use text. I imagine the application front-end will use ADO to access the data, and the message will be dumped into some kind of text box. In that case, all you're really doing is replacing the entire text column with a new message. The worst case would be to use a WRITETEXT operation to re-write the column. Look in Books Online for WRITETEXT and TEXT_PTR(), there are examples there on how it works. I don't think it'll be too bad, but check it out and see if you want to go that route.Edited by - robvolk on 03/21/2002 13:31:36 |
|
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2002-03-21 : 13:32:43
|
| Thanks.. You've both given me stuff to think about... 2 type pages is alot, but I know as soon as I'm done, they are going to decide they want to put 10-page press releases and whatnot on there. Off to BOL, to get a feel for TEXT! UGH! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-21 : 13:40:03
|
| Hey, I managed to try this, and it works!CREATE PROCEDURE UpdateMessage @msgID int, @msgtxt text ASUPDATE myTableSET textColumn=@msgtxtWHERE IDCol=@msgIDIf you're using ADO, you can create a Command object to use this SP name, add each parameter, and set their values to the user input. I only tested this with relatively small amounts of text (250 characters max) but I don't forsee any issues.Let us know how it works out! |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-03-21 : 15:29:22
|
| IMHO - I'd use TEXT. Snitz uses text just fine for this message board. All the stories read on SQLTeam are stored in a text field. You can use a proc like the one Rob just posted to post into the databse. Substituting and INSERT for the UPDATE works just as well. I personally think that would be a LOT easier than using a bunch of varchar fields and trying to join them together.I wrote an article on this (http://www.sqlteam.com/item.asp?ItemID=202) a long time ago. I don't know that it will be much help.I think that when you find something you specifically can't do with a TEXT field then can look at other options.Just remember to SELECT that text field as the last field in your select statement and pull it out of the record set last.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
Jay99
468 Posts |
Posted - 2002-03-21 : 15:55:56
|
| I'll be damned . . . I took BOL at face value about TEXT > 8k characters and did the whole dance with textprt, readtext and writetext . . . then on the asp side doing the getchunk, blah, blah, blah . . . If I had just said, screw it, I'm gonna just try using it like a 'regular' filed, I coulda saved myself a bunch of time . . .Lesson Learned ...Jay<O> |
|
|
|
Doug G
Constraint Violating Yak Guru
331 Posts |
Posted - 2002-03-21 : 22:39:15
|
| Make sure you test with large amounts of data. There have been problems with Snitz forums and very long posts, with people splitting their long post across multiple topics.======Doug G====== |
|
|
|
|
Forum Message Data Type