Among the extended SQL statements supported by OceanBase Embedded SQL in C (ECOB), the following are related to LobLocator: ALLOCATE, DESCRIBE, WRITE, READ, VAR, TYPE, and FREE.
ALLOCATE statement
The ALLOCATE statement is used to allocate a LOB locator and initialize it to null.
Syntax:
EXEC SQL ALLOCATE :lob;
Sample statement:
OCIClobLocator *clob ;
EXEC SQL ALLOCATE :clob;
DESCRIBE statement
The DESCRIBE statement is used to describe the attributes of a LOB.
Syntax:
EXEC SQL [AT [:]database] LOB DESCRIBE :src GET attribute1 [{, attributeN}]
INTO :hv1 [[INDICATOR] :hv_ind1] [{, :hvN [[INDICATOR] :hv_indN] }] ;
At present, the attribute value can only be LENGTH, indicating the byte length of a BLOB or a BFILE, or the character length of a CLOB or a NCLOB. For a BFILE, the length includes the end of file (EOF), if any. The length of an empty LOB is zero. An uninitialized LOB/BFILE has an undefined length.
Sample statement:
#include <oci.h>
...
OCIBFileLocator *bfile ;
char directory[31], filename[256] ;
short d_ind, f_ind ;
···
EXEC SQL ALLOCATE :bfile ;
EXEC SQL SELECT a_bfile INTO :bfile FROM lob_table WHERE ... ;
EXEC SQL LOB DESCRIBE :bfile
GET DIRECTORY, FILENAME INTO :directory:d_ind, :filename:f_ind ;
WRITE statement
The WRITE statement is used to write the content of a buffer to a LOB.
Syntax:
EXEC SQL [AT [:]database] LOB WRITE [APPEND] [ FIRST | NEXT | LAST | ONE ]
:amt FROM :buffer [WITH LENGTH :buflen] INTO :dst [AT :dst_offset] ;
If a LOB already contains data, the data is overwritten by the data stored in the buffer. If the specified offset exceeds the end of the data in the current LOB, the LOB is padded with zero bytes or spaces.
If the APPEND keyword is specified in the WRITE statement, data will be automatically appended to the end of the LOB. If APPEND is specified, it is assumed that the destination offset is the end of the LOB. It is wrong to specify a destination offset if the APPEND keyword is specified in the WRITE statement.
The data in the buffer can be written to the LOB at a time by using the default ONE orientation, or written to the LOB piece-wise by using the standard polling method. The FIRST keyword is used to start polling, and subsequent pieces are written to the LOB by using the NEXT keyword. The LAST keyword is used to write the last piece that terminates the write. In the piece-wise write mode, if the pieces have different sizes and come from different locations, the buffer and length may vary in each call.
After all data in the buffer is written to the LOB, if the total amount of data transferred to the database is less than the data amount specified by the amt parameter, an error is returned.
Sample statement:
EXEC SQL LOB WRITE ONE :vamt FROM :vBuffer WITH LENGTH :vBufLength INTO :tmp_Clob AT :vSrcOffset ;
EXEC SQL LOB WRITE FIRST :vamt FROM :vBuffer WITH LENGTH :vBufLength INTO :tmp_Clob AT :vSrcOffset ;
EXEC SQL LOB WRITE LAST :vamt FROM :vBuffer INTO :tmp_Clob ;
EXEC SQL LOB WRITE NEXT :vamt FROM :vBuffer INTO :tmp_Clob ;
READ statement
The READ statement is used to read all or part of the content in a LOB or BFILE to the buffer.
Syntax:
EXEC SQL [AT [:]database] LOB READ :amt FROM :src [AT :src_offset]
INTO :buffer [WITH LENGTH :buflen] ;
The BFILE must already exist on the database server and have been opened by using an input locator. The database must have the privileges to read files, and users must have the read privilege on directories.
It is wrong to read data from an uninitialized LOB or BFILE.
The length of the buffer can be determined in the following ways:
If the WITH LENGTH clause exists, the buffer length can be determined from the buflen parameter.
If the WITH LENGTH clause does not exist, the buffer length can be determined by processing the host variable of the buffer in OUT mode.
Sample statement:
OCIClobLocator *clob;
unsigned long amt = 20;
unsigned long offset = 1;
char buf[1024] = {0};
EXEC SQL ALLOCATE :clob;
EXEC SQL SELECT col1 INTO :clob FROM t1;
EXEC SQL LOB READ :amt FROM :clob AT :offset INTO :buf;
VAR statement
The VAR statement is used to perform host variable equivalence, or allocate a specific external data type to a single host variable to override the default data type. This statement contains an optional CONVBUFSZ clause that specifies the buffer size used for character set conversion.
The host variable must be declared in ECOB in advance.
Sample statement:
EXEC SQL BEGIN DECLARE SECTION;
...
char dept_name[15]; -- default datatype is CHAR
EXEC SQL VAR dept_name IS STRING; -- reset to STRING
...
char buffer[200]; -- default datatype is CHAR
EXEC SQL VAR buffer IS RAW(200); -- refer to RAW
...
EXEC SQL END DECLARE SECTION;
TYPE statement
The TYPE statement is used to perform user-defined type equivalence, or make an external data type equivalent to a user-defined data type to allocate the external data type to the entire host variable class.
Syntax:
EXEC SQL TYPE user_type IS type_name [ (length) ] [REFERENCE];
User-defined data types must be declared in ECOB in advance.
Sample statement:
struct screen
{
short len;
char buff[4000];
};
typedef struct screen graphics;
EXEC SQL TYPE graphics IS VARRAW(4000);
graphics crt; — host variable of type graphics
...
FREE statement
The FREE statement is used to release the memory reserved by the ALLOCATE statement.
Syntax:
EXEC SQL FREE :lob
Sample statement:
EXEC SQL FREE :clob
SAVEPOINT statement
The SAVEPOINT statement is used to set a transaction savepoint. If a savepoint with the same name already exists in the current transaction, the old savepoint is deleted and a new savepoint is set. After a transaction ends, all savepoints are automatically released.
Syntax:
EXEC SQL SAVEPOINT pointname;
Sample statement:
EXEC SQL SAVEPOINT saveX;
exec sql rollback to savepoint savex;
ALLOCATE statement
The ALLOCATE statement is used to allocate a cursor variable.
Syntax:
EXEC SQL ALLOCATE :sql_cursor;
When a cursor variable is allocated, the server does not need to be called during precompilation or running. If the ALLOCATE statement contains errors, such as an undeclared host variable, ECOB will return a precompilation error. The stack memory is used when a cursor variable is allocated. Therefore, you can release a cursor variable during a loop of the program.
Sample statement:
SQL_CURSOR sql_cursor;
char col2[100]={0};
EXEC SQL ALLOCATE :sql_cursor;
EXEC SQL OPEN :sql_cursor FOR SELECT col2 FROM test;
EXEC SQL FETCH :sql_cursor INTO :col2;
EXEC SQL CLOSE :sql_cursor;
EXEC SQL FREE :sql_cursor;