Data type enum for Text? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Data type enum for Text?

Author

Topic

settinUpShop
Starting Member

28 Posts

Posted - 2003-10-07 : 12:46:19

Hi, I'm new to the boards here, and relatively new to T-SQL as well. In going through my trusty reference chart for DataTypeEnums, I don't see anything for the "Text" datatype, which is what I need in order to create/pass a parameter to a stored procedure. What am I missing here?

Thanks for any help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-07 : 12:52:10

Here ya go:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10245[/url]

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-07 : 14:01:41

You sure you want text?

What's the largest length of your data?

I thought I could do this....but it dudn't work


USE Northwind
GO

CREATE PROC myPROC
	@y text
AS
SELECT @y
GO

DECLARE @x varchar(8000)
SELECT @x = REPLICATE('x',8000)

EXEC myProc @x+@x+@x
GO

DROP PROC myProc
GO

Anyway to get text data in to a sproc?

Tara's history lesson thread is a good read...

Only they discuss using tables...

I still think you're not interested in text though...

Do you know what books online is?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

settinUpShop
Starting Member

28 Posts

Posted - 2003-10-07 : 14:07:15

Thanks for the link Tara. Interesting discussion on what I had hoped to be a more straight forward solution.

I have another idea that might work in my situation. It isn't mandetory that I use a stored procedure to update the Text field. What if I insert a new record into the table with a stored procedure as I had planned, but exclude the Text datatype field in the insert, which I've set to accept null values. After my stored procedure finishes, then i could pass a string holding the value that will go into the text datatype, into use a basic sql query in ASP and update the field in the database that way?

Is this a viable solution?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-07 : 14:11:39

I would not recommend this solution, but yes I think you could do it that way as well. You won't receive the benefits of stored procedures though, which is performance AND security. You will need to grant explicit table privileges now instead of just execute on the stored procedure.

Tara

settinUpShop
Starting Member

28 Posts

Posted - 2003-10-07 : 14:16:06

quote:
Originally posted by X002548

You sure you want text?

What's the largest length of your data?

I still think you're not interested in text though...

Do you know what books online is?

Am I sure? Pretty sure. that column holds page content for a content management tool I'm building. So it can contain large amounts of HTML code. realistically tho, maybe i could get away with a maxed out varchar. 8000 characters is alot, but I thought better to let people go over that limit if need be.

yes, if you're talking about SQL Server Books Online. I use it a fair amount.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-07 : 14:19:59

Why not just store the path of a file that contains your text data?

Seems to be the prefered approach....

because READTEXT/WRITETEXT, ect are a royal pain...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

settinUpShop
Starting Member

28 Posts

Posted - 2003-10-07 : 14:51:44

quote:
Originally posted by X002548

Why not just store the path of a file that contains your text data?

Seems to be the prefered approach....

because READTEXT/WRITETEXT, ect are a royal pain...

Tell me more. I'm listening. :) I can see why what you suggest would be better. You're saying create a txt file for each record? So if I have 10 pages in my content management database, then there would be 10 text files, one for each record.

Since I'm hosting my site on IIS, I could store these text files on the web server, in a directory dedicated for these types of files. Then all I'd need to have in the database column where I use to have my text datatype, is a reference to the txt file's filename, since the directory could be a fixed value. I like, I like. All of the writing, reading would be handled with the FSO object in ASP.

Any links to more information on this type of solution are apprecaited. I'll start looking for them as well. Thanks Brett.

settinUpShop
Starting Member

28 Posts

Posted - 2003-10-07 : 15:08:23

one sticky point I just thought of could be when I display the first 300 characters of Page_Content for all the pages in my "SelectPageToEdit.asp" page. I'm not sure how intensive it would be to open up what could potentially be around 100 text files or more and read the first 300 characters.

BUT this may not really be too great of an issue, as these are ADMIN pages. So there is a little more give in how long the page takes to load up.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-07 : 15:13:52

I know Rob or Graz wrote an article about this.

Go to the home page and search the articles, or stay here and search the posts...

This is a topic that come up a lot...

Text or image...both are a pain...and both work the same way...

I'll keep looking, but you should be able to find sonmething here (guarantee it)

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

settinUpShop
Starting Member

28 Posts

Posted - 2003-10-07 : 15:45:27

found this:
http://www.sqlteam.com/item.asp?ItemID=202

And now I'm starting to lean back towards just keeping all my data in the same place - that being the database. Hosting companies he says sometimes limit the length of text fields, but that's not an issue for me as this on my companies webserver.

I also like the sound of this:

quote:

In my youthful innocence I created a 55,000 character text field using an ASP page (I used TEXTAREA). I passed this using a POST to another ASP page and called a stored procedure passing my 55,000 character field as a parameter. It got passed and INSERTed just fine. I returned it back to the page using a SELECT and it displayed just fine. Took a while to scroll down the page but all the text was there.

But I thought the thread that Tara had pointed me to had already proven that such an approach does not work. A parameter of that character length WILL NOT be accepted into a stored procedure, or will it?

Ultimately I think I might just want to keep the data all in the database. What I suggested earlier is the method I think I'll use to update the database. The added security of using a stored procedure will be lost on the sql query made via ASP, BUT because the site is on an intranet, I think that's less of an issue. the improved performance of the stored procedure is not as important here since these are administation pages which will not be receiving a lot of concurrent hits.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-07 : 17:14:49

Well I read that article

Maybe graz (Bill btw) will grace us with his wisdom....

Seems like a solution...

I guess it's just images that are a pain...

I always thought text was a pain...it still is really

take for example the snippet



USE Northwind
GO

CREATE PROC myProc
@x text
AS DECLARE @y text
SELECT @x,@y
GO

CREATE PROC myProc
@x text
AS DECLARE @y varchar(8000)
SELECT @x,@y
GO

DROP PROC myProc
GO

You can pass it in, but can't define a local variable...and how then do you work with it?

It's still more an object than it is "text"

MOO

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-07 : 21:47:28

OK, sorry about this being so misleading. The article Tara linked to was concerned with manipulating a text value before or after it was passed to a procedure, and the problem with text parameters is that you CANNOT manipulate them, and you CANNOT declare them, you can only PASS them to a procedure. Like so:

CREATE PROCEDURE TextPass @txt text AS
SELECT @txt --this is OK, you're not changing anything
INSERT INTO myTable(txtColumn) VALUES(@txt) --this is also OK
DECLARE @t1 text --nope, can't declare a text variable
SET @txt='Hello!' --nope, can't change an existing text variable

You CAN pass a varchar variable in its place and it will be converted to text, BUT it cannot exceed 8,000 characters. Anything over 8,000 characters must either be constructed of multiple varchar's concatenated together, or passed as a literal. You can do the following:

DECLARE @sometext varchar(8000)
SET @sometext='This is a really long string...NOT!!!!'
EXECUTE TextPass @txt=@sometext

...or...

EXECUTE TextPass @txt=@sometext + @sometext + @sometext

...or...

EXECUTE TextPass @txt='This is a somewhat longer string than that other really lame attempt at a string. It is also a string literal, meaning that you are not assigning it to another variable and then passing that variable on to the procedure.'

Sort of like making a shape out of clay, once the clay dries out you can't change it anymore. Text variables dry out the instant you pass them to a procedure. But if all you're going to do is INSERT the unmodified text variable into a table, then they work beautifully.

And in case you were looking for the ADO DataTypeEnum for text, it's adLongVarChar (value: 201). Ntext is adLongVarWChar, #203.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-08 : 05:14:09

Bottom line is - If you do this at the front end...


MyCMd.CommandText = "up_insert_content"
MyCmd.Parameters.Append (MyCmd.CreateParameter("content_description", adLongVarChar, adParamInput , 25000, ContentDescription)) 
(Note that ContentDescription is a variable containing upto 25000 characters)

...and do this at the back end...


CREATE PROCEDURE up_insert_content 
 (@content_description TEXT)
AS
BEGIN
   INSERT INTO Content_Table (content_description)
   VALUES (@content_description)
END

...everything should work just fine.

Owais

Make it idiot proof and someone will make a better idiot

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-08 : 09:27:51

ok..

sooooooooooooo...

Does that bring us back to storing filenames and paths?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

settinUpShop
Starting Member

28 Posts

Posted - 2003-10-11 : 18:45:53

robvolk thanks for explaining this to me. The adLongVarChar is just what i was looking for. Wish they could have named it just adText or something. Anyway, I've decided to just update the text datatype column in the stored procedure. i don't need to make any modifications to the data. Just put it in there. All the editing of these fields are taken care of on a page I've created using ASP and HTML forms. So all's good. But i'm happy to understand the limitations of the text datatype after all of this.

Thanks everyone!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources