eamner


Hello.
I'm working with SQL Server 2005 Standard edition.
I have a Java program that loads PDF files into the database. I have a table called T08_entity which, among others, has two IMAGE columns. The first Image column is for the original PDF file. The second one is for the PDF file with modified permissions (printing, saving, etc). This is made using the i-text library.
The programs looks for the content of a disk folder, reads the contents of the folder, and inserts, one by one, the pdf files (besides other fields, like the name of the file, and ID, etc... but these are varchar or int fields. No problem with these.
When the folder has only small files (smaller that 7-8 mb), it loads them without any problem into the database. But when the folder has bigger files (>10mb, more or less...) I get an OUT OF MEMORY error.
I'm using the latest sqljdbc.jar driver (v1.2.2727). My server computer has only 1GB of RAM... but I've read that this latest driver can load big amounts of binary data using the connection property "responseBuffering=adaptive".
Here is a sample of my code (at least the most relevant lines):

This is my connection code:
public String getConnectionUrl(){
return "jdbc:sqlserver://"+serverName+":"+portNumber+";databaseName="
+databaseName+";responseBuffering=adaptive;selectMethod=cursor";
}

public java.sql.Connection getConnection(){
try{
...
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password); if (con.getAutoCommit())
{
con.setAutoCommit(false);
}
...
}
catch(Exception e){
System.out.println("etc, etc...");
}
return con;
}


The following is a loop where each loop represents a file in the folder:
File[] contenido = archivo.listFiles();
for(int i=0;i<contenido.length;i++)
{
if (contenido[i].isDirectory())
{
procesarDirectory(contenido[i]);
}else
{
insertDirectory(contenido[i]);
}
}


...And this is the insertDirectory procedure which inserts every file: the pdffile and pdffilenoperm are the IMAGE columns. The rest are varchar or int columns:

public void insertDirectorio(File archivo)
{
...
if (archivo.isFile()){
pstmt =con.prepareStatement("INSERT INTO temp_carga "+
"(directory, name, dir_sup, filetype, pfilesize,pdffile,pdffilenoperm)"+
" values ( , , , , , , )");
}
...

long tamano = archivo.length();

pstmt.setString(1,pdffile.getPath());
pstmt.setString(2,pdffile.getName());
pstmt.setString(3,pdffile.getParent());

pstmt.setString(4,filetype);

if (pfilesize != 0) {
pstmt.setLong(5,pfilesize);
}
else
{
pstmt.setString(5,null);
}

if (pdffile.isFile()) {
try{

//INSERTS ORIGINAL FILE................
int fileLength = Integer.MIN_VALUE;
is = new FileInputStream(pdffile);
fileLength= (int) pdffile.length();
pstmt.setBinaryStream(6, is, fileLength);

//INSERTS FILE WITHOUT PERMISSIONS
(THIS PART OF THE CODE IS LONG AND IRRELEVANT, IT JUST USES THE ITEXT LIBRARY TO MODIFY THE PDF FILE. AT THE END, I HAVE THE FILE IN AN OUTPUT STREAM, AS SHOWN HERE:)
ByteArrayInputStream inputnoimp = new ByteArrayInputStream(outnoimp.toByteArray());
pstmt.setBinaryStream(7,inputnoimp,(int)outnoimp.size());
} catch(Exception e) {
err = e.toString();
}
}
pstmt.executeUpdate();
con.commit();
pstmt.close();
this.closeConnection();
}catch(java.sql.SQLException e) {
err = e.toString();
}
}


Well, as I said, when I run the program, when it reads smaller files, there's no problem. But when it gets a big file, I get the OUT OF MEMORY error. I have another application that reads pdf files ONE AT A TIME, using a code very much like this one, and it reads big files (>30mb) with no problems. The problems is with this one.
Any help will be appreciated. If you have any question to clarify the problem, just tell me.
Thanks in advance.
Eric.




Re: Out of memory error inserting Files

Anshul Anand


