Database processing method and apparatus using handle

Details Database processing method and apparatus using handle Database processing method and apparatus using handle

2001-10-11

2002-07-23

Breene, John (Department: 2177)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000, C707S793000

Reexamination Certificate

active

06424964

ABSTRACT:

BACKGROUND OF THE INVENTION
The invention relates to database processing method and apparatus and, more particularly, to database processing method and apparatus which can define a Large OBject data type (LOB type) and an Abstract Data Type (ADT type).
A database management system (hereinbelow, abbreviated to DBMS), particularly, a relational DBMS processes an inquiry expressed by a non-procedure like language. SQL is used as a database language. With respect to the database language SQL, studies regarding the standard of the next SQL (ISO, ANSI) are being progressed at present.
The LOB type is one of main functions of the next SQL. The LOB type is a type which enables the DBMS to handle data such as document, moving picture, audio data, or the like of an amount that is extremely larger than that of data which has hitherto been handled by a conventional database. In accordance with a type of data to be stored, a CLOB type for storing character data such as characters or the like and a BLOB type for storing binary data such as image, audio, or the like are prepared.
As for data such as moving picture, audio, or the like, since a size of each data is large, data of a few megabytes or gigabytes is also considered. It is difficult for the user to store or read out data of the LOB type to/from the DBMS in a lump. Therefore, an interface to divisionally write or read out data by a plurality of operations is generally used.
An example of the operation for such LOB data is shown below. An LOB locator is used in order to hold the LOB data as variables in the DBMS and to operate the LOB data by an UAP (user application). Operation examples are shown by a description by a C language. A sentence structure which begins with “EXEC SQL” buried in the C language program is a description of the DBMS operation.
EXEC SQL BEGIN DECLARE SECTION;
SQL TYPE IS BLOB_LOCATOR resume;
char buffer[1000]=“ ”;
int number;
int start;
int end;
EXEC SQL END DECLARE SECTION;
In the above description, variables defined within a range from “SQL BEGIN DECLARE SECTION” to “END DECLARE SECTION” denote a definition of the variables which are used for the DB operation. “resume” denotes the LOB locator.
EXEC SQL DECLARE cl CURSOR FOR
SELECT no, resume FROM emp_resume WHERE
format=‘ascii’;
EXEC SQL OPEN cl;
EXEC SQL FETCH cl INTO :number, :resume;
It denotes a description indicating that a retrieval for obtaining values of “no” and “resume” in the line of “format=‘ascii’” in an “emp_resume” table is executed. It is assumed that the “emp_resume” table has previously been defined. First, a cursor “cl” is defined by “DECLARE CURSOR” and the retrieving condition is designated by “SELECT”. The cursor “cl” is opened by next “OPEN” and one line adapted to the above condition is extracted by next “FETCH”. Now assuming that the “resume” column is of the LOB type, a locator value indicative of the LOB data is set to “:resume”.
EXEC SQL SET :buffer=SUBSTR(:resume, :start, :size);
It is a description indicating that a SUBSTR function is executed to the LOB data indicated by the “:resume” locator and the result is stored into a “buffer”. The SUBSTR function is a process for extracting data within a range from a “:start” position to a “:size” size of the LOB data shown by the “:resume” locator.
EXEC SQL SET :resume=“Hello !!”;
It is a description indicating that “Hello !!” is set to the LOB data shown by the “:resume” locator (namely, data is substituted into variables).
EXEC SQL INSERT into emp_resume values(:number, :resume);
It is a description indicating that an integer value of “:number” and the LOB data shown by the “:resume” locator are stored into the “emp_resume” table (namely, an insertion of data into an actual database).
As for the LOB data, the operation as mentioned above can be performed.
ADT (Abstract Data Type) is also one of the main functions of the next SQL. ADT is a data type of a user definition in which a type defined by the user can be designated when a table is defined in a manner similar to a type of a system definition. Since the ADT type is a data type formed by the user and is a data type which is not known by the DB, it is difficult to extract the ADT type data by the UAP such as a C language. For this purpose, an operating method using an ADT locator is provided. In a manner similar to the case of the LOB type, an example of the operation for the ADT type data is shown below.
EXEC SQL CREATE VALUE TYPE real_estate
(rooms
INTEGER,
size
DECIMAL(8,2),
text_description
VARCHAR(1024),
location
address,
document
doc);
It is a description indicating that a “real_estate” type of the user definition is defined.
EXEC SQL CREATE VALUE TYPE address
(street
CHAR(30),
city
CHAR(20),
state
CHAR(2),
zip
INTEGER);
EXEC SQL CREATE VALUE TYPE doc
(format
CHAR(20),
data
BLOB(10M));
It is a description indicating that an “address” type and a “doc” type are defined in a manner similar to the definition of the “real_estate” type.
EXEC SQL CREATE TABLE real_estate_info
(price
INTEGER,
owner
CHAR(40),
property
real_estate);
It is a description indicating that a “real_estate_info” table is defined. Particularly, a column of “proper” in the table is set to the “real_estate” type of the user definition.
EXEC SQL BEGIN DECLARE SECTION
SQL TYPE IS ADT_LOCATOR FOR real_estate re_locator;
SQL TYPE IS BLOB(2G) doc_file;
struct{
short
len;
char
data[1024];
}text_descr;
EXEC SQL END DECLARE SECTION;
Variables defined within a range from “SQL BEGIN DECLARE SECTION” to “END DECLARE SECTION” in the above description denote a definition of the variables which is used for the DB operation. “re_locator” indicates the ADT locator.
EXEC SQL DECLARE rel CURSOR FOR
SELECT property FROM real_estate_info WHERE price<400000 FOR UPDATE OF property;
EXEC SQL OPEN rel;
In the above description, a cursor “rel” is defined by “DECLARE CURSOR” and a retrieving condition is designated by “SELECT”. A retrieving condition is a retrieval for obtaining “property” in the line of “price<400000” of the “real estate_info” table in order to update “property”. After that, the cursor “rel” is opened by “OPEN”.
while(SQLCODE==O){
EXEC SQL FETCH rel INTO :re_locator;
EXEC SQL SET :text_descr=text_description(:re_locator);
EXEC SQL SET :doc_file=data(document(:re_locator));
EXEC SQL UPDATE real_estate_info
SET property=:re_locator WHERE CURRENT OF rel;
}
EXEC SQL CLOSE rel;
The operation which is executed in the above “while” sentence will now be sequentially explained. First, one line is extracted by the cursor “rel” by “FETCH”. It denotes that the setting of the current position and the positioning of the ADT locator (a locator value indicative of the ADT data is set to “:re_locator”) are performed. The next “SET” sentence indicates that the operation to designate “:re_locator” as an ADT locator and to extract “text_description” of a variable length character train from “property” as a “real_estate” type of the user definition is performed. The next “SET” sentence denotes that the operation to designate “:re_locator” as an ADT locator, to extract “document” as a “doc” type from “property” as a “real_estate” type of the user definition, to extract BLOB data “data” in the “doc” type data, and to transfer it to a client is executed. The next “UPDATE” indicates the execution of the operation to reflect the value of the ADT data designated by the ADT locator “re_locator” to the DB (“real_estate_info” table). After the “while” sentence, the cursor “rel” is closed by “CLOSE”.
The following operation can be executed with respect to the ADT data.
The locator has been disclosed in Don Chamberlin, “Using The New DB2”, pp. 236-241, 1996.
On the other hand, in recent years, the application of the relational DBMS has been being progressed mainly with respect to a data process of business affairs, so that a database system which can scalably cope with increases in transaction amount and database amount exceeding a progress of a performance of a CPU o

Affiliated with

Also associated with

Rating

Database processing method and apparatus using handle does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Database processing method and apparatus using handle, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Database processing method and apparatus using handle will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-2881967