Help - Storing large text fields within SQL Server???? 
Author Message
 Help - Storing large text fields within SQL Server????

I was wondering if the following is possible with SQL Server.

I would like to create a form where the user can type their document into
the fields on the form. The data would then be stored in the SQL database.
The user in the future would be able to retreive their data for updating
purposes and the fields would populate with the data they previously
entered.

The problem I am having are the following:
1) How many characters can a field have within a SQL database? A couple of
the fields might have up to 3000 characters that are entered into a text box
on the screen. Can this large amount of information be stored in a SQL
database?

2) I would like to create a search form where the user types in keywords and
the documents which contain the words would be returned. Can the above field
be searched by keywords? The data returned from the search would be passed
to a template file which would contain the fields that are populated.

Is there an easier way to maintain amounts of data that can retrieved by a
search and placed into a certain area on a template form?

Any help would be greatly appreciated.

Thanks,
Ray Kaiser



Sat, 29 Apr 2000 03:00:00 GMT
 Help - Storing large text fields within SQL Server????

1: Use the text to store any string length. (up to 2 GIG of text per row if
my memory is right)

2:As for search, you could do
select ... where textColumn Like '%MyString%',
but that would be rather slow.
I have implemented an up to 3 Keyword + Author name Search on a 255 Char
field that is really quick.
13000 records on table, and the recordset returns in usually less than a
second.
The trick is to have a 'Dictonary' table where
every single word appears once only (Unique index on word) as long as it
appears in your text field.
Then you need a X-ref table between the word table and the table that
contains the text Field.
I then use Stored procure that has take 4 parameters (3 word + Author
name). The SP select the IDs of Text Records into
1,2,3, or 4 temp table if the parameters depending on a Keyword being null
or not. The idea is to use the the Index on the word table
knowing that only a few Record ids will be returned into each tempo table.
Then the SP links the temp table and returns the record set.
Add to this that every time a text is changed it needs to be reindexed (Add
delete words into the word table & update the X-Ref table).

If you want some code sample, Just ask.

--
Frdric.




Quote:
> I was wondering if the following is possible with SQL Server.

> I would like to create a form where the user can type their document into
> the fields on the form. The data would then be stored in the SQL
database.
> The user in the future would be able to retreive their data for updating
> purposes and the fields would populate with the data they previously
> entered.

> The problem I am having are the following:
> 1) How many characters can a field have within a SQL database? A couple
of
> the fields might have up to 3000 characters that are entered into a text
box
> on the screen. Can this large amount of information be stored in a SQL
> database?

> 2) I would like to create a search form where the user types in keywords
and
> the documents which contain the words would be returned. Can the above
field
> be searched by keywords? The data returned from the search would be
passed
> to a template file which would contain the fields that are populated.

> Is there an easier way to maintain amounts of data that can retrieved by
a
> search and placed into a certain area on a template form?

> Any help would be greatly appreciated.

> Thanks,
> Ray Kaiser



Sun, 30 Apr 2000 03:00:00 GMT
 Help - Storing large text fields within SQL Server????

Ray,

You can use a field of type text

text
Is a variable-length datatype that can hold as many as 2 (31)  - 1
(2,147,483,647) characters. The text definitions do not include a length,
and text values must be surrounded by single quotation marks in an INSERT
statement. You can use the LIKE keyword and wildcard characters with text
data.
You can explicitly convert text data to char or varchar with the CONVERT
function, but the limit is the maximum length of these datatypes, 255
bytes.

What I usually do is hold it in blocks of char (255) and split the text
within the code.  This would make it much more efficient in SQL Server,
hold on to valuable space and makes it kmuch easier to search.  My old
table structure would have been

create table customer_Notes (
CustID int,
Notes text
)

would now be

create table customer_Notes (
CustID int,
Statementid int
Notes char(255)
)

--
I hope this was of use.

Steve Robinson



Sun, 30 Apr 2000 03:00:00 GMT
 Help - Storing large text fields within SQL Server????

Ray,

You can use a field of type text

text
Is a variable-length datatype that can hold as many as 2 (31)  - 1
(2,147,483,647) characters. The text definitions do not include a length,
and text values must be surrounded by single quotation marks in an INSERT
statement. You can use the LIKE keyword and wildcard characters with text
data.
You can explicitly convert text data to char or varchar with the CONVERT
function, but the limit is the maximum length of these datatypes, 255
bytes.

What I usually do is hold it in blocks of char (255) and split the text
within the code.  This would make it much more efficient in SQL Server,
hold on to valuable space and makes it kmuch easier to search.  My old
table structure would have been

create table customer_Notes (
CustID int,
Notes text
)

would now be

create table customer_Notes (
CustID int,
Statementid int
Notes char(255)
)

--
I hope this was of use.

Steve Robinson



Sun, 30 Apr 2000 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Need help storing large text and images into SQL Server 6.5

2. Ned help storing large text and images into SQL Server 6.5

3. Need help storing large text and images into SQL Server 6.5

4. Storing large text fields ---- urgent help required.

5. Writing Large Text field to a text File in Stored Procedure

6. Sending a large text field to SQL Server

7. Storing Large Text Docs in SQL Server

8. Using SQL Server to store large text files

9. Using SQL Server to store large text files

10. Using SQL Server to store large text files


 
Powered by phpBB® Forum Software