Home » RDBMS Server » Server Administration » lob
| lob [message #371810] |
Fri, 08 December 2000 18:58  |
R.Rajan
Messages: 2
Registered: December 2000
|
Junior Member |
|
|
|
How do i store a bitmap in my table using lob? Explain me clearly with an illustration
|
|
|
|
| Re: lob [message #371872 is a reply to message #371810] |
Fri, 15 December 2000 11:23  |
Jeff
Messages: 63
Registered: July 1999
|
Member |
|
|
Assuming you have a bitmap image stored in a file named "image.bmp", the following java code will read the file and write the contents to a BLOB column named IMAGE in table IMAGE_STOR:
Statement stmt = con.createStatement;
//insert the BLOB locator, index is '123'
stmt.execute("INSERT INTO IMAGE_STOR VALUES ('123', empty_blob())");
//select and lock the BLOB row
ResultSet rs = stmt.executeQuery("SELECT IMAGE FROM IMAGE_STOR WHERE IMAGE_INDEX = '123' FOR UPDATE");
rs.next();
//obtain the BLOB locator
BLOB blob = ((OracleResultSet)rs).getBLOB(1);
//get an output stream to the BLOB
OutputStream outStream = blob.getBinaryOutputStream();
//get the bitmap data
File bmpFile = new File("image.bmp");
FileInputStream inStream = new FileInputStream(bmpFile);
//read the input stream and write the output stream by chunks
byte[[]] chunk = new byte(blob.getChunkSize());
int i=-1;
while ((i=inStream.read(chunk)) != -1) {
outStream.write(chunk, 0 , i);
}
//close the streams
inStream.close();
outStream.close();
//close the statement
stmt.close();
...hope this helps
Jeff
|
|
|
|
| Re: lob [message #373292 is a reply to message #371810] |
Tue, 10 April 2001 06:48 |
Jake Wheat
Messages: 3
Registered: April 2001
|
Junior Member |
|
|
Is it possible to do same for a stored procedure - i.e. pass a blob in from JDBC which is then inserted to a table in the stored procedure rather than direct table access?
Presumably the problem here is that there is then no lob to lock for update.
I have acheived this easily using ODBC but JDBC seems not to have an easy way of doing this?
Cheers
Jake
|
|
|
|
| Re: lob [message #373294 is a reply to message #371810] |
Tue, 10 April 2001 11:33 |
Jeff
Messages: 63
Registered: July 1999
|
Member |
|
|
I'm not sure what your specific requirements are, however it may help to keep in mind that all LOB access via JDBC is done through LOB locators. You cannot directly "insert a LOB into a table"; you must obtain the locator of the LOB, and then you may access and manipulate the data to which the LOB locator points.
There are actually two issues here:
1) You cannot pass LOBs as parameters. Oracle instead passes LOB locators. When you declare a variable with datatype BLOB, it can only take the locator of the LOB and not the value of it.
2) As you mentioned, the LOB must be locked prior to updating. I can think of two possibilities for your situation, either you intend to pass the locator to the specific LOB which you are manipulating (which does not seem logical), or you intend to pass the locator to an external LOB, whose data you intend to copy to a newly created LOB in the target table. In the first situation, you would presumably have locked the LOB at the time the locator was obtained, and you would pass that locator along with a set of data which the procedure would write to the LOB. In the second situation, the procedure would need sufficient information to create the row (including the "empty" lob locator), obtain and lock the locator of the "target" LOB, and then read the data from the "input" LOB and write it to the "output" LOB.
I hope this helps. If you can provide more details of your situation, I will try to give you a more specific answer.
Jeff
|
|
|
|
| Re: lob [message #373354 is a reply to message #371810] |
Thu, 12 April 2001 06:41 |
Jake Wheat
Messages: 3
Registered: April 2001
|
Junior Member |
|
|
Thanks very much for you response, here are some more details on my problem.
Oracle 8.1.7 is installed on a i86 running Redhat linux 6.2. I am using the thin driver with JDBC.
I have the following stored procedures:
Procedure binarySPA( rsReturn out tDynamicCursor,
vBinary in BLOB,
vMimeType in VARCHAR2 );
Procedure binarySPB( rsReturn out tDynamicCursor,
vBinary in VARCHAR2,
vMimeType in VARCHAR2 );
I am able to add any binaries I like using the second stored procedure plus upload to table like so:
Connection connection = getConnection();
connection.setAutoCommit(false);
Statement st = null;
ResultSet resultSet = null;
String guid = null;
st = connection.createStatement();
guid = Utils.getUUID();
st.execute ("INSERT INTO tblBinary VALUES (empty_blob(), '" + guid + "')");
resultSet = st.executeQuery("SELECT BINARY FROM tblBinary WHERE GUID='" + guid + "' FOR UPDATE");
resultSet.next();
BLOB blob = ((OracleResultSet)resultSet).getBLOB(1);
OutputStream outstream = blob.getBinaryOutputStream();
int size = blob.getBufferSize();
byte[[]] buffer = new bytesize;
int length = -1;
while ((length = b.read(buffer)) != -1)
outstream.write(buffer, 0, length);
b.close();
b = null;
outstream.close();
and passing the guid to spAddDocBinaryFromTable, which copies the binary from tblBinary and deletes it from tblBinary.
Unfortunately, this requires external table access, which I would like to avoid, but also doubles the time it takes to add
a binary to the database. Since some of our clients work with image files upwards of 100mb, adding using this method current takes more
than ten minutes. I would really like to reduce this time.
I tried creating the blob using the following java code:
====================================
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
import java.io.*;
import java.util.*;
import java.net.*;
public class TestBlob4 {
public static void main(String [[]] argv) {
final int USE_BYTE_ARRAY = 0, USE_OUTPUT_STREAM = 2, USE_TEMPORARY_LOB = 3;
final int TECHNIQUE = USE_TEMPORARY_LOB;
byte [[]] testData = "this is some test data".getBytes();
ByteArrayInputStream testDataStream = new ByteArrayInputStream(testData);
Connection connection = null;
CallableStatement st = null;
ResultSet resultSet = null;
try {
oracle.jdbc.pool.OracleConnectionPoolDataSource dataSource = new oracle.jdbc.pool.OracleConnectionPoolDataSource();
dataSource.setDriverType("thin");
dataSource.setServerName("xxx");
dataSource.setDatabaseName("xxx");
dataSource.setPortNumber(xxx);
dataSource.setUser("xxx");
dataSource.setPassword("xxx");
connection = dataSource.getConnection();
connection.setAutoCommit(false);
//***********************************************
String stString = "{call XXX.binarySPA(:1,:2,:3)}";
st = connection.prepareCall(stString);
st.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
BLOB blob;
switch (TECHNIQUE) {
case USE_BYTE_ARRAY:
blob = new BLOB((OracleConnection)connection, testData);
st.setBlob(2, blob);
break;
case USE_OUTPUT_STREAM:
blob = new BLOB((OracleConnection)connection);
uploadToBlob(testDataStream, blob);
st.setBlob(2, blob);
break;
case USE_TEMPORARY_LOB:
blob = BLOB.empty_lob();
String getString = "{call DBMS_LOB.CreateTemporary(:1,:2)}";
st = connection.prepareCall(getString);
st.setBlob(1, blob);
st.setBoolean(2, false);
st.execute();
uploadToBlob(testDataStream, blob);
st.setBlob(6, blob);
break;
}
st.setObject(3, "text/plain");
st.execute();
resultSet = (ResultSet)st.getObject(1);
resultSet.next();
System.out.println(resultSet.getString(1));
connection.commit();
} catch (Exception e) {
e.printStackTrace();
if (connection != null)
try {
connection.rollback();
} catch (SQLException err) {
err.printStackTrace();
}
} finally {
try {
if (resultSet != null) resultSet.close();
if (st != null) st.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private static void uploadToBlob(InputStream is, BLOB blob) throws SQLException, IOException {
OutputStream outstream = blob.getBinaryOutputStream();
int size = blob.getBufferSize();
byte[[]] buffer = new bytesize;
int length = -1;
while ((length = is.read(buffer)) != -1)
outstream.write(buffer, 0, length);
outstream.close();
is.close();
}
}
====================================
But I get the following errors:
case USE_OUTPUT_STREAM:
java.lang.NullPointerException
at oracle.sql.Datum.getBytes(Datum.java:147)
at oracle.jdbc.driver.OraclePreparedStatement.setDatum(OraclePreparedStatement.java:1408)
at oracle.jdbc.driver.OraclePreparedStatement.setBLOB(OraclePreparedStatement.java:1431)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2013)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2052)
at oracle.sql.LobPlsqlUtil.plsql_getChunkSize(LobPlsqlUtil.java:1201)
at oracle.sql.LobPlsqlUtil.plsql_getChunkSize(LobPlsqlUtil.java:121)
at oracle.jdbc.dbaccess.DBAccess.getLobChunkSize(DBAccess.java:955)
at oracle.sql.LobDBAccessImpl.getChunkSize(LobDBAccessImpl.java:111)
at oracle.sql.BLOB.getChunkSize(BLOB.java:228)
at oracle.sql.BLOB.getBufferSize(BLOB.java:242)
at oracle.sql.BLOB.getBinaryOutputStream(BLOB.java:202)
at TestBlob4.uploadToBlob(TestBlob4.java:101)
at TestBlob4.main(TestBlob4.java:55)
case USE_BYTE_ARRAY:
java.sql.SQLException: ORA-22281: cannot perform operation with an updated locator
ORA-06512: at "XXX.XXX", line XXX
ORA-06512: at line 1
The PL/SQL code is just an assignment:
lBinary := vBinary;
I get the same error when I try an insert the blob into a table.
case USE_TEMPORARY_LOB:
PLS-00306: wrong number or types of arguments in call to 'CREATETEMPORARY'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
I can't find any other way to get a blob into the database.
Any help is much appreciated, as my colleagues and I have been having a nightmare trying to figure this stuff out.
Jake.
|
|
|
|
| Re: lob [message #373366 is a reply to message #373294] |
Thu, 12 April 2001 14:53 |
Jeff
Messages: 63
Registered: July 1999
|
Member |
|
|
I will try to address each of of the 3 techniques that you attempt in your example, and the errors that you are encountering:
First, your USE_BYTE_ARRAY case: in this code, you create a locator to a temporary LOB, and use this locator to populate the LOB. With LOB buffering enabled, if the buffering subsystem is not flushed, the locator is considered an updated locator. You cannot copy from this LOB due to the fact that an updated locator cannot be the source of a copy operation. Also, due to the way that Oracle implements temporary LOBs, each locator actually points to its own copy of the temporary LOB, thus when your statement lBinary := vBinary is executed, the database actually attempts to make a copy of the temporary LOB that lBinary points to, and set vBinary to point to this new copy.
Second, your USE_OUTPUT_STREAM case: in this code, you essentially are creating a locator that points to a new uninitialized temporary LOB. Temporary LOBs do not implement the concept of initialized, but empty, LOBs. Since the LOB is not properly initialized, subsequent operations fail.
Finally, your USE_TEMPORARY_LOB case: the DBMS_LOB.CREATETEMPORARY method takes a 3rd parameter indicating "duration" of the temporary LOB (value is one of: CALL, TRANSACTION, or SESSION).
Of the 3 methods that you are trying, the 3rd is probably the most promising. In general, I would suggest avoiding directly instantiating the LOB classes. In addition, it would be a good idea to use OracleCallableStatement rather than CallableStatement to ensure that variables are properly bound. With some tweaking, you should theoretically be able to create and populate a temporary LOB and pass the locator to your stored procedure, then have your stored procedure obtain a locator to the persistent LOB and copy the data from the temporary LOB to the persistent LOB. This avoids external table access however, unfortunately, I would not expect that there will be much performance gain over what you encountered with copying between persistent LOBs.
|
|
|
|
| Re: lob [message #373434 is a reply to message #371810] |
Tue, 17 April 2001 04:28 |
Jake Wheat
Messages: 3
Registered: April 2001
|
Junior Member |
|
|
Thanks for your help, Jeff, I have found out the problem with my createtempory jdbc code, it wanted an int in the first argument and not a blob. I am sucessfully using the following code:
OracleCallableStatement cst = (OracleCallableStatement)connection.prepareCall("begin dbms_lob.createtemporary(?,false);end;");
cst.registerOutParameter(1,Types.BLOB);
cst.execute();
BLOB blob = (BLOB)cst.getBlob(1);
I have had no problems populating or passing this blob to stored procedures.
Once again, thanks very much for your help,
Jake.
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 01:03:25 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
