2. Supported Objects, SQL Statements, and Data Types#

This chapter gives a description of schema objects in remote databases that are accessible through SQL statements and database links provided by Altibase, and of data types that are available for use with database links.

Database Link and SQL Statements#

This section describes the SQL statements that are available to use with Database Link.

Altibase Database Link provides two ways to access remote schema objects. One way is to access a table or view of a remote server with the location description(@), as in the older versions, and another way is to directly send an entire SQL statement to a remote server, as in the pass-through style.

To support the pass-through style, Altibase Database Link provides the REMOTE_TABLE keyword and built-in REMOTE procedures and functions. The REMOTE_TABLE keyword or REMOTE function sends the entire SQL statement to the remote server, which directly processes the SQL statement.

REMOTE functions are divided into functions that can bind values to parameter markers in SQL statements and those that do not.

The following table shows the availability of the REMOTE_TABLE keyword and the REMOTE functions for each SQL statement type.

	REMOTE_TABLE Keyword	REMOTE_EXECUTE_IMMEDIATE	REMOTE Functions that support binding
DDL Statements	X	O	O
DCL Statements	X	O	O
DML Statements (INSERT, UPDATE, DELETE)	X	O	O
SELECT Statements	O	X	O

REMOTE_TABLE#

The REMOTE_TABLE keyword can only be used in the FROM clause of the SELECT statement input to a local server. Following the REMOTE_TABLE keyword, only the SELECT statement to be executed on the remote server can be specified. The REMOTE_TABLE keyword cannot be used with SQL statements including parameter markers; it cannot be bound.

The syntax on how to use the REMOTE_TABLE keyword in SQL statements is explained in Chapter 4: Database Link-Related SQL Statements.

REMOTE_EXECUTE_IMMEDIATE#

This is a built-in stored procedure which executes input SQL statements on the remote server through the Database Link. This procedure can execute all DML, DDL and DCL statements supported by Altibase Database Link, excluding the SELECT statement. SQL statements including parameter markers cannot be executed with this procedure; it cannot be bound.

For the syntax of this procedure, please refer to Chapter 4. Database Link-Related SQL Statements.

REMOTE Functions Supporting Binding#

This section describes the REMOTE functions that can execute an SQL statement that includes a parameter marker through a database link.

In order to execute SQL statements including parameter markers and bind values to each parameter, the following REMOTE functions can be used. These functions can only be used in stored procedures, however, and must be called in the following order:

REMOTE_ALLOC_STATEMENT
REMOTE_BIND_VARIABLE
REMOTE_EXECUTE_STATEMENT
REMOTE_NEXT_ROW
REMOTE_GET_COLUMN_VALUE_type
REMOTE_FREE_STATEMENT

Below is the order in which REMOTE functions must be called to execute SELECT statements including parameter markers in stored procedures.

REMOTE_ALLOC_STATEMENT
REMOTE_BIND_VARIABLE
REMOTE_EXECUTE_STATEMENT
REMOTE_NEXT_ROW
REMOTE_GET_COLUMN_VALUE_type
REMOTE_FREE_STATEMENT

Below is the order in which REMOTE functions must be called to execute DML and DDL statements(excluding SELECT statements) including parameter markers in stored procedures.

REMOTE_ALLOC_STATEMENT
REMOTE_BIND_VARIABLE
REMOTE_EXECUTE_STATEMENT
REMOTE_FREE_STATEMENT

For the syntax and further information on each REMOTE function, please refer to Chapter 4: Database Link-Related SQL Statements

Accessible Remote Schema Objects#

This section describes remote schema objects that are accessible through Database Link.

[Table 2-1] Remote schema objects accessible through Database Link

Remote Schema Object	Is it accessible with the location descriptor @?	Is it accessible with REMOTE functions
Table	O	O
Index	X	O
View	O	O
Stored Procedure	X	O
Sequence	X	O
Queue	X	O
Trigger	X	O
Synonym	X	O
Constraint	X	O

As the above table shows, the only objects accessible from the remote server using the location descriptor(@) with Database Link are tables and views. With REMOTE functions of pass-through style, however, almost all schema objects of the remote node can be accessed.

Note: The location description(@) is supported for compatibility with the previous version. When using the location descriptor(@), only the SELECT statement is executable on the remote server, as in the previous version.

The following section shows how to access a remote schema object using the REMOTE function and examples of SQL statements.

Tables#

The core function of database links is to provide interoperability between tables with data distributed in separate databases. With Altibase Database Link, SELECT, INSERT, UPDATE, DELETE and DDL statements can be executed on remote tables.

For a SELECT query, a remote table can be queried using the REMOTE_TABLE keyword in the FROM clause, as shown below:

SELECT * FROM REMOTE_TABLE( link1, 'select * from t1' );

To reference a remote object in the WHERE clause, a subquery should be used, as shown below:

SELECT * FROM t1 WHERE t1.c1 = ( SELECT * FROM REMOTE_TABLE( link1, 'select c2 from t2' ) );

