Wednesday, November 29, 2006

Get Bolder with LOBs Manipulation in Java/JDBC

The Oracle database supports very large CLOB/BLOB columns/data however, the average Java/JDBC applications manipulates mostly medium size LOBs of tens/hundreds Kbytes in size.

WTF with LOB Programming in Java/JDBC

If you have ever dealt with LOBs in Java/JDBC applications, you know that it is not always straightforward, and sometime difficult to program cleanly. Let's take a closer look at the currently available approaches with Orace JDBC.

3 Ways to Slice and Dice LOBs

As of 10g Release 2, Oracle JDBC furnishes 3 ways of accessing and manipulating LOBs including:Oracle's LOB methods, optimized standard LOB methods, and LONG Streaming. Which one to use and when?In this article, i want to shed some light so as to make the reader more comfortable, bolder, when using CLOBs, BLOBs, and BFILE in Java/JDBC applications.

Oracle LOB Approach
LOB manipulation is done in two steps

Step#1: Manipulate the LOB Locator

Create or Retrieve, Insert, and Update the LOB locator using createTemporary, getEmptyCLOB, getEmptyBLOB methods. setBlob, setBLOB, setClob, setCLOB, getBlob, getBLOB, getClob, updateBlob, updateClob, setBLOB, setBLOBATName, setBFILE, updateBLOB, …, and updateCLOB methods.

ResultSet rs = stmt.executeQuery(“SELECT blobcol from XobTab”;
...
BLOB BlobLoc = ((OracleResultSet)rs).getBLOB(1);

Step#2: Manipulate the LOB Content

Write LOB content using getString, getSubString, and getBytes, setStrings, and putBytes methods.

tempLob.putBytes (1, bytarr);
PreparedStatement pstmt = conn.prepareStatement ( "insert into XobTab values (?)”;
((OraclePreparedStatement)ps).setBLOB (1, tempLob);

Read the LOB contents and Stream the content out, using getBinaryStream, get-String, getCharacterStream, and getAsciiStream.

byte[] bytarr = new byte[20]; // 20 bytes chunk
BLOB LOBLoc = ps.getBLOB (1);
InputStream is = LOBLoc.getBinaryStream();
while ((length = stream.read(bytarr)) != -1)
{
// process the chunk
bytarr = new byte[10];
}
is.close();

In pre-10g releases, JDBC-Thin used the PL/SQL DBMS_LOB package for LOB operations; in 10g, it uses a more nativead faster protocol however, certain operations such as hasPattern and isSubLob are still done via the dbms_lob package.

Standard and Optimized LOB Approach

For LOBs of size less than 2 gigabytes, LOB manipulation has been simplified especially in 10g Release 2) by removing the need to explicitly manage the locator. The standard methods getBytes, getBinaryStream, getString, getCharacterStream, getAsciiStream setBytes, setBinaryStream, setString, setCharacterStream, and setAsciiStream have been extended to take BLOB, CLOB and BFILE columns as parameters. This approach simplifies LOB programming; under the covers, the driver does the right thing (i.e., taking care of the locator, etc).

Example: Strings or byte arrays can be stored/read into/from the database, by simply using setString or setBytes, getString or getBytes.

LONG Streaming Approach

LONG streaming is furnished to help faster retrieval of data from a LOB without all the LOB overhead (i.e., retrieving the lob locators and going through the lob protocol), which can be very costly for small data. All you need to do is define the column as a LONG, then the driver takes care of LOB to LONG conversion and vice versa. This technique is faster than the locator approach (up to an order ofmagnitude) however, it is not as flexible as the traditional or optimized LOB manipulation and has the following restrictions: LONG streaming always starts at the beginning of the LOB (i.e., you cannot position or start anywhere); you may read as much data as you wish but only a single pass is allowed (i.e., you cannot read the stream twice).
Using this approach, CLOBs can be declared as LONGVARCHAR using defineColumnType. and streamed using getAsciiStream by redefining it a as
(OracleStatement)stmt.defineColumnType(,Types.LONGVARCHAR);

Similarly BLOBs columns can be declared as LONGVARBINARY using defineColumnType then streamed using getBinaryStream
(OracleStatement)stmt.defineColumnType(, Types.LONGVARBINARY);

Example:
// Pretend that these columns are of LONG and LONG RAW types
(OracleStatement) stmt.defineColumnType(1,Types.LONGVARBINARY);
(OracleStatement)stmt.defineColumnType(2,Types.LONGVARCHAR);
...
ResultSet rs = stmt.executeQuery(“select LOBCOL from XOBTab”);
// Retrieving a Clob with getString");
ocrs.setCommand ("SELECT lobcol FROM XOBTab");
ocrs.execute ();
while (ocrs.next ())
{
bytarr = stmt.getString (1));
}
ocrs.close ();

// Retrieving a Blob with getBytes");
ocrs.execute ();
while (ocrs.next ())
{
ocrs.getBytes (2).length);
}

Get More

This article, excerpted from my book, summarizes the various approaches to LOB manipulation using Oracle JDBC drivers. You can find more details and code snippets in either the Oracle JDBC 10g R2 doc or in chapter 8 of my book.

52 comments:

Jack said...

Kuassi,

Have you ever run across a Java Applet (deployed in a browser) that lets you maintain a CLOB column in the database?

Thanks,
Jack

Kuassi Mensah said...

Jack,

Can you elaborate a bit more because Applets must use JDBc-Thin, which lets you maintain CLOB.

Have you experienced otherwise?

Kuassi

Jack said...

Kuassi,

Yes, I have your book (excellent!) and it confirmed what I understood to be the correct way to handle this (with JDBC/Applets). I'm always glad to find a consensus on the correct way to program something... it is a continual frustration for me to seek out the "best practice" approach, what with all the options available to solve a problem now-a-days.

Your coverage in Chapter 8 on JDBC and LOBs seems complete enough, but I am still a novice with Java/J2EE (however, I'm an expert at PL/SQL). I am looking for a working example of a CLOB Editor based on a Java applet. It seems to me that this would be the ideal solution to the problem I'm running across with my mod_plsql version of a CLOB Editor... namely the 32K limit (or less with multibyte character sets) on VARCHAR2 data passed as a PL/SQL formal parameter.

I found a very nice CLOB Editor but, alas, it is only Windows based. I really need a pure browser based one. With a lot of elbow grease I'm sure I could develop one in Java, but I'm too far behind the eight ball in my learning curve and I think it would just take too long. Have you run across a piece of code that handles this?

Thanks,
Jack
(sorry for the late reply)

Jack said...

Kuassi,

Any comments?

Jack

Kuassi Mensah said...

Jack,

Unfortunately, i could not find any CLOB editor other than the one you've already found.

Kuassi

Don Hunt said...

This is an extremely helpful post, thank you very much.

One question. Is there anyway to get this optimization 'Long Streaming Approach' when reading from a BLOB column that is part of a cursor (ResultSet) returned from a stored procedure?

Anonymous said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,按摩棒,震動按摩棒,微調按摩棒,情趣按摩棒,逼真按摩棒,G點,跳蛋,跳蛋,跳蛋,性感內衣,飛機杯,充氣娃娃,情趣娃娃,角色扮演,性感睡衣,SM,潤滑液,威而柔,香水,精油,芳香精油,自慰套,自慰,性感吊帶襪,吊帶襪,情趣用品加盟AIO交友愛情館,情人歡愉用品,美女視訊,情色交友,視訊交友,辣妹視訊,美女交友,嘟嘟成人網,成人網站,A片,A片下載,免費A片,免費A片下載愛情公寓,情色,舊情人,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,一葉情貼圖片區,情色小說,色情,色情遊戲,情色視訊,情色電影,aio交友愛情館,色情a片,一夜情,辣妹視訊,視訊聊天室,免費視訊聊天,免費視訊,視訊,視訊美女,美女視訊,視訊交友,視訊聊天,免費視訊聊天室,情人視訊網,影音視訊聊天室,視訊交友90739,成人影片,成人交友,美女交友,微風成人,嘟嘟成人網,成人貼圖,成人電影,A片,豆豆聊天室,聊天室,UT聊天室,尋夢園聊天室,男同志聊天室,UT男同志聊天室,聊天室尋夢園,080聊天室,080苗栗人聊天室,6K聊天室,女同志聊天室,小高聊天室,上班族聊天室,080中部人聊天室,同志聊天室,聊天室交友,中部人聊天室,成人聊天室,一夜情聊天室,情色聊天室,寄情築園小遊戲情境坊歡愉用品,情趣用品,成人網站,情人節禮物,情人節,AIO交友愛情館,情色,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,七夕情人節,色情,情色電影,色情網站,辣妹視訊,視訊聊天室,情色視訊,免費視訊聊天,美女視訊,視訊美女,美女交友,美女,情色交友,成人交友,自拍,本土自拍,情人視訊網,視訊交友90739,生日禮物,情色論壇,正妹牆,免費A片下載,AV女優,成人影片,色情A片,成人論壇,情趣,免費成人影片,成人電影,成人影城,愛情公寓,成人影片,保險套,舊情人,微風成人,成人,成人遊戲,成人光碟,色情遊戲,跳蛋,按摩棒,一夜情,男同志聊天室,肛交,口交,性交,援交,免費視訊交友,視訊交友,一葉情貼圖片區,性愛,視訊,視訊聊天,A片,A片下載,免費A片,嘟嘟成人網,寄情築園小遊戲,女同志聊天室,免費視訊聊天室,一夜情聊天室,聊天室

vuong said...

先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
先物 比較
事業再生
経営計画書
経営改革
経営改善
会社分割
経営コンサルティング
お見合いパーティー 大阪
お見合いパーティー 大阪
お見合いパーティー 大阪
お見合いパーティー 大阪
お見合いパーティー 大阪
お見合いパーティー 大阪
お見合いパーティー 大阪
お見合いパーティー 大阪
お見合いパーティー 大阪
お見合いパーティー 大阪
浮気調査 東京
浮気調査 東京
備考調査 追跡調査 東京
不倫調査 離婚調査 東京
浮気調査 素行調査 東京 
ストーカー相談 ストーカー対策 東京
結婚調査 結婚詐欺 東京
信用調査 企業調査 東京
調査料金 東京
身上調査 身元調査 身辺調査 東京
所在調査 東京
夫 妻 浮気 東京

r4 card said...

I am looking for a working example of a CLOB Editor based on a Java applet. It seems to me that this would be the ideal solution to the problem I'm running across with my mod_plsql version of a CLOB Editor.

wedding reception las vegas said...

I really need a pure browser based one. With a lot of elbow grease I'm sure I could develop one in Java, but I'm too far behind the eight ball in my learning curve and I think it would just take too long.

lakeside said...

All you need to do is define the column as a LONG, then the driver takes care of LOB to LONG conversion and vice versa. This technique is faster than the locator approach.Las Vegas Lakeside Weddings

las vegas themed weddings said...

All our laptop AC adapters are brand new, with the excellent service from our customer service team.
the most convenient and cheap battery online shop in uk.

concrete batch mix plant said...

This technique is faster than the locator approach (up to an order ofmagnitude) however, it is not as flexible as the traditional or optimized LOB manipulation and has the following restrictions.

concrete batch mix plant said...

The viability and profitability of the algae installation will also depend on how the biomass bi-product is exploited.

Mobile Concrete Batching Plant said...

LONG streaming is furnished to help faster retrieval of data from a LOB without all the LOB overhead (i.e., retrieving the lob locators and going through the lob protocol), which can be very costly for small data.

portable batch plant said...

I'm always glad to find a consensus on the correct way to program something... it is a continual frustration for me to seek out the "best practice" approach, what with all the options available to solve a problem now-a-days.

concrete batch plant said...

Write LOB content using getString, getSubString, and getBytes, setStrings, and putBytes methods.

asphalt mix plant said...

This technique is faster than the locator approach (up to an order ofmagnitude) however, it is not as flexible as the traditional or optimized LOB manipulation and has the following restrictions.

Mobile Concrete Batching Plant said...

It is a continual frustration for me to seek out the "best practice" approach, what with all the options available to solve a problem now-a-days.

Pneumatic Cylinder said...

This technique is faster than the locator approach (up to an order ofmagnitude) however, it is not as flexible as the traditional or optimized LOB manipulation and has the following restrictions.

Pneumatic Solenoid Valves said...

The viability and profitability of the algae installation will also depend on how the biomass bi-product is exploited.

Pneumatic Filters said...

I am looking for a working example of a CLOB Editor based on a Java applet. It seems to me that this would be the ideal solution to the problem I'm running across with my mod_plsql version of a CLOB Editor.

soil stabilization plant said...

I'm always glad to find a consensus on the correct way to program something... it is a continual frustration for me to seek out the "best practice" approach, what with all the options available to solve a problem now-a-days

bitumen sprayer truck said...

With a lot of elbow grease I'm sure I could develop one in Java, but I'm too far behind the eight ball in my learning curve and I think it would just take too long.

mixing concrete said...

All you need to do is define the column as a LONG, then the driver takes care of LOB to LONG conversion and vice versa.

Concrete batch mix plant said...

It seems to me that this would be the ideal solution to the problem I'm running across with my mod_plsql version of a CLOB Editor.

Mobile Concrete Batching Plant said...

With a lot of elbow grease I'm sure I could develop one in Java, but I'm too far behind the eight ball in my learning curve and I think it would just take too long.

Wet mix macadam plant said...

AC adapters are brand new, with the excellent service from our customer service team.
the most convenient and cheap battery online shop in uk.

Data Entry India said...

It is only Windows based. I really need a pure browser based one.

Data Entry Outsourcing said...

With a lot of elbow grease I'm sure I could develop one in Java, but I'm too far behind the eight ball in my learning curve and I think it would just take too long.

Outsourcing Transcription Services said...

I'm sure I could develop one in Java, but I'm too far behind the eight ball in my learning curve and I think it would just take too long.

asphalt mix plant said...

It is a continual frustration for me to seek out the "best practice" approach, what with all the options available to solve a problem now-a-days.

Mobile Concrete Batching Plant said...

Also how quickly the system can respond to a change in demand might vary considerably across different storage technologies.

Mobile Concrete Batching Plant said...

Have you ever run across a Java Applet (deployed in a browser) that lets you maintain a CLOB column in the database?

bitumen sprayer said...

I could develop one in Java, but I'm too far behind the eight ball in my learning curve and I think it would just take too long.

concrete batch plant said...

It is not as flexible as the traditional or optimized LOB manipulation and has the following restrictions.

portable batch plant said...

All our products are brand new, with the excellent service from our laptop battery of customer service team.

Mobile Concrete Batching Plant said...

This approach simplifies LOB programming; under the covers, the driver does the right thing (i.e., taking care of the locator, etc).

Asphalt Drum Mix Plant said...

The viability and profitability of the algae installation will also depend on how the biomass bi-product is exploited.

Wet Mix Plant said...

it is a continual frustration for me to seek out the "best practice" approach, what with all the options available to solve a problem now-a-days.

Concrete Batch Mix Plant said...

This technique is faster than the locator approach (up to an order ofmagnitude) however, it is not as flexible as the traditional or optimized LOB manipulation and has the following restrictions.

asphalt mix plant said...

Similarly BLOBs columns can be declared as LONGVARBINARY using defineColumnType then streamed using getBinaryStream.

data entry India said...



As of 10g Release 2, Oracle JDBC furnishes 3 ways of accessing and manipulating LOBs including:Oracle's LOB methods, optimized standard LOB methods, and LONG Streaming.

concrete batch plant said...

If you have ever dealt with LOBs in Java/JDBC applications, you know that it is not always straightforward, and sometime difficult to program cleanly.

concrete batch plant said...

I am looking for a working example of a CLOB Editor based on a Java applet.

Honda Motorcycle Fairings said...

The Oracle JDBC 10g drivers, more specifically it's connection cache (a.k.a. Implicit Connection Cache) leverages RAC by subscribing to the following events and status.

data entry india said...

It is a continual frustration for me to seek out the "best practice" approach, what with all the options available to solve a problem now-a-days.

data entry india said...

With a lot of elbow grease I'm sure I could develop one in Java, but I'm too far behind the eight ball in my learning curve and I think it would just take too long.

Data Entry Outsourcing said...

i want to shed some light so as to make the reader more comfortable, bolder, when using CLOBs, BLOBs, and BFILE in Java/JDBC applications.

Data Entry Outsourcing said...

Can you elaborate a bit more because Applets must use JDBc-Thin, which lets you maintain CLOB.

Data Entry Outsourcing said...

The Oracle database supports very large CLOB/BLOB columns/data however, the average Java/JDBC applications manipulates mostly medium size LOBs of tens/hundreds Kbytes in size.

KITS Technologies said...

Hey, thanks for the article post.Really looking forward to read more. Really Great.
selenium online trainings
selenium trainings