Using BYTEA Data with Java

The BYTEA data type stores a binary string in a sequence of bytes; digital images and sound files are often stored as binary data. Advanced Server can store and retrieve binary data via the BYTEA data type.

The following Java sample stores BYTEA data in an Advanced Server database and then demonstrates how to retrieve that data. The example requires a bit of setup; Listings 1.12-a, 1.12-b, and 1.12-c create the server-side environment for the Java example.

Listing 1.12-a creates a table (emp_detail) that stores BYTEA data. emp_detail contains two columns: the first column stores an employee’s ID number (type INT) and serves as the primary key for the table; the second column stores a photograph of the employee in BYTEA format:

CREATE TABLE emp_detail
(
  empno INT4 PRIMARY KEY,
  pic   BYTEA
);

Listing 1.12-b creates a procedure (ADD_PIC) that inserts a row into the emp_detail table:

CREATE OR REPLACE PROCEDURE ADD_PIC(p_empno IN int4, p_photo IN bytea) AS
BEGIN
  INSERT INTO emp_detail VALUES(p_empno, p_photo);
END;

And finally, Listing 1.12-c creates a function (GET_PIC) that returns the photograph for a given employee:

CREATE OR REPLACE FUNCTION GET_PIC(p_empno IN int4) RETURN BYTEA IS
DECLARE
  photo BYTEA;
BEGIN
  SELECT pic INTO photo from EMP_DETAIL WHERE empno = p_empno;
  RETURN photo;
END;

Inserting BYTEA Data into an Advanced Server Database

Listing 1.13 shows a Java method that invokes the ADD_PIC procedure (see Listing 1.12-b) to copy a photograph from the client file system to the emp_detail table on the server:

public void InsertPic(Connection con)
{
  try
  {
    Console c = System.console();
    int empno = Integer.parseInt(c.readLine("Employee No :"));
    String fileName = c.readLine("Image filename :");
    File f = new File(fileName);

    if(!f.exists())
    {
      System.out.println("Image file not found. Terminating...");
      return;
    }

    CallableStatement stmt = con.prepareCall("{call ADD_PIC(?, ?)}");
    stmt.setInt(1, empno);
    stmt.setBinaryStream(2, new FileInputStream(f), (int)f.length());
    stmt.execute();
    System.out.println("Added image for Employee "+empno);
  }
  catch(Exception err)
  {
    System.out.println("An error has occurred.");
    System.out.println("See full details below.");
    err.printStackTrace();
  }
}

InsertPic() prompts the user for an employee number and the name of an image file:

int empno = Integer.parseInt(c.readLine("Employee No :"));
String fileName = c.readLine("Image filename :");

If the requested file does not exist, InsertPic() displays an error message and terminates:

File f = new File(fileName);

if(!f.exists())
{
  System.out.println("Image file not found. Terminating...");
  return;
}

Next, InsertPic() prepares a CallableStatement object (stmt) that calls the ADD_PIC procedure. The first placeholder (?) represents the first parameter expected by ADD_PIC (p_empno); the second placeholder represents the second parameter (p_photo). To provide actual values for those placeholders, InsertPic() calls two setter methods. Since the first parameter is of type INTEGER, InsertPic() calls the setInt() method to provide a value for p_empno. The second parameter is of type BYTEA, so InsertPic() uses a binary setter method; in this case, the method is setBinaryStream():

CallableStatement stmt = con.prepareCall("{call ADD_PIC(?, ?)}");
stmt.setInt(1, empno);
stmt.setBinaryStream(2 ,new FileInputStream(f), f.length());

Now that the placeholders are bound to actual values, InsertPic() executes the CallableStatement:

stmt.execute();

If all goes well, InsertPic() displays a message verifying that the image has been added to the table. If an error occurs, the catch block displays a message to the user:

System.out.println("Added image for Employee \""+empno);
catch(Exception err)
{
  System.out.println("An error has occurred.");
  System.out.println("See full details below.");
  err.printStackTrace();
}

Retrieving BYTEA Data from an Advanced Server Database

Now that you know how to insert BYTEA data from a Java application, Listing 1.14 demonstrates how to retrieve BYTEA data from the server:

public static void GetPic(Connection con)
{
  try
  {
    Console c = System.console();
    int empno = Integer.parseInt(c.readLine("Employee No :"));
    CallableStatement stmt = con.prepareCall("{?=call GET_PIC(?)}");
    stmt.setInt(2, empno);
    stmt.registerOutParameter(1, Types.BINARY);
    stmt.execute();
    byte[] b = stmt.getBytes(1);

    String fileName = c.readLine("Destination filename :");
    FileOutputStream fos = new FileOutputStream(new File(fileName));
    fos.write(b);
    fos.close();
    System.out.println("File saved at \""+fileName+"\"");
  }
  catch(Exception err)
  {
    System.out.println("An error has occurred.");
    System.out.println("See full details below.");
    err.printStackTrace();
  }
}

GetPic() starts by prompting the user for an employee ID number:

int empno = Integer.parseInt(c.readLine("Employee No :"));

Next, GetPic() prepares a CallableStatement with one IN parameter and one OUT parameter. The first parameter is the OUT parameter that will contain the photograph retrieved from the database. Since the photograph is BYTEA data, GetPic() registers the parameter as a Type.BINARY. The second parameter is the IN parameter that holds the employee number (an INT), so GetPic() uses the setInt() method to provide a value for the second parameter.

CallableStatement stmt = con.prepareCall("{?=call GET_PIC(?)}");
stmt.setInt(2, empno);
stmt.registerOutParameter(1, Types.BINARY);

Next, GetPic() uses the getBytes getter method to retrieve the BYTEA data from the CallableStatement:

stmt.execute();
byte[] b = stmt.getBytes(1);

The program prompts the user for the name of the file where it will store the photograph:

String fileName = c.readLine("Destination filename :");

The FileOutputStream object writes the binary data that contains the photograph to the destination filename:

FileOutputStream fos = new FileOutputStream(new File(fileName));
fos.write(b);
fos.close();

Finally, GetPic() displays a message confirming that the file has been saved at the new location:

System.out.println("File saved at \""+fileName+"\"");