SQL Geeks/Gurus?

[jfields 0]

Hey SQL Gurus, I have a dilemma.

I'm trying to work on a website that will store large amounts of text. The site is running ASP pages with an IIS/MS SQL backend. I was planning to use the TEXT datatype and READTEXT, but that seems to be a pain in the butt. The syntax is killing me.

Another thought was to use BLOBs as a transitional storage,
as it is an easy way for the data to come in from the
source (website), then use SQL to dump the BLOB data out
to individual files as they come in. The files would have to be
dynamically and uniquely named (w/ datetime?), with the
name being stored in the database. Then the database row
with the temporary BLOB could be dropped to release the
space. The created files could be inserted into the website on the fly as .asp includes or something.

Thoughts?

[PhreeZone 20]

I'd go for the BLOB's since if the site grows too much the TEXT datatype only gets harder to manage.

Yesterday is history
And tomorrow is a mystery

Parachutemanuals.com

[jfields 0]

The question is how?

The data comes into the database in a TEXT data field now. If I could spit it out to a .txt or .asp file immediately and store the file name, I'd be happy with that. I'm just not sure how to approach it.

I put a question into the MS SQL programming newsgroup as well, but so far no luck on feedback.

[karma 3]

Quote

The data comes into the database in a TEXT data field now. If I could spit it out to a .txt or .asp file immediately and store the file name, I'd be happy with that. I'm just not sure how to approach it.

Don't do that. That would only make the site more static. And if I read your first post right you want to use a DB to keep your site dynamic.

I would say it makes more sense to put the incoming data into the DB as a BLOB data field. Then you can just use it almost the same way as you would the TEXT field.

Hope this makes any sense.

[mattwhoo 0]

I could probably help you out a bit but i need a little better understanding of what the text is used for and were the text is coming from. I use the text field a lot mostly to large chunks of xml data in the database. Is the data coming in need to be able to be edited or is it one way only? It might be worth while just writing out text files and then possibly creating some sort of storage table with the file name and location. It really depends on what your trying to do any more information you can give would help a lot.

Matt

Providing ingenious solutions to all of your nonexistent problems! (For a fee)

[karma 3]

Also. If you want to add extra functions ones your site is done, it's easier on you if you use blobs.

For instance. Say you want to add a search function. It's much easier to search a database than it is to search trough a lot of files on your webserver.

[Auryn 0]

Why not store the text in files and use the database to point to filenames?

[karma 3]

Quote

Why not store the text in files and use the database to point to filenames?

You can do this but then you'll have a shit load a work to do when you want to add extra features like a search machine for your site .

Just trying to save the man some extra work.

[PhillyKev 0]

Have you considered XML?

[jfields 0]

Let me give some more info, since it seems like it would help...

The site will have lots of text entries, from about 1 typed page to about 10 pages. Either way, way too long for char or varchar. Once written, the text will not change. Searchability would be nice, which is why I wanted to go with the TEXT datatype originally.

I was reading through past READTEXT questions on the Microsoft SQL Programming newsgroup, and a lot of people were advocating writing the data out to separate files, with filenames and locations stored in the database. Storage would be easy and it would be easy to dish the text files back out as includes in ASP pages. But that ease seems like it would cost by losing many of the inherant advantages of keeping the information within the database.

I have yet to get the READTEXT function really working right. I can get the text into the database through a web form, but I have a hell of a time reading it back out. I followed the syntax in my SQL reference book, but no luck. I get a message that one row was affected, but no data appears in the data window. I can't use regular select statements because the size limit of the data won't let me.

Make more sense?

Thanks.

[AndyMan 7]

I can speak from authority on this one.

I've played lead technical roles on www.littletikes.com and www.rubbermaid.com, and a senior techie at www.molson.com. I'm currently working on a very high end custom solution for the home building industry.

There's absolutely NOTHING wrong, with storing text files on a reliable disk. The one trueism of database powered websites is that every DB transaction is a serious bottleneck, in terms of processing time required to establish a connection, make a request, the DB to find the data, return the data, and close the connection.

Try and remember what databases were designed to do, which is manage relatively small chunks of relational data. Your text files are not "small chunks of relational data".

Putting large text files into your DB is a waste, unless there's specific functions you're looking for in the DB to require this. One example of this kind of function would be a "copy" stored procedure, that will copy a huge set of data, and your textfiles are part of this huge set. True requirements for these functions, are rare however. It's usually much easier to manage singular objects (like your textfiles) outside of the DB in application level code.

I don't know what kind of site you're building, but the usage of these textfiles also help determine how to manage them.

If, for example, you're trying to build a site like Slashdot that manages huge amounts of text, constantly generating the same files over and over again, you're getting even a bigger hit when you put them in the DB, because when each page is built dynamically, you're ruling out any caching on behalf of the webserver. If you were to put the textfiles simply on the disk, the webserver would store frequently used files in memory.

