CLOB (original) (raw)

A CLOB (character large object) value can be up to 2,147,483,647 characters long. A CLOB is used to store unicode character-based data, such as large documents in any character set.

The length is given in number characters for both CLOB, unless one of the suffixes K, M, or G is given, relating to the multiples of 1024, 1024*1024, 1024*1024*1024 respectively.

Length is specified in characters (unicode) for CLOB.

Syntax

{CLOB |CHARACTER LARGE OBJECT}(length [{{K |M |G}]))

Corresponding Compile-Time Java Type

java.sql.Clob

JDBC Metadata Type (java.sql.Types)

CLOB

Corresponding Compile-Time Java Type

java.sql.Clob

JDBC Metadata Type (java.sql.Types)

CLOB

Use the getClob method on the java.sql.ResultSet to retrieve a CLOB handle to the underlying data.

import java.sql.*;

public class clob { public static void main(String[] args) { try { String url = "jdbc:derby:clobberyclob;create=true";

        Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
        Connection conn = DriverManager.getConnection(url);

        Statement s = conn.createStatement();
        s.executeUpdate("CREATE TABLE documents (id INT, text CLOB(64 K))");
        conn.commit();

        // --- add a file
        java.io.File file = new java.io.File("asciifile.txt");
        int fileLength = (int) file.length();

        // - first, create an input stream
        java.io.InputStream fin = new java.io.FileInputStream(file);
        PreparedStatement ps = conn.prepareStatement("INSERT
        INTO documents VALUES (?, ?)");
        ps.setInt(1, 1477);

        // - set the value of the input parameter to the input stream
        ps.setAsciiStream(2, fin, fileLength);
        ps.execute();
        conn.commit();

        // --- reading the columns
        ResultSet rs = s.executeQuery("SELECT text FROM documents
         WHERE id = 1477");
        while (rs.next()) {
            java.sql.Clob aclob = rs.getClob(1);
            java.io.InputStream ip = rs.getAsciiStream(1);
            int c = ip.read();
            while (c > 0) {
                System.out.print((char)c);
                c = ip.read();
            }
            System.out.print("\n");
            // ...
        }
    } catch (Exception e) {
        System.out.println("Error! "+e);
    }
}

}