Inserting a BLOB file to a CUBRID database

In this tutorial you will learn how to insert a binary file into a CUBRID database. Before we continue you should remember that LOB data in CUBRID is stored outside the database on the external storage while the references to those files are stored in database columns. For more info, refer to Working with CUBRID BLOB / CLOB Data Types.

This example assumes the following database schema.

CREATE TABLE images(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    image BLOB
);

Notice that our BLOB column does not have NOT NULL or UNIQUE constraints because in CUBRID LOB columns (both BLOB and CLOB) cannot set these constraints.

The example below is written in Java although it is possible to accomplish the same task using other APIs.

import cubrid.jdbc.driver.CUBRIDConnection;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;

public class Basic {
    public static CUBRIDConnection connect() {
        Connection conn = null;
        try {
            Class.forName("cubrid.jdbc.driver.CUBRIDDriver");
            conn = DriverManager.getConnection("jdbc:cubrid:localhost:33000:demodb:dba:");
            conn.setAutoCommit (false) ;
        } catch ( Exception e ) {
            System.err.println("Error: " + e.getMessage());
        }
        return (CUBRIDConnection)conn;
    }

    public static void main(String[] args) throws Exception {
        CUBRIDConnection conn = null;
        PreparedStatement preStmt = null;

        conn = connect();

        try {
            String fileName = "/path/to/binary/file.gif";
            File file = new File(fileName);

            if (!file.exists()) {
                throw new IOException("File " + fileName + " cannot be found.");
            }
            else{
                FileInputStream fileInputStream = new FileInputStream(file);

                // create a prepared statement to insert BLOB data.
                preStmt = conn.prepareStatement("insert into images (image) VALUES ( ? )");

                // create a BLOB object which will store our binary file
                Blob blob = conn.createBlob();
                byte[] b = new byte(int) file.length();

                // read all bytes from the file stream to our binary array,
                // and make sure we read all the bytes from the file
                assert fileInputStream.read(b) == file.length();

                // now set these bytes to our BLOB object
                blob.setBytes(1, b);

                // finally bind our BLOB object to the prepared statement
                preStmt.setBlob(1, blob);

                // execute the prepared query
                preStmt.executeUpdate();
                preStmt.close();
            }

            // since we have set auto_commit mode to false, we need to manually commit the changes.
            conn.commit();
            conn.close();
        } catch ( Exception e ) {
            conn.rollback();
            System.err.println("SQLException : " + e.getMessage());
        } finally {
            if ( conn != null ) conn.close();
        }
    }
}

That is all you need to read a binary file and send it to the database. CUBRID will store the file in a LOB directory, which was set when the database was created. By default it is located in the same directory where the database volume files are located. For more info, see --lob-base-path option for cubrid createdb utility.

Once the file is stored into the disk, the path to the binary file (LOB Locator) is stored in the image BLOB column in our table.