Access the RTF document for Oracle in Delphi (Author: Su Yong)

Relational databases provide storage and extraction of large documents. For large documents such as video, audio, and image, fields need to be opened for storing abstract information. Therefore, large fields are not accessed during query and retrieval, the operation of large fields is only performed during storage and extraction. For example, you cannot query the like prefix for the long raw type in Oracle, or use the equal sign "=" for query. This is inconvenient for storing large text segments (with a capacity of more than 2 K) and full-text retrieval. This article describes how to use the string data type of a database to access and query large text segments. Here we take the Oracle database and Delphi application as an example to introduce how to access the RTF document in the database.

For plain text, you can simply split it into several strings and store them in the varchar (2000) field. You can use like for full-text search. To retain paragraph information such as line breaks, you should also save the line breaks (#13 #10) as part of the string. The memo control (not dbmemo) can be provided for data input, and each row is connected sequentially. When the connected string is nearly 2000 characters (single-byte characters) in length, store a record and repeat the preceding operations on the remaining rows. In this way, the plain text is saved into several varchar (2000) fields with a length not greater than 2000. Here, we need to open up fields to store text numbers and subnumbers, so as to distinguish different texts and connect all substrings sequentially when reading texts. When querying plain text, like querying common varchar fields, you can use like or equal sign "=" (almost unnecessary ). Note that the keywords provided by the user may be stored in different substrings and cannot be queried. Therefore, duplicate strings should be stored during design. For example, only the first 1900 characters in each substring are valid characters, and the last 100 characters are used to store the first 100 characters of the next substring. This avoids the case where keywords are separated. The only drawback is that the length of the query keyword entered by the user cannot exceed 100 characters (50 Chinese characters). However, this is normal and cannot be considered insufficient.

In fact, you can also use this technique to access and query RTF documents. In this case, the RichEdit control (not dbrichedit) is used to input and display the RTF document, instead of the memo control. The access to RTF documents cannot pass the attributes of memo like accessing plain text. strings [Index] operations (although the RichEdit control has the same attributes), because this operation cannot save the document format. You need to use two methods of RichEdit: savetofile and loadfromfile. You need to know that in the RTF file, the font size, font size, text, and other formats and content information are described with pure characters. Therefore, operations can be performed as plain text during storage and extraction. However, for queries, you cannot directly use the like keyword. Because every Chinese Character in the RTF document is stored in a special representation, only single-byte characters are stored as they are. Therefore, keywords must be processed during query before they can be used in query statements.

Before testing this example, you must have the following data structure:

(* Create Table Test ({table name test} docid number not null, {document number} docname varchar (40) not null, {document title} subid number not null, {document sub-number} text varchar (2000) not null, {sub-document content} primary key (docid, subid); {Union primary key }*) the following is the main part of the program instance :{......} const bufsize = 2000; {maximum capacity of the string} type tbuffer = array [1 .. bufsize] of char; {String cache} tfileofchar = file of char; {character type file} tchnchar = string [2]; {Chinese character type} {SQL query Query, return the value of the first field in the first record} function selectsql (S: string): variant; begin result: = NULL; with tadoquery. create (Application) Do try connection: = fmain. adoconnection1; SQL. append (s); SQL. savetofile ('C:/a.txt '); open; Result: = Fields [0]. asvariant; finally free; end; {the following function saves the RTF document to the database} function rtftodb (arichedit: trichedit; {document container} docname: string; {document title} atable: tadotable {operation table}): Boolean; {return type} cons T tmpfilename = 'C:/X. RTF '; {temporary document} var docid, subid, L: integer; {local variable} s: string; {string} f: tfileofchar; {character file} Buf: tbuffer; {text cache} begin arichedit. lines. savetofile (tmpfilename); {First saved file} assignfile (F, tmpfilename); {open file} reset (f); try docid: = {generate new document number} selectsql ('select nvl (max (docid) + 1,101) from test'); with atable do if not active then active: = true; {open confirmation table} subid: = 0; {initialize Sub-ID} while not EOF (f) Do begin Inc (subid); blockread (F, Buf, bufsize, L); {read two thousand characters} s: = Buf; setlength (S, L); {count of actually read bytes} with atable do begin {Add a document} append; fieldbyname ('docid '). asinteger: = docid; fieldbyname ('cname '). asstring: = docname; fieldbyname ('subid '). asinteger: = subid; fieldbyname ('text '). asstring: = s; post; end; Result: = true; {storage successful} stored t result: = false; {storage loss Failed} end; closefile (f); {close file} deletefile (tmpfilename); {delete file} end; {the following function reads the RTF document from the database, and in the specified container, display} function rtffromdb (arichedit: trichedit; {RTF document container} docname: string; {document title} aquery: tadoquery {operator dataset}): Boolean; {return type} const tmpfilename = 'C:/temp/X. RTF '; {temporary file} var S: string; {local string variable} f: tfileofchar; {character file} Buf: tbuffer; {String cache} I, L: integer; {local variable} begin arichedit. clear; {clear the currently displayed content Capacity} assignfile (F, tmpfilename); {associated file} Try rewrite (f); {open the file and prepare to write data read from the database} with aquery do begin active: = false; {close dataset} SQL. clear; {recreate SQL statement} SQL. append ('select subid, text from test where docname = ''' + docname + ''' order by subid'); open; {open dataset} If recordcount <> 0 then begin {confirm that the dataset is not empty} First; {move to the first record-Sub-document} repeat {read a sub-document and write the file} s: = fieldbyname ('text '). asstring; L: = length (s ); For I: = 1 to L do Buf [I]: = s [I]; blockwrite (F, Buf, L); next; until EOF; end; closefile (f); {close file} arichedit. lines. loadfromfile (tmpfilename); {install the RTF document from the file} result: = true; {read Successful} failed t {read Failed} Try closefile (f); fail t end; Result: = false; end; deletefile (tmpfilename); {Delete temporary file} end; {the following function converts a single Chinese character into a form represented in RTF.} {For example, the Chinese characters "country" are ASCII (B9) and ASCII (FA), which are in hexadecimal format ;} {In the RTF file, the "country" field occupies 8 Bytes:} {/'B9/'fa} {. Therefore, you need to convert the field before querying. The representation method contains the delimiters used for the {string: single-byte "'". Therefore, this must be considered during conversion ,} {otherwise, the correct SQL query statement} function chnchartortfcode (CH: tchnchar): string; var C1, C2: Char; O1, O2: byte; s: string; begin C1: = CH [1]; C2: = CH [2]; O1: = ord (C1); O2: = ord (C2); s: = format ('/''' % 2x', [O1]) + format ('/''' % 2x', [O2]); Result: = lowercase (s); {convert to lower case} end; {convert the keyword to the string used in like.} {Here, the method used to distinguish Chinese characters is based on encoding. {According to the dubyte encoding rules in windows, for dubyte characters} {for example, a Chinese character is composed of two bytes, and the first byte is} {boot character. The ASCII code of the Chinese character guide character is greater than 127. Therefore, you can use this feature to distinguish Chinese characters from single-byte characters.} Function makelikertfstring (strtofind: string): string; var I: integer; chnchar: tchnchar; s: string; begin S: = ''; I: = 0; while I <length (strtofind) Do begin Inc (I); If INTEGER (strtofind [I]) >=$ 80 then begin {the first byte of Chinese characters must not be less than 128} chnchar: = strtofind [I] + strtofind [I + 1]; Inc (I); s: = S + chnchartortfcode (chnchar); End else begin {single byte character} s: = S + strtofind [I]; If strtofind [I] = ''' then s: = S + strtofind [I]; {special processing of a single marker} end; end; Result: = s; end; {construct a query statement for full-text search of keywords} function makelikestring (strtofind: string): string; var S: string; begin s: = makelikertfstring (strtofind); s: = 'select distinct docname from test where text like ''% '+ S +' %''; Result: = s; end; {......}