| Author |
Topic |
|
jackstow
Posting Yak Master
160 Posts |
 Posted - 2002-02-06 : 05:24:31
|
| I've a question about ntext and text. How do you use them in a stored procedure? When I try to use an ntext data type in a proc i get this error -'The assignment operator operation cannot take a ntext data type as an argument' - this is the proc;CREATE PROCEDURE test_content @sd nvarchar(200) OUTPUT,@ft ntext OUTPUTASSELECT TOP 1 @sd = short_description, @ft = full_text FROM content I presume there's some special way of dealing with ntext. Also has anybody got some general thoughts on ntext. Is it 'unclean' like cursors or cool. I'm thinking of using ntext as nvarchar(4000) just isn't big enough and I don't really want to split stuff up over several columns - in fact, when I do I get errors about exceding the max rowsize. |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-02-06 : 09:25:47
|
Had this problem and solved it simplier by put an index column on a row and grabbing all entries for a specific set and then concatenating the indexed chunks together (might sound different than it looks)TABLE TextSets - TextSetID INT PRIMARY KEY - SomeRefPointID INT REFERENCES SomeTable(ID) - Index TINYINT -- ASSUMING YOU WILL NEVER HAVE A DOCUMENT BIGGER THAN 4000*255 bytes - Text NVARCHAR(4000) Whatever way you choose to code it just take the entire chunk you get and say:WHILE LENGTH OF CHUNK > 0 SMALL_CHUNK = LEFT(CHUNK, BUFFER_SIZE) CHUNK = RIGHT(CHUNK, LEN(CHUNK) - 4000) DOES TEXT SET EXIST? YES: UPDATE Value = SMALL_CHUNK NO: INSERT INTO TextSets VALUES (REFID, INDEX, SMALL_CHUNK) INDEX = INDEX + 1END WHILE// DELETE ALL REMAINING CHUNKS IF UPDATE...DELETE FROM TextSets WHERE OtherRefPointID = REFID AND Index >= INDEX and maybe to read back the code have a function like thisRECORDSET = SELECT VALUE FROM TextSets WHERE SomeRefPointID = REFID ORDER BY IndexMY_CHUNK = ""WHILE RECORDSET HAS RECORDS MY_CHUNK = MY_CHUNK + (RECORDSET COLUMN (VALUE)) MOVE NEXT RECORD IN RECORDSETEND WHILE that should be all you need to implement such a feature... its extremely easy to do, as for speed, obviously the bigger the string is the longer it will take and be VERY VERY careful doing the CONCATENATION and make sure you don't write a memory leaking function I haven't used this yet in a production environment however i have tested it with strings as large as 64KB ... it seemed to work fine and eliminated the need to use BLOB data such as TEXT... ... Onamuji |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-06 : 10:06:25
|
Take a look at this thread Jack:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10245Ilya has some code near the end that works, but take a look at the whole thing, it'll give you an appreciation for the complexities! He uses text columns here, but ntext will also work.I like Onamuji's idea and think you should go with it, because sticking with nvarchar, even if it's a ton of variables, is still easier than trying to slice, dice or otherwise modify ntext values. As that thread will demonstrate, I had some pretty aggravated opinions on MS dropping the ball on this!Honestly though, I love the idea of text/ntext columns, it's just that using the damn things is so hard. Maybe the upcoming .Net infestation implementation will make them a lot easier to use. |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-02-06 : 10:09:23
|
| Thats a neat idea Onamuji ! I'll give it a go. How do you deal with ntext if you've already got it though..Oh I see - ntext best avoided then.. pity.Edited by - jackstow on 02/06/2002 10:10:01Edited by - jackstow on 02/06/2002 10:11:20 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-06 : 10:14:29
|
| Well, like I said, take a look at the thread and try Ilya's code. You'll probably have to modify it, but it's definitely solid. If you get it working to your satisfaction, great! But if you spend 10 hours with it and you're ready to pick up a rifle and head over to Steve Ballmer's or Bills Gates' house, then go with Onamuji's idea instead. |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-02-06 : 10:24:19
|
| Well, with the gun laws here in the UK being a bit more stringent I may just have to be content with stirring my cup of tea vigorously and mumbling under my breath. Stif upper lip n all. |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-02-06 : 12:03:12
|
| One thing occurs to me about this. I can see how you get the text out using a recordset ************RECORDSET = SELECT VALUE FROM TextSets WHERE SomeRefPointID = REFID ORDER BY IndexMY_CHUNK = ""WHILE RECORDSET HAS RECORDS MY_CHUNK = MY_CHUNK + (RECORDSET COLUMN (VALUE)) MOVE NEXT RECORD IN RECORDSETEND WHILE************-- but how would I get this out as an OUTPUT parameter or assign it to a variable within a stored proc - I'm still restricted by a max of 4000 for nvarchar am I not?Jack |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-06 : 12:11:49
|
| You'd have to use multiple nvarchar(4000) variables if you need more than 4000 characters. You can't manipulate a text/ntext variable, you can only pass them into a sproc, and you can't assign a value to them either. REALLY frustrating! |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-02-06 : 12:20:41
|
| Thought: What use would such a huge string be good for other than just simple READ and WRITE to the database. I would think such a thing might good for ARTICLES and/or FORUMS and right now due to the whole 4hrs of sleep thing I can't fathom a use for a text element as an OUTPUT parameter ... nor would it make any sense... I guess if you do need to do this you can load TEXT or NTEXT into a TEXT or NTEXT variable by doing SELECT @var = TEXTPTR(TextColumn) FROM TableA BOL has some good documentation on using TEXT and NTEXT ... ... Onamuji |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-02-06 : 12:40:29
|
| I was thinking along the lines of an ASPX page calling a stored proc to get some text ie;STORED PROC*********CREATE PROCEDURE show_text @ft ntext OUTPUTASSELECT TOP 1 @ft = full_text FROM content ASPX PAGE************Dim objCmd As SQLCommandobjCmd = New SQLCommand()objCmd.Connection = ConnectionobjCmd.CommandText = "show_text"objCmd.CommandType = CommandType.StoredProcedureobjCmd.Parameters.Add(New SQLParameter("@ft", SQLDBType.NText))objCmd.Parameters("@ft").Direction = ParameterDirection.Output objCmd.Connection.Open()objCmd.ExecuteNonQuery()html_content.text = objCmd.Parameters("@ft").Value.ToString()****..but as I've found out this is impossible this way... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-06 : 12:53:18
|
| Why not SELECT the ntext value as a recordset, then put the value into an ASP variable? I'm not up on VB support for unicode, so I don't know for sure if it's possible. If you can't put it in a variable, it will still be available through rs.fields.value.Unfortunately Onamuji, TEXT_PTR() won't return the text value, and it can't be used on an text variable. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-02-06 : 13:18:15
|
| I know, it's a pointer to the beginning of the text ... and TEXTSIZE() is supposed to get the text size ... i think ... anyhow ... if you are using .NET then my solution is perfectly valid ... (the orginal one) because that's how I am thinking of using it ... just open the DataSet from the SELECT statement that gets all the chunks and orders them by Index position ... then just contatenate them together... and yes .NET supports unicode ... http://www.4guysfromrolla.com/webtech/053001-1.shtml .. give that a try ... couldn't get it to come up (shakes fist angrily at firewall admin) ... I was thinking of writing a wrapper class/function that takes a recordset and concatenates a column together ... you don't want to use OUTPUT parameters for this method ... but you could investigate how ADO.net supports the whole GetChunk() method that was in ADO 2.x ... then you could read your data properly as for using TEXT and NTEXT as output params... ... Onamuji |
|
|
|
|
to ntext or not to ntext..