to beckham: 确实如此,我试过也不能插入4000B以上的内容。也许应该用特定的方法插入,但仅用insert into是不行的。我手头上没有操作CLOB的资料。 to 棒棒糖: 我用ORACLE的ODBC驱动也不行。 to 谈趣者: 难道只能用BLOB了?LOB好象操作起来很麻烦,能给个用ODBC操作的例子吗?谢谢。
有谁能提供ASP下的CLOB操作方法吗?
ASP与VB关系很大,你可以自己将下面的程序改成ASP 存:Dim Chunk() As Byte If Imageabove.Tag <> "" Then 'Dim Chunk() As Byte Chunk() = Image2Chunk(tmpFile1) .Fields("tp1").AppendChunk Chunk() .UpdateBatch adAffectCurrent End IfPrivate Function Image2Chunk(Filename As String) As Variant On Error GoTo ProcErr Dim Datafile As Integer Dim FileLength As Long Dim Chunk() As Byte
Datafile = FreeFile Open Filename For Binary Access Read As Datafile FileLength = LOF(Datafile) If FileLength = 0 Then GoTo ProcErr ReDim Chunk(FileLength) Get Datafile, , Chunk() Close Datafile
ProcExit: Image2Chunk = Chunk() Exit FunctionProcErr: Image2Chunk = 0 End Function取: If (rs.Fields("tp1").ActualSize = 0) Then Image1.Picture = Nothing 'Exit Sub Else 'Dim Chunk() As Byte Chunk() = rs.Fields("tp1").GetChunk(rs.Fields("tp1").ActualSize) Set Image1.Picture = Chunk2Image(Chunk(), "temp.fil") Image1.Tag = "hello" End IfPublic Function Chunk2Image(Chunk() As Byte, Optional Filename As String) As Variant On Error GoTo ProcErr Dim KeepFile As Boolean Dim Datafile As Integer KeepFile = True If Trim(Filename) = "" Then Filename = "c:\tmpxxdb.fil" KeepFile = False End If Datafile = FreeFile Open Filename For Binary Access Write As Datafile Put Datafile, , Chunk() Close DatafileProcExit: Set Chunk2Image = LoadPicture(Filename) On Error Resume Next ' If Not KeepFile Then Kill filename Exit FunctionProcErr: On Error Resume Next Kill Filename Chunk2Image = 0 End Function
通过JDBC访问oracle数据库的CLOB类型 1. 关于CLOB(Character Large Object) CLOB可用于存放大文本数据,最多可存储4GB数据,在应用开发中比较常见.java提供的sql.Clob类与之对应.它提供两个方法来读取Clob的数据: getCharacterStream() 方法返回按unicode编码的输入流(java.io.Reader对象) getAsciiStream() 方法返回按ASCII编码的输入流(java.io.InputStream对象) 所以如果你的数据库中有可能存储中文字符的话,就要使用前一个方法. 现在举一个实际的例子,让我一步步学习如何使用CLOB. 首先,创建一个含CLOB字段的表: create table test (id INTEGER, content clob);接下来, 我们通过JSP往这张表里插入一条记录,然后再获取显示它. 插入操作: <% Connection con = DriverManager.getConnection(...); //该行从略 String content = request.getParameter("content"); //大文本数据 con.setAutoCommit(false); //* String sql = "insert into test values(1, empty_clob())"; Statement stmt = con.createStatement(); stmt.executeUpdate(sql);
con.commit(); //* sql = "select content from test where id=1 for update"; ResultSet rs = stmt.executeQuery(sql); if (rs.next()) { oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1); clob.putString(1, content); sql = "update test set content=? where id=" + seq; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setClob(1, clob); pstmt.executeUpdate(); } %>以上需要注意的几点是: 1)clob类型的数据不能直接insert,要先通过empty_clob()方法给它分配一个locator(同理,blob的用empty_blob()函数分配locator).然后把它select出来(此时它当然没有数据,但结果集不是空的),得到一个Clob的对象,修改该对象的内容让它满足我们的需要,再通过update方法更新该行记录.2) 通过select修改含lob类型的记录时一定要锁定该行(通过for update关键字实现),否则oracle会报错.3) 刚插入的记录就select for update, 会出现"违反读取顺序"错误,解决办法是将自动提交功能置为false,即不允许自动提交,然后commit它,再select,就可以了. 这就是上面那段代码里//*两行的作用.下面,我们将刚插入的记录从数据库中读取出来并显示之: <% String sql = "select content from test where doc_id=1"; ResultSet rs = stmt.executeQuery(sql); String content = ""; if (rs.next()) { oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("content"); if (clob != null){ Reader is = clob.getCharacterStream(); BufferedReader br = new BufferedReader(is); String s = br.readLine(); while (s != null) { content += s + "<br>"; s = br.readLine(); } } out.println(content); %>
Create Or Replace Package updown Is Procedure write_info ( who in varchar2, description in varchar2, -- file in varchar2 textfiles IN varchar2, binaryfile IN varchar2) ; procedure download(file_name in varchar2); procedure html;
End; / show errors;Create Or Replace Package body updown as
Procedure write_info ( who in varchar2, description in varchar2, -- file in varchar2 textfiles IN varchar2, binaryfile IN varchar2) as begin htp.htmlopen; htp.headopen; htp.title('File Uploaded'); htp.headclose; htp.bodyopen; htp.header(1,'Upload Status'); htp.print('Uploaded ' || textfiles ||' successfully'); htp.print('Uploaded ' || binaryfile ||' successfully'); update fali_doc set name = to_char(sysdate,'YYYYMMDDHHMMSS') where name = textfiles; update fali_doc set name = to_char(sysdate,'YYYYMMDDHHMMSSmmss') where name = binaryfile; commit; -- update the file name in the table fali_doc*/
htp.bodyclose; htp.htmlclose; commit; end ;
procedure download(file_name in varchar2) is content_type varchar2(50) default null; begin htp.p('<html>'); htp.p('<head>');-- select mime_type into content_type from doccontent where name=file_name; htp.p('</head>'); htp.p('<body>'); wpg_docload.download_file(file_name);
改用blob吧。
确实如此,我试过也不能插入4000B以上的内容。也许应该用特定的方法插入,但仅用insert into是不行的。我手头上没有操作CLOB的资料。
to 棒棒糖:
我用ORACLE的ODBC驱动也不行。
to 谈趣者:
难道只能用BLOB了?LOB好象操作起来很麻烦,能给个用ODBC操作的例子吗?谢谢。
存:Dim Chunk() As Byte
If Imageabove.Tag <> "" Then
'Dim Chunk() As Byte
Chunk() = Image2Chunk(tmpFile1)
.Fields("tp1").AppendChunk Chunk()
.UpdateBatch adAffectCurrent
End IfPrivate Function Image2Chunk(Filename As String) As Variant
On Error GoTo ProcErr
Dim Datafile As Integer
Dim FileLength As Long
Dim Chunk() As Byte
Datafile = FreeFile
Open Filename For Binary Access Read As Datafile
FileLength = LOF(Datafile)
If FileLength = 0 Then GoTo ProcErr
ReDim Chunk(FileLength)
Get Datafile, , Chunk()
Close Datafile
ProcExit:
Image2Chunk = Chunk()
Exit FunctionProcErr:
Image2Chunk = 0
End Function取:
If (rs.Fields("tp1").ActualSize = 0) Then
Image1.Picture = Nothing
'Exit Sub
Else
'Dim Chunk() As Byte
Chunk() = rs.Fields("tp1").GetChunk(rs.Fields("tp1").ActualSize)
Set Image1.Picture = Chunk2Image(Chunk(), "temp.fil")
Image1.Tag = "hello"
End IfPublic Function Chunk2Image(Chunk() As Byte, Optional Filename As String) As Variant
On Error GoTo ProcErr
Dim KeepFile As Boolean
Dim Datafile As Integer KeepFile = True
If Trim(Filename) = "" Then
Filename = "c:\tmpxxdb.fil"
KeepFile = False
End If Datafile = FreeFile
Open Filename For Binary Access Write As Datafile
Put Datafile, , Chunk()
Close DatafileProcExit:
Set Chunk2Image = LoadPicture(Filename)
On Error Resume Next
' If Not KeepFile Then Kill filename
Exit FunctionProcErr:
On Error Resume Next
Kill Filename
Chunk2Image = 0
End Function
以上为部分C#.NET代码
OleDbCommand command1=new OleDbCommand(sql,connection);
command1.Parameters.Add("@xxx",OleDbType.LongVarChar,nr.Length).Value=nr;
OleDbDataReader datareader1=command1.ExecuteReader();记住给分!
1. 关于CLOB(Character Large Object)
CLOB可用于存放大文本数据,最多可存储4GB数据,在应用开发中比较常见.java提供的sql.Clob类与之对应.它提供两个方法来读取Clob的数据:
getCharacterStream() 方法返回按unicode编码的输入流(java.io.Reader对象)
getAsciiStream() 方法返回按ASCII编码的输入流(java.io.InputStream对象)
所以如果你的数据库中有可能存储中文字符的话,就要使用前一个方法.
现在举一个实际的例子,让我一步步学习如何使用CLOB.
首先,创建一个含CLOB字段的表:
create table test (id INTEGER, content clob);接下来, 我们通过JSP往这张表里插入一条记录,然后再获取显示它.
插入操作:
<%
Connection con = DriverManager.getConnection(...); //该行从略
String content = request.getParameter("content"); //大文本数据
con.setAutoCommit(false); //*
String sql = "insert into test values(1, empty_clob())";
Statement stmt = con.createStatement();
stmt.executeUpdate(sql);
con.commit(); //*
sql = "select content from test where id=1 for update";
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);
clob.putString(1, content);
sql = "update test set content=? where id=" + seq;
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setClob(1, clob);
pstmt.executeUpdate();
}
%>以上需要注意的几点是:
1)clob类型的数据不能直接insert,要先通过empty_clob()方法给它分配一个locator(同理,blob的用empty_blob()函数分配locator).然后把它select出来(此时它当然没有数据,但结果集不是空的),得到一个Clob的对象,修改该对象的内容让它满足我们的需要,再通过update方法更新该行记录.2) 通过select修改含lob类型的记录时一定要锁定该行(通过for update关键字实现),否则oracle会报错.3) 刚插入的记录就select for update, 会出现"违反读取顺序"错误,解决办法是将自动提交功能置为false,即不允许自动提交,然后commit它,再select,就可以了. 这就是上面那段代码里//*两行的作用.下面,我们将刚插入的记录从数据库中读取出来并显示之:
<%
String sql = "select content from test where doc_id=1";
ResultSet rs = stmt.executeQuery(sql);
String content = "";
if (rs.next()) {
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("content");
if (clob != null){
Reader is = clob.getCharacterStream();
BufferedReader br = new BufferedReader(is);
String s = br.readLine();
while (s != null) {
content += s + "<br>";
s = br.readLine();
}
}
out.println(content);
%>
Procedure write_info (
who in varchar2,
description in varchar2,
-- file in varchar2
textfiles IN varchar2,
binaryfile IN varchar2) ;
procedure download(file_name in varchar2);
procedure html;
End;
/
show errors;Create Or Replace Package body updown as
Procedure write_info (
who in varchar2,
description in varchar2,
-- file in varchar2
textfiles IN varchar2,
binaryfile IN varchar2) as
begin
htp.htmlopen;
htp.headopen;
htp.title('File Uploaded');
htp.headclose;
htp.bodyopen;
htp.header(1,'Upload Status');
htp.print('Uploaded ' || textfiles ||' successfully');
htp.print('Uploaded ' || binaryfile ||' successfully'); update fali_doc set name = to_char(sysdate,'YYYYMMDDHHMMSS')
where name = textfiles;
update fali_doc set name = to_char(sysdate,'YYYYMMDDHHMMSSmmss')
where name = binaryfile;
commit;
-- update the file name in the table fali_doc*/
htp.bodyclose;
htp.htmlclose;
commit;
end ;
procedure download(file_name in varchar2) is
content_type varchar2(50) default null;
begin
htp.p('<html>');
htp.p('<head>');-- select mime_type into content_type from doccontent where name=file_name; htp.p('</head>');
htp.p('<body>'); wpg_docload.download_file(file_name);
htp.p('</body>');
htp.p('</html>'); end download;
procedure html is
begin
htp.p(' <html>
<head>
<title>test upload</title>
</head>
<body>
<FORM enctype="multipart/form-data" action="/ga/owa/updown.write_info" method="POST">
<p>Author’s Name:<INPUT type="text" name="who">
<p>Description:<INPUT type="text" name="description"><br>
<p>需要上载的文件1<INPUT type="file" name="textfiles"><br>
<p>需要上载的文件2<INPUT type="file" name="binaryfile"><br>
<p><INPUT type="submit">
</FORM>
</body>
</html> ');
end ;
end ;
/
show errors;