Skip to content
≡

Admin

Developer

Tools

External Tools

3. Advanced Functions#

This chapter introduces advanced functions provided by the Altibase JDBC driver and explains how to use them.

Auto-generated Keys#

Auto-generated keys are values which distinctly point to each row in a table, and are automatically generated in the database.

In Altibase, a sequence can act as auto-generated keys. This section explains how to obtain the values of auto-generated keys in JDBC.

How to Use Auto-generated Keys#

To obtain an auto-generated key, first the Statement object is executed with a method specifying the column for which auto-generated keys are to be obtained. The ResultSet of the auto-generated keys can be retrieved with the getGeneratedKeys() method.

Or, after having created the PreparedStatement object with a method specifying the column for which auto-generated keys are to be obtained and executing it, the ResultSet of the autogenerated keys can be retrieved with the getGeneratedKeys() method.

The following are Statement methods that execute SQL statements which retrieve autogenerated keys.

public boolean execute(String aSql, int aAutoGeneratedKeys) throws SQLException; 
public boolean execute(String aSql, int[] aColumnIndexes) throws SQLException; 
public boolean execute(String aSql, String[] aColumnNames) throws SQLException;

The following are Connection methods that create the PreparedStatement object which retrieve auto-generated keys.

public PreparedStatement prepareStatement(String aSql, int aAutoGeneratedKeys) throws SQLException; 
public PreparedStatement prepareStatement(String aSql, int[] aColumnIndexes) throws SQLException; 
public PreparedStatement prepareStatement(String aSql, String[] aColumnNames) throws SQLException;

After having executed a SQL statement in one of the above two ways, auto-generated keys can be obtained by a ResultSet object with the following Statement method.

public ResultSet getGeneratedKeys() throws SQLException;

Restrictions#

When obtaining auto-generated keys in Altibase, the following restrictions apply:

  • Its use is only supported for simple INSERT statements.

  • Since Altibase does not support columns with the AUTO INCREMENT property, autogenerated keys can only be obtained from a sequence.

The following is an example of a SQL statement from which auto-generated keys can be obtained.

INSERT INTO t1 (id, val) VALUES (t1_id_seq.nextval, ?);

The following is an example of a SQL statement from which auto-generated keys cannot be obtained.

SELECT * FROM t1;
EXEC p1;

If a SQL statement which does not produce auto-generated keys is executed with the generator flag (Statement.RETURN_GENERATED_KEYS), the flag is ignored and the getGeneratedKeys() method returns an empty result set.

Example#

sStmt.executeUpdate(sQstr, Statement.RETURN_GENERATED_KEYS);
ResultSet sKeys = sStmt.getGeneratedKeys();
while (sKeys.next())
{
    int sKey = sKeys.getInt(1);

    // do somethings...
}
sKeys.close();
sStmt.close();

Timeout#

This section gives an explanation of timeouts which can occur in a client session connected to the Altibase server and provides code examples to show how to set properties related to timeouts.

Login Timeout#

A login timeout occurs when a connect method of a Connection object is called and a response is not received from the server within the maximum waiting time. The maximum waiting time is set in the login_timeout property and the unit is seconds.

Code Examples#

The following are code examples which show two ways to set the login_timeout property.

  1. Create a Connection object with the Properties object to which the timeout property has been added.
   Properties sProps = new Properties();
   ...
   sProps("login_timeout", "100");
   ...
   Connection sCon = DriverManager.getConnection( sUrl, sProps );
  1. Create a Connection object with a connection URL which specifies the timeout property.
String sUrl = "jdbc:Altibase://localhost:20300/mydb?login_timeout=100";
Connection sCon = DriverManager.getConnection( sUrl );

Response Timeout#

A response timeout occurs when the maximum waiting time for a response from the Altibase server is exceeded. The maximum waiting time is set in the response_timeout property and the unit is seconds.

This value is applied to all methods which communicate with the server.

Code Examples#

The following are code examples which show different ways to set the response_timeout property

  1. Create a Connection object with a Properties object to which the timeout property has been added.
   Properties sProps = new Properties();
   ...
   sProps("response_timeout", "100");
   ...
   Connection sCon = DriverManager.getConnection( sUrl, sProps );
  1. Create a Connection object with a connection URL which specifies the timeout property.
String sUrl = "jdbc:Altibase://localhost:20300/mydb?response_timeout=100";
Connection sCon = DriverManager.getConnection( sUrl );
  1. Pass it as an argument when the application is running.
java ... -DALTIBASE_RESPONSE_TIMEOUT=100 ...
  1. Set the environment variable.
// Linux
export ALTIBASE_RESPONSE_TIMEOUT=100

DataSource#

The Altibase JDBC driver offers a way to connect to the database with a file that contains connection configurations. Datasource is the set of connection information to a database server in the configuration file.

How to set DataSource#

DataSource is set in the following format to the altibase_cli.ini file.

# comment

[ datasource_name ]
Server=localhost # comment
Port=20300
User=sys
Password=manager

Additional connection properties can be added by writing strings of the “key=value” format in lines.

The JDBC driver searches for the altibase_cli.ini file in the paths of the following order:.

  1. /altibase_cli.ini

  2. \$HOME/altibase_cli.ini

  3. \$ALTIBASE_HOME/conf/altibase_cli.ini

Connecting with DataSource#

To connect to the server with DataSource, the DSN (DataSouce Name) specified in the altibase_cli.ini file should be specified in a connection URL, instead of the IP address and port number.

The following is an example of a connection URL using a DSN.

jdbc:Altibase://datasource_name
jdbc:Altibase://datasource_name:20301
jdbc:Altibase://datasource_name:20301?sys=user&password=pwd

When specifying a DSN in a connection URL, port or other properties can be additionally specified. If a property specified in the altibase_cli.ini file is duplicately specified in a connection URL, however, the file value is ignored and the connection URL value is used.

Connection Pool#

A Connection Pool can be set and managed in the following manner.

  • Use AltibaseConnectionPoolDataSource: When using a Connection Pool in WAS, specify this class in the JDBC Connection Pool configuration of WAS. The name of this class was ABConnectionPoolDataSource for Altibase JDBC drivers of versions prior to 6.3.1.

Property information configured in AltibaseConnectionPoolDataSource is as follows.

Property Name Description
databaseName Database name
dataSourceName DataSource Name
loginTimeout Maximum wait time for database login
logWriter Log writer for DataSource
password Database password
portNumber Database port number
serverName Database server name
URL Connection string information for Altibase connections (uppercase note)
user Database user ID

Configuring WAS (Web Application Server)#

Altibase can be used with the following web application servers.

  • Tomcat 8.x

  • Code example

    Context initContext = new InitialContext();
Context envContext  = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/altihdb");
Connection conn = ds.getConnection();
// ...

  • WebLogic 12.x

  • Jeus 6.x

For more detailed information on how to configure and use the connection pool and JDBC driver on each web application, please refer to their manuals.

Tomcat 8.x#

For more detailed information on how to install and configure Apache Tomcat, please refer to http://tomcat.apache.org/tomcat-8.0-doc/index.html.

Context configuration#

Add the JNDI DataSource to the Context as below:

<Context>


 <Resource name="jdbc/altihdb" auth="Container" type="javax.sql.DataSource"
 driverClassName="Altibase.jdbc.driver.AltibaseDriver"
 url="jdbc:Altibase://localhost:20300/mydb" 
 username="SYS" password="MANAGER" 
 maxTotal="100" maxIdle="30" maxWaitMillis="10000" />

</Context>
web.xml configuration#
<!----- web.xml ----->
<resource-ref>
 <description>Altibase Datasource example</description>
 <res-ref-name>jdbc/altihdb</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
</resource-ref>
Code example#
Context initContext = new InitialContext();
Context envContext  = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/altihdb");
Connection conn = ds.getConnection();
// ...
WebLogic 12.x#

For more detailed information on how to install and configure the WebLogic server, please refer to http://docs.oracle.com/middleware/1213/wls/index.html.

