Error in loading big file

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.ServerTransactionImp l.internalRollback
(ServerTransactionImpl.java:396)
at weblogic.transaction.internal.ServerTransactionImp l.rollback
(ServerTransactionImpl.java:362)
Any body can help? Thanks in advance.
Message posted via http://www.sqlmonster.com
Fred Wang via SQLMonster.com wrote:

> 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.ServerTransactionImp l.internalRollback
> (ServerTransactionImpl.java:396)
> at weblogic.transaction.internal.ServerTransactionImp l.rollback
> (ServerTransactionImpl.java:362)
> Any body can help? Thanks in advance.
>
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
|||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
Message posted via http://www.sqlmonster.com
|||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
|||| From: "Fred Wang via SQLMonster.com" <forum@.SQLMonster.com>
| Subject: Error in loading big file
| Date: Fri, 11 Feb 2005 23:13:31 GMT
| Organization: http://www.SQLMonster.com
| Message-ID: <2f0972878fc74c0f8e40521ba0ad6ae3@.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!TK2MSFT NGP08.phx.gbl!tk2msftngp13
.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.jdbcdriver:866
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| 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.ServerTransactionImp l.internalRollback
| (ServerTransactionImpl.java:396)
| at weblogic.transaction.internal.ServerTransactionImp l.rollback
| (ServerTransactionImpl.java:362)
|
| Any body can help? Thanks in advance.
|
| --
| Message posted via http://www.sqlmonster.com
|
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.SQLSer verDriver");
// 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.

Error in Deployment SSAS Project

Hi all,

I am getting an error while deploying an Analysis services project to my local server.

The error is as follows:

"Internal error: An unexpected exception occured."

I have no idea what this error refers to.....

Regards...

Girija Shankar

Hi,

I encountered this error before.

you should be able to see the detailed erroe message which you can discover from 'Event Viewer'.

Probably you have not started up your Analysis services server (you may want to look at if your analysis services has started up and if not, restart it and deploy your cube again).

Hope it helps.

With best regards,

Yours sincerely,

error handling vbscript via sqlserver

Hi,

I wrote a small vbscript that sends information from a local computer to SQL server.
The method I use for connecting to the SQL server is via ODBC DSN.
This scripts runs on a DMZ located machine, while the SQL server is inside our LAN.

All works fine, but when the network connection between DMZ machine and SQL server lost
for a min. I receive an error.

I monitored the error and tried to error handle it.

after network is back the script is still on, but the error still there.

this is the part where I get the error:

-----------------------
Public function reso(byval res)

'error 3705 occurs here

objConn.Open strConn

Set objrs = objconn.Execute("exec cmp_creation "& Computer &"," & res)


objConn.Close

end function
-----------------------

Help would be much appreciatedWhy not use a DSN-less connection?

Also, if the server is in your DMZ and the SQL Server is behind a firewall (assumed), I would be sure to use the IP address instead of the network name.

Regards,

hmscott|||I'm not sure about the DSN-Less connection.... what would it change regarding my problem? also, what port do I have to open for a DSN-Less connection?|||I don't think that there's anything really wrong with a DSN connection and I'm not positive it would rectify your particular issue. However, DSN connections leave "footprints" on the server in your DMZ (in the registry) which are potentially vulnerable.

DSN and DSN-less connections use the same ports to connect to their respective data sources. Thus, a DSN connection to SQL Server would use port 1433 as would a DSN-less connection.

In general (from what I understand), DSN-less connections are "lighter" on the server, incurring less memory overhead. They are not, however, necessarily faster.

In your script, do you check for the state of the connection? Is it just opened once and left open? Or is it opened and closed as necessary?

Regards,

hmscott

Originally posted by eransp
I'm not sure about the DSN-Less connection.... what would it change regarding my problem? also, what port do I have to open for a DSN-Less connection?