REMOTE functions Supportive of Batch

REMOTE functions Supportive of Batch#

This section will elucidate REMOTE functions and other related functions in order to facilitate Altibase database link to execute batch process . The remote functions supportive of batch can be used within stored procedures, and the following sequence is recommended when executing a query in the remote server with batch processing.

REMOTE_ALLOC_STATEMENT_BATCH
REMOTE_BIND_VARIABLE_BATCH
REMOTE_ADD_BATCH
REMOTE_EXECUTE_BATCH
REMOTE_GET_RESULT_COUNT_BATCH
REMOTE_GET_RESULT_BATCH
REMOTE_FREE_STATEMENT_BATCH

IS_ARRAY_BOUND#

This function confirms if array variables are bound or not.

Syntax#

BOOLEAN variable: = IS_ARRAY_BOUND ();

Parameters#

None

Return Value#

TRUE is returned if array variables are bound, but otherwise it FALSE is returned.

Example#

<Query> Create a stored procedure inserting values to table t1 that the remote server link1 indicates. Use the parameter binding when inserting.

iSQL> CREATE OR REPLACE PROCEDURE PROC1( A1 IN INTEGER, A2 IN INTEGER )
AS
    result INTEGER;
    i      INTEGER;
    count  INTEGER;
    sum    INTEGER := 0;
BEGIN
    PRINTLN('--- 0 ---');
    IF IS_ARRAY_BOUND() = TRUE THEN

    PRINTLN('--- 1 ---');
    /* First bound */
    IF IS_FIRST_ARRAY_BOUND() = TRUE THEN
    pkg1.stmtID := REMOTE_ALLOC_STATEMENT_BATCH( 'link1', 'insert into t1 values(?, ?)');
    PRINTLN('ALLOC BATCH');
    END IF;

    PRINTLN('--- 2 ---');
    /* Main Task */
    /* INSERT INTO T1 VALUES (A1, A2); */

    result := REMOTE_BIND_VARIABLE_BATCH( 'link1', pkg1.stmtID, 1, A1 );
    result := REMOTE_BIND_VARIABLE_BATCH( 'link1', pkg1.stmtID, 2, A2 );
    result := REMOTE_ADD_BATCH( 'link1', pkg1.stmtID );

    /* Last bound */
    IF IS_LAST_ARRAY_BOUND() = TRUE THEN
    result := REMOTE_EXECUTE_BATCH( 'link1', pkg1.stmtID );

    count := REMOTE_GET_RESULT_COUNT_BATCH( 'link1', pkg1.stmtID );
    FOR i IN 1 .. count LOOP
    result := REMOTE_GET_RESULT_BATCH( 'link1', pkg1.stmtID, i );
    sum := sum + result;
    END LOOP;
    PRINTLN('SUM is' || sum);

    result := REMOTE_FREE_STATEMENT_BATCH( 'link1', pkg1.stmtID );
    PRINTLN('FREE BATCH');
    END IF;
    PRINTLN('--- 3 ---');
    ELSE
    PRINTLN('NORMAL?');
    END IF;
END;
/

IS_FIRST_ARRAY_BOUND#

This function verifies whether array variables have been firstly bound or not.

Syntax#

BOOLEAN variable: = IS_FIRST_ARRAY_BOUND ();

Parameters#

None

Return Values#

If array variables were bound and firstly executed, TRUE is returned, but otherwise FALSE is return

Example#

Please refer to IS_ARRAY_BOUND function.

IS_LAST_ARRAY_BOUND#

This function verifies whether array variables have been lastly bound or not.

Syntax#

BOOLEAN variable: = IS_LAST_ARRAY_BOUND ();

Parameters#

None

Return Values#

The function returns TRUE if array variables have been lastly bound, but otherwise it returns FALSE.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_ADD_BATCH#

This function is used to add a SQL statement which will be executed with batch processing.

Syntax#

INTEGER REMOTE_ADD_BATCH (
    dblink_name              IN VARCHAR,
    statement_id             IN BIGINT);

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID

Return Value#

Returning negative numbers indicates failure, otherwise it indicates success of the execution.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_ALLOC_STATEMENT_BATCH#

This function is used for batch processing as well as retrieving an ID of the remote statement (Statement ID) in order to execute the database SQL statement.

Syntax#

BIGINT REMOTE_ALLOC_STATEMENT_BATCH (
    dblink_name        IN VARCHAR,
    statement_text     IN VARCHAR);

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_text	Input	VARCHAR	The SQL statement to execute on a remote server

Return Value#

If succeeded, the statement ID is returned, but if it fails, negative numbers are returned.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_BIND_VARIABLE_BATCH#

This function associates with batch processing, and it binds values to the parameter marker in the SQL statement.

Syntax#

INTEGER REMOTE_BIND_VARIABLE_BATCH (
    dblink_name              IN VARCHAR,
    statement_id             IN BIGINT,
    parameter_number         IN VARCHAR,
    value                    IN VARCHAR);

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of database link object
statement_id	Input	BIGINT	The statement ID
parameter_number	Input	VARCHAR	The parameter number which will be bound in the SQL statement. It begins with 1.
Value	Input	VARCHAR	The binding value

Return Value#

If negative numbers are returned, it indicates the execution has failed, but otherwise it indicates success of the execution.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_EXECUTE_BATCH#

This function is used to actually execute the SQL statement with batch processing in the remote server by using variables of the remote statement ID obtained by the REMOTE_ALLOC_STATEMENT_BATCH function.

Syntax#

INTEGER REMOTE_STATEMENT_BATCH (
    dblink_name              IN VARCHAR,
    statement_id             IN BIGINT );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID

Return Value#

Returning negative numbers indicates failure; otherwise, it indicates success.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_FREE_STATEMENT_BATCH#

This function frees up the executed statement through the batch processing.

Syntax#

INTEGER REMOTE_FREE_STATEMENT_BATCH (
    dblink_name              IN VARCHAR,
    statement_id             IN BIGINT );

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID

Return Value#

Returning negative numbers indicates failure, but otherwise it indicates success.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_GET_RESULT_COUNT_BATCH#

This function verifies the number of SQL statements executed by the batch process after implementing the REMOTE_EXECUTE_BATCH function.

Syntax#

INTEGER REMOTE_GET_RESULT_COUNT_BATCH (
    dblink_name              IN VARCHAR,
    statement_id             IN BIGINT);

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID

Return Value#

Returning negative numbers indicates failure; otherwise, it indicates success.

Example#

Please refer to IS_ARRAY_BOUND function.

REMOTE_GET_RESULT_BATCH#

This function confirms the result of SQL statement update executed by batch processing after executing the REMOTE_EXECUTE_BATCH.

Syntax#

INTEGER REMOTE_GET_RESULT_BATCH (
    dblink_name              IN VARCHAR,
    statement_id             IN BIGINT,
    index                    IN INTEGER);

Parameters#

Name	In/Output	Data Type	Description
dblink_name	Input	VARCHAR	The name of the database link object
statement_id	Input	BIGINT	The statement ID
index	Input	INTEGER	The sequence number (index) of SQL statements which are batch processed.

Return Value#

If a negative number is returned, it indicates failure; otherwise, execution result (row count) of a SQL statement indicated by the input parameter index is returned.

Example#

Please refer to IS_ARRAY_BOUND function.