trooper411
asked on
Using JDBC to insert an Oracle CLOB into a table, file is very large (1 MB or more)
I am attempting to insert records in a table in which one field is a clob. I have included the inserting logic below in hopes that this will best help explain my issue. Please note that I am trying to upload a file and insert that file directly into a table in an oracle database, so a file is not contained on the file system. This logic does work except for large files. When uploading files closer to 1 MB, the page seems to timeout. Is there something
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<%@ page errorPage="JavaError.jsp" %>
<%@ page import="java.io.*,oracle.s
<html>
<%
String userid="username";
MultipartParser mp = new MultipartParser(request,50
FilePart part = (FilePart)mp.readNextPart(
if (part.isFile()) {
String filename = (part).getFileName();
InputStream ins = (part).getInputStream();
BufferedReader buffer = new BufferedReader(new InputStreamReader(ins));
String line = null;
bulkload.OracleConnection oconn = new bulkload.OracleConnection(
Connection conn = oconn.getConnection();
PreparedStatement pstmt = null;
conn.setAutoCommit(false);
oracle.sql.CLOB clob = null;
ResultSet rs = null;
java.sql.Timestamp now = new java.sql.Timestamp(System.
// Create CLOB in the table tblFileCLOBs
pstmt = conn.prepareStatement("ins
pstmt.setString(1,filename
pstmt.setTimestamp(2,now);
pstmt.setString(3,userid);
pstmt.executeUpdate();
conn.commit();
// Retrieve the newly created CLOB
PreparedStatement pstmt2= conn.prepareStatement("sel
pstmt2.setString(1,filenam
pstmt2.setTimestamp(2,now)
pstmt2.setString(3,userid)
rs = pstmt2.executeQuery();
rs.next();
clob = ((OracleResultSet)rs).getC
// Output the StringBuffer to the CLOB
PrintWriter prnw= new PrintWriter(clob.getCharac
while ((line=buffer.readLine()) != null) {
out.println(line);
prnw.println(line);
}
prnw.flush();
buffer.close();
ins.close();
prnw.close();
conn.commit();
pstmt.close();
conn.close();
}
%>
</html>
thanks for any help you can give. I have also post this message on the Oracle Metalink iTAR system. I will update this post if I get a response from them.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<%@ page errorPage="JavaError.jsp" %>
<%@ page import="java.io.*,oracle.s
<html>
<%
String userid="username";
MultipartParser mp = new MultipartParser(request,50
FilePart part = (FilePart)mp.readNextPart(
if (part.isFile()) {
String filename = (part).getFileName();
InputStream ins = (part).getInputStream();
BufferedReader buffer = new BufferedReader(new InputStreamReader(ins));
String line = null;
bulkload.OracleConnection oconn = new bulkload.OracleConnection(
Connection conn = oconn.getConnection();
PreparedStatement pstmt = null;
conn.setAutoCommit(false);
oracle.sql.CLOB clob = null;
ResultSet rs = null;
java.sql.Timestamp now = new java.sql.Timestamp(System.
// Create CLOB in the table tblFileCLOBs
pstmt = conn.prepareStatement("ins
pstmt.setString(1,filename
pstmt.setTimestamp(2,now);
pstmt.setString(3,userid);
pstmt.executeUpdate();
conn.commit();
// Retrieve the newly created CLOB
PreparedStatement pstmt2= conn.prepareStatement("sel
pstmt2.setString(1,filenam
pstmt2.setTimestamp(2,now)
pstmt2.setString(3,userid)
rs = pstmt2.executeQuery();
rs.next();
clob = ((OracleResultSet)rs).getC
// Output the StringBuffer to the CLOB
PrintWriter prnw= new PrintWriter(clob.getCharac
while ((line=buffer.readLine()) != null) {
out.println(line);
prnw.println(line);
}
prnw.flush();
buffer.close();
ins.close();
prnw.close();
conn.commit();
pstmt.close();
conn.close();
}
%>
</html>
thanks for any help you can give. I have also post this message on the Oracle Metalink iTAR system. I will update this post if I get a response from them.
Check your webserver settings... Sometimes the webserver will restrict the size of the amount of data posted from a form. i've seen this before, and the error is that the webserver will just terminate the connection. let me know what you find in the webserver settings.
ASKER
I am having our web administrators look into this, however, I don't believe this is the issue. I can use the O'Reilly MultipartRequest to upload the file. However, I want to use the MultipartParser, uploads the file as chunks, so that I can simply just store the file in the database rather than on the filesystem. By doing so, I can keep all security and navigation to the web container and the database.
I know what you are trying to do... I did the same thing before with MultipartRequest, but what ended up being the problem was that the webserver wouldn't allow posts of more than 1 MB. Remember that the webserver forwards the request to the application server. So, if the webserver isn't a happy camper, the application server never receives the request. One way to test this is in your servlet first line (output something to the log file), then you will know if the webserver ever even forwarded the request to the application server (of course i'm talking about the case where you upload something greater than the limit). This will tell you if this may be the problem. The problem may very well have nothing to do with the webserver, but it is a good idea to eliminate the possibility first.
ASKER
How would I go about including text in the application server log file in order to check what you describe. I can display (out.println) the file contents as I pull the chunks, so I know that I can get the file to the web server, but you are saying that when I send this data to the database, I am sending it through the application server due to the jdbc connection; and the application server can have a limit on file size (in the same way as the web server can)? I am interested in this conclusion. Please expand.
OK, I am unclear as to what exactly your tests have shown. I was under the impression that when someone tries to post data from a file > 1 MB, you get an error (with nothing sent to the database). That was what I thought you said. But, in this last message, are you saying that you get all the data up to 1 MB and then it just stops? If the case is the first one where you get nothing, I was thinking you might be experiencing this:
User submits form --> Webserver gets request --> Request is sent to Application Server to handle
And what I was saying is that the webserver might not allow a request with a content size > 1 MB, in which case it terminates the connection right there and never sends it to the application server in which case YOUR JAVA CODE would never be executed hence nothing written to the application log file if you tried to write some stuff. You get the flow? But, if you are positive that your application does receive the request and just fails somewhere along the process of putting the data into the DB, then that may be another problem. Can you clarify? Thx.
User submits form --> Webserver gets request --> Request is sent to Application Server to handle
And what I was saying is that the webserver might not allow a request with a content size > 1 MB, in which case it terminates the connection right there and never sends it to the application server in which case YOUR JAVA CODE would never be executed hence nothing written to the application log file if you tried to write some stuff. You get the flow? But, if you are positive that your application does receive the request and just fails somewhere along the process of putting the data into the DB, then that may be another problem. Can you clarify? Thx.
ASKER
I use a MultipartParser object which takes a file sent by the client html post and takes a line of characters from this file. Then I send this line to the clob in my database table. I loop this through until the multipartparser has no more characters to send. Hopefully, when the jsp hits eof on the file, the database has been populated in the clob column with the data from the file (which would be more than 750kb). I can get files to go successfully through this process which are 121 kb or lower (only tested up to 121kb so far). Files are getting to the database and populated. However, any attempt at a larger file seems to fail, returning the internal server error or dns error web message. Since I can get other files to work successfully through this process, I would assume that there must be some sort of buffer issue. I then took the clob portion of the code out and simply tried to upload the client file and display it on in the browser. This worked successfully and with quite a bit of speed. I then reentered the clob code and the code failed. Again, the file I am uploading is one of the files larger than 750kb, which are giving me problems. I have not been able to test the response time of the clob code, with out the upload. I did get a small response from Oracle Metalink from a technician who took my code and modified it to work in a non-webserver based application and stated that the code works fine for a file that is 3mb. I am starting to believe that this could be an issue with the webserver/database communication via the JDBC connection.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh man, you know what else you should try. You should probably just use MultipartRequest. Do something like:
MultipartRequest mp = new MultipartRequest(request, "c:/temp", 50*1024*1024);
I had no problems before and I was using MultipartRequest, not MultipartParser. Let me know what you find....
MultipartRequest mp = new MultipartRequest(request, "c:/temp", 50*1024*1024);
I had no problems before and I was using MultipartRequest, not MultipartParser. Let me know what you find....
Oh man, you know what else you should try. You should probably just use MultipartRequest. Do something like:
MultipartRequest mp = new MultipartRequest(request, "c:/temp", 50*1024*1024);
I had no problems before and I was using MultipartRequest, not MultipartParser. Let me know what you find....
MultipartRequest mp = new MultipartRequest(request, "c:/temp", 50*1024*1024);
I had no problems before and I was using MultipartRequest, not MultipartParser. Let me know what you find....
Hi Trooper,
I'm also using the MultipartParser to upload documents as Blob in a Oracle database. This isn't given me any problems with files larger then 1 MB. I'm using Oracle 8.1.7 database, oracle thin driver (classes12.jar), Apache 2.0 and Tomcat 4.0.3 on both NT machines and Linux machines.
May be this gives you a direction to look for?
If paskal says MultipartParser should be fine, then I would look back at this: "What you should probably try is to handle the uploaded file first. That is, save it to file system to clear out the request. THEN after dealing with the request is taken care of, start the DB stuff. So try to segment the two different processes rather than connecting the request directly to the DB insertion"... paskal, to you go straight from request into database or do you save to the server filesystem first? That would be interesting to know.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
AND VOILA, we now see the problem... When using MultipartRequest, you specify max upload size as:
MultipartRequest mp = new MultipartRequest(request, "c:/temp", 50*1024*1024);
Here, paskal shows that you specify a larger max upload size using MultipartParser as:
MultipartParser mp = new MultipartParser(request, Constants.MAXUPLOADSIZE);
Although Constants.MAXUPLOADSIZE seems pretty large... hahaha. At any rate, trooper411, the default size for this library is 1MB. I looked it up. So now we know the reality behind your problem. You need to specify something larger than the default.
MultipartRequest mp = new MultipartRequest(request, "c:/temp", 50*1024*1024);
Here, paskal shows that you specify a larger max upload size using MultipartParser as:
MultipartParser mp = new MultipartParser(request, Constants.MAXUPLOADSIZE);
Although Constants.MAXUPLOADSIZE seems pretty large... hahaha. At any rate, trooper411, the default size for this library is 1MB. I looked it up. So now we know the reality behind your problem. You need to specify something larger than the default.
If you do decide to award points to me, make sure to give half to paskal. Nice work, paskal...
Tbone,
Or I'm overlooking something or you are to quick to draw conclusions.
I've defined the max upload size as
public static int MAXUPLOADSIZE = 10*1024*1024; // 10 Mb
But since trooper is using :
MultipartParser mp = new MultipartParser(request,50
in his original posting, this should be enough for 50 MB files.
Or am I mistaking here???
ASKER
I was able to load in larger files today (however, I didn't change any code except for the MultipartParser portion of the code). I simply loaded a file already contained on the web server. Again, the small files loaded successfully, however the larger files took a considerable amount of time to load. Therefore, I reverted back to previous code where I used the MultipartRequest object in order to upload a file to our file system on the webserver. Then, I built in logic to load the file into the tables only if the file exists() on the current web server. My overall issue is that the four web servers we use here are now connected in anyway besides sharing a common database connection. This was why I had the idea of loading the file into the database.
However, now, we are using the similar file structures on all the four servers and a program called suresync to sync the servers based on our CSC. This seems to give us the desired effect.
Again, I was able to upload a file larger than 1 MB. Then I was able to store a file more than 1 MB in the table in oracle. I could not however get the entire process to work. Therefore, I am abandoning this idea.
Please let me know what would be fair for the issuance of points. I am willing to split the points between tbone343 and paskal, so long as this is agreeable.
trooper411
However, now, we are using the similar file structures on all the four servers and a program called suresync to sync the servers based on our CSC. This seems to give us the desired effect.
Again, I was able to upload a file larger than 1 MB. Then I was able to store a file more than 1 MB in the table in oracle. I could not however get the entire process to work. Therefore, I am abandoning this idea.
Please let me know what would be fair for the issuance of points. I am willing to split the points between tbone343 and paskal, so long as this is agreeable.
trooper411
Whoops.. First paskal: It had been so long since I looked back at the top that for some reason my memory told me that he had used the default constructor. But, yeah that's right. That was the whole reason I said to use MultipartRequest instead... hahaha. I've been just tryin to help trooper411 attack it from every front. So it looks like trooper411 has some good feedback here:
>I was able to load in larger files today (however, I didn't change any code except for the MultipartParser portion of the code). I simply loaded a file already contained on the web server. Again, the small files loaded successfully, however the larger files took a considerable amount of time to load.
Trooper, if you can't put large files already existing on the server filesystem into the database THEN the problem area seems like it must be just the DB stuff, i.e. it has nothing to do with MultipartRequest/Multipart
Along those lines, I've never tried using:
bulkload.OracleConnection oconn = new bulkload.OracleConnection(
Connection conn = oconn.getConnection();
Perhaps, you could try just making an ordinary connection... I'm not sure what "bulkload" does... And now for my consultant opinion: For strict performance sake, I would never put files into the DB. I know people think it is a good idea, but my personal opinion is that it is not a good idea. There is just way too much overhead. You have to go from the webserver to the application server then make a connection and upload. Now, even going the other way when a user wants to retrieve it, they issue a request to the webserver which goes to the application server and another connection is made to pull out the bytes. But if you just put it on the filesystem, then you can serve it fast as hell just from the webserver. That is, the webserver need to forward a request for a Word doc to the application server at all. If you every stress test a *.jsp page versus an HTML file or anything else served by the webserver, they won't even compare. Webserver will kick ass. So then many people start saying: what about clustering and what a pain in the butt it is to maintain all these separate filesystems and have them replicate. Well, in actuality, this is the wrong method of implementation as well. The KEY is not to replicate, but rather to establish a single Network File Server. And you configure the entire cluster to use this SINGLE file server. Now, there is a single point of failure, but that's no different than having a single database which is what you were going to do in the first place. And, if you spend the right amount of money and configure it correctly, a NFS can be extremely stable. When we install software on site, we always recommend this solution but everyone always seems to do the replicate crap. I still don't know why they do this though... .Anyway, that is just my two sense. Splitting the points sounds fairest. Good Luck, Trooper...
>I was able to load in larger files today (however, I didn't change any code except for the MultipartParser portion of the code). I simply loaded a file already contained on the web server. Again, the small files loaded successfully, however the larger files took a considerable amount of time to load.
Trooper, if you can't put large files already existing on the server filesystem into the database THEN the problem area seems like it must be just the DB stuff, i.e. it has nothing to do with MultipartRequest/Multipart
Along those lines, I've never tried using:
bulkload.OracleConnection oconn = new bulkload.OracleConnection(
Connection conn = oconn.getConnection();
Perhaps, you could try just making an ordinary connection... I'm not sure what "bulkload" does... And now for my consultant opinion: For strict performance sake, I would never put files into the DB. I know people think it is a good idea, but my personal opinion is that it is not a good idea. There is just way too much overhead. You have to go from the webserver to the application server then make a connection and upload. Now, even going the other way when a user wants to retrieve it, they issue a request to the webserver which goes to the application server and another connection is made to pull out the bytes. But if you just put it on the filesystem, then you can serve it fast as hell just from the webserver. That is, the webserver need to forward a request for a Word doc to the application server at all. If you every stress test a *.jsp page versus an HTML file or anything else served by the webserver, they won't even compare. Webserver will kick ass. So then many people start saying: what about clustering and what a pain in the butt it is to maintain all these separate filesystems and have them replicate. Well, in actuality, this is the wrong method of implementation as well. The KEY is not to replicate, but rather to establish a single Network File Server. And you configure the entire cluster to use this SINGLE file server. Now, there is a single point of failure, but that's no different than having a single database which is what you were going to do in the first place. And, if you spend the right amount of money and configure it correctly, a NFS can be extremely stable. When we install software on site, we always recommend this solution but everyone always seems to do the replicate crap. I still don't know why they do this though... .Anyway, that is just my two sense. Splitting the points sounds fairest. Good Luck, Trooper...
that should be: the webserver "doesn't" need
I can't type as fast as i think... :-)
I can't type as fast as i think... :-)
ASKER
I received a response from the Metalink support service of Oracle. After modify the code from using writers to streams, the process was cut to a mere 10 secs. The new code sets a buffer array to the chunk size of the clob, then loops by reading the resource (file) by the chunk size, puts the bytes in the byte array and writes these bytes to the clob asciioutputstream. Thanks to all of you for your help and comments.
trooper411
trooper411
Trooper,
Thx for the points and 'A'-grade !
Thx for the points and 'A'-grade !