We have a similar kind of problem with SQL Server 2005 v1.2 JDBC driver, where the application is failing with java.lang.OutOfMemoryError. We have a database of around 50 milion rows and our application has to query that database and operate on the large dataset. However, we are not facing this problem with databases having 1 million rows.

Database is running fine even after the java.lang.OutOfMemoryError in the application side. However, the application is not responding after this error.

We are facing this problem with SQL Server 2005 Standard Edition as well as Enterprise Edition.

Any help or suggestions will be highly appreciated.






Re: Out of memory error inserting Files

eamner

If you're using the v1.2.2727 version of the driver, you can try playing with the "responseBuffering=adaptive" and "selectMethod=cursor" properties in yout connection url. They're supposed to improve the performance for large rowsets. But still, I have the error mentioned in my first post. Hope anybody could help me.
Greetings,
Eric.





Re: Out of memory error inserting Files

David Olix - MSFT

Hi,

Thank you for your detailed post. And thank you for using the Microsoft SQL Server JDBC Driver.

Inserting/Updating large values is typically not influenced by adaptive response buffering. Adaptive response buffering helps primarily with large queries (e.g. SELECTs over millions of rows, getting large column values, etc.)

Not knowing what the "irrelevant" code does here, I'd have to guess that you may have several copies of each PDF file in memory. Does the irrelevant code consume (and buffer) the original PDF InputStream If you use mark/reset on any of the streams in this code, the JVM may be making a copy of the stream internally. Certainly the call to outnoimp.toByteArray() creates a copy in memory. If outnoimp is a ByteArrayOutputStream, that's another copy of the original. Also, how long does your code hold references to these streams

Check the call stack of the OutOfMemoryError. While the call stack doesn't always indicate the source of the problem, it often points out where to start looking. Another useful tool for diagnosing OutOfMemoryErrors is a heap profiler, like HPROF (http://java.sun.com/developer/technicalArticles/Programming/HPROF.html).

Provided both streams are set via setBinaryStream and both are going to IMAGE columns (so no client side type conversion), the JDBC driver will consume the streams only when it sends them to SQL Server, so it should handle very large streams with no problems.

Regards,

--David Olix [MSFT]





Re: Out of memory error inserting Files

eamner

Hello David,
I didn't write the "irrelevant" code basically because, if I don't use it, I still get the same error. That part of the code simply manipulates the InputStream with a Reader, and using the i-text library, I remove a few permissions on the pdf file. Anyway here is the portion of code:

Reader reader = new InputStreamReader(new FileInputStream(pdffile));
ByteArrayOutputStream baos = new ByteArrayOutputStream();
Writer writer = new OutputStreamWriter(baos);
int c = reader.read();
while (c>=0) {
writer.write(c);
c = reader.read();
}
writer.close();
reader.close();
byte[] fileBytes = (byte[]) baos.toByteArray();
ByteArrayOutputStream outnoimp = new ByteArrayOutputStream();

PdfReader file_original = new PdfReader(fileBytes);

archivo_original.setViewerPreferences(PdfWriter.HideMenubar | PdfWriter.HideToolbar);
com.lowagie.text.pdf.PdfEncryptor.encrypt(file_original, (OutputStream) outnoimp, null,null,160, false);
ByteArrayInputStream inputnoimp = new ByteArrayInputStream(outnoimp.toByteArray());

pstmt.setBinaryStream(7,inputnoimp,(int)outnoimp.size());



After reading your post, now I realize that the problem isn't in the sqljdbc driver, but in the way I manipulate the file streams. As you can guess, I'm not an expert on managing memory heaps in java, so, even though the problem is not directly related to sqlserver, I'd very much appreciate if you give me an advice on how to "clean" the memory after each insertion. Meanwhile, I'll check the link you kindly provided me, and will look for more information.
Thanks,
Eric.




Re: Out of memory error inserting Files

eamner

Hi,
Well, I think I managed to solve it. I added this parameters:
-Xms256m -Xmx512m
...to my application, and so far it's running ok. Now it inserts smaller and bigger files.
Anyway I still have to improve the performance of the load, freeing up the memory.
Thanks.
Eric.