CLOB data type

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}] ) ]

Default

A CLOB without a specified length is defaulted to two giga characters (2,147,483,647).

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.

Related information

See Mapping of java.sql.Blob and java.sql.Clob interfaces.

Example

        String url = "jdbc:derby:clobberyclob;create=true";
        Connection conn = DriverManager.getConnection(url);

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

        // - first, create an input stream
        InputStream fis = new FileInputStream("asciifile.txt");

        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, fis);
        ps.execute();

        // --- reading the columns back
        ResultSet rs = s.executeQuery(
            "SELECT text FROM documents WHERE id = 1477");

        while (rs.next()) {
            Clob aclob = rs.getClob(1);
            InputStream ip = aclob.getAsciiStream();

            for (int c = ip.read(); c != -1; c = ip.read()) {
                System.out.print((char)c);
            }

            ip.close();
        }
        s.close();
        ps.close();
        rs.close();
        conn.close();