To execute a DML or DDL statement on a remote server, the REMOTE_EXECUTE_IMMEDIATE procedure should be used, as shown below:

REMOTE_EXECUTE_IMMEDIATE( 'link1', 'insert into t1 values(1)' );
REMOTE_EXECUTE_IMMEDIATE( 'link1', 'create table t1(c1 integer primary key, c2 integer)' );

For SELECT, DML and DDL statements including parameter markers, REMOTE functions should be used in the order described in the "REMOTE functions supporting binding" section.

Below is an example of a stored procedure which executes an INSERT statement including a parameter marker with a REMOTE function:

DECLARE
statement_id
result
row_count
BEGIN
statement_id := REMOTE_ALLOC_STATEMENT( 'link1', 'insert into T1 values(?)' );
result := REMOTE_BIND_VARIABLE( 'link1', statement_id, 1, '20' );
row_count := REMOTE_EXECUTE_STATEMENT( 'link1', statement_id );
result := REMOTE_FREE_STATEMENT( 'link1', statement_id );
RETURN row_count;
END;

The following example retrieves a table on a remote server using the location descriptor(@). Use of the location descriptor is supported to maintain compatibility with older versions:

SELECT * FROM t1@link1;

Index#

Indexes can be created on the remote server using Altibase Database Link:

REMOTE_EXECUTE_IMMEDIATE( 'link1', 'create index index1 on t1 (c1 asc, c2 desc)' );

Views#

Like tables, the execution of SELECT, DML and DDL statements is also supported on views, and can be used in the same manner.

The following example retrieves a view of a remote server using a REMOTE function:

SELECT * FROM REMOTE_TABLE( link1, 'select * from v1' );

The following example creates a view on a remote server using a REMOTE function:

REMOTE_EXECUTE_IMMEDIATE( 'link1', 'create view v1 as select c1, c2, c3 from t1' );

The following example retrieves a view on a remote server using the location descriptor(@):

SELECT * FROM v1@link1;

Stored Procedures#

Stored procedures on remote servers are accessible through Altibase Database Link. In other words, a stored procedure can be created on a remote server and a procedure existing on a remote server can be called from a local server.

If a REMOTE function is used in a stored procedure on a local server, all remote objects accessible by the REMOTE function can be referenced.

The following example executes a SELECT statement which invokes a stored procedure on a remote server, using the REMOTE_TABLE keyword:

SELECT * FROM REMOTE_TABLE( link1, 'select remote_function1() from dual' );
SELECT * FROM t1 WHERE t1.c1 = ( SELECT * FROM REMOTE_TABLE( link1, 'select remote_function1() from dual' ) );

The following example creates a stored procedure on a remote server and calls it, using the REMOTE_EXECUTE_IMMEDIATE procedure:

