# CLOB

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 gigabytes (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 java.sql.Blob and java.sql.Clob.

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

Parent topic: Data types
Related concepts
Built-In type overview
Numeric types
Related reference
Data type assignments and comparison, sorting, and ordering
BIGINT
BLOB
CHAR
CHAR FOR BIT DATA
DATE
DECIMAL
DOUBLE
DOUBLE PRECISION
FLOAT
INTEGER
LONG VARCHAR
LONG VARCHAR FOR BIT DATA
NUMERIC
REAL
SMALLINT
TIME
TIMESTAMP
VARCHAR
VARCHAR FOR BIT DATA
XML data type