I'd certainly avoid using BLOBS, they're a pain to use, unless you really like 'getChunk()' methods. Myself, I don't.

Searching the files when they're on the disk becomes a whole lot easier, too, especially since you're on the Microsoft platform. MS Indexing Service integrates beautifully into IIS. Putting the files on the disk and letting Indexing Service map them will give you a tremendously flexible search solution.

Given the brief synopsis you've given us, I would store the filenames in the DB, and have the files themselves simply put on a reliable somewhere hopefully RAID, and dynamically include them at runtime. It goes without saying that you'll want to programically give these files distinct filenames, perhaps based on DB formed sequence.

_Am

__

You put the fun in "funnel" - craichead.

[AndyMan 7]

Quote

I was reading through past READTEXT questions on the Microsoft SQL Programming newsgroup, and a lot of people were advocating writing the data out to separate files, with filenames and locations stored in the database.

If you haven't guessed from my other post, I strongly support this solution.

I do, however, question the wisdom of asking a bunch of skydivers to overrule IT profesionals you've found elsewhere.

_Am

__

You put the fun in "funnel" - craichead.

[f1freak 0]

Well said and so true.....
I work for a IIS SQL web hosting company....
you see to many people trying to use the database for things that it's not intended.....
I mean there is nothing wrong with storing the text in a DB but if it will be static data for a period of time you would be better off to pull the data once and store in say an XML or Txt file....

HAVE FUN...
...JUST DONT DIE

[jfields 0]

Okay. So, to use the text file approach, what next?

Let's use the following example:

Table: Testtable
Field1: TestID (INT, identity field)
Field2: junk (TEXT)

How would I write the data in the "junk" TEXT field out to a text file with a SQL command? Anything wrong with using the INT from the "TestID" field as the filename, followed by a .txt or .asp extension? If it is unique, wouldn't that work? Then after writing the file, the database could store the filename and location in a different field, then delete the row out of "Testtable".

Code, people, I'm pushing for code!

Please? Pretty please, with beer on top?

[f1freak 0]

Use the Filesystem object and create a txt file.....
and then you can modify or creat new
as needed...

HAVE FUN...
...JUST DONT DIE

[jfields 0]

Travis,

Thanks. I guess I'll have to take that advice then wait until I get to work tomorrow where my reference books are.. unless Andy chimes in with my code. One can always be lazy and hope....

[f1freak 0]

Hey man.....
Here's a good site if you need help with asp\vbscript.....
http://www.4guysfromrolla.com/
I'll PM you with samples from work tomorrow.....

HAVE FUN...
...JUST DONT DIE

[jfields 0]

I've been to that site. Also ASPin.com and a bunch of others. I got some help there on my cookie/SQL user login system. It is up and running now. Once I get the text issue hooked up, I'll be good to go to start entering the beginning data.

Once I have a few entries done, I'll open it up to some beta testers. You'll be on the invite list.

Thanks for the help!

[f1freak 0]

Any time man.....
i have been there...,.

HAVE FUN...
...JUST DONT DIE

[AndyMan 7]

Sorry dude, I haven't written a line of Microsoft code in about two years. I've gone to the dark side, so no code for you! nazi>

Your idea of using a SQL Server itentiy column as the filename, and putting ".txt" on the end sounds just fine, but I would not use the coumn that's the primary key. Have a PK, then the fileNumber. You might also want to add some meta data to this table like 'created by', 'created date', 'updated by', 'updated date', 'number of updates', etc.

How do you get your current stuff onto disk?

Just new up a FileSystem Object, then new up an ADODB connection, execute your select statement, then as you iterate through the recordset, opening a new file, writing the text, then closing the file on each iteration.

Easy. Anyways, I would concentrate your reading on the FileSystem object, and ADODB.

If you wanna be real cool about this, you write your text into an XML file, then write an XML StyleSheet to describe how to display it, then transform the two of them into beautifully formatted document. Unfortunately, instructions on how to do this are out of scope for this thread.... If you're not current on XML it's a bit confusing.


_Am

__

You put the fun in "funnel" - craichead.

[jfields 0]

Andy,

I would add additional data fields and keep track of other stuff, but that is easy. The area where I haven't dabbled is in having SQL deal with files. So I need to study up on that. I'm a SQL novice.

XML is out. While it might be a wonderful answer to the scenario, I know diddly squat about it, so it is even more impractical.

Thanks for the direction.

[f1freak 0]

A suggestion.....
Play with XML as much as possible.....
it's going to help you so much in the near future....
oh, and damn i forgot to send the sample code today.....
sorry man...

HAVE FUN...
...JUST DONT DIE