lobtest.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.LOBTest

   Copyright 2003, 2005 The Apache Software Foundation or its licensors, as applicable.

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

 */

package org.apache.derbyTesting.functionTests.tests.jdbcapi;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Array;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.io.Reader;
import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Ref;
import java.net.URL;
import java.sql.PreparedStatement;
import org.apache.derby.tools.ij;
import org.apache.derbyTesting.functionTests.util.TestUtil;
import org.apache.derby.tools.JDBCDisplayUtil;

/**
 * @author Jonas S Karlsson
 */

public class LOBTest {
	/* the default framework is embedded*/
	public static final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	public static final String protocol = "jdbc:derby:";
        
        
	public static Connection connectAndCreate(String dbname) throws Exception {
		// connect and create db
		Class.forName(driver).newInstance(); // load driver
		Connection conn = DriverManager.getConnection(protocol+dbname
													  +";create=true");

		conn.setAutoCommit(false);
		return conn;
	}
	public static void disconnect(Connection conn) throws Exception {
		conn.commit();
		conn.close();
	}
    public static void printSQLError(SQLException e) {
        while (e != null) {
            System.out.print("\t");
            JDBCDisplayUtil.ShowSQLException(System.out, e);
            e = e.getNextException();
        }
    }
	//////////////////////////////////////////////////////////////////////
	public static void largeTest(String[] args) throws Exception{
		System.out.println("connecting");
		Connection conn = connectAndCreate("LOBdb");
		Statement s = conn.createStatement();

		try {
			System.out.println("dropping");
			s.executeUpdate("DROP TABLE atable");
		} catch (Exception e) {
		}

		System.out.println("creating");
		s.executeUpdate("CREATE TABLE atable (a INT, b LONG VARCHAR FOR BIT DATA)");
		conn.commit();
		java.io.File file = new java.io.File("short.utf");
		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 atable VALUES (?, ?)");
		ps.setInt(1, 1);

		// set the value of the input parameter to the input stream
//              ps.setBinaryStream(2, fin, fileLength);
		ps.setBinaryStream(2, fin, -1);
		System.out.println("inserting");
		ps.execute();
		conn.commit();

		// reading the columns
		System.out.println("reading");
		ResultSet rs = s.executeQuery("SELECT b, octet_length(b) FROM atable WHERE a = 1");
		while (rs.next()) {
			java.sql.Clob aclob = rs.getClob(1);
			java.io.InputStream ip = rs.getAsciiStream(1);
			System.out.println("octet_length = "+rs.getInt(2));
		}

		System.out.println("disconnecting");
		disconnect(conn);
	}

    public static void typeTest(String[] args) throws Exception {

		// use the ij utility to read the property file and
			// make the initial connection.
		ij.getPropertyArg(args);
		Connection conn = ij.startJBMS();
		
		// old bit datatype, converted later to char () for bit data
		new LOBTester(conn, "bit", "(8 )").test();
		new LOBTester(conn, "bit", "(8 )").test();

		new LOBTester(conn, "blob", "(2 M)").test();
		new LOBTester(conn, "blob", "(2 K)").test();
		new LOBTester(conn, "blob", "(64  )").test();

		new LOBTester(conn, "clob", "(2 K)").test();
		new LOBTester(conn, "clob", "(64  )").test();


    	new LOBTester(conn, "blob", "(2 M)").testBlobInsert();
		disconnect(conn);
	}
    public static void main(String[] args) {
        try {
//			if (args.length > 0) {
//				largeTest(args);
//			} else {
				typeTest(args);
//			}
		}
		catch (Throwable e) {
			LOBTest.printException(e);
		}
	}

	public static void printException(Throwable e) {
		//System.out.println("\t Exception thrown:");
		if (e instanceof SQLException) 
			printSQLError((SQLException)e);
		else
			e.printStackTrace();
    }
}


class LOBTester {
    String typeName;
    String typeSpec;
    String table;
    String[] colNames;
    String[] colTypes;
	int columns;
	String[] colData;

    Connection conn;
    Statement st;

    String[] typeNames = { "int", "char(10)", "varchar(80)", "long varchar", "char(10) for bit data", "long varchar for bit data", "blob(80)" };

	static int BIT_OFFSET = 4;
	static int LONG_VARBINARY_OFFSET = 5;
	static int BLOB_OFFSET = 6;
	static int TYPE_COL_OFFSET= 7;

    public LOBTester(Connection c, String typeName, String typeSpec) throws SQLException {

        this.conn = c;
        this.typeName = typeName;
        this.typeSpec = typeSpec;
        this.table = typeName+"_table";
        this.st = this.conn.createStatement();

        columns = typeNames.length+1;
        this.colNames = new String[columns];
        this.colTypes = new String[columns];
        for(int i=0; i<columns-1; i++) {
            String colName = "col_"+i;
            colNames[i] = colName;
            colTypes[i] = typeNames[i];
        }
        colNames[columns-1] = "typecol";
	String tmpTypeNameSpec;
		if (typeName.equals("bit"))
			tmpTypeNameSpec="char" +" "+typeSpec + " for bit data";
		else
			tmpTypeNameSpec=typeName+" "+typeSpec;
		colTypes[columns-1] = tmpTypeNameSpec;
		colData = new String[] { "100","'101'","'102'", "'103'",
								 TestUtil.stringToHexLiteral("104"),
								 TestUtil.stringToHexLiteral("105"),
								 "CAST (" +TestUtil.stringToHexLiteral("106") +" AS " +
								 colTypes[BLOB_OFFSET] +")",
								 "CAST (" +TestUtil.stringToHexLiteral("107") +" AS " +
								 tmpTypeNameSpec + ")" };
		
    }
    public static void printResultSet(ResultSet rs) throws SQLException {
        if (rs==null) return;
        ResultSetMetaData md = rs.getMetaData();
        int cols = md.getColumnCount();

        boolean hasNext = true;
        // according to javadoc, rs already points to first
        // row, but it won't work if we don't call next()!

        // print some metadata
        for(int col=1; col<=cols; col++) {
            System.out.println("\t---- "+col);
            System.out.println("\tColumn    : "+md.getColumnName(col));
            System.out.println("\tType      : "+md.getColumnType(col));
            System.out.println("\tTypeName  : "+md.getColumnTypeName(col));
            System.out.println("\tClassName : "+md.getColumnClassName(col));
            System.out.println("\tLabel     : "+md.getColumnLabel(col));
            System.out.println("\tDisplaySz : "+md.getColumnDisplaySize(col));
            System.out.println("\tPrecision : "+md.getPrecision(col));
            System.out.println("\tScale     : "+md.getScale(col));
            System.out.println("\tisCurrency: "+md.isCurrency(col));
            System.out.println("\tisCaseSens: "+md.isCaseSensitive(col));
            System.out.println("\tisDefWrite: "+md.isDefinitelyWritable(col));
            System.out.println("\tisWrite   : "+md.isWritable(col));
            System.out.println("\tisSearchab: "+md.isSearchable(col));
//			System.out.println("\tSchemaName: "+md.getSchemaName(col));
            System.out.print("\n");
        }

        // print actual data
        while (rs.next()) { // for each row
            for(int col=1; col<=cols; col++) {
                Object c = rs.getObject(col);
                if (c==null)
                    System.out.println("\tOUT = NULL");
                else {
                    // fixup if it contains classname (remove "random" part after @)
                    String v = c.toString();
                    if (v.indexOf('@') != -1) {
                        v = v.substring(0, v.indexOf('@')+1);