storing long text

Question

what is the best way to store long texts (articles) in a database? it doesnt need to be searchable.

i want to allow ppl to read the first chapter of every book in my bookstore. dumping it into a database field makes it difficult to style paragraphs using css..

EDIT: access database

Answer 1

If it is sql server 2005 USE VARCHAR(MAX)

EDIT,

It seems he saif access,

so i would go with memo

Up to 63,999 characters. (If the Memo field is manipulated through DAO and only text and numbers [not binary data] will be stored in it, then the size of the Memo field is limited by the size of the database.)

or OLE Object (if you can)

An object (such as a Microsoft Excel spreadsheet, a Microsoft Word document, graphics, sounds, or other binary data) linked (OLE/DDE link: A connection between an OLE object and its OLE server, or between a Dynamic Data Exchange (DDE) source document and a destination document.) to or embedded (embed: To insert a copy of an OLE object from another application. The source of the object, called the OLE server, can be any application that supports object linking and embedding. Changes to an embedded object are not reflected in the original object.) in a Microsoft Access table.

Up to 1 gigabyte (limited by available disk space)

Answer 2

Use a CLOB.

Answer 3

I don't know which database you're using, but if text doesn't need to be searchable, then you can simply store the HTML formatted text (for instance, value coming from an FCKEditor or components like this). If you need also searchability, then you can store both HTML an plain text in two separated fields.

Fields can be nvarchar(MAX) if you use MS SQL Server 2008 or any equivalent datatype on other databases.

EDIT: Seems you're using Access, so go for Memo data type! If you decide to store HTML, consider to store only a generic markup (div, p) to divide your text, than later apply CSS formatting, wrapping stored text within another div specifing formatting classes for children elements.

Answer 4

Put it in a TEXT field, and put it with their <p> so you'll be able to style paragraphs.

As it doesn't need to be searchable, it won't impact your sql performance.

Answer 5

For SQL Server

TEXT / NTEXT for SQL Server 2000

VARCHAR(MAX) / NVARCHAR(MAX) for SQL Server 2005 onwards

Answer 6

you have several options:

• store it as a long single string with no formatting, which will look bland on the screen.

• store it as a long single string with embedded html and css, which will be a bad choice if you ever want to make your site have a different look/feel.

• normalize it so you have tables to store books, chapters, paragraphs, etc. you could then format and style the text as you load it into the application.

Answer 7

I would propose storing the first chapter as pdf file. This is secure and allows for good formatting. Then use a blob, clob, varchar, or text field depending on your product (see the other answers).

Or you could use images and look into something like amazone's "look inside". It would work with the same db techniques.

Alternatively you could use something like markup.

I personally do not like to put html in my database. Even if it is only for output. Too easy to put in some javascript. But maybe I'm just too cautious.

Answer 8

The main difference between long text (CLOB / TEXT / VARCHAR(MAX)) and long data (BLOB / IMAGE / VARBINARY(MAX)) is that the former is subject to character set conversions while the former is not.

If you need to make character set conversion on the database side, use CLOB and similar.

If you always want to retrieve your data as you atored it, byte-to-byte (as opposed character-to-character), use BLOB and similar.

Answer 9

I wouldn't store any of the documents in the database, but store the data in files in the file system, and the only thing that's in the database would be a pointer to the data files.

You don't give any details in your question that would suggest any need whatsoever to store the documents in the database itself.

And there are very few circumstances where it's advantageous.

Answer 10

The following applies to Jet 4.0 only, being the version of the Access Database Engine in the era Access2000 to Access2003 inclusive:

I wouldn't store any of the documents in the database, but store the data in files in the file system, and the only thing that's in the database would be a pointer to the data files.

You don't give any details in your question that would suggest any need whatsoever to store the documents in the database itself.

And there are very few circumstances where it's advantageous.

If you are using ACE, being the version of the Access Database Engine in the Access2007 era, the Attachment data type would be an option, however I don't really know how it works, I've never used it so I can't recommend it nor say whether it's better or worse for this purpose. I'm also wary of new data types in the first release of a major version of the Access Database Engine. I just remember all the issues with byte and decimal fields at the introduction of Jet 4 and don't want to commit to something that may never work properly. The Attachment type in the ACCDB format was introduced for Sharepoint compatibility, and that outside dependency is something that gives me pause. Will the ACCEDB data type change someday if Sharepoint changes the way it works? I'm not sure I'd want to take that risk.