REMOTE_EXECUTE_IMMEDIATE( 'link1',
'create or replace procedure proc1 as i integer;
begin i := 0;
    while i <> 1000
    loop
        insert into t1 values(i);
        i := i + 1;
    end loop;
end;
/' );
REMOTE_EXECUTE_IMMEDIATE( 'link1', 'execute proc1' );

Restrictions#

ROWTYPE variables and cursors dependent on a table in a remote server cannot be declared in a stored procedure in a local server.

When using the location descriptor(@), the same restrictions apply as to older versions. Stored procedures existing in remote servers are inaccessible with the use of the location descriptor(@), however, tables or views in remote servers are accessible with the use of the location descriptor(@) inside stored procedures in local servers.

Sequences#

Sequences in remote servers are accessible with Altibase Database Link.

The following example executes a SELECT statement which references a sequence on a remote server, using the REMOTE_TABLE keyword:

SELECT * FROM REMOTE_TABLE( link1, 'select seq1.currval, seq1.nextval from dual' );

The following example executes an INSERT statement which references a sequence on a remote server, using the REMOTE_EXECUTE_IMMEDIATE procedure:

REMOTE_EXECUTE_IMMEDIATE( 'link1', 'insert into t1 values(seq1.nextval)' );

The following example creates a sequence in a remote server:

REMOTE_EXECUTE_IMMEDIATE( 'link1', 'create sequence seq1 start with 1 increment by 1' );

Queues#

Queues in remote servers are accessible with Altibase Database Link.

The following example retrieves a queue in a remote server, using the REMOTE_TABLE keyword:

SELECT * FROM REMOTE_TABLE( link1, 'select message from q1' );

The following example inputs a message to a queue in a remote server, using the REMOTE_EXECUTE_IMMEDIATE procedure:

REMOTE_EXECUTE_IMMEDIATE( 'link1', 'enqueue into q1(message) values(\'test message\')' );

The following example creates a queue in a remote server:

REMOTE_EXECUTE_IMMEDIATE( 'link1', 'create queue q1(40) maxrows 1000' );

Triggers#

Triggers in remote servers are accessible with Altibase Database Link.

The following example creates a trigger in a remote server:

REMOTE_EXECUTE_IMMEDIATE( 'link1', 
'create trigger trigger1 after delete on orders
referencing old row old_row
for each row as
begin
    insert into log1 values( old_row.c1,
                             old_row.c2,
                             old_row.c3,
                             sysdate );
end;
/' );

Synonyms#

Synonyms in remote servers are accessible with Altibase Database Link.

The following example retrieves a synonym in a remote server, using the REMOTE_TABLE keyword:

SELECT * FROM REMOTE_TABLE( link1, 'select * from synonym_table' );
SELECT * FROM REMOTE_TABLE( link1, 'select synonym_name from user_synonyms' );

The following example executes a DML statement on a synonym in a remote server:

REMOTE_EXECUTE_IMMEDIATE( 'link1', 'insert into synonym_table values( seq1.nextval )' );

Constraints#

Constraints in remote servers are accessible with ATLIBASE Database Link.

The following example executes a DML statement on a constraint of a remote server:

REMOTE_EXECUTE_IMMEDIATE( 'link1', 'set constraints all differed' );

The following example executes a DDL statement which adds a constraint on a table in a remote server:

REMOTE_EXECUTE_IMMEDIATE( 'link1', 'alter table t1 add constraint const1 unique(c1)' );

Data Types Supported by Database Link#

Since Altibase Database Link uses the JDBC interface, standard data types of the JDBC v3.0 Specification are supported.

The following figure depicts how data type is converted between Altibase(a local server) and AltiLinker, and between AltiLinker and JDBC drivers for each heterogeneous database.

[Figure 2-1] Conversion of data types

The following table shows to which JDBC and standard SQL data types Altibase data types are mapped.

[Table 2-2] Data Types for Database Link

JDBC Data Type	Altibase SQL Data Type	Standard SQL Data Type	Supported	Comments
java.sql.Types.CHAR	CHAR	CHAR	O
java.sql.Types.VARCHAR	VARCHAR	VARCHAR	O
java.sql.Types.LONGVARCHAR		LONGVARCHAR	X
java.sql.Types.NCHAR	NCHAR	NCHAR	X	JDBC v4.0
java.sql.Types.NVARCHAR	NVARCHAR	NVARCHAR	X	JDBC v4.0
java.sql.Types.LONGNVARCHAR		LONGNVARCHAR	X	JDBC v4.0
java.sql.Types.NUMERIC	NUMERIC	NUMERIC	O
java.sql.Types.DECIMAL	DECIMAL	DECIMAL	O
java.sql.Types.BIT	SMALLINT	BIT	O	Altibase's BIT type differs from the standard which can write only one BIT; It is mapped to a SMALLINT type.
	BIT (bitset)		X	Altibase's BIT type saves a BIT set.
java.sql.Types.BOOLEAN	SMALLINT	BOOLEAN	O	Since Altibase does not provide BOOLEAN type, it is mapped to a SMALLINT type. If the remote server is Altibase, INSERT of data is impossible for this type.
java.sql.Types.TINYINT	SMALLINT	TINYINT	O	Altibase does not provide TINYINT type; it is mapped to SMALLINT type.
java.sql.Types.SMALLINT	SMALLINT	SMALLINT	O
java.sql.Types.INTEGER	INTEGER	INTEGER	O
java.sql.Types.BIGINT	BIGINT	BIGINT	O
java.sql.Types.REAL	REAL	REAL	O
java.sql.Types.FLOAT	FLOAT	FLOAT	O
java.sql.Types.DOUBLE	DOUBLE	DOUBLE	O
java.sql.Types.BINARY	BINARY	BINARY	X	If the remote server is Altibase, INSERT of data is impossible for this type.
java.sql.Types.VARBINARY		VARBINARY	X
java.sql.Types.LONGVARBINARY		LONGVARBINARY	X
java.sql.Types.DATE	DATE	DATE	O	If the year value is smaller than 0(B.C.), Altibase processes it as 0.
java.sql.Types.TIME	DATE	TIME	O	The minimum unit of Oracle's TIMESTAMP is nanoseconds but Altibase is microseconds; nanoseconds are converted to microseconds.
java.sql.Types.TIMESTAMP	DATE	TIMESTAMP	O	The minimum unit of Oracle's TIMESTAMP is nanoseconds but Altibase is microseconds; nanoseconds are converted to microseconds. If the year value is smaller than 0(B.C.), Altibase processes it as 0.
java.sql.Types.CLOB	CLOB	CLOB	X
java.sql.Types.NCLOB		NCLOB	X	JDBC v4.0
java.sql.Types.BLOB	BLOB	BLOB	X
java.sql.Types.ARRAY			X
java.sql.Types.DISTINCT			X
java.sql.Types.STRUCT			X
java.sql.Types.REF			X
java.sql.Types.DATALINK			X
java.sql.Types.JAVA_OBJECT			X
java.sql.Types.NUMERIC	NUMERIC	NUMERIC	O
	NIBBLE		X
	VARBIT		X
	INTERVAL		X

Administrator

Reference

Developer

Tools

External Tools