Database processing method and apparatus using handle

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

1999-09-27

2001-12-04

Choules, Jack (Department: 2177)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000, C707S793000, C707S793000

Reexamination Certificate

active

06327585

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
13
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=‘lascii’” in an “emp
13
resume” table is executed. It is assumed that the “emp
13
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
13
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
13
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
13
estate
(rooms INTEGER,
size DECIMAL(
8
,
2
),
text
13
description VARCHAR(
1024
),
location address,
document doc);
It is a description indicating that a “real
13
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(
10
M));
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
13
estate” type.
EXEC SQL CREATE TABLE real
13
estate
13
info
(price INTEGER,
owner CHAR(
40
),
property real
13
estate);
It is a description indicating that a “real
13
estate
13
info” table is defined. Particularly, a column of “proper” in the table is set to the “real
13
estate” type of the user definition.
EXEC SQL BEGIN DECLARE SECTION
SQL TYPE IS ADT
13
LOCATOR FOR real
13
estate re
13
locator;
SQL TYPE IS BLOB(
2
G) doc
13
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
13
locator” indicates the ADT locator.
EXEC SQL DECLARE rel CURSOR FOR
SELECT property FROM real
13
estate
13
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
13
estate
13
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
13
locator”) are performed. The next “SET” sentence indicates that the operation to designate “:re
13
locator” as an ADT locator and to extract “text
13
description” of a variable length character train from “property” as a “real
13
estate” type of the user definition is performed. The next “SET” sentence denotes that the operation to designate “:re
13
locator” as an ADT locator, to extract “document” as a “doc” type from “property” as a “real
13
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
13
locator” to the DB (“real
13
estate
13
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 DB
2
”, 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

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-2582112