R: Inserting a BLOB

[Amleto Di Salle]

Hi,
This is an example.

Note the SQL statements.
Furthermore, if you have a table with data and after you want to add a
NULL BLOB column you have a problem in order to update the old rows. 
You have to delete these rows and re-insert "using" the empty_blob
function. You cannot update the old rows, i.e. I haven't succeed! :-)


BR
/Amleto

P.S.: Sorry for the spaghetti code! This is only an example.

----------------------------------
SQL:

CREATE TABLE TEST.TEST (
ID NUMBER ( 20 ) NOT NULL,
IMAGE BLOB
);
----------------------------------
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

import oracle.jdbc.pool.OracleConnectionPoolDataSource;

public class TestBlob {
    public static void main( String[] args ) {

        String databaseUrl =
"jdbc:oracle:thin:@127.0.0.1:1521:OracleDB";
        String databaseUser = "system";
        String databasePassword = "manager";

        try {
            DataSource dataSource = getDataSource( databaseUrl,
"oracle.jdbc.driver.OracleDriver", databaseUser, databasePassword );
            Connection connection =  dataSource.getConnection();
            connection.setAutoCommit( false );
            PreparedStatement preparedStatement =
connection.prepareStatement( "INSERT INTO TEST.TEST (ID, IMAGE) VALUES
(?, empty_blob())" );
            preparedStatement.setInt( 1, 1 );
            preparedStatement.executeUpdate();

            PreparedStatement ps = connection.prepareStatement("SELECT *
FROM TEST.TEST WHERE ID=? FOR UPDATE");
            ps.setInt( 1, 1);
            ResultSet rs = ps.executeQuery();
            rs.next();

            oracle.sql.BLOB image =
((oracle.jdbc.driver.OracleResultSet)rs).getBLOB("IMAGE");
            BufferedInputStream bufferedIn = new BufferedInputStream(new
FileInputStream("C:\\Images\\image.jpg"));
            OutputStream os = null;
            os = image.getBinaryOutputStream();
            int buffSize = image.getBufferSize();
            byte[] buffer = new byte[buffSize];
            int length = -1;

            while ((length = bufferedIn.read(buffer)) != -1) {
            os.write(buffer, 0, length);
            }

            bufferedIn.close();
            os.flush();
            os.close();
            connection.commit();

        }
        catch ( Exception e ) {
            e.printStackTrace();
        }


    }
    

    public static OracleConnectionPoolDataSource getDataSource( String
URL,
                                                                String
driverType, String user, String password ) throws
InstantiationException, Exception {
        if ( ( URL == null ) || ( URL.length() == 0 ) || ( driverType ==
null ) || ( driverType.length() == 0 ) || ( user == null ) || (
user.length() == 0 ) || ( password == null ) || ( password.length() == 0
) ) {
            throw new InstantiationException( "Error!!" );
        }

        OracleConnectionPoolDataSource dataSource = null;
        try {
            dataSource = new OracleConnectionPoolDataSource();
            dataSource.setURL( URL );
            dataSource.setDriverType( driverType );
            dataSource.setUser( user );
            dataSource.setPassword( password );
        }
        catch ( SQLException ex ) {
            throw new Exception( "Error!!", ex );
        }
        return dataSource;
    }    
}

-----Messaggio originale-----
Da: Arundhati Kalia [mailto:[EMAIL PROTECTED] 
Inviato: giovedì 7 ottobre 2004 9.21
A: [EMAIL PROTECTED]
Oggetto: Inserting a BLOB


Hi All,

Can anyone please guide me to the most efficient way of inserting a BLOB
in Oracle database thro Java ?

TIA
Arundhati

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]