1. Starting JDBC#

This chapter describes the basic method of using Altibase's JDBC driver.

Installing the JDBC Driver#

Download and install the Altibase package from the Altibase Technical Support Portal.

Once the package is installed, the Altibase JDBC driver can be found in the $ALTIBASE_HOME/lib directory.

Version Compatibility#

The Altibase 7.1 JDBC driver is a Type 4 pure Java JDBC driver, which conforms to the JDBC 3.0 specification and operates normally on JDK 1.5. or higher.

Checking the JDBC Driver Version#

The versions of the installed JDBC driver and the JDK with which the driver has been compiled can be checked as below:

$ java -jar $ALTIBASE_HOME/lib/Altibase.jar
JDBC Driver Info : Altibase 7.1.0.0.0 with CMP 7.1.3 for JDBC 3.0 compiled with JDK 5

Setting the CLASSPATH#

To use Altibase JDBC, the Altibase JDBC driver must be added to the CLASSPATH environment variable.

Altibase provides both Altibase.jar file supporting logging function and Altibase_t.jar file which does not support the logging function.

ex) When using the bash shell in the Unix environment:

$ export CLASSPATH=$ALTIBASE_HOME/lib/Altibase.jar:.:$CLASSPATH

Setting the LB_LIBRARY_PATH#

When using the auto-tuning function of asynchronous prefetch, a JNI module is required and the directory where the libaltijext.so file is located must be added to the LD_LIBRARY_PATH environment variable. Even if the JNI module fails to load, functionality other than auto-tuning will still work.

ex) When using the bash shell in the Unix environment:

$ export LD_LIBRARY_PATH=$ALTIBASE_HOME/lib:.:$LD_LIBRARY_PATH

Connecting to the Database#

This section describes the basic method of connecting to Altibase server by JDBC in program code.

Loading the Driver#

How to load the Driver class of the Altibase JDBC driver and locate the driver are shown below:

Register the Altibase JDBC driver with the DriverManager.
```
Class.forName("Altibase.jdbc.driver.AltibaseDriver");
```

Acquire the driver from the DriverManager with the connection URL.

String sURL = "jdbc:Altibase://localhost:20300/mydb";
Driver sDriver = DriverManager.getDriver( sURL );

Configuring Connection Information#

Multiple connection information can be passed to the driver with the Properties object. The following is a code example which sets connection attributes in the Properties object.

Properties sProps = new Properties();
sProps.put("user", "SYS");
sProps.put("password", "MANAGER");

Connecting to the Database#

The database can be connected to with the connection URL and connection information. The following is a code example which connects to the database and acquires the Connection object.

Connection sCon = sDriver.connect(sURL, sProps);

The format of the connection URL used for the argument of the connect method above is shown below:

jdbc:Altibase://server_ip:server_port/dbname

Example#

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Properties;
public class ConnectionExample
{
    public static void main(String[] args) throws Exception
    {
        String sURL = "jdbc:Altibase://localhost:20300/mydb";
        Properties sProps = new Properties();
        sProps.put("user", "SYS");
        sProps.put("password", "MANAGER");
        Class.forName("Altibase.jdbc.driver.AltibaseDriver");
        Driver sDriver = DriverManager.getDriver(sURL);
        Connection sCon = sDriver.connect(sURL, sProps);
    }
}

Compiling and Running#

The JDBC application can be compiled and ran as follows:

$ javac ConnectionExample.java
$ java ConnectionExample

Connection Information#

This section describes the connection properties that are available when connecting to Altibase, and explains how to set the connection properties.

Setting Connection Attributes#

Connection attributes necessary for database connection can be set in the Properties object or specified in the connection URL.

Using the Connection URL#

The value of the property can be specified by suffixing a "?" at the end of the connection URL and following it with the "key=value" format. Multiple properties can be input by connecting them with an "&".

The following is an example of a connection URL.

"jdbc:Altibase://localhost:20300/mydb?fetch_enough=0&time_zone=DB_TZ"

Using the Properties Object#

After the Properties object has been created and the key and value have been input, it is ready to be used as connection information.

The following example uses the Properties object:

Properties sProps = new Properties();
sProps.put("fetch_enough", "30");
sProps.put("time_zone", "DB_TZ");

...

Connection sCon = DriverManager.getConnection( sURL, sProps );

About Connection Attributes#

This section offers descriptions of the connection attributes available for use when connecting to Altibase. The following items are included in the description of each attribute:

Default value: The value used by default, if no other value is specified
Range: The value available for specification
Mandatory: Whether or not the attribute must be specified
Setting range: Whether the attribute which is set affects the whole system or only affects the given session.
Description

alternateservers#

Default Value
Range	[ host_name:port_number[/dbname] [, host_name:port_number[/dbname] ]*
Mandatory	No
Setting Range
Description	The list of servers available for connection in the event of a Connection Failover. For instructions on its use, please refer to "JDBC and Failover" in Chapter 3.

app_info#

Default Value
Range	A random string
Mandatory	No
Setting Range	The session
Description	Specifies the string to be stored in the CLIENT_APP_INFO column in V$SESSION.

auto_commit#

Default Value	true
Range	[true \| false]
Mandatory	No
Setting Range	The session
Description	Sets whether or not to automatically commit the transaction when the execution of a statement is complete.

defer_prepares#

Default Value	off
Range	[on \| off]
Mandatory	No
Setting Range	The session
Description	This can specify whether or not to hold the communication with server when PrepareStatement is called. If only one connection is shared by multiple threads, it will not work properly. If this attribute is set to ON, prepare request is not sent to the server until the Execute function is called even if the PrepareStatement is called. However, the prepare request is immediately sent to the server if the following methods listed below are called after PrepareStatement (). getMetData getParameterMetaData setObject(int, Object, int) In addition, the statement pool option should be turned off if the deferred option is turned on because a conflict may occur when the statement pool of the DBCP is enabled.

ciphersuite_list#

Default Value	Please refer to the list of supported cipher suites for JRE.
Range	Random string
Mandatory	No
Setting Range	N/A
Description	This is a list of available ciphers for communication through SSL.

clientside_auto_commit#

Default Value	off
Range	[on \| off]
Mandatory	No
Setting Range	The session
Description	Sets whether autocommit operations are to be controlled by the Altibase server or the JDBC driver. The value set for this attribute is applied only if the auto_commit attribute is set to true or omitted. on: JDBC driver controls autocommit. off: Altibase server controls autocommit. For more detailed information, please refer to "Controlling Autocommit" in Chapter 3.

connectionretrycount#

Default Value	0
Range	A numerical value within the Unsigned Integer range [1 - 231]
Mandatory	No
Setting Range
Description	Specifies the number of times reconnection is attempted to another server in the event of a Connection Failover. If this value is 1, reconnection to another server is attempted once; therefore, connection is attempted two times in total. For instructions on its use, please refer to "JDBC and Failover" in Chapter 3..

connectionretrydelay#

Default Value	0
Range	A numerical value within the Unsigned Integer range [1 - 2^31]
Mandatory	No
Setting Range
Description	Specifies the waiting time during which connection is attempted to another server when the ConnectionRetryCount is set in the event of a Connection Failover. The unit is seconds. For instructions on its use, please refer to "JDBC and Failover" in Chapter 3.

database#

Default Value	mydb
Range	The database name
Mandatory	No
Setting Range	N/A
Description	The name of the database created in the Altibase server to be connected

datasource#

Default Value
Range	[0 - 65535]
Mandatory	Mandatory to use the Datasource
Setting Range	N/A
Description	The name of the Datasource

date_format#

Default Value	Sets the value of the ALTIBASE_DATE_FORMAT environment variable. On omission, the in/output format of the DATE type takes the value set for the server.
Range	A DATE format string
Mandatory	No
Setting Range	The system
Description	Sets the in/output format of the DATE type. If the format is set and a data of another format is input by the client, instead of treating it as a DATE type, an error is returned.

ddl_timeout#

Default Value	0
Range	A numerical value within the Unsigned Integer range
Mandatory	No
Setting Range	The session
Description	Sets the time limit for the execution of DDL statements. Queries which exceed the execution time limit are automatically terminated. The unit is seconds. The value 0 indicates infinity.

description#

Default Value
Range	A random string
Mandatory	No
Setting Range	N/A
Description	The description part of the Datasource

fetch_async#

Default Value	Off
Range	[off \| preferred ]
Mandatory	No
Setting Range
Description	Improves fetch performance by performing prefetch asynchronously. off: Perform a prefetch synchronously (default) preferred: Perform a prefetch asynchronously. Asynchronous prefetch can be set for each statement, but only one statement per connection is performed asynchronously.

fetch_auto_tuning#

Default Value	on (for Linux) off (otherwise)
Range	[ on \| off ]
Mandatory	No
Setting Range
Description	When prefetching is performed asynchronously, specify whether to auto-tuning according to the network status. Auto-tuning is a function that automatically adjusts the number of prefetch rows according to network conditions. This feature is only available on Linux. off: Do not use auto-tuning. (Default on non-Linux OS) on: Enable auto-tuning. (Default on Linux)

fetch_enough#

Default Value	0
Range	[0 - 2147483647]
Mandatory	No
Setting Range	The session
Description	Sets the FetchSize of the current session. This indicates the number of rows to be retrieved per FETCH from the server. If this value is 0, the JDBC driver fetches the maximum size of data that can be contained in one network packet from the server.

fetch_timeout#

Default Value	60
Range	A numerical value within the Unsigned Integer range
Mandatory	No
Setting Range	The session
Description	Sets the time limit for the execution of SELECT statements. Queries which exceed the execution time limit are automatically terminated. The unit is seconds. The value 0 indicates infinity.

idle_timeout#

Default Value	0
Range	A numerical value within the Unsigned Integer range
Mandatory	No
Setting Range	The session
Description	Sets the time limit during which the Connection stays connected without performing any operations. The connection is automatically released when the time is up. The unit is seconds. The value 0 indicates infinity.

isolation_level#

Default Value
Range	[2 \| 4 \| 8]
Mandatory	No
Setting Range	The system
Description	2 : TRANSACTION_READ_COMMITTED 4 : TRANSACTION_REPEATABLE_READ 8: TRANSACTION_SERIALIZABLE

keystore_password#

Default Value	N/A
Range	Random string
Mandatory	No
Setting Range	N/A
Description	Specifies the password for keystone_url.

keystore_type#

Default Value	JKS
Range	[ JKS, JCEKS, PKCS12, etc]
Mandatory	No
Setting Range	N/A
Description	Specifies the keystore type for keystore_url.

keystore_url#

Default Value	N/A
Range	Random string
Mandatory	No
Setting Range	N/A
Description	Specifies the path to the keystore (a container for its own private key and the certificates with their corresponding public keys).

lob_cache_threshold#

Default Value	8192
Range	[0 - 524288]
Mandatory	No
Setting Range	The session
Description	Sets the maximum size of the LOB data to be cached on the client.

lob_null_select#

Default value	off
Range	[on \| off ]
Mandatory	No
Setting range	Session
Description	Whether ResultSet.getBlob(), ResultSet.getClob() returns an object when the lob column value is null. off: Return null on: Return LOB object

login_timeout#

Default Value
Range	A numerical value within the Unsigned Integer range
Mandatory	No
Setting Range	The session
Description	Sets the maximum waiting time for logging in. For more detailed information, please refer to "Timeout" in Chapter 3.

max_statements_per_session#

Default Value
Range	A numerical value within the Signed Short range
Mandatory	No
Setting Range	The session
Description	Specifies the maximum number of statements available for execution in one session. The value 0 indicates infinity.

ncharliteralreplace#

Default Value	false
Range	[true \| false]
Mandatory	No
Setting Range	The session
Description	Specifies whether or not to check the existence of an NCHAR string(literal) within SQL statements on the client.

prefer_ipv6#

Default Value	false
Range	[true \| false]
Mandatory	No
Setting Range
Description	For more detailed information, please refer to "IPv6 Connectivity".

password#

Default Value
Range
Mandatory	Yes
Setting Range	N/A
Description	The password of the user ID

port#

Default Value	20300
Range	[0 - 65535]
Mandatory	No
Setting Range	N/A
Description	Specifies the port number of the server to be connected to. If ssl_enable is false, 20300 is used; otherwise, 20443 is used.

privilege#

Default Value
Range	[normal \| sysdba]
Mandatory	No
Setting Range	N/A
Description	The connection mode normal: normal mode sysdba: DBA mode

query_timeout#

Default Value	600
Range	A numerical value within the Unsigned Integer range
mandatory	No
Setting Range	The session
Description	Sets the time limit for query execution. Queries which exceed the execution time limit are automatically terminated. The unit is seconds The value 0 indicates infinity.

remove_redundant_transmission#

Default Value	0
Range	[0\|1]
Mandatory	No
Setting Rage	The session
Description	Sets whether or not to use the duplicate data compression method for CHAR, VARCHAR, NCHAR, NVARCHAR type strings.

response_timeout#

Default Value
Range	A numerical value within the Unsigned Integer range
Mandatory	No
Setting Range	The session
Description	Sets the maximum waiting time for a response. For more detailed information, please refer to "Timeout" in Chapter 3.

reuse_resultset#

Defualt Value	true
Range	[true \| false]
Mandatory	No
Setting Range	The session
Description	Specifies whether to reuse the ResultSet object when multiple ResultSet objects were created by executeQuery() method on the same PreparedStatement object. When it is set to true, it reuses the ResultSet object. When close() method is used on the first newly created ResultSet object, the resource is released and an error occurs when another ResultSet object is used. To prevent this, the next ResultSet object has to be created after the resource of the first ResultSet object is released. When it is set to false, ResultSet object will not be reused.

sessionfailover#

Default Value	off
Range	[on \| off]
Mandatory	No
Setting Range
Description	Sets whether or not to use STF (Session Time Failover). For instructions on its use, please refer to "JDBC and Failover" in Chapter 3.

server#

Default Value	localhost
Range	Please refer to "IPv6 Connectivity" in Chapter 2. Basic Functions of this manual.
Mandatory	Yes
Setting Range	N/A
Description	The IP address or host name of the Altibase server to be connected

socket_immediate_close#

Default Value	false
Range	[ true \| false ]
Mandatory	No
Setting Range	The session
Description	Enable or disable the TCP socket option SO_LINGER. - true: Sets the SO_LINGER value to 0. The connection is terminated immediately upon closing the socket, and any remaining data is not sent. - false: Disables SO_LINGER. The socket closes immediately, but if any data is left in the socket buffer, the kernel will attempt to send it for a certain period. This connection attribute is supported by Altibase JDBC driver version 7.3.0.0.7 and above.

sock_rcvbuf_block_ratio#

Default Value	0
Range	[ 0 - 216 ]
Mandatory	No
Setting Range
Definition	Sets the size of the socket receive buffer in 32K increments. If the value of this property is set to 2, the socket receive buffer is 64K in size. If the value of this attribute is not set, refer to ALTIBASE_SOCK_RCVBUF_BLOCK_RATIO environment variable to set the value. If the maximum socket receive buffer size among the TCP kernel parameters is set to less than the socket receive buffer size set by this property value, this property may be ignored or an error may be generated depending on the OS. (In case of Linux OS, it corresponds to 'net.core.rmem_max' TCP kernel parameter)

ssl_enable#

Default Value	false
Range	[true \| false ]
Mandatory	No
Setting Range	The session
Description	Specifies whether or not to connect to the database over SSL connection. For more detailed information, please refer to the Altibase SSL/TLS User's Guide.

time_zone#

Default Value	DB_TZ (The timezone set in the database is used)
Range
Mandatory	No
Setting Rage	The session
Description	Sets the time zone. For more detailed information, please refer to the TIME_ZONE property in General Reference.

truststore_password#

Default Value	N/A
Range	Random String
Mandatory	No
Setting Range	N/A
Description	Specifies the password for truststore_url.

truststore_type#

Default Value	JKS
Range	[ JKS, JCEKS, PKCS12 외]
Mandatory	No
Setting Range	N/A
Description	Specifies the truststore type for truststore_url.

truststore_url#

Default Value	N/A
Range	Random string
Mandatory	No
Setting Range	N/A
Description	Specifies the path to the truststore (a keystore containing certificates that belong to the communication partners).

user#

Default Value
Range
Mandatory	Yes
Setting Range	N/A
Description	The user ID of the database to be connected

utrans_timeout#

Default Value	3600
Range	A numerical value within the Unsigned Integer range
Mandatory	No
Setting Range	The session
Description	Sets the time limit for the execution of UPDATE statements. Queries which exceed the execution time limit are automatically terminated. The unit is seconds. The value 0 indicates infinity.

verify_server_certificate#

Default Value	true
Range	[true \| false ]
Mandatory	No
Setting Range	N/A
Description	Specifies whether or not to authenticate the server's CA certificate. If this value is false, the client application will not authenticate the server's CA certificate.

getprocedures_return_functions#

Default Value	true
Range	[true \| false ]
Mandatory	No
Setting Range	N/A
Description	Specifies whether to include stored procedures in the result DatabaseMetaData.getProcedures() and DatabaseMetaData.getProcedureColumns() methods return. When this value is set to true, it includes stored procedures. When this value is set to false, stored procedures are not included. Hence, DatabaseMetaData.getFunctions() and DatabaseMetaData.getFunctionColumns() have to be used separately to retrieve the stored procedures information.

getcolumns_return_jdbctype#

Default Value	false
Range	[true \| false ]
Mandatory	No
Setting Range	N/A
Description	Specifies the value of DATA_TYPE among the result of DatabaseMetaData.getColumns method. When the value is set to true, it is returned in SQL data type of java.sql.Type specified in JDBC API. When it is set to false, it is returned in data type that is specified in V$DATATYPE.

batch_setbytes_use_lob#

Default Value	true
Range	[true \| false ]
Mandatory	No
Setting Range	N/A
Description	Specifies whether to process in binary type or BLOB type when PreparedStatement.setBytes() is executed by executeBatch() for the BLOB type columns. If the value is set to true, it is processed in BLOB type. In case BLOB data exceed 65,534 bytes which is the maximum size binary data can process, this value has to be set to true unless java.lang.ClassCastException error occurs. When it is set to false, it is processed in binary type.

Using Statement and ResultSet#

This section offers basic instructions through program codes on how to connect to the Altibase server and execute SQL statements with JDBC. For convenience, instructions on how to handle exceptions are omitted.

Example#

import java.util.Properties;
import java.sql.*;

//...

String sURL      = "jdbc:Altibase://localhost:20300/mydb";
String sUser     = "SYS";
String sPassword = "MANAGER";

Connection sCon = null;

//Set properties for Connection    
Properties sProps = new Properties();
sProps.put( "user",     sUser);
sProps.put( "password", sPassword);

// Load class to register Driver into DriverManager   
Class.forName("Altibase.jdbc.driver.AltibaseDriver");

// Create a Connection Object
sCon = DriverManager.getConnection( sURL, sProps );

// Create a Statement Object
Statement sStmt = sCon.createStatement();

// Execute DDL Type Query
sStmt.execute("CREATE TABLE TEST ( C1 VARCHAR (10) )");

// Execute Inserting Query
sStmt.execute("INSERT INTO TEST VALUES ('ABCDE')");

// Execute Selecting Query
// Get Result Set from the Statement Object
ResultSet sRs = sStmt.executeQuery("SELECT * FROM TEST");

// Get ResultSetMetaData Object
ResultSetMetaData sRsMd = sRs.getMetaData();

// Retrieve ResultSet
while(sRs.next())
{
    for(int i=1; i<=sRsMd.getColumnCount(); i++)
    {
        // Get Actual Data and Printout
        System.out.println(sRs.getObject(i));
    }
}

// Eliminate ResultSet Resource
sRs.close();

// Execute Updating Query
sStmt.execute("UPDATE TEST SET C1 = 'abcde'");

// Execute Selecting Query
// Get Result Set from the Statement Object
sRs = sStmt.executeQuery("SELECT * FROM TEST");

// Get ResultSetMetaData Object
sRsMd = sRs.getMetaData();

// Retrieve ResultSet
while(sRs.next())
{
    for(int i=1; i<=sRsMd.getColumnCount(); i++)
    {
        // Get Actual Data and Printout
        System.out.println(sRs.getObject(i));
    }
}

// Eliminate ResultSet Resource
sRs.close();

// Execute Deleting Query
sStmt.execute("DELETE FROM TEST");

// Execute Selecting Query
// Get Result Set from the Statement Object
sRs = sStmt.executeQuery("SELECT * FROM TEST");

// Get ResultSetMetaData Object
sRsMd = sRs.getMetaData();

// Retrieve ResultSet
while(sRs.next())
{
    for(int i=1; i<=sRsMd.getColumnCount(); i++)
    {
        // Get Actual Data and Printout
        System.out.println(sRs.getObject(i));
    }
}

// Eliminate Resources
sRs.close();
sStmt.close();

JDBC Connection Failover#

Due to the termination of one server in an environment where multiple Altibase servers are running, network failure or etc., the service of an application implemented with the Altibase JDBC driver can be compromised. In the event of such a failure, the client which connected to the server on which the failure occurred detects the situation and automatically connects to another server and processes the statements that were being executed; this process is called a Fail-Over. For instructions on how to use the Fail-Over feature in JDBC applications, please refer to the Chapter 4 of Replication Manual.