You can configure the connection pool and the JDBC datasource by referring to the following links:

  • http://docs.oracle.com/middleware/1213/wls/WLACH/taskhelp/jdbc/jdbc_datasources/CreateDataSources.html
  • http://docs.oracle.com/middleware/1213/wls/WLACH/pagehelp/JDBCjdbcdatasourcesjdbcdatasourceconfigconnectionpooltitle.html

The configuration options for Altibase are as follows:

  • Database Type: Other
  • Driver Class Name: Altibase.jdbc.driver.AltibaseDriver
  • URL: jdbc:Altibase://localhost:20300/mydb
Jeus 6.x#

Set the Connection Pool by editing the element of the $JEUS_HOME/config/JeusMain.xml file.

<!------ JeusMain.xml ----->
<resource>
    <data-source>
        <database>
            <vendor>others</vendor>
            <export-name>jdbc/altihdb</export-name>
            <data-source-class-name>
                Altibase.jdbc.driver.AltibaseConnectionPoolDataSource
            </data-source-class-name>
            <data-source-type>ConnectionPoolDataSource</data-source-type>
            <auto-commit>true</auto-commit>
            <property>
                <name>PortNumber</name>
                <type>java.lang.Integer</type>
                <value>20300</value>
            </property>
            <property>
                <name>Password</name>
                <type>java.lang.String</type>
                <value>MANAGER</value>
            </property>
            <property>
                <name>ServerName</name>
                <type>java.lang.String</type>
                <value>localhost</value>
            </property>
            <property>
                <name>ConnectionAttributes</name>
                <type>java.lang.String</type>
                <value>;create=true</value>
            </property>
            <property>
                <name>DatabaseName</name>
                <type>java.lang.String</type>
                <value>mydb</value>
            </property>
            <property>
                <name>User</name>
                <type>java.lang.String</type>
                <value>SYS</value>
            </property>
        </database>
    </data-source>
</resource>

Validating Connections#

The Altibase JDBC driver supports a lightweight ping query for connection validation, which is more efficient than a regular query. It can be used in WAS (Web Application Server) with options like poolPingQuery or validationQuery.

The query patterns are as follows, and they can also be used with PreparedStatement:

/* PING */ SELECT 1
/* ping */ select 1
/*   PING  */ SELECT   1
/*   PING  */ select 1
/*   PING  */ select 1
/*   PING  */ select 1
/*   PING  */ seLECt 1
/*   ping  */ SELECT   1

Warning : The following pattens are not allowed.

/* PING */SELECT 1
/*+ ping */ select 1
/*ping  */ select 1
/* ping */ select 1 from dual
/** PING */ SELECT 1
//* PING */ SELECT 1

Multiple ResultSet#

PSM(Stored procedures and stored functions) for Altibase can return a multiple number of result sets to the client. Using an example that returns multiple result sets, this section offers instructions through a code example on how to use these result sets in JDBC applications.

The following is an example of a PSM which returns multiple result sets.

CREATE TYPESET my_type
AS
    TYPE my_cur IS REF CURSOR;
END;

CREATE PROCEDURE p1 (p1 OUT MY_TYPE.MY_CUR, p2 out MY_TYPE.MY_CUR)
AS
BEGIN
    OPEN p1 FOR 'SELECT * FROM t1';
    OPEN p1 FOR 'SELECT * FROM t2';
END;

The following is an example of a code which uses multiple result sets returned by a call to a PSM in a JDBC application.

CallableStatement sCallStmt = connection().prepareCall("{call p1()}");
sCallStmt.execute();
ResultSet sRs = null;
ResultSetMetaData sRsMd = null;


do{
    sRs = sCallStmt.getResultSet();
    sRsMd = sRs.getMetaData();

    if(sRsMd != null)
    {
        while(sRs.next())
        {
            // do something
            for(int i=1; i <= sRsMd.getColumnCount(); i++)
            {
                System.out.println(sRs.getString(i));
            }
        }
    }
}while(stmt.getMoreResults());
sCallStmt.close();

JDBC and Failover#

This section explains how to use the Failover feature in an Altibase JDBC application.

What is a Failover?#

When a failure occurs on the database server and the connection is disconnected, a Failover is a feature which enables the application to immediately establish a connection to another server to continue the execution of the previously executed operation.

Failover can operate in the following two ways:

  • CTF(Connection Time Failover)
    CTF attempts to connect to another server when an attempt to connect to the database is unsuccessful. CTF can occur when the connect method of a Connection object is called.

  • STF(Session Time Failover)
    STF connects to another server and continuously executes the user-specified operation when a connection error occurs before the result of a SQL statement is received from the server. STF can occur on the execution of all methods communicating with the server, excluding the connect method.

For more detailed information on Failover, please refer to the “Failover” chapter of Replication Manual.

How to Use Failover#

This section explains how to use the CTF and STF features in JDBC applications.

CTF#

The CTF feature can be used by adding the following properties to the Properties object.

Properties sProps = new Properties();
sProps.put("alternateservers", "(database1:20300, database2:20300)");
sProps.put("connectionretrycount", "5");
sProps.put("connectionretrydelay", "2");
sProps.put("sessionfailover", "off");

For more detailed information on each of the properties, please refer to “Connection Information” of Chapter 1.

STF#

The STF feature can be used by additionally setting "SessionFailover=on" to the properties which set the CTF feature.

In communication situations other than attempting to establish connection to the database server, the client first processes CTF and restores the connection when it detects server failure. Thereafter, the client executes the callback function registered by the user and raises a Failover Success Exception for the user to acknowledge that a Failover has occurred. If Failover fails to every server, the driver throws the Exception which originally occurred.

The following is an interface for the Failover callback function written by the user.

public interface AltibaseFailoverCallback
{
    public final static class Event
    {
        public static final int BEGIN     = 0;
        public static final int COMPLETED = 1;
        public static final int ABORT     = 2;
    }
    public final static class Result
    {
        public static final int GO   = 3;
        public static final int QUIT = 4;
    }
    int failoverCallback(Connection aConnection,
                         Object     aAppContext,
                         int        aFailoverEvent);
};

The following is a code example which shows the process of a user registering and freeing a Failover callback function.

public class  UserDefinedFailoverCallback implements AltibaseFailoverCallback
{
    ...

    public int failoverCallback(Connection aConnection,
                                Object     aAppContext,
                                int        aFailoverEvent)
    {
        // User Defined Code
        // Must return either Result.GO or Result.QUIT.
    }

    ...
}

If the Failover callback function written by the user is called by the JDBC driver, one of the Event constants included in the above AltibaseFailoverCallback interface is passed to aFailoverEvent, which is the third argument of the callback function. The meaning of each Event constant is as follows:

  • Event.BEGIN: Session Failover is started

  • Event.COMPLETED: Session Failover has succeeded

  • Event.ABORT: Session Failover has failed

The Result constants included in the AltibaseFailoverCallback interface are values which can be returned by the callback function written by the user. If values other than Result constants are returned from the callback function, Failover does not operate normally.

  • Result.GO: If this constant value is returned from the callback function, the JDBC driver continually runs the next process of STF.
  • Result.QUIT: If this constant value is returned from the callback function, the JDBC driver terminates the STF process.

The following is a code example of an object which can be used as the second argument of the Failover callback function written by the user.

public class UserDefinedAppContext
{
    // User Defined Code
}

If there is a need to use information of an application implemented by the user during the STF process, the object to be passed to the callback function while registering the Failover callback function can be specified. If this object is specified as the second argument of the registerFailoverCallback method which registers the callback function, this object is passed when the callback function is actually called. The following is an example which depicts this process in code.

// Create a user-defined callback function object.
UserDefinedFailoverCallback sCallback = new UserDefinedFailoverCallback();
// Create a user-defined application information object
UserDefinedAppContext sAppContext = new UserDefinedAppContext();

...

Connection sCon = DriverManager.getConnection(sURL, sProp);
// Register the callback function with the user-defined application object
((AltibaseConnection)sCon).registerFailoverCallback(sCallback, sAppContext);

...

// Free the callback function
((AltibaseConnection)sCon).deregisterFailoverCallback();

Code Example#

This is a code example which implements a callback function for STF.

The following is an example of a simple code which is regardless of various circumstances; therefore, it should be noted that it cannot be used as it is in user applications.

public class MyFailoverCallback implements AltibaseFailoverCallback
{
    public int failoverCallback(Connection aConnection, Object aAppContext,int aFailoverEvent)
    {
        Statement sStmt = null;
        ResultSet sRes = null;

        switch (aFailoverEvent)
        {
            // Necessary operations before starting Failover on the user application logic can be executed.
 case Event.BEGIN:
                System.out.println(“Failover Started .... “);
                break;
            // Necessary operations after completing Failover on the user application logic can be executed.
 case Event.COMPLETED:
                try
                {
                    sStmt = aConnection.createStatement();
                }
                catch( SQLException ex1 )
                {
                    try
                    {
                        sStmt.close();
                    }
                    catch( SQLException ex3 )
                    {
                    }
                    return Result.QUIT;
                }

                try
                {
                    sRes = sStmt.executeQuery("select 1 from dual");
                    while(sRes.next())
                    {
                        if(sRes.getInt(1) == 1 )
                        {
                            break;
                        }
                    }
                }
                catch ( SQLException ex2 )
                {
                    try
                    {
                        sStmt.close();
                    }
                    catch( SQLException ex3 )
                    {
                    }
                    // Terminates the Failover process.
                    return Result.QUIT;
                }
                break;
        }
        // Continues the Failover process.
        return Result.GO;
    }
}

The following is a code example which checks whether or not STF was successful. Whether STF succeeded or failed can be confirmed by checking whether ErrorCode of SQLException is identical to Validation.FAILOVER_SUCCESS. The Failover validation code is inserted inside the while loop because the operation which was previously under execution must be executed again, even if Failover succeeds.

// Must be implemented so that the operation to be executed can be re-executed..
// The while loop has been used in this case.
while (true)
{
    try
    {
        sStmt = sConn.createStatement();
        sRes = sStmt.executeQuery("SELECT C1 FROM T1");
        while (sRes.next())
        {
            System.out.println("VALUE : " + sRes.getString(1));
        }
    }
    catch (SQLException e)
    {
        // Whether or not the Failover has succeeded.
        if (e.getErrorCode() == AltibaseFailoverCallback.FailoverValidation.FAILOVER_SUCCESS)
        {
            // Since Failover has succeeded, Exception is ignored and the process is continued. continue;
        }
        System.out.println("EXCEPTION : " + e.getMessage());
    }
    break;
}

JDBC Escapes#

The JDBC specification provides the escape syntax for the JDBC application to understand vendor specific SQL for database products. The JDBC driver converts a SQL statement which includes the escape syntax to a native SQL statement for its database.

The following table is an organization of SQL statements which include the escape syntax supported by the JDBC specification and SQL statements converted by the JDBC driver for use in Altibase.

Type

SQL statements supported in the JDBC specification

SQL statements converted for use in Altibase

ESCAPE

SELECT cVARCHAR FROM t1 WHERE cVARCHAR LIKE '%a|%b%' {escape '|'}

SELECT cVARCHAR FROM t1 WHERE cVARCHAR LIKE '%a|%b%' escape '|'

FN

SELECT {fn concat('concat', 'test')} FROM dual

SELECT concat('concat', 'test') FROM dual

DTS

UPDATE t1 SET cDATE = {d  '1234-12-30'}

UPDATE t1 SET cDATE = to_date('1234-12-30', 'yyyy-MM-dd')

UPDATE t1 SET cDATE = {t  '12:34:56'}

UPDATE t1 SET cDATE = to_date('12:34:56', 'hh24:mi:ss')

UPDATE t1 SET cDATE = {ts '2010-01-23 12:23:45'}

UPDATE t1 SET cDATE = to_date('2010-01-23 12:23:45', 'yyyy-MM-dd hh24:mi:ss')

UPDATE t1 SET cDATE = {ts '2010-11-29 23:01:23.971589'}

UPDATE t1 SET cDATE = to_date('2010-11-29 23:01:23.971589', 'yyyy-MM-dd hh24:mi:ss.ff6')

CALL

{call p1()}

execute p1()

{? = call p2(?)}

execute ? := p2(?)

OJ

SELECT * FROM {oj t1 LEFT OUTER JOIN t2 ON t1.cINT = t2.cINT}

SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.cINT = t2.cINT

How to Use ResultSet#

This section describes the types of ResultSets supported by the Altibase JDBC driver and how to use them.

Creating ResultSet#

A ResultSet is created when a query statement is executed on the database, and it corresponds to the ResultSet object of JDBC.

The following methods create the ResultSet object in JDBC.

public Statement createStatement(int aResultSetType, int aResultSetConcurrency) throws SQLException;

public Statement createStatement(int aResultSetType, int aResultSetConcurrency, int aResultSetHoldability) throws SQLException;

public PreparedStatement prepareStatement(String aSql, int aResultSetType, int aResultSetConcurrency) throws SQLException;

public PreparedStatement prepareStatement(String aSql, int aResultSetType, int aResultSetConcurrency, int aResultSetHoldability) throws SQLException;

public CallableStatement prepareCall(String aSql, int aResultSetType, int aResultSetConcurrency) throws SQLException

public CallableStatement prepareCall(String aSql, int aResultSetType, int aResultSetConcurrency, int aResultSetHoldability) throws SQLExc

ResultSet Types#

The ResultSet object of JDBC manages and retains the cursor which points to the current position within the result set. The cursor of a basic ResultSet object is not updateable and only moves forward; however, a scrollable and updateable ResultSet object can be created with the use of options.

The following are ResultSet object types available for user specification.

  • TYPE_FORWARD_ONLY
    Unscrollable; the cursor can be moved only forward. Data of the ResultSet is determined at the point in time at which the cursor opens in the database server.

  • TYPE_SCROLL_INSENSITIVE
    Scrollable; the cursor can be moved forward, backwards, or moved to a specified location. Data of the ResultSet is determined at the point in time at which the cursor opens in the database server. Memory can be increased cause the result set retrieved from the server is accumulated and cached on the client side.

  • TYPE_SCROLL_SENSITIVE
    Scrollable; the cursor can be moved forward, backwards, or moved to a specified location. The ResultSet is determined at the point in time at which the cursor opens in the database server; however, data within the ResultSet is determined at the point in time at which the client retrieves or updates it. Memory can be increased cause the result set retrieved from the server is accumulated and cached on the client side.

Concurrency#

This option determines whether or not to allow updates through the ResultSet object. One of the following two constants is available for use:

  • CONCUR_READ_ONLY
    Does not allow updates; the default value.

  • CONCUR_UPDATABLE
    Allows updates with the ResultSet object.

Holdability#

This option determines whether or not to retain the ResultSet object after the transaction has been committed. One of the following two constants are available for use:

  • CLOSE_CURSORS_AT_COMMIT
    The cursor is closed when the transaction is committed.

  • HOLD_CURSORS_OVER_COMMIT
    The cursor is left open, even if the transaction is committed. If the transaction has been committed at least once after the cursor has been opened, the cursor is left open during future commit and rollback operations. If the transaction has not been committed even once since the cursor has been opened, however, the cursor is closed when the transaction is rolled back.

Notes#

  • Since the JDBC driver caches as many number of rows as the value set for FetchSize for the ResultSet object on the client, data left in the cache can be retrieved by the application, even if the cursor is closed. If you want the application to immediately detect that the cursor has been closed, set FetchSize to 1.

  • The default value of Holdability for the Altibase JDBC driver is CLOSE_CURSORS_AT_COMMIT, and is different from the default value for the JDBC specification, HOLD_CURSORS_OVER_COMMIT.

Open ResultSet objects must be closed prior to switching the autocommit mode with the setAutoCommit() method in a session where Holdability is HOLD_CURSORS_OVER_COMMIT. The following is a code example which raises an error.

