Error in loading big file

[Fred Wang via SQLMonster.com]

Hi All,
I have an application on WL8.1 with sp3, the database is SQL Server 2000. I
have code below to load local file into database. The data type in database
is image.

PreparedStatement pStatement = null;
InputStreammyStream myStream = new InputStream();
myStream.setEmbeddedStream( is );
pStatement.setBinaryStream( 1, myStream, -1 );
pStatement.executeUpdate();
pStatement.close();
pStatement = null;

is is InputStream from a local file and the sql statement is
insert into file_content(content) values(?)

This workes fine for the files with size less than 150M, but for those big
files (>150M), it doesn't work and I got error message as below:

<Feb 11, 2005 12:00:41 PM PST> <Notice> <EJB> <BEA-010014> <Error occurred
while attempting to rollback transaction:
javax.transaction.SystemException: Heuristic hazard:
(weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
(javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
been closed.))
javax.transaction.SystemException: Heuristic hazard:
(weblogic.jdbc.wrapper.JTSXAResourceImpl, HeuristicHazard,
(javax.transaction.xa.XAException: [BEA][SQLServer JDBC Driver]Object has
been closed.))
at weblogic.transaction.internal.ServerTransactionImpl.internalRollback
(ServerTransactionImpl.java:396)
at weblogic.transaction.internal.ServerTransactionImpl.rollback
(ServerTransactionImpl.java:362)

Any body can help? Thanks in advance.

--
Message posted via http://www.sqlmonster.com

[Joe Weinstein]

Hi. That means the DBMS choked on the submission and killed the whole
JDBC connection. Check your DBMS log for problems. You may be running
out of space in the DBMS's transaction log, which must have a separate
copy of your insert data, to prepare for DBMS commit or rollback.
Joe Weinstein at BEA

[Fred Wang via SQLMonster.com]

Thanks Joe. I do agree. So what shall I do? We don't have idea how big
could the file be. Is there any way we may cut file into smaller pieces,
ship the to db and reassembly there? Thanks a lot

[Joe Weinstein]

Fred Wang via SQLMonster.com wrote:

> Thanks Joe. I do agree. So what shall I do? We don't have idea how big
> could the file be. Is there any way we may cut file into smaller pieces,
> ship the to db and reassembly there? Thanks a lot

Well, there might be some ugly hacks, but fundamentally you just want to
get some SQLServer DBA help to configure it so it will handle the type of
transaction you want to do. An RDBMS isn't usually ideal as a store for
huge blobs... Sort of like using the bank to deposit those huge polynesian
coral wheel money tokens. Even the islanders stopped moving them around.
One sunk in a bay while being transported by boat, so they just left it there,
and people just started agreeing on who owned it at any time... ;)
Joe Weinstein at BEA

[Carb Simien [MSFT]]

--------------------
| From: "Fred Wang via SQLMonster.com" <fo...@SQLMonster.com>
| Subject: Error in loading big file
| Date: Fri, 11 Feb 2005 23:13:31 GMT
| Organization: http://www.SQLMonster.com
| Message-ID: <2f0972878fc74c0f...@SQLMonster.com>
| X-Abuse-Report: http://www.SQLMonster.com/Uwe/NB/Abuse.aspx
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: 178.67-18-207.reverse.theplanet.com 67.18.207.178
| Lines: 1
| Path:
TK2MSFTNGXA01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13
.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.jdbcdriver:866
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver

You could insert the new data in chunks by calling UPDATETEXT multiple
times. From Java, you could iteratively call read() on a FileInputStream
object and populate a byte array buffer. For each iteration, you can
execute the stored procedure below and pass the buffer as an input
parameter. This operation may not be logged, depending on the recovery
model of your database (simple or bulk-logged). This may be the way to go
to avoid excessive transaction log usage. However, the downfall is that
you do not get the benefit of a transaction, so a failure along the way
would leave the BLOB data in an incomplete state.

T-SQL
======
CREATE TABLE FredWang([ID] INT PRIMARY KEY, [blob] IMAGE)
GO
INSERT INTO FredWang VALUES(1, CONVERT(VARBINARY(8000), ''))
GO

CREATE PROCEDURE usp_InsertBlob
(
@row BIGINT,
@insertOffset INT = NULL,
@deleteLength INT = 0,
@data VARBINARY(8000)
)
AS
BEGIN
DECLARE @textPointer BINARY(16)
SELECT @textPointer = TEXTPTR(blob) FROM FredWang WHERE [ID] = @row

DECLARE @query VARCHAR(4000)
UPDATETEXT FredWang.blob @textPointer @insertOffset @deleteLength @data
END
GO

Java
=====
import java.sql.*;
import java.io.*;

public class query
{
public static void main(String[] args) throws Exception
{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int chunkSize = 8000;

try
{
// Load the JDBC driver
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

// Connect to SQL Server
String url =
"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jdbc;";
conn = DriverManager.getConnection(url, "sa", "password");

// Load the file into memory
FileInputStream file = new FileInputStream("c:\\large_file.jpg");

// Insert the BLOB data in chunks
String sql = "EXEC usp_InsertBlob 1, DEFAULT, DEFAULT, ?";
pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
byte [] buff = new byte[chunkSize];
while(file.read(buff) != -1)
{
pstmt.setBytes(1, buff);
pstmt.executeUpdate();
}
file.close();
}
catch (SQLException sqlEx)
{
while (sqlEx != null)
{
System.out.println("SQLState: " + sqlEx.getSQLState());
System.out.println("Message: " + sqlEx.getMessage());
System.out.println("Error code: " + sqlEx.getErrorCode());
sqlEx = sqlEx.getNextException();
System.out.println();
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
// Cleanup
if (rs != null)
rs.close();
rs = null;
if (pstmt != null)
pstmt.close();
pstmt = null;
if (conn != null)
conn.close();
conn = null;
}
}
}

Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.