(三)用jdbc处理lob
exmple 4.
首先是Getting BLOB and CLOB Locators from a Result Set
// Select LOB locator into standard result set.
ResultSet rs =stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");
while (rs.next())
{// Get LOB locators into Java wrapper classes.
oracle.jdbc2.Blob blob = (oracle.jdbc2.Blob)rs.getObject(1);
oracle.jdbc2.Clob clob = (oracle.jdbc2.Clob)rs.getObject(2);
[...process...]
}
然后是Read BLOB data from BLOB locator.
InputStream byte_stream = my_blob.getBinaryStream();
byte [] byte_array = new byte [10];
int bytes_read = byte_stream.read(byte_array);
和Writing BLOB Data
java.io.OutputStream outstream;
// read data into a byte array
byte[] data = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
// write the array of binary data to a BLOB
outstream = ((BLOB)my_blob).getBinaryOutputStream();
outstream.write(data);
还有Passing a BLOB Locator to a Prepared Statement
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
"INSERT INTO blob_table VALUES(?)");
ops.setBLOB(1, my_blob);
ops.execute();
最后应该注意：
insert的时候一定要用empty_blob()初始化
stmt.execute ("insert into my_blob_table values ('row1', empty_blob()"); （四）在pro*c中的处理
PRO*C可以用三种方式对LOB字段处理。
1、The DBMS_LOB package inside PL/SQL blocks.
2、OCI (Oracle Call Interface) function calls.
3、Embedded SQL statements.
Embedded SQL statements.的方式简单而且比较灵活。OTN上提供一个例子：
In this example we will be reading data from a BLOB with an unknown arbitrary length into a buffer and then writing the data from the buffer into an external file.
Our buffer is small, so depending on the size of the BLOB we are reading, we may
be able to read the BLOB value into the buffer in a single READ statement or we
may be required to utilize a standard polling method instead.
First we start off with oci.h and some simple local variable declarations
example 5.
#include <oci.h>
OCIBlobLocator *blob ;
FILE *fp ;
unsigned int amt, offset = 1 ;
Now we need a buffer to store the BLOB value and then write to the file from:
#define MAXBUFLEN 5000
unsigned char buffer[MAXBUFLEN] ;
EXEC SQL VAR buffer IS RAW(MAXBUFLEN) ;
Allocate the BLOB host variable and select a BLOB which we will READ:
EXEC SQL ALLOCATE :blob ;
EXEC SQL SELECT a_blob INTO :blob FROM lob_table WHERE ... ;
We can then open the external file to which we will write the BLOB value:
fp = fopen((const char *)"image.gif", (const char *)"w") ;
If the buffer can hold the entire LOB value in a single READ we need to catch the
NOT FOUND condition to signal LOB READ termination:
EXEC SQL WHENEVER NOT FOUND GOTO end_of_lob ;
Now do our first READ.We set the amount to the maximum value of 4 Gigabytes. It
is larger than our buffer so if the LOB doesn't fit we will READ using a polling
mode:
amt = 4294967295 ;
EXEC SQL LOB READ :amt FROM :blob AT ffset INTO :buffer ;
If we get here then it means that the buffer was not large enough to hold the entire
LOB value, so we must write what we have using binary I/O and continue reading:
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ;
We use a standard polling method to continue reading with the LOB READ inside
of an infinite loop. We can set up the NOT FOUND condition to terminate the loop:
EXEC SQL WHENEVER NOT FOUND DO break ;
while (TRUE)
{
During polling, the offset is not used so we can omit it in subsequent LOB READs.
We need the amount, however, because it will tell us how much was READ in the
last READ invocation
EXEC SQL LOB READ :amt FROM :blob INTO :buffer ;
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ;
}
Here, we have reached the end of the LOB value. The amount holds the amount of
the last piece that was READ. During polling, the amount for each interim piece
was set to MAXBUFLEN, or the maximum size of our buffer:
end_of_lob:
(void) fwrite((void *)buffer, (size_t)amt, (size_t)1, fp) ;
（五） 在delphi中的处理
对于lob字段而言，个人认为其使用比long类型有很大的灵活性，而且lob字段可以保存各类的数据，可以保存图片，大量的文字，现就clob跟blob两种类型加以说明，其中blob保存图片信息，clob保存大量文字。
exmple 6.
Create table test_table
(c_no number(1) not null,
c_blob blob,
c_clob clob,
constraint pk_test_table primary key (c_no)); unit Unit1; interface uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DBCtrls, Grids, DBGrids, DB, DBTables, ExtDlgs; type
TForm1 = class(TForm)
Database1: TDatabase; //用于连接数据库
Table1: TTable; //获取表信息
DataSource1: TDataSource;
DBGrid1: TDBGrid;
DBMemo1: TDBMemo; //显示c_clob字段内容
DBImage1: TDBImage; //显示c_blob字段内容
Button1: TButton; //插入按钮
Button2: TButton; //保存按钮
Table1C_NO: TFloatField; //Tfiled
Table1C_BLOB: TBlobField;
Table1C_CLOB: TMemoField;
OpenPictureDialog1: TOpenPictureDialog; //从文件获取图片
OpenDialog1: TOpenDialog; //从文件获取文字
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end; var
Form1: TForm1; implementation {$R *.dfm} procedure TForm1.Button1Click(Sender: TObject);
begin //插入操作
with Table1 do
begin
Insert; //将表状态置为插入状态
if OpenPictureDialog1.Execute then //获得图片信息
Table1C_BLOB.LoadFromFile(OpenPictureDialog1.FileName);
if OpenDialog1.Execute then //获得文字信息
Table1C_CLOB.LoadFromFile(OpenDialog1.FileName);
end;
end; procedure TForm1.Button2Click(Sender: TObject);
begin //提交插入内容
try
Table1.Post;
except
Application.MessageBox('错误发生','警告',0);
end;
end; end. 注意：
openpiceturedilog只能打开dmp,ico,wmf等文件，事先需要将图片文件格式保存成这几类；
在文字字段不从文件获得时，可以手动输入
本例只是对lob字段的一个小小的探索，用法不当及需改正之处，还请多多指教。 注:本篇文章大部分例子均取自论坛，如有侵犯您的版权，请来信告知，我会做相应处理。
注意：
openpiceturedilog只能打开dmp,ico,wmf等文件，事先需要将图片文件格式保存成这几类；
在文字字段不从文件获得时，可以手动输入
exmple 4.
首先是Getting BLOB and CLOB Locators from a Result Set
// Select LOB locator into standard result set.
ResultSet rs =stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");
while (rs.next())
{// Get LOB locators into Java wrapper classes.
oracle.jdbc2.Blob blob = (oracle.jdbc2.Blob)rs.getObject(1);
oracle.jdbc2.Clob clob = (oracle.jdbc2.Clob)rs.getObject(2);
[...process...]
}
然后是Read BLOB data from BLOB locator.
InputStream byte_stream = my_blob.getBinaryStream();
byte [] byte_array = new byte [10];
int bytes_read = byte_stream.read(byte_array);
和Writing BLOB Data
java.io.OutputStream outstream;
// read data into a byte array
byte[] data = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
// write the array of binary data to a BLOB
outstream = ((BLOB)my_blob).getBinaryOutputStream();
outstream.write(data);
还有Passing a BLOB Locator to a Prepared Statement
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement
"INSERT INTO blob_table VALUES(?)");
ops.setBLOB(1, my_blob);
ops.execute();
最后应该注意：
insert的时候一定要用empty_blob()初始化
stmt.execute ("insert into my_blob_table values ('row1', empty_blob()"); （四）在pro*c中的处理
PRO*C可以用三种方式对LOB字段处理。
1、The DBMS_LOB package inside PL/SQL blocks.
2、OCI (Oracle Call Interface) function calls.
3、Embedded SQL statements.
Embedded SQL statements.的方式简单而且比较灵活。OTN上提供一个例子：
In this example we will be reading data from a BLOB with an unknown arbitrary length into a buffer and then writing the data from the buffer into an external file.
Our buffer is small, so depending on the size of the BLOB we are reading, we may
be able to read the BLOB value into the buffer in a single READ statement or we
may be required to utilize a standard polling method instead.
First we start off with oci.h and some simple local variable declarations
example 5.
#include <oci.h>
OCIBlobLocator *blob ;
FILE *fp ;
unsigned int amt, offset = 1 ;
Now we need a buffer to store the BLOB value and then write to the file from:
#define MAXBUFLEN 5000
unsigned char buffer[MAXBUFLEN] ;
EXEC SQL VAR buffer IS RAW(MAXBUFLEN) ;
Allocate the BLOB host variable and select a BLOB which we will READ:
EXEC SQL ALLOCATE :blob ;
EXEC SQL SELECT a_blob INTO :blob FROM lob_table WHERE ... ;
We can then open the external file to which we will write the BLOB value:
fp = fopen((const char *)"image.gif", (const char *)"w") ;
If the buffer can hold the entire LOB value in a single READ we need to catch the
NOT FOUND condition to signal LOB READ termination:
EXEC SQL WHENEVER NOT FOUND GOTO end_of_lob ;
Now do our first READ.We set the amount to the maximum value of 4 Gigabytes. It
is larger than our buffer so if the LOB doesn't fit we will READ using a polling
mode:
amt = 4294967295 ;
EXEC SQL LOB READ :amt FROM :blob AT ffset INTO :buffer ;
If we get here then it means that the buffer was not large enough to hold the entire
LOB value, so we must write what we have using binary I/O and continue reading:
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ;
We use a standard polling method to continue reading with the LOB READ inside
of an infinite loop. We can set up the NOT FOUND condition to terminate the loop:
EXEC SQL WHENEVER NOT FOUND DO break ;
while (TRUE)
{
During polling, the offset is not used so we can omit it in subsequent LOB READs.
We need the amount, however, because it will tell us how much was READ in the
last READ invocation
EXEC SQL LOB READ :amt FROM :blob INTO :buffer ;
(void) fwrite((void *)buffer, (size_t)MAXBUFLEN, (size_t)1, fp) ;
}
Here, we have reached the end of the LOB value. The amount holds the amount of
the last piece that was READ. During polling, the amount for each interim piece
was set to MAXBUFLEN, or the maximum size of our buffer:
end_of_lob:
(void) fwrite((void *)buffer, (size_t)amt, (size_t)1, fp) ;
（五） 在delphi中的处理
对于lob字段而言，个人认为其使用比long类型有很大的灵活性，而且lob字段可以保存各类的数据，可以保存图片，大量的文字，现就clob跟blob两种类型加以说明，其中blob保存图片信息，clob保存大量文字。
exmple 6.
Create table test_table
(c_no number(1) not null,
c_blob blob,
c_clob clob,
constraint pk_test_table primary key (c_no)); unit Unit1; interface uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DBCtrls, Grids, DBGrids, DB, DBTables, ExtDlgs; type
TForm1 = class(TForm)
Database1: TDatabase; //用于连接数据库
Table1: TTable; //获取表信息
DataSource1: TDataSource;
DBGrid1: TDBGrid;
DBMemo1: TDBMemo; //显示c_clob字段内容
DBImage1: TDBImage; //显示c_blob字段内容
Button1: TButton; //插入按钮
Button2: TButton; //保存按钮
Table1C_NO: TFloatField; //Tfiled
Table1C_BLOB: TBlobField;
Table1C_CLOB: TMemoField;
OpenPictureDialog1: TOpenPictureDialog; //从文件获取图片
OpenDialog1: TOpenDialog; //从文件获取文字
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end; var
Form1: TForm1; implementation {$R *.dfm} procedure TForm1.Button1Click(Sender: TObject);
begin //插入操作
with Table1 do
begin
Insert; //将表状态置为插入状态
if OpenPictureDialog1.Execute then //获得图片信息
Table1C_BLOB.LoadFromFile(OpenPictureDialog1.FileName);
if OpenDialog1.Execute then //获得文字信息
Table1C_CLOB.LoadFromFile(OpenDialog1.FileName);
end;
end; procedure TForm1.Button2Click(Sender: TObject);
begin //提交插入内容
try
Table1.Post;
except
Application.MessageBox('错误发生','警告',0);
end;
end; end. 注意：
openpiceturedilog只能打开dmp,ico,wmf等文件，事先需要将图片文件格式保存成这几类；
在文字字段不从文件获得时，可以手动输入
本例只是对lob字段的一个小小的探索，用法不当及需改正之处，还请多多指教。 注:本篇文章大部分例子均取自论坛，如有侵犯您的版权，请来信告知，我会做相应处理。
注意：
openpiceturedilog只能打开dmp,ico,wmf等文件，事先需要将图片文件格式保存成这几类；
在文字字段不从文件获得时，可以手动输入
本人怎么会有那么高水平呢~
同时有 PB/VB/DELPHI/JDBC/Pro*c 的例子~但绝对不是从 google 里搜索出来的~!!!
是从我硬盘里 COPY 出来的(我也忘记是什么时候收藏的 :-0 )反正,好东西就要共享~ :-)
http://211.99.196.144:8090/forum1/frontshow/index3.jsp?boardid=3&discription=OTN+%B3%C9%D4%B1%B7%FE%CE%F1
吧. 我等你的贴... :-)