sCon = getConnection();
sStmt = sCon.createStatement();
byte[] br;
byte[] bb = new byte[48];
for(byte i = 0; i < bb.length;i++) bb[i] = i;

sCon.setAutoCommit(false);

sStmt.executeUpdate("insert into Varbinary_Tab values(null)");
sCon.commit();

sPreStmt = sCon.prepareStatement("update Varbinary_Tab set VARBINARY_VAL=?");
sPreStmt.setObject(1, bb, java.sql.Types.VARBINARY);
sPreStmt.executeUpdate();

sRS = sStmt.executeQuery("Select VARBINARY_VAL from Varbinary_Tab");
sRS.next();
br = sRS.getBytes(1);

sCon.commit();
sCon.setAutoCommit(true); -> (1)

The following exception is raised at 1.

java.sql.SQLException: Several statements still open
    at Altibase.jdbc.driver.ex.Error.processServerError(Error.java:320)
    at Altibase.jdbc.driver.AltibaseConnection.setAutoCommit(AltibaseConnection.java:988)
    at HodabilityTest.testHoldability(HodabilityTest.java:46)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:616)

sRs.close() must be called before sCon.setAutoCommit(true) to not raise an exception.

  • ? : The client session must be in Non-Autocommit mode or the clientside_auto_commit connection attribute must be set to on to use a ResultSet object whose Holdability type is HOLD_CURSORS_OVER_COMMIT. If the clientside_auto_commit connection attribute is set to on, the Holdability type is automatically changed to HOLD_CURSORS_OVER_COMMIT.
Example#
Statement sUpdStmt = sConn.prepareStatement("UPDATE t1 SET val = ? WHERE id = ?");
Statement sSelStmt = sConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
ResultSet sRS = sSelStmt.executeQuery("SELECT * FROM t1");
while (sRS.next())
{
    // TODO : set parameters

    sUpdStmt.execute();
    sConn.commit();
}
sRS.close();

Restrictions#

To use an Updatable ResultSet or a Scrollable ResultSet, a SELECT query statement which retrieves the ResultSet is restricted in the following ways:

To use an Updatable ResultSet,

  • Only one table can be specified in the FROM clause.
  • Only pure columns can be specified in the SELECT list; expressions or functions cannot be included. Columns with a NOT NULL constraint, and without a default value must be included in the SELECT list.

To use a Scrollable-Sensitive ResultSet,

  • Only one table can be specified in the FROM clause.

When executing PSM, only ResultSet objects of the default type are available for use. If the user specifies an option which is not of the default type, the option is ignored.

Since for a ResultSet object which is CONCUR_UPDATABLE and TYPE_SCROLL_SENSITIVE, one more Statement is used within the JDBC driver, it can easily exceed the limited number of Statements; therefore, the maximum number of Statements must be set for occasions on which such ResultSet types are used a lot.

Since an updateable and scrollable ResultSet contains a large amount of data, its memory usage is higher than a forward only ResultSet. A large ResultSet can cause memory to become scarce, so its use is not recommended.

The characteristics of the ResultSet are determined by the ResultSet type, concurrency type and holdability type described above. The user can specify random combinations for these three values; however, depending on the query statement that generates the ResultSet, the user-defined combination can be invalid. In this case, the driver does not raise an exception, but converts it to a valid combination. In the following example, the invalid types on the left side are automatically converted to the valid types on the right side.

  • TYPE_SCROLL_SENSITIVE → TYPE_SCROLL_INSENSITIVE

  • CONCUR_UPDATABLE → CONCUR_READ_ONLY

  • HOLD_CURSORS_OVER_COMMIT → CLOSE_CURSORS_AT_COMMIT

When a conversion is made internally, whether or not a conversion has occurred can be confirmed through warnings.

If the type of ResultSet object is TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE, the result of the result set is limited to 349,502 due to the increase of memory usage. If this value is exceeded, a dynamic array cursor overflow error may occur.

Detecting Holes#

A ResultSet object of the TYPE_SCROLL_SENSITIVE type retrieves the newest data from the server when performing a FETCH. Therefore, a row which was visible when the cursor opened can become invisible as the row is scrolled. For example, if a row in a ResultSet object is deleted by another Statement, the row is no longer visible in the ResultSet object. Such an invisible row is called a Hole.

The following is a code example which detects Holes in JDBC.

while (sRS.next())
{
    if (sRS.rowDeleted())
    {
        // HOLE DETECTED!!!
    }
    else
    {
        // do something ...
    }
}

Valid data cannot be obtained from a Hole, and a ResultSet returns one of the following values for a Hole

  • A SQL data type NULL
  • A reference type NULL
  • The value 0.

#### Fetch Size

When retrieving data for the ResultSet object from the server, the Altibase JDBC driver retrieves multiple rows at once, instead of retrieving one row each time, and caches them in the client to enhance performance. This is called a prefetch, and the number of rows to be fetched can be set with the setFetchSize() method of the Statement object.

public void setFetchSize(int aRows) throws SQLException;

A value between the range of 0 to 2147483647 can be set for the Altibase JDBC driver. The JDBC specification defines that an exception must be raised when a value outside of this range is specified; however, the Altibase JDBC driver does not raise an exception and ignores it, for the sake of convenience.

If the value is set to 0, the Altibase server voluntarily determines the size to return to the client in one go. In this case, the number of rows to be returned differ, according to the size of a row.

The FetchSize value is especially important for the Scroll-Sensitive ResultSet. When the user retrieves data from a Scroll-Sensitive ResultSet, the driver returns the prefetched rows first. Even if data of the database has been updated, as long as the row exists in the prefetched cache, data of the cache is returned to the user. If the user wants to see the newest data of the database, FetchSize should be set to 1. By doing so, however, the frequency of retrieving data from the server increases and performance can be lowered.

Refreshing Rows#

With the refreshRow() method of the ResultSet object, data which has been previously retrieved from the server can be re-fetched, without executing the SELECT statement. The refreshRow() method retrieves as many number of rows as the value set for FetchSize, based on the current row. To use this method, a cursor must be pointing to any row in the ResultSet.

This method operates when the ResultSet object is of the following types:

  • TYPE_SCROLL_SENSITIVE & CONCUR_UPDATABLE

  • TYPE_SCROLL_SENSITIVE & CONCUR_READ_ONLY

If this method is called for a TYPE_FORWARD_ONLY type, an exception is raised; for a TYPE_SCROLL_INSENSITIVE type, nothing happens.

Atomic Batch#

The Altibase JDBC driver not only guarantees the atomicity of batch operations, but also supports fast INSERT operations of bulk data through the Atomic Batch feature.

This section explains how to use the Atomic Batch feature which the Altibase JDBC driver supports.

How to Use Atomic Batch#

In order to use the Atomic Batch feature, you must first create the PreparedStatement object and then cast the object to the AltibasePreparedStatement class type in java programming.

The following method, setAtmoicBatch(), can be used to enable the Atomic Batch feature. 

public void setAtomicBatch(boolean aValue) throws SQLException

To confirm whether or not Atomic Batch is set for the PreparedStatement object, call the getAtomicBatch() method as below. 

public boolean getAtomicBatch()

Restrictions#

When using the Atomic Batch feature in Altibase, the following restrictions apply:

  • Only supports simple INSERT statements. Consistency for complex INSERT statements or DML statements, such as UPDATE, DELETE, etc., cannot be assured.

  • If a trigger fires with Each Statement as the unit, the trigger fires only once.

  • SYSDATE operates only once.

Example#

......
Connection con = sConn = DriverManager.getConnection(aConnectionStr, mProps);
Statement stmt = con.createStatement();

try
{ 
    stmt.execute("Drop table " + TABLE_NAME); } catch (SQLException e) { }
    stmt.execute("create table " + TABLE_NAME + "(c1 VARCHAR (1000))");

    PreparedStatement sPrepareStmt = con.prepareStatement("insert into " + TABLE_NAME + " values(?)");
    ((AltibasePreparedStatement)sPrepareStmt).setAtomicBatch(true);

    for(int i = 1; i <= MAX_RECORD_CNT; i++)
    {
        sPrepareStmt.setString(1, String.valueOf(i % 50));
        sPrepareStmt.addBatch();

        if(i%BATCH_SIZE == 0)
        {
            sPrepareStmt.executeBatch();       
            con.commit();
        }
    }
    con.commit();
} 
catch (SQLException e) 
{
    System.out.println(e.getMessage());
}
......

Date, Time, Timestamp#

This section explains the meanings of Date, Time, and Timestamp which are DATE types, and describes the data conversion range supported by the Altibase JDBC driver.

Meanings#

  • Date: Expresses only the date
  • Time: Expresses the time(the date can be included)
  • Timestamp: Expresses the date, time, seconds and further subdivisions of time

Conversion Table#

The following table shows the formats that are processed by the Altibase JDBC driver according to the object type passed to the setObject method.

Delivery Object String Date Time Timestamp
setObject
(DATE)		 2134-12-23 00:00:00.0
An error is raised if the user inputs values to the hour:minute:second. The driver sets it to 0.		 2134-12-23 00:00:00.0
The values input to the hour:minute:second are ignored by the driver.		 SQLException: UNSUPPORTED_TYPE_CONVERSION 2134-12-23 12:34:56.123456
setObject
(TIME)		 1970-01-01 12:34:56.0
An error is raised if the user inputs values to the year:month:date or a value to the nanosecond. The driver sets it to the standard year:month:date.		 2134-12-23 12:34:56.0 2134-12-23 12:34:56.0 2134-12-23 12:34:56.0
setObject
(TIMESTAMP)		 2134-12-23 12:34:56.123456 2134-12-23 00:00:00.0
The values input to the hour:minute:second are ignored by the driver.		 SQLException: UNSUPPORTED_TYPE_CONVERSION 2134-12-23 12:34:56.123456
setString() Must be input in the format set for the DATE_FORMAT property. - - -
setDate() - 2134-12-23 00:00:00.0
hour:minute:second are ignored by the driver.		 - -
setTime() - - 2134-12-23 12:34:56.0 -
setTimestamp() - - - 2134-12-23 12:34:56.123456

The following table shows the values returned from the DATE type value (1234-01-23 12:23:34.567123) stored in the database with the getDate(), getTime(), and getTimestamp() methods.

Function Return Value
getDate() 1234-01-23 00:00:00.0
getTime() 1234-01-23 12:23:34.0
getTimestamp() 1234-01-23 12:23:34.567123

GEOMETRY#

This section offers instructions on how to use GEOMETRY type data provided by Altibase in JDBC applications

How To Use GEOMETRY#

GEOMETRY type data can be used with the byte array in Altibase JDBC applications.

When inserting data(including NULL) to a GEOMETRY type column in the database with the IN parameter of PreparedStatement, the data type must be specified with the AltibaseTypes.GEOMETRY constant.

For more detailed information on how to directly write GEOMETRY type data in a query statement, please refer to Spatial SQL Reference.

Examples#

The following is code example which inserts data to a GEOMETRY type column in a JDBC application.

int sSize = ... ;
byte[] sGeometryData = new byte[sSize];

Connection sConn = ... ;
PreparedStatement sPstmt = sConn.prepareStatement("INSERT INTO TEST_TABLE VALUES (?)");
sPstmt.setObject(1, sGeometryData, AltibaseTypes.GEOMETRY);
sPstmt.executeQuery();

...

LOB#

This sections offers instructions on how to use LOB type data provided by Altibase in a JDBC application.

Prerequisites#

  • Altibase supports the LOB data types, BLOB and CLOB, and each can have the maximum size of 4GB-1byte. In JDK 1.5, if sPstmt is cast to AltibasePreparedStatement object, setBinaryStream() method defined as long type length variable can be called. 
    import Altibase.jdbc.driver.AltibasePreparedStatement;
...
((AltibasePreparedStatement)sPstmt).setBinaryStream(1, sInputStream, sLength);
...

To manipulate LOB data, the autocommit mode of a session must satisfy one of the following conditions.

  • The autocommit mode of a session must be disabled with setAutoCommit(false) of the Connection object and the user must manually control transactions.
  • Clientside_auto_commit must be set to on to enable the JDBC driver to control the autocommit operations of transactions.

Using BLOB#

How to manipulate BLOB data in a JDBC application is shown in the following code examples.

Writing BLOB Data Through the PreparedStatement Object#

The following statement creates the table used in the exampleㄴ.

CREATE TABLE TEST_TABLE ( C1 BLOB );
Using the setBinaryStream method with an InputStream object#
InputStream sInputStream = ...
long sLength = ...
... 
PreparedStatement sPstmt = connection().prepareStatement("INSERT INTO TEST_TABLE VALUES (?)");
...
sPstmt.setBinaryStream(1, sInputStream, sLength);
...
sPstmt.execute();
...
Using the setBinaryStream method with an OutputStream object#
byte[] sBuf = ...
... 
PreparedStatement sPstmt = connection().prepareStatement("SELECT * FROM TEST_TABLE FOR UPDATE");

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    Blob sBlob = sPstmt.getBlob(1);
    OutputStream sOutputStream = sBlob.setBinaryStream(1);
    sOutputStream.write(sBuf);
    sOutputStream.close();
    ...
}
...
sPstmt.execute();
...
Using the setBlob method with a Blob object#
java.sql.Blob sBlob = ...
...
PreparedStatement sPstmt = connection().prepareStatement("INSERT INTO TEST_TABLE
VALUES (?)");
...
sPstmt.setBlob(1, sBlob);
...
sPstmt.execute();
...
Using the setObject method with a Blob object#
java.sql.Blob sBlob = ...
...
PreparedStatement sPstmt = connection().prepareStatement("INSERT INTO TEST_TABLE
VALUES (?)");
...
sPstmt.setObject(1, sBlob);
...
sPstmt.execute();
...
Specifying a SQL type for the setObject method#
java.sql.Blob sBlob = ...
...
PreparedStatement sPstmt = connection().prepareStatement("INSERT INTO TEST_TABLE
VALUES (?)");
...
sPstmt.setObject(1, sBlob, AltibaseTypes.BLOB);
...
sPstmt.execute();
...
Writing BLOB Data Through the ResultSet object#

The following statement creates the table used in the examples.

CREATE TABLE BLOB_TABLE ( BLOB_COLUMN BLOB );
Using the updateBinaryStream method with an InputStream object#
InputStream sInputStream = ...
long sLength = ...
...
PreparedStatement sPstmt = connection().prepareStatement("SELECT BLOB_COLUMN FROM BLOB_TABLE", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    sRs.updateBinaryStream(1, sInputStream, sLength);
    sRs.updateRow();
    ...
}
...
Using the updateBlob method with a Blob object#
java.sql.Blob sBlob = ...

...

PreparedStatement sPstmt = connection().prepareStatement("SELECT BLOB_COLUMN FROM BLOB_TABLE", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    sRs.updateBlob(1, sBlob), ;
    sRs.updateRow();
    ...
}

...
Using the updateObject method with a Blob object#
java.sql.Blob sBlob = ...
...  
PreparedStatement sPstmt = connection().prepareStatement("SELECT BLOB_COLUMN FROM BLOB_TABLE", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    sRs.updateObject(1, sBlob);
    sRs.updateRow();
    ...
}
...
Specifying the SQL type for the updateObject method#
java.sql.Blob sBlob = ...

...

PreparedStatement sPstmt = connection().prepareStatement("SELECT BLOB_COLUMN FROM BLOB_TABLE", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    sRs.updateObject(1, sBlob, AltibaseTypes.BLOB);
    sRs.updateRow();
    ...
}
...
Updating BLOB Data with the SELECT … FOR UPDATE Statement#
byte[] sBytes = new byte[sLength];
...

PreparedStatement sPstmt = connection().prepareStatement("SELECT BLOB_COLUMN FROM BLOB_TABLE FOR UPDATE");

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    Blob sBlob = sRs.getBlob(1);
    sBlob.setBytes(0, sBytes);
    ...
}
...
Reading BLOB Data#
Using the getBinaryStream method with an InputStream object#
...
PreparedStatement sPstmt = connection().prepareStatement("SELECT BLOB_COLUMN
FROM BLOB_TABLE");
ResultSet sRs = sPstmt.executeQuery();
while(sRs.next())
{
  ...
  InputStream sInputStream = sRs.getBinaryStream(1);
  ...
}
...
Using the getBlob method with an InputStream object#
...
PreparedStatement sPstmt = connection().prepareStatement("SELECT BLOB_COLUMN
FROM BLOB_TABLE");
ResultSet sRs = sPstmt.executeQuery();
while(sRs.next())
{
  ...
  Blob sBlob = sRs.getBlob(1);
  InputStream sInputStream = sBlob.getBinaryStream();
  ...
}
...
Using the getBlob method with a byte array#
...
final int sReadLength = 100;

PreparedStatement sPstmt = connection().prepareStatement("SELECT BLOB_COLUMN FROM BLOB_TABLE");

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    Blob sBlob = sRs.getBlob(1);
    long sRemains = sBlob.length();
    long sOffset = 0;
    while(sRemains > 0)
    {
        byte[] sReadBytes = sBlob.getBytes(sOffset, sReadLength);
        sRemains -= sReadBytes.length;
        sOffset += sReadBytes.length;
        ...
    }
    ...
}

...
Altering BLOB Data#
Truncation#
Statement sStmt = ...

ResultSet sRs = sStmt.executeQuery("SELECT * FROM t1 FOR UPDATE");

while(sRs.next())
{
    ...
    int sLength = ... ;
    Blob sBlob = sRs.getBlob(2);

    // After executing this method
    // sBlob.length() == sLength
    sBlob.truncate(sLength);
}

...

Using CLOB Data#

How to use CLOB data in a JDBC application is shown in the following code examples.

Writing CLOB data Through PreparedStatemen#

The following statement creates the table used in the examples.

CREATE TABLE TEST_TABLE ( C1 BLOB );
Using the setCharacterStream method with a Reader object#

In JDK 1.5, if sPstmt is cast to the AltibasePreparedStatement object, the setCharacterStream() method defined as a long length variable can be called.

import Altibase.jdbc.driver.AltibasePreparedStatement;
...
((AltibasePreparedStatement)sPstmt).setCharacterStream(1, sReader, sLength);
...
Using the setCharacterStream method with a Writer object#
char[] sBuf = ...

...

PreparedStatement sPstmt = connection().prepareStatement("SELECT * FROM TEST_TABLE FOR UPDATE");

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    Clob sClob = sPstmt.getClob(1);
    Writer sWriter = sClob.setCharacterStream(1);
    sWriter.write(sBuf);
    sWriter.close();
    ...
}

...

sPstmt.execute();

...
Using the setClob method with a Clob object#
java.sql.Clob sClob = ...
...
PreparedStatement sPstmt = connection().prepareStatement("INSERT INTO TEST_TABLE
VALUES (?)");
...
sPstmt.setClob(1, sClob);
...
sPstmt.execute();
...
Using the setObject method with a Clob object#
java.sql.Clob sClob = ...
...
PreparedStatement sPstmt = connection().prepareStatement("INSERT INTO TEST_TABLE
VALUES (?)");
...
sPstmt.setObject(1, sClob);
...
sPstmt.execute();
...
Specifying a SQL type for the setObject method#
java.sql.Clob sClob = ...
...
PreparedStatement sPstmt = connection().prepareStatement("INSERT INTO TEST_TABLE
VALUES (?)");
...
sPstmt.setObject(1, sClob, AltibaseTypes.Clob);
...
sPstmt.execute();
...
Writing CLOB data Through the ResultSet Object#

The following statement creates the table used in the examples.

CREATE TABLE CLOB_TABLE ( CLOB_COLUMN CLOB );
Using the updateCharacterStream method with a Reader object#
Reader sReader = ...
long sLength = ... // The length of source from which Reader is linked

...

PreparedStatement sPstmt = connection().prepareStatement("SELECT CLOB_COLUMN FROM CLOB_TABLE", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    sRs.updateCharacterStream(1, sReader, sLength);
    sRs.updateRow();
    ...
}

...
Using the updateClob method with a Clob object#
java.sql.Clob sClob = ...

...

PreparedStatement sPstmt = connection().prepareStatement("SELECT CLOB_COLUMN FROM CLOB_TABLE", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    sRs.updateClob(1, sClob);
    sRs.updateRow();
    ...
}

...
Using the updateObject method witha Clob object#
java.sql.Clob sClob = ...

...

PreparedStatement sPstmt = connection().prepareStatement("SELECT CLOB_COLUMN FROM CLOB_TABLE", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    sRs.updateObject(1, sClob);
    sRs.updateRow();
    ...
}

...
Specifying a SQL type for the updateObject method#
java.sql.Clob sClob = ...

...

PreparedStatement sPstmt = connection().prepareStatement("SELECT CLOB_COLUMN FROM CLOB_TABLE", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    sRs.updateObject(1, sClob, AltibaseTypes.CLOB);
    sRs.updateRow();
    ...
}

...
Inserting CLOB data With the SELECT … FOR UPDATE Statement#
...

String sStr = ... ; 
PreparedStatement sPstmt = connection().prepareStatement("SELECT CLOB_COLUMN FROM CLOB_TABLE FOR UPDATE");

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    Clob sClob = sRs.getClob(1);
    sClob.setString(0, sStr);
    ...
}

...
Reading CLOB Data#
Using the getCharacterStream method with a Reader object#
...
PreparedStatement sPstmt = connection().prepareStatement("SELECT CLOB_COLUMN FROM CLOB_TABLE");

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    Reader sReader = sRs.getCharacterStream(1);
    ...
}
...
Using the getClob method with a Reader object#
...  
PreparedStatement sPstmt = connection().prepareStatement("SELECT CLOB_COLUMN FROM CLOB_TABLE");

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    Clob sClob = sRs.getClob(1);
    Reader sReader = sClob.getCharacterStream();
    ...
}
...
Using the getClob method with a String object#
...
final int sReadLength = 100;

PreparedStatement sPstmt = connection().prepareStatement("SELECT CLOB_COLUMN FROM CLOB_TABLE");

ResultSet sRs = sPstmt.executeQuery();

while(sRs.next())
{
    ...
    Clob sClob = sRs.getClob(1);
    long sRemains = sClob.length();
    long sOffset = 0;
    while(sRemains > 0)
    {
        String sStr = sClob.getSubString(sOffset, sReadLength);
        sRemains -= sStr.length;
        sOffset += sStr.length;
        ...
    }
    ...
}

...
Altering CLOB Data#
Truncation#
Statement sStmt = ...

ResultSet sRs = sStmt.executeQuery("SELECT * FROM t1 FOR UPDATE");

while(sRs.next())
{
    ...
    int sLength = ... ;
    Clob sClob = sRs.getClob(2);

    // After executing this method
    // sClob.length() == sLength
    sClob.truncate(sLength);
}

...

Freeing Resources#

For JDBC applications which obtain data through a large number of LOB objects, the obtained LOB objects must be freed. The LOB object must be freed specifically, regardless of whether or not the transaction is committed.

The following is a code example which frees a Blob object.

...
Blob sBlob = sRs.getBlob(1);
// Freeing Lob Locator
((Altibase.jdbc.driver.AltibaseLob)sBlob).free();
...

As for Blob objects, further operations cannot be executed on an object if a Clob object is freed with the free method, since the corresponding Lob Locator is freed from the server.

The following is a code example which frees the BlobInputStream and BlobOutputStream objects.

...
Clob sClob = sRs.getClob(1);
// Freeing Lob Locator
((Altibase.jdbc.driver.AltibaseLob)sClob).free();
...

Further operations cannot be executed on an object if the BlobInputStream object or the BlobOutputStream object is freed with the freeLocator method, since the corresponding Lob Locator is freed from the server.

The following is a code example which frees the ClobReader and ClobWriter objects.

InputStream sInputStream = sRs.getBinaryStream(1);

// Freeing Lob Locator
((Altibase.jdbc.driver.BlobInputStream)sInputStream).freeLocator();


CallableStatement sCallStmt = aConn.prepareCall("INSERT INTO TEST_TABLE VALUES (?)");
sCallStmt.registerOutParameter(1, Types.BLOB);
sCallStmt.execute();

Blob sBlob = sCallStmt.getBlob(1);
OutputStream sOutputStream = sBlob.setBinaryStream(1);

// Freeing Lob Locator
((Altibase.jdbc.driver.BlobOutputStream)sOutputStream).freeLocator();

Further operations cannot be executed on an object if the BlobInputStream object or the BlobOutputStream object is freed with the freeLocator method, since the corresponding Lob Locator is freed from the server.

The following is a code example which frees the ClobReader and ClobWriter objects.

Reader sClobReader = sRs.getCharacterStream(1);

// Freeing Lob Locator
((Altibase.jdbc.driver.ClobReader)sClobReader).freeLocator();


CallableStatement sCallStmt = aConn.prepareCall("INSERT INTO TEST_TABLE VALUES (?)");
sCallStmt.registerOutParameter(1, Types.CLOB);
sCallStmt.execute();

Clob sClob = sCallStmt.getClob(1);
Writer sClobWriter = sClob.setCharacterStream(1);

// Freeing Lob Locator
((Altibase.jdbc.driver.ClobWriter)sClobWriter).freeLocator();

Further operations cannot be executed on an object if the ClobReader object or the ClobWriter object is freed with the freeLocator method, since the corresponding Lob Locator is freed from the server.

Restrictions#

Even if clientside_auto_commit is set to on to enable the JDBC driver to control the autocommit operations of transactions the following restrictions still apply to the manipulation of LOB data.

If LOB data retrieved from the ResultSet object(cursor) is used with the executeUpdate() method of another Statement before the cursor is closed, no more fetch operations are possible from the cursor since the Lob locator is freed. The following is a code example which raises such an error.

PreparedStatement sPreStmt =
    sCon.prepareStatement( "INSERT INTO TEST_TEXT " +
                           "VALUES ( ?, ?, ?, ? )" );
Statement sStmt = sCon.createStatement();
ResultSet sRS = sStmt.executeQuery( "SELECT ID, TEXT " +
                                    "  FROM TEST_SAMPLE_TEXT " );
while ( sRS.next() ) -> (2)
{
    sID   = sRS.getInt( 1 );
    sClob = sRS.getClob( 2 );
    switch ( sID )
    {
        case 1 :
            sPreStmt.setInt(    1, 1 );
            sPreStmt.setString( 2, "Altibase Greetings" );
            sPreStmt.setClob(   3, sClob );
            sPreStmt.setInt(    4, (int)sClob.length() );
            break;
        case 2 :
            sPreStmt.setInt(    1, 2 );
            sPreStmt.setString( 2, "Main Memory DBMS" );
            sPreStmt.setClob(   3, sClob );
            sPreStmt.setInt(    4, (int)sClob.length() );
            break;
        default :
            break;
    }
    sPreStmt.executeUpdate(); -> (1)
}

(1): If sPreStmt.executeUpdate() is called while ResultSet sRS is open, the JDBC driver automatically commits transactions and by doing so, the Lob locator of sClob is freed.

(2): An exception can be raised at sRs.next() since the Lob locator is freed

Thus, when manipulating LOB data in such a logic as above, the autocommit mode of a session must first be disabled by calling setAutoCommit(false).

Controlling Autocommit#

The autocommit mode of a session can be set with the auto_commit connection attribute or the setAutoCommit method of the JDBC Connection object for Altibase JDBC applications. If autocommit is enabled with auto_commit=true or the setAutoCommit(true) method, the Altibase server automatically commits transactions.

Autocommit can also be enabled with the clientside_auto_commit connection attribute. If clientside_auto_commit is set to on, instead of the Altibase server, the JDBC driver automatically commits transactions.

When clientside_auto_commit is set to off, the autocommit mode of a session is determined by the setAutoCommit method.

To disable autocommit for a session, call setAutoCommit(false).

Calling setAutoCommit(false) on a client_auto_commit=on session disables autocommit and calling setAutoCommit(true) thereafter restores the session to the autocommit mode of the JDBC driver.

When autocommit is disabled, the user must manually commit or rollback with the commit() or rollback() method.

The following table sums up the above.

Autocommit Mode How to set
Server automatically commits transactions auto_commit=true(or omitted) or setAutoCommit(true)
JDBC driver automatically commits transactions auto_commit=true(or omitted) and clientside_auto_commit=on
Autocommit is disabled auto_commit=false or setAutoCommit(false)

BIT, VARBIT#

This section offers information on managing BIT,VARBIT type data in JDBC application and related considerations.

How to Use#

BIT, VARBIT type data can be manipulated by using Java BitSet class in ALTIBASE HDB JDBC application.

When using IN parameter of PreparedStatement, either the Types.BIT type should be specified or the value can be specified with BitSet or a character string when not specifying the type.

Notes#

In order to create BIT,VARBIT value with a bit ending with '0', use AltibaseBitSet which can be set with bit values of specified length or character string values. The Java BitSet is implemented to remember which bits have been set () because a BIT value of 0 length cannot be created.

Example#

The following is an example code for inserting data in BIT, VARBIT type columns in JDBC application.

...
BitSet sBitSet1 = new BitSet();
sBitSet1.set(1);
BitSet sBitSet2 = new AltibaseBitSet( 5 );
sBitSet1.set(2);

PreparedStatement sPstmt = sConn.prepareStatement("INSERT INTO TEST_TABLE VALUES (?)");
sPstmt.setObject(1, sBitSet1, Types.BIT);
sPstmt.executeUpdate();
sPstmt.setObject(1, sBitSet2);
sPstmt.executeUpdate();
sPstmt.setObject(1, "0110100", Types.BIT);
sPstmt.executeUpdate();
...

JDBC Logging#

JDBC Logging means to record all sorts of logs occurring in the Altibase JDBC driver, and the log can be recorded by using java.util.logging package. This section will discuss how to use and configure the JDBC logging.

Installing JDBC Logging#

In order to record a log from the JDBC driver, the JDBC jar file with an added logging function should be used. Also, it should be used after activating a logging function in the ALTIBASE_JDBC_TRACE environment variable.

JRE Version#

JRE 1.5 or above must be installed in order to execute the JDBC logging and other library is not necessary.

Setting the CLASSPATH#

To use the JDBC Logging, Altibase_t.jar file must be added to the CLASSPATH environment variable.

ex) When using the bash shell in the Unix environment

export CLASSPATH=$ALTIBASE_HOME/lib/Altibase_t.jar:.:$CLASSPATH
Activating Logging#

Global logging is activated without modifying a program by altering the ALTIBASE_JDBC_TRACE environment variables with JVM parameters. However, a client program should be re-started in order to apply the modified values of the ALTIBASE_JDBC_TRACE. 

java -DALTIBASE_JDBC_TRACE=true ...

Instruction on JDBC Logging#

Setting java.util.logging file#

Setting java.util.logging can be executed in $JRE_HOME/lib/logging.properties or it can be separately configured on java.util.logging.config.file as follows. 

java -Djava.util.logging.config.file=$ALTIBASE_HOME/sample/JDBC/Logging/logging.properties -DALTIBASE_JDBC_TRACE=true ...

Altibase provides a logging.properties sample file in a directory of $ALTIBASE_HOME/sample/JDBC/Logging, and by using or referencing it the user can directly create a configuring file and use it Djava.util.logging.config.file property.

Logger Types#

A Logger is constructed with a tree structure and it is used to partially adjust setting or the amount of logs. The logger types supported by Altibase JDBC driver are as follows.

Logger Description
altibase.jdbc Altibase JDBC messages(JDBC API call, such as connection, statement, prepared statement, etc.)
altibase.jdbc.pool Messages regarding the connection pool
altibase.jdbc.rowset ResultSet message
altibase.jdbc.xa xa messages
altibase.jdbc.failover failover messages
altibase.jdbc.cm CmChannel network packet messages
Logger Levels#

If a logger level is specified, the amount of logs can be concretely adjusted. The following table explicates the provided levels by Altibase JDBC driver, and the logs are left in detail more and more as it goes to FINEST from SEVER. If the CONFIG level is specified, logs of SEVERE, WARNING, INFO, CONFIG levels are left.

Logger level Description
OFF Off is not record the log
SEVERE SQLException or when an inner error occurred, the relevant logs are recorded in the SEVERE level.
WARNING SQLWarning is left the log in the WARNING level.
INFO The JDBC driver internally logs by monitoring specific objects.
CONFIG This level is usually used to check what kind of a SQL statement is internally executed in the JDBC driver. In terms of the PreparedStatement, sql is displayed when preparing; however, in terms of a statement, the sql is displayed with milli sec unit when it is being executed.
FINE This level leaves argument values and return values in the log when entering to the standard JDBC API. The amount of logs can be large since logs are left when entering to the API, and the time which is taken for connection or statement to close is additionally displayed.
FINEST In the FINEST level, the packet information exchanged between JDBC driver and Altibase server is logged. It is the largest amount of logs.
logging.properties#

The following is a logging.properties sample which leaves network packet logs with the log level of CONFIG. More information can be found in $ALTIBASE_HOME/sample/JDBC/Logging/logging.properties as well. .

handlers= java.util.logging.FileHandler, java.util.logging.ConsoleHandler --> FileHandler and ConsoleHandler
added as basic handlers.
.level = CONFIG --> the root logger should be specified with the CONFIG.

# default file output is in same directory.
java.util.logging.FileHandler.level = CONFIG
java.util.logging.FileHandler.pattern = ./jdbc_trace.log
java.util.logging.FileHandler.limit = 10000000
java.util.logging.FileHandler.count = 1
java.util.logging.FileHandler.append = false
java.util.logging.FileHandler.formatter = Altibase.jdbc.driver.logging.SingleLineFormatter
 -->  This is a section in which configuring the default setting of java.util.logging.FileHandler. The level of FileHandler is specified with CONFIG since only the sql information is needed.

java.util.logging.ConsoleHandler.level = CONFIG
java.util.logging.ConsoleHandler.formatter = Altibase.jdbc.driver.logging.SingleLineFormatter
 -->  This is a section configuring java.util.logging.ConsoleHandler. SingleLineFormatter is used to print out logs in a line.

altibase.jdbc.level = CONFIG
altibase.jdbc.rowset.level = SEVERE
altibase.jdbc.cm.level = FINEST
altibase.jdbc.cm.handlers = Altibase.jdbc.driver.logging.MultipleFileHandler
#altibase.jdbc.cm.handlers = java.util.logging.FileHandler
 --> This is a section in which altibase jdbc logger is configured, and the rowset level is specified with SEVERE since the record set information needs to be excluded. The Network packet information should be loged;thus, the cm level is specified with FINEST. Moreover, in terms of the network packet, MultipleFileHandler was used as a handler in order to store a file per each session. 

Altibase.jdbc.driver.logging.MultipleFileHandler.level = FINEST
Altibase.jdbc.driver.logging.MultipleFileHandler.pattern = ./jdbc_net_%s.log
Altibase.jdbc.driver.logging.MultipleFileHandler.limit = 10000000
Altibase.jdbc.driver.logging.MultipleFileHandler.count = 1
Altibase.jdbc.driver.logging.MultipleFileHandler.formatter = java.util.logging.XMLFormatter
--> By using jdbc_net_%s.log,it is configured that a file will be created per a session ID. Also, a log will be created in a XML formatter file by using XMLFormatter.

Hibernate#

Altibase provides nonstandard SQL while Hibernate facilitates such provision of Altibase as supporting Dialect class. In order to interlock Altibase, the Altibase JDBC Driver should be configured and AltibaseDialect.class should be specified as well into configuration under Hibernate.

AltibaseDialect#

Official Support Starting From Hibernate 6.4#

Starting from Hibernate 6.4, AltibaseDialect is included in the Hibernate ORM package. Now, to use AltibaseDialect, users just need to add Maven dependencies configuration.

Before Hibernate 6.4#

In versions of Hibernate before 6.4, as AltibaseDialect is not included, it is necessary to directly specify AltibaseDialect.class. To achieve this, users need to compile the AltibaseDialect.java file provided by Altibase (including AltibaseLimitHandler.java if necessary) and port it to the files provided by Hibernate. The AltibaseDialect.java file and the AltibaseLimitHandler.java file are Available from the Altibase Github site. For detailed instructions on how to port AltibaseDialect, please refer to ALTIBASE_DIALECT_PORTING.

Maven Dependencies Configuration#

Add AltibaseDialect Dependency#

Starting from Hibernate 6.4, the AltibaseDialect has been added to hibernate-community-dialect. Add the dependency as follows:

<dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-community-dialects</artifactId>
    <version>6.4.1.Final</version>
</dependency>
Add Altibase JDBC Driver Dependency#

From patch version Altibase 7.1.0.9.0, the Altibase JDBC driver can be downloaded from the Maven Central Repository. Add to Altibase JDBC driver dependency as follows:

<dependency>
    <groupId>com.altibase</groupId>
    <artifactId>altibase-jdbc</artifactId>
    <version>7.1.0.9.2</version>
</dependency>

When the Lob column value is null, Hibernate operates based on the assumption that ResultSet.getBlob() and ResultSet.getClob() will return null according to the JDBC specification. However, in Altibase 7.1, if the lob column value is null, a Lob object is returned. Therefore it is required to set the 'lob_null_select' connection attribute to "off" explicitly to use Lob-related functions in the Hibernate.

jdbc:Altibase://127.0.0.1:20300/mydb?lob_null_select=off

SQL Plan#

This function is to import the SQL execution plan as string as a non-standard API. The execution plan shows the sequence of actions Altibase performs to execute the statements. Option can be ON, OFF, or ONLY, and the default setting is OFF.

How to use#

To get the execution plan, before executing the SQL statement, the setExplainPlan(byteaExplainPlanMode) method of the AltibaseConnection object must be called to specify what content to get the execution plan. The aExplainPlanMode options that can be specified are described in the table below

Factor#

PROPERTY PROPERTY VALUE DESCRIPTION
AltibaseConnection.EXPLAIN_PLAN_OFF 0 After executing the SELECT statement, the Plan Tree information is not displayed, only the result record is displayed
AltibaseConnection.EXPLAIN_PLAN_ON 1 After executing the SELECT statement, it shows the information of the Plan Tree along with the result record. In the Plan Tree, the number of records accessed, the amount of memory occupied by tuples, and the cost are displayed.
AltibaseConnection.EXPLAIN_PLAN_ONLY 2 After executing the SELECT statement, it shows the information of the Plan Tree along with the result record. If EXPLAIN PLAN = ONLY, only the execution plan is created without query execution, so items that value is determined after actual, execution, such as the ACCESS item, are displayed as question marks ("??").

Code Example#

AltibaseConnection sConn = (AltibaseConnection)DriverManager.getConnection(sURL, sProps);
sConn.setExplainPlan(AltibaseConnection.EXPLAIN_PLAN_ONLY);
AltibaseStatement  sStmt = (AltibaseStatement)sConn.prepareStatement("SELECT sysdate FROM dual");
System.out.println(sStmt.getExplainPlan());

Result Example#

------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 1, TUPLE_SIZE: 8, COST: 0.01 )
 SCAN ( TABLE: DUAL, FULL SCAN, ACCESS: ??, COST: 0.01 )
------------------------------------------------------------