MS SQL + huge amounts of text

L

Liquidkristal

Supreme [H]ardness
Joined
Dec 17, 2000
Messages
5,075
I need to finda way to store large amounts of text in a row, I currently have 2 columns setup as varchar(6000) but the row itself can only hold 8000kb

I have done a bit of looking around and have found that you can use the text type and get SQL to figure it out somehow..

but nowhere says how to do this from an ASP page. I've read through books online and I see that I have to run it via stored procedure and use dbwritetext

any ideas?
 
You are best of setting that text field to type TEXT as opposed to varchar. I would only suggest using varchar when you are using small text entries such as names, e-mail addresses, etc... What type of info are you storing into this text field?
 
Are you using stored procedures to send the data to the database?

If so then you need to create a parameter of type adLongVarChar if I remember rightly.

Personally, I would create a field of type nText in the database, and then use the type adLongVarWChar so your input will be UTF8. If you do this now, then you will save on some problems later!
 
You do not have to use stored procedures to insert large text columns in mysql (I've done it with text columns over 1MB in size). You can user a standard insert statement. There are also different varieties of TEXT columns in mysql. Check here http://www.mysql.com/doc/en/Column_types.html . When inserting large text fields, remember to escape all the bad characters. (" and ' come to mind, but I think there are others)

Lastly, MySQL doesn not support stored procedures yet (unless you're using 5.0 ALPHA which would be a BAD idea for a production server).
 
Originally posted by GhettoChrist
You are best of setting that text field to type TEXT as opposed to varchar. I would only suggest using varchar when you are using small text entries such as names, e-mail addresses, etc... What type of info are you storing into this text field?
Click to expand...

3 form fields that can each hold 6000 characters and the form id and a few other id's
 
Sounds to me like you need a BLOB.

However, you need to consider whether or not the data you are trying to store could be normalised in any way that would make it more sensible.
 
Originally posted by :LJ:
Sounds to me like you need a BLOB.

However, you need to consider whether or not the data you are trying to store could be normalised in any way that would make it more sensible.
Click to expand...

hmm, straming it into a file as binary probably won't work as isn't there a limit of 8K per row in MSSQL?

saying that it'd probably be a load easier to do (just stream it in as binary I guess)
 
you're definately looking at a BLOB..maybe even large blob. I think the limit for an entry is something like 1 meg...or something else extremely high which you'll never have to worry about unless you plan on archiving the internet within a single row
:D
 
Originally posted by Fox2k
you're definately looking at a BLOB..maybe even large blob. I think the limit for an entry is something like 1 meg...or something else extremely high which you'll never have to worry about unless you plan on archiving the internet within a single row
:D
Click to expand...

do you know of any good references I could use to learn how to store, retreive blobs?
 
Originally posted by Liquidkristal
do you know of any good references I could use to learn how to store, retreive blobs?
Click to expand...

Read the online MySQL docs. They are the best thing out there for learning how to use it. Get a textbook on DB theory if you want to learn about design, normalization, etc.
 
MSDN to the rescue.... :)

You probably want a TEXT field. The max size for a single entry is then one or two terabytes (depending on the exact type).

The MSDN info is here: http://msdn.microsoft.com/library/en-us/tsqlref/ts_nos-nz_0lyd.asp


If you have SQL server installed though, you have all the documentation installed locally though. In the Sql Server program group there will be one entry that has "books" in the name. If for some reason it's not there, you can just start up the query analyzer from the enterprise manager, then go to help -> T-SQL help.

Typing something like "data types" into the index would certainly come up with the same type of info as I already linked.
 
cool.

[MS] I don't suppose you have any examples of how the ntext, text and images datatypes can be used from an ASP page.

thats the bit thats got me stumped, from what I have seen a system stored proc needs to be run against the table where a pointer reference is stored, and the actual data is store elsewhere
 
While I haven't used it a whole bunch, I think for simple statements, it acts just like a regular char\nchar\varchar\nvarchar.

Thus if you were querying out an email message template, it would be something to the effect of:
SELECT Subject, Body WHERE ID = <valid index>

"Subject" might be a varchar, and "Body" might be a text type. If you were using an SqlDataReader, for instance, you'd get both via a call to the GetString method. There's plenty of ways to get at the data, but the program will probably not be able to tell that's it's getting a text type rather than a varchar, the'll act the same.

IIRC there's certain things you can't do with text entries, but if you're just retrieving the data, no problem.
 
First thing, forget everything mentioned by people who spoke about MySQL

Secondly, you need to use TEXT as your column type, this will allow you to store (more or less) unlimited amounts of text in the column.

You would retrieve the values in exactly the same way as you would retrieve any row type, i.e.

SELECT myTextValue, myIntValue, myDateValue FROM table WHERE blah = whatever.

There are a few limitations when using TEXT, but in my experience they do not tend to cause any major problems.

- Text columns cannot be index
- Text data is not stored in the same way as normal table data, this isnt exactly correct, but MS SQL stores values in TEXT columns in files seperate to the main database file.
- When you view the table containing the text in Enterprise Manager you will usually just see "<long text>" to view the contents with EM you normally need to cast the select value as a varchar
 
cool i didnt know TEXT fields could hold that much

Definately a better alternative as I think blobs store info as binary and aren't as easy to work with.
 
Originally posted by BuddhistPunk
First thing, forget everything mentioned by people who spoke about MySQL

Secondly, you need to use TEXT as your column type, this will allow you to store (more or less) unlimited amounts of text in the column.

You would retrieve the values in exactly the same way as you would retrieve any row type, i.e.

SELECT myTextValue, myIntValue, myDateValue FROM table WHERE blah = whatever.

There are a few limitations when using TEXT, but in my experience they do not tend to cause any major problems.

- Text columns cannot be index
- Text data is not stored in the same way as normal table data, this isnt exactly correct, but MS SQL stores values in TEXT columns in files seperate to the main database file.
- When you view the table containing the text in Enterprise Manager you will usually just see "<long text>" to view the contents with EM you normally need to cast the select value as a varchar
Click to expand...

thank you :)

how about inserting, updating, is that the same or different?
 
Originally posted by Liquidkristal
thank you :)

how about inserting, updating, is that the same or different?
Click to expand...

Exactly the same :

INSERT INTO myTable (textVal, intVal) VALUES ('the quick brown fox jumped over the lazy dogs', 1)

UPDATE myTable SET textVal = 'To infinity and beyond' WHERE blah = whatever.

One to to remember is that, with longer text the chances are you will introduce characters that need to be escaped (i.e. ') and these need to be handled before sending data to the db
 
Buddhistpunk, thank you :)

for providing a simple solution to my problem..

ok, for extra bonus points.. why do MS, and all the other sites that go on about storing text values, and ntext values require stored procedures to be run, table types to be altredin the master dbase, etc.

when all that is needed is normal 'first thing you learn' SQL do the complex solutions offer more, are they quicker, do they store it in a differnet fashion, etc.

(I'm just curious thats all)
 
There are a few good reasons for using stored procedures in databases :

1) Speed, In MS Sql stored procedures are "pre-compiled" which makes running them much faster than simply sending a full sql command string to the database.

2) Security, you can set your tables to only allow selects, updates etc from stored procedures this will prevent malicious users from using code injection without having to go through the hassle of validating input code side.

3) Code Simplicity, lets say you perform the same query various times in your code, but at some point in the future you want to change something (i.e. add an extra return value), with a stored procedure you can just that one procedure rather than having to hunt through all your code and change the procedure string.
 
You must log in or register to reply here.
Back
Top