When I develop BS structure programs, I often need to process some large-capacity text data in ORACLE, so after repeated testing, I finally used ASP to successfully solve the problem of large text data access under ORACLE.
1. Operating environment:
1. Microsoft windows 2000 Server + IIS 5.0
2. Oracle8i Chinese Standard Edition
2. Create a data table:
CREATE TABLE SYSTEM.TEST(
BLOB LONG,
ID NUMBER)
/
3. Source program:
1. Data storage program: test.asp
<%
'Form submission processing part
'------------------------------------------------ -
If request(ok)=1 then
'Character conversion function
function tansstr(sstr)
sstr=replace(sstr, , )
sstr=replace(sstr,chr(13) & chr(10),<br>)
tansstr=sstr
end function
'Submit data assignment
a=lenb(request(text))
b=len(request(text))
c=tansstr(request(text))
'Open the database, open the test data table with Rs as the record set
Set OraSession=CreateObject(OracleInProcServer.XOraSession)
Set OraDatabase=OraSession.DbOpenDatabase(autop,system/manager,0)
Set rs=OraDatabase.CreateDynaset(select * from test order by id desc,0)
'Find the ID value
if rs.eof then
id=1
else
id=rs(id)+1
end if
'Because it is limited by the length of SQL statements, data is stored in non-SQL statements.
'------------------------------------------------ -------
'New record
rs.DbAddNew
'The classic is in this sentence: Use the DbAppendChunk method of the Fields object of the RS record set to handle the problem of large field storage.
rs.Fields(blob).DbAppendChunk(c)
'Save ID value
rs(id)=id
'Refresh the record set
rs.DbUpdate
'Display results section
'------------------------------------------------ --------
Response.write The data has been stored in the database. <br>
Response.write total number of characters occupied: & formatnumber(b,2,-2,-2,-1) & words<br>
Response.write total number of bytes occupied: & formatnumber(a,2,-2,-2,-1) & Byte<br>
Response.write <a href='view.asp'>Please read...
'Close the data connection.
rs.close
set rs=nothing
Set OraSession=nothing
Response.end
End If
%>
<html>
<body>
<form method=POST action=test.asp>
<p>Solution to the problem of accessing Oracle large fields in ASP:</p>
<p><textarea rows=13 name=text cols=104></textarea></p>
<p><input type=submit value=Save name=B1></p>
<input type=hidden name=ok value=1>
</form>
</body>
</html>
2. Data calling program: view.asp
<%
'Connect to the database and open the data table in read-only mode
Set OraSession=CreateObject(OracleInProcServer.XOraSession)
Set OraDatabase=OraSession.DbOpenDatabase(autop,system/manager,0)
Set Rs=OraDatabase.DbCreateDynaset(select * from test order by id desc,4)
'Assign initial value: define the byte size of each interception as 1024byte, the maximum can be set to 65280byte (64K)
Size=65280
I=0
Do
'Read data in a loop using the DbGetChunk method of the Fields object of the Rs record set
Text=Rs.Fields(Blob).DbGetChunk(I*Size,Size)
Response.write Text
'Find the detailed number of bytes of data taken out each time
Text_Size=Lenb(Text)
I=I+1
'If the detailed number of bytes of data taken out each time is less than the intercepted byte size to be defined, it means that the piece of data has been completed and the loop is exited.
Loop until Text_Size<Size
'Close data connection
Set OraSession=nothing
%>
4. Postscript:
Because the data type is long, ORACLE stipulates that the capacity of long is <2GB. The above method can store large text <2GB. However, I found that when reading, I can only read the maximum content of 64K. Maybe it is due to the influence of the above method. ASP limitations.
Everyone, if you have more experience or better solutions, please let us know.