Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

BlobByteArrayType/DefaultLobHandler breaks with Hibernate 3.1.3 and PostgreSQL [SPR-2318] #7007

Closed
spring-issuemaster opened this issue Jul 18, 2006 · 8 comments

Comments

@spring-issuemaster
Copy link
Collaborator

commented Jul 18, 2006

Bill Schneider opened SPR-2318 and commented

BlobByteArrayType and DefaultLobHandler assume they can call ResultSet.getBytes and PreparedStatement.setBytes on underlying BLOB columns. However, VARBINARY and BLOB are distinct JDBC types, and the byte-array methods on the underlying JDBC driver may not always work with BLOB columns. For example, PostgreSQL does not support get/setBytes on an 'oid' column.

This became an issue with Hibernate 3.x because Hibernate now maps BLOBs to oids in PostgreSQL when it used to use bytea in 2.x.

I can think of two potential solutions:

  • use get/setAsBinaryStream across the board instead of get/setBytes
  • make a new PostgreSQLLobHandler to parallel Oracle's (except, the underlying stream methods actually work :-))

It seems like it would be the lesser of two evils to always assume LOBs are to be handled as streams in the underlying JDBC driver, but not sure if that breaks any assumptions on other databases. It may be the case that we need to have distinct LobHandlers for each DB implementation (not much unlike Hibernate dialects) to work around differences in JDBC driver behavior.


Affects: 1.2.8

1 votes, 3 watchers

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator Author

commented Jul 18, 2006

Bill Schneider commented

Correction: setBinaryStream etc. doesn't work with PostgreSQL "oid" columns either.
Again, this is justifiable with strict interpretation of the JDBC spec--JDK 1.4 javadoc lists getBinaryStream and setBinaryStream as working for [LONG]VARBINARY columns, not BLOBs.

So most likely the DefaultLobHandler should call getBlob/setBlob. possibly with a default Blob implementation that just wraps an InputStream, like Hibernate has.

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator Author

commented Dec 31, 2006

Juergen Hoeller commented

I guess we need to add a special LobHandler implementation that uses Blob objects, intended for Postgres. I'm reluctant to change DefaultLobHandler for this, since that class is known to work well with all major databases except Oracle (and Postgres). I'm not sure how other JDBC drivers would react when given custom Blob objects... It's safer to stick with byte array access as default there.

Juergen

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator Author

commented Feb 18, 2008

Ignacio Coloma commented

FYI: This works, but requires a transaction. I must have missed something in my tests (it does not work inside a JpaTests), but when deployed in a webapp works fine:

public class PostgresLobHandler extends DefaultLobHandler {

@Override
public LobCreator getLobCreator() {
	return new PostgresLobCreator();
}

protected class PostgresLobCreator extends DefaultLobCreator {

	// see doc at
	// http://jdbc.postgresql.org/documentation/83/binary-data.html#binary-data-example
	
	// ps.setBlob(int, InputStream) is not yet implemented in postgresql jdbc 8.3, dbcp 1.2, c3p0 0.9.1).
	@Override
	public void setBlobAsBinaryStream(PreparedStatement ps, int paramIndex, InputStream contentStream,
			int contentLength) throws SQLException {

		// BlobImpl is the Hibernate class mentioned here
		ps.setBlob(paramIndex, new BlobImpl(contentStream, contentLength));
		/* 

		this also works:

		PGConnection conn = (PGConnection) new CommonsDbcpNativeJdbcExtractor().getNativeConnectionFromStatement(ps);
		LargeObjectManager manager = conn.getLargeObjectAPI();
		int oid = manager.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
		LargeObject obj = manager.open(oid, LargeObjectManager.WRITE);
		OutputStream outputStream = obj.getOutputStream();
		try {
			IOUtils.copy(contentStream, outputStream);
		} catch (IOException e) {
			throw new SQLException(e);
		} finally {
			IOUtils.closeQuietly(outputStream);
		}
		ps.setInt(paramIndex, oid);

*/
}

}

}

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator Author

commented Mar 6, 2008

Juergen Hoeller commented

Does BLOB reading actually work using getBinaryStream/getBytes against Postgres? I'm wondering since your custom LobHandler seems to override setBlobAsBinaryStream but leaves getBlobAsBinaryStream untouched...

Even in the case of just writing being affected, I suppose we'll have to override setBlobAsBytes as well - for completeness?

Juergen

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator Author

commented Mar 8, 2008

Juergen Hoeller commented

I've added a "wrapAsLob" property to DefaultLobHandler, for passing Blob/Clob instances to the JDBC driver (e.g. for PostgreSQL's driver). This wraps the content/stream in a Blob or Clob instance, respectively, when setting it on the PreparedStatement (using a temporary Blob/Clob wrapper).

I suppose this works fine against PostgreSQL. This will be available in the next 2.5.3 snapshot (http://static.springframework.org/downloads/nightly/snapshot-download.php?project=SPR)

BTW, DefaultLobHandler already has a "streamAsLob" setting since Spring 2.5 - using the JDBC 4.0 setBlob/setClob variants that take a stream argument directly. So in case of a fully compliant JDBC driver, that would be the preferred option. However, there are hardly any JDBC 4.0 drivers out there at this point...

Juergen

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator Author

commented May 12, 2009

Malix Ren commented

getBlobAsBinaryStream doesn't work for oid data type yet.
DefaultLobHandler delegates this method to ResultSet, which will return the byte[] for oid value, not for blob content. For Ex. oid=17273, this will return ['1','7', '2', '7', '3' ], not the real content.

Best regards
Malix

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator Author

commented May 12, 2009

Juergen Hoeller commented

Malix, are you setting DefaultLobHandler's "wrapAsLob" property to "true" there? This should use the Blob API instead of the ResultSet API underneath then...

Juergen

@spring-issuemaster

This comment has been minimized.

Copy link
Collaborator Author

commented May 13, 2009

Malix Ren commented

Thanks Juergen.
I was working with 2.5.6.
DefaultLobHandler.java:
public InputStream getBlobAsBinaryStream(ResultSet rs, int columnIndex) throws SQLException {
logger.debug("Returning BLOB as binary stream");
return rs.getBinaryStream(columnIndex);
}

I noticed it was modified in 3.0 trunk:
https://src.springframework.org/svn/spring-framework/trunk/org.springframework.jdbc/src/main/java/org/springframework/jdbc/support/lob/DefaultLobHandler.java

public InputStream getBlobAsBinaryStream(ResultSet rs, int columnIndex) throws SQLException {
logger.debug("Returning BLOB as binary stream");
if (this.wrapAsLob) {
Blob blob = rs.getBlob(columnIndex);
return blob.getBinaryStream();
}
else {
return rs.getBinaryStream(columnIndex);
}
}

Are you planning to merge back to 2.5.x?

Best Regards
Malix

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.