inserting video files in to the database 2

[onlymullapudi]

I want to insert video files in to the oracle 10g database, through jdbc.If anyone has good material can they send the link to me or tell how to create a table with lob fields, and how to insert and access them

 

[sedj]

Normally I would not post complete solutions where a poster had not made an effort to actually show any attempt to solve their problem, but as it happens, I recently did this ... so count yourself lucky !!!

Create a table :

CREATE TABLE tblobs (number session_id, my_blob BLOB);

Write a procedute to insert a BLOB into the table, and then provide a lob locator to that object :

CREATE OR REPLACE PACKAGE BODY tblob
IS
    PROCEDURE STORE(io_blob IN OUT BLOB, in_session_id NUMBER := 1)
    IS
    BEGIN
        DELETE      tblobs
              WHERE session_id = in_session_id;

        INSERT INTO tblobs
                    (
                     session_id,
                     tblob)
             VALUES (
                     in_session_id,
                     EMPTY_BLOB)
             RETURN tblob
               INTO io_blob;
    END;
END;
/

Then use the following java to insert a file as byte[] data into the tblob table using the proc above :

// @sedj
// Jan 2006

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

class TestBlobInsert {

 	public static void main(String args[]) throws Exception {
		// get our test binary data
		// I've tested this code on over 12Mb's of data ...
		File f = new File(args[0]);
		FileInputStream fis = new FileInputStream(f);
		byte binaryData[] = new byte[(int)f.length()];
		fis.read(binaryData);

		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@exdpdev2:1521:exdpdev2", "orabatch", "dev");


		//// IMPORTANT ////
		// You must set autocommit off here - else you'll get erros on transaction exectution
		con.setAutoCommit(false);


		CallableStatement stmt = con.prepareCall( "{call tblob.store(?)}");


		// arg 1 of the proc is a BLOB locator.
		stmt.registerOutParameter(1, Types.BLOB);
		stmt.execute();

		// Get the blob locator
		oracle.sql.BLOB blob = (oracle.sql.BLOB)stmt.getBlob(1);
		if (blob != null) {
			// Get an output stream to it, and write our binary data to it
			OutputStream os = blob.getBinaryOutputStream();
			os.write(binaryData);

			// close the locator
			os.close();
		} else {
			System.out.println("Blob is null !!!");
		}

		// Close the statement
		stmt.close();

		//// IMPORTANT ////
		// don't forget to commit AND turn back autocommit for
		// other pooled connections else you'll have problems !
		con.commit();
		con.setAutoCommit(true);

		con.close();
	}
}

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[onlymullapudi]

I want to put a video file in to the clob field of the table. what i want to know is how to access the video file, when i am inserting in to the table

 

[sedj]

Your original post contained 3 separate questions :

1) I want to insert video files in to the oracle 10g database, through jdbc.
2) tell how to create a table with lob fields
3)how to access them

The hardest one being number two - how to insert a file, as a blob, into a table. I have provided you with complete working code for two of those questions.

The least I think you can do ishave an attempt to work out on your own how to do number 3) (how to access them).

Especially when if you even bothered to look at the code I provided, it shows you pretty much how to do it !!!!
I won't bother in the future !

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[onlymullapudi]

I know the code given by u, u r putting a text file, what i want to do is put a video file.

any how thanks for u r reply

 

[sedj]

Errmmm ... why do you say a text file ? Have you actually read the code ? Have you perhaps even tried running the code ?

What do you think these lines are doing :

        FileInputStream fis = new FileInputStream(f);
        byte binaryData[] = new byte[(int)f.length()];
        fis.read(binaryData);

That will read ANY file from disk into a byte array - which is BINARY data - and then inserts this binary data into a BLOB (a BINARY Large Object).

A file is a file - there is no difference between a file that happens to contain text, to a file that happens to contain binary data, such as some video format.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk