Skip to content
≡

Administrator

Developer

Tools

External Tools

LOB

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 2Gbytes.

  • 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 );
1. 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();
...
2. 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();
...
3. 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();
...
4. 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();
...
  1. 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 );
1. 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();
    ...
}
...
2. 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();
    ...
}

...
3. 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();
    ...
}
...
4. 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#

1. 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);
  ...
}
...
2. 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();
  ...
}
...
3. 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 );
1. Using the setCharacterStream method with a Reader object#
Reader sReader = ...
long sLength = ...
...
PreparedStatement sPstmt = connection().prepareStatement("INSERT INTO TEST_TABLE
VALUES (?)");
...
sPstmt.setCharacterStream(1, sReader, sLength);
...
sPstmt.execute();
...
2. 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();

...
3. 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();
...
4. 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();
...
5. 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 );
1. 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();
    ...
}

...
2. 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();
    ...
}

...
3. 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();
    ...
}

...
4. pecifying 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#

1. 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);
    ...
}
...
2. 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();
    ...
}
...
3. 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).