/* 
 * This sample demonstrate basic File support in the oci8 driver
 */

import java.sql.*;
import java.io.*;
import java.util.*;

// Importing the Oracle Jdbc driver package makes the code more readable
import oracle.jdbc.driver.*;

public class FileExample
{
  public static void main (String args [])
       throws Exception
  {
    // Load the Oracle JDBC driver
    Class.forName ("oracle.jdbc.driver.OracleDriver");

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    //
    // The sample creates a DIRECTORY and you have to be connected as
    // "system" to be able to run the test.
    // I you can't connect as "system" have your system manager
    // create the directory for you, grant you the rights to it, and
    // remove the portion of this program that drops and creates the directory.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "system", "manager");

    // It's faster when auto commit is off
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    try
    {
      stmt.execute ("drop directory TEST_DIR");
    }
    catch (SQLException e)
    {
      // An error is raised if the directory does not exist.  Just ignore it.
    }
    stmt.execute ("create directory TEST_DIR as '/private/local/filetest'");

    try
    {
      stmt.execute ("drop table test_dir_table");
    }
    catch (SQLException e)
    {
      // An error is raised if the table does not exist.  Just ignore it.
    }

    // Create and populate a table with files
    // The files file1 and file2 must exist in the directory TEST_DIR created
    // above as symbolic name for /private/local/filetest.
    stmt.execute ("create table test_dir_table (x varchar2 (30), b bfile)");
    stmt.execute ("insert into test_dir_table values ('one', bfilename ('TEST_DIR', 'file1'))");
    stmt.execute ("insert into test_dir_table values ('two', bfilename ('TEST_DIR', 'file2'))");

    // Select the file from the table
    ResultSet rset = stmt.executeQuery ("select * from test_dir_table");
    while (rset.next ())
    {
      String x = rset.getString (1);
      OracleBfile bfile = ((OracleResultSet)rset).getBfile (2);
      System.out.println (x + " " + bfile);

      // Dump the file contents
      dumpBfile (conn, bfile);
    }
  }

  // Utility function to dump the contents of a Bfile
  static void dumpBfile (Connection conn, OracleBfile bfile)
    throws Exception
  {
    OracleCallableStatement read =
      (OracleCallableStatement)
        conn.prepareCall ("begin dbms_lob.read (?, ?, ?, ?); end;");

    System.out.println ("Dumping file " + filegetname (conn, bfile));
    System.out.println ("File exists: " + fileexists (conn, bfile));
    System.out.println ("File open: " + fileisopen (conn, bfile));

    System.out.println ("Opening File: ");

    bfile = fileopen (conn, bfile);

    System.out.println ("File open: " + fileisopen (conn, bfile));

    long length = getLength (conn, bfile);
    System.out.println ("File length: " + length);

    long i = 0;
    int chunk = 10;

    while (i < length)
    {
      read.setBfile (1, bfile);
      read.setLong (2, chunk);
      read.registerOutParameter (2, Types.NUMERIC);
      read.setLong (3, i + 1);
      read.registerOutParameter (4, Types.VARBINARY);
      read.execute ();

      long read_this_time = read.getLong (2);
      byte [] bytes_this_time = read.getBytes (4);

      System.out.print ("Read " + read_this_time + " bytes: ");

      int j;
      for (j = 0; j < read_this_time; j++)
	System.out.print (bytes_this_time [j] + " ");
      System.out.println ();

      i += read_this_time;
    }

    fileclose (conn, bfile);
    fileisopen (conn, bfile);

    read.close ();
  }

  // Utility function to get the length of a Bfile
  static long getLength (Connection conn, OracleBfile bfile)
    throws SQLException
  {
    OracleCallableStatement cstmt =
      (OracleCallableStatement)
        conn.prepareCall ("begin ? := dbms_lob.getLength (?); end;");
    try
    {
      cstmt.registerOutParameter (1, Types.NUMERIC);
      cstmt.setBfile (2, bfile);
      cstmt.execute ();
      return cstmt.getLong (1);
    }
    finally
    {
      cstmt.close ();
    }
  }

  // Utility function to test if a Bfile exists
  static boolean fileexists (Connection conn, OracleBfile bfile)
    throws SQLException
  {
    OracleCallableStatement cstmt =
      (OracleCallableStatement)
        conn.prepareCall ("begin ? := dbms_lob.fileexists (?); end;");

    try
    {
      cstmt.registerOutParameter (1, Types.NUMERIC);
      cstmt.setBfile (2, bfile);
      cstmt.execute ();
      return cstmt.getBoolean (1);
    }
    finally
    {
      cstmt.close ();
    }
  }

  // Utility function to return the filename of a Bfile
  static String filegetname (Connection conn, OracleBfile bfile)
    throws SQLException
  {
    OracleCallableStatement cstmt =
      (OracleCallableStatement)
        conn.prepareCall ("begin dbms_lob.filegetname (?, ?, ?); end;");

    try
    {
      cstmt.setBfile (1, bfile);
      cstmt.registerOutParameter (2, Types.VARCHAR);
      cstmt.registerOutParameter (3, Types.VARCHAR);
      cstmt.execute ();
      
      return cstmt.getString (3);
    }
    finally
    {
      cstmt.close ();
    }
  }

  // Utility function to open a Bfile.
  // Note that an open Bfile is a different object from the
  // closed one.  The fileopen entrypoint returns the open file object
  // which is the one you have to use to read the file contents.
  static OracleBfile fileopen (Connection conn, OracleBfile bfile)
    throws SQLException
  {
    OracleCallableStatement cstmt =
      (OracleCallableStatement)
        conn.prepareCall ("begin dbms_lob.fileopen (?, 0); end;");

    try
    {
      cstmt.setBfile (1, bfile);
      cstmt.registerOutParameter (1, OracleTypes.BFILE);
      cstmt.execute ();
      return cstmt.getBfile (1);
    }
    finally
    {
      cstmt.close ();
    }
  }

  // Utility function to test if a Bfile is open
  static boolean fileisopen (Connection conn, OracleBfile bfile)
    throws SQLException
  {
    OracleCallableStatement cstmt =
      (OracleCallableStatement)
        conn.prepareCall ("begin ? := dbms_lob.fileisopen (?); end;");

    try
    {
      cstmt.registerOutParameter (1, Types.NUMERIC);
      cstmt.setBfile (2, bfile);
      cstmt.execute ();
      return cstmt.getBoolean (1);
    }
    finally
    {
      cstmt.close ();
    }
  }

  // Utility function to close a Bfile
  static void fileclose (Connection conn, OracleBfile bfile)
    throws SQLException
  {
    OracleCallableStatement cstmt =
      (OracleCallableStatement)
        conn.prepareCall ("begin dbms_lob.fileclose (?); end;");

    System.out.println ("Closing bfile.");
    cstmt.setBfile (1, bfile);
    cstmt.execute ();
    cstmt.close ();
  }
}