Results 1 to 10 of 21

Thread: Insert TIFF as BLOB in Oracle fails, how can I configure OracleLobHandler in JPA?

Hybrid View

  1. Feb 9th, 2012, 02:48 AM #1
    jbbarquero
    jbbarquero is offline Senior Member
    Join Date
    May 2006
    Location
    Madrid
    Posts
    383

    Default Insert TIFF as BLOB in Oracle fails, how can I configure OracleLobHandler in JPA?

    Hello,

    I want to store/load TIFF images using a BLOB column in Oracle (9i, I think) but it doesn't work: "java.sql.SQLException: operación no permitida: streams type cannot be used in batching"

    I've created the project using Roo 1.2 and I'd like to know how can I configure JPA in order to manage the BLOBs for Oracle.

    Or any other alternative that fits well in a Spring project.

    Thank you very much.
    Javier.

    ADDITIONAL INFO:

    • The integration tests succeed, but code with an actual TIFF image doesn't work


    Having the Entity:

    Code:
    @RooJavaBean
@RooToString
@RooJpaActiveRecord(identifierColumn = "ID_IMAGEN", table = "T_IMAGEN", versionField = "")
public class Imagen {

//...

    @NotNull
    @Column(name = "IMAGEN")
    @Lob
    @Basic(fetch = FetchType.LAZY)
    private byte[] image;

    @Column(name = "TIPO_MIME")
    @Size(max = 50)
    private String mimeType;

    @Column(name = "NUMERO_PAGINAS")
    @Max(99L)
    private Integer pagesNumber;

//...
    Roo builds a DataOnDemand that uses a String to fill the byte[] field:

    Code:
    privileged aspect ImagenDataOnDemand_Roo_DataOnDemand { 

    public void ImagenEscanerDataOnDemand.setImage(Imagen obj, int index) {
        byte[] imagen = String.valueOf(index).getBytes();
        obj.setImagen(imagen);
    }
    But my code (I put it in the integration test) doesn't work:

    Code:
    @RooIntegrationTest(entity = Imagen.class, findAll=false)
public class ImagenIntegrationTest {

    @Autowired
    //Yepes, "pushed in".
    private ImagenDataOnDemand dod;

	@Test
	public void testSaveImage() throws IOException {

		Imagen imagen = dod.getNewTransientImagen(Integer.MAX_VALUE);
		
		bytes = FileUtils.readFileToByteArray("src/test/resources/images/tiff.tiff");
		imagenEscaner.setImagen(bytes);
		
		imagenEscaner.persist();
		imagenEscaner.flush();

	}

    • I'm not the only one with that problem


    More than a year ago, eleonhardo made the same question: How to configure OracleLobHandler in ROO/JPA

    • Yes, I tried the Jose Delgado's tutorial (AKA delgad9 or JD)


    The instructions from Spring Roo: Saving/Retreving BLOB object in Spring Roo works well with MySQL, and even for PostgreSQL (if you take care of retrieving the data in a transaction due to the way PostgreSQL manages "big data") and even from both DBMS (lot of fun here, because you need to configure the DB separately. The next step would be to use JTA)

    • Finally, I know there are problems managing BLOBs in Oracle, but the only solutions I found are related to Hibernate or plain JDBC.


    According OracleLobHandler (See also LobHandler) Oracle uses "its own proprietary BLOB/CLOB API"

    I've found instructions for configuring OracleLobHandler in Hibernate.

    To change the driver or the Oracle version (it could be a solution) is not an option for the time being, it depends on the Production department.

    Alternatively, I could use plain JDBC, as it's explained in the manual: 13.7.2 Handling BLOB and CLOB objects

    But I'd rather to use JPA, since it's totally configured thanks to Roo.

    This is what I want, and that is the reason for this looooooooooooooong question.

    Thank you very much for reading that.

    J.
    Reply With Quote Reply With Quote
  2. Feb 10th, 2012, 08:45 AM #2
    jbbarquero
    jbbarquero is offline Senior Member
    Join Date
    May 2006
    Location
    Madrid
    Posts
    383

    Default

    It seems I made a very extensive POST for a simple question:

    How can I store a TIFF image in a BLOB Column in Oracle? The code created with Roo that works for MySQL and PostgreSQL, doesn't work for Oracle 8.1 (not Oracle 9, as I said before)
    Reply With Quote Reply With Quote
  3. Feb 10th, 2012, 08:49 AM #3
    jbbarquero
    jbbarquero is offline Senior Member
    Join Date
    May 2006
    Location
    Madrid
    Posts
    383

    Default

    Dear (b)log:

    First attempt: failure.

    applicationContext.xml
    Code:
        <bean class="org.springframework.jdbc.support.nativejdbc.OracleJdbc4NativeJdbcExtractor" id="nativeJdbcExtractor" />
    
    <bean class="org.springframework.jdbc.support.lob.OracleLobHandler" id="lobHandler">
    	<property name="nativeJdbcExtractor" ref="nativeJdbcExtractor" />
    </bean>
    Entity:
    Code:
    public class Imagen {

    @Transactional
    public void save() {
        if (this.entityManager == null) this.entityManager = entityManager();
        this.entityManager.getEntityManagerFactory().getProperties().put("lobHandler", this.lobHandler);
        this.entityManager.persist(this);
    }
    Test:
    Code:
    public class ImagenIntegrationTest {

		//Imagen.persist();
		Imagen.save();
		
        
    }
    ERROR:
    java.lang.UnsupportedOperationException
    at java.util.Collections$UnmodifiableMap.put(Collecti ons.java:1301)
    at com.malsolo.blobs.domain.Imagen.save(Imagen.java:1 02)
    at com.malsolo.blobs.domain.ImagenIntegrationTest.tes tSaveImageForGi(ImagenIntegrationTest.java:87)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:79)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:618)
    at org.junit.runners.model.FrameworkMethod$1.runRefle ctiveCall(FrameworkMethod.java:44)
    at org.junit.internal.runners.model.ReflectiveCallabl e.run(ReflectiveCallable.java:15)
    at org.junit.runners.model.FrameworkMethod.invokeExpl osively(FrameworkMethod.java:41)
    at org.junit.internal.runners.statements.InvokeMethod .evaluate(InvokeMethod.java:20)
    at org.springframework.test.context.junit4.statements .RunBeforeTestMethodCallbacks.evaluate(RunBeforeTe stMethodCallbacks.java:74)
    at org.springframework.test.context.junit4.statements .RunAfterTestMethodCallbacks.evaluate(RunAfterTest MethodCallbacks.java:83)
    at org.springframework.test.context.junit4.statements .SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.springframework.test.context.junit4.SpringJUni t4ClassRunner.runChild(SpringJUnit4ClassRunner.jav a:231)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild( BlockJUnit4ClassRunner.java:48)
    at org.junit.runners.ParentRunner$3.run(ParentRunner. java:231)
    at org.junit.runners.ParentRunner$1.schedule(ParentRu nner.java:60)
    at org.junit.runners.ParentRunner.runChildren(ParentR unner.java:229)
    at org.junit.runners.ParentRunner.access$000(ParentRu nner.java:50)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRu nner.java:222)
    at org.springframework.test.context.junit4.statements .RunBeforeTestClassCallbacks.evaluate(RunBeforeTes tClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements .RunAfterTestClassCallbacks.evaluate(RunAfterTestC lassCallbacks.java:71)
    at org.junit.runners.ParentRunner.run(ParentRunner.ja va:292)
    at org.springframework.test.context.junit4.SpringJUni t4ClassRunner.run(SpringJUnit4ClassRunner.java:174 )
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestR eference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecutio n.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:197)
    Reply With Quote Reply With Quote
  4. Feb 10th, 2012, 08:50 AM #4
    jbbarquero
    jbbarquero is offline Senior Member
    Join Date
    May 2006
    Location
    Madrid
    Posts
    383

    Default

    Second attempt: failure

    applicationContext.xml
    Code:
        <bean class="org.springframework.jdbc.support.nativejdbc.OracleJdbc4NativeJdbcExtractor" id="nativeJdbcExtractor" />
    
    <bean class="org.springframework.jdbc.support.lob.OracleLobHandler" id="lobHandler">
    	<property name="nativeJdbcExtractor" ref="nativeJdbcExtractor" />
    </bean>

    <bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
    	<property name="dataSource" ref="dataSource" />
    </bean>
    Entity:
    Code:
    public class Imagen {

    @Transactional
    public void save() {
        jdbcTemplate.execute("insert into gi_imagen_escaner "
	        + " (alto, ancho, fecha_alta, datos, ... ) "
	        + " values (?, ?, ?, ?, ...) "
        	,
        	new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
				
				@Override
				protected void setValues(PreparedStatement ps, LobCreator lobCreator)
						throws SQLException, DataAccessException {
					
					ps.setInt(1, Imagen.this.alto);
					ps.setInt(2, Imagen.this.ancho);
					ps.setDate(3, new java.sql.Date(Imagen.this.fechaAlta.getTime()));
					lobCreator.setBlobAsBytes(ps, 5, Imagen.this.imagen);
					...
				}
			}
        );
    }
    Test:
    Code:
    public class ImagenIntegrationTest {

		//Imagen.persist();
		Imagen.save();
    ERROR
    java.lang.NoSuchMethodError: java/sql/Connection.unwrap(Ljava/lang/ClassLjava/lang/Object;
    at org.springframework.jdbc.support.nativejdbc.Jdbc4N ativeJdbcExtractor.doGetNativeConnection(Jdbc4Nati veJdbcExtractor.java:98)
    at org.springframework.jdbc.support.nativejdbc.Native JdbcExtractorAdapter.getNativeConnection(NativeJdb cExtractorAdapter.java:99)
    at org.springframework.jdbc.support.nativejdbc.Native JdbcExtractorAdapter.getNativeConnectionFromStatem ent(NativeJdbcExtractorAdapter.java:135)
    at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.getOracleConnection(OracleLob Handler.java:527)
    at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.createLob(OracleLobHandler.ja va:486)
    at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.setBlobAsBytes(OracleLobHandl er.java:368)
    at com.malsolo.blobs.domain.Imagen$1.setValues(Imagen .java:129)
    at org.springframework.jdbc.core.support.AbstractLobC reatingPreparedStatementCallback.doInPreparedState ment(AbstractLobCreatingPreparedStatementCallback. java:72)
    at org.springframework.jdbc.core.support.AbstractLobC reatingPreparedStatementCallback.doInPreparedState ment(AbstractLobCreatingPreparedStatementCallback. java:1)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:587)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:615)
    at com.malsolo.blobs.domain.Imagen.save(Imagen.java:1 14)
    at com.malsolo.blobs.domain.ImagenIntegrationTest.tes tSaveImageForGi(ImagenIntegrationTest.java:87)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:79)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:618)
    at org.junit.runners.model.FrameworkMethod$1.runRefle ctiveCall(FrameworkMethod.java:44)
    at org.junit.internal.runners.model.ReflectiveCallabl e.run(ReflectiveCallable.java:15)
    at org.junit.runners.model.FrameworkMethod.invokeExpl osively(FrameworkMethod.java:41)
    at org.junit.internal.runners.statements.InvokeMethod .evaluate(InvokeMethod.java:20)
    at org.springframework.test.context.junit4.statements .RunBeforeTestMethodCallbacks.evaluate(RunBeforeTe stMethodCallbacks.java:74)
    at org.springframework.test.context.junit4.statements .RunAfterTestMethodCallbacks.evaluate(RunAfterTest MethodCallbacks.java:83)
    at org.springframework.test.context.junit4.statements .SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.springframework.test.context.junit4.SpringJUni t4ClassRunner.runChild(SpringJUnit4ClassRunner.jav a:231)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild( BlockJUnit4ClassRunner.java:48)
    at org.junit.runners.ParentRunner$3.run(ParentRunner. java:231)
    at org.junit.runners.ParentRunner$1.schedule(ParentRu nner.java:60)
    at org.junit.runners.ParentRunner.runChildren(ParentR unner.java:229)
    at org.junit.runners.ParentRunner.access$000(ParentRu nner.java:50)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRu nner.java:222)
    at org.springframework.test.context.junit4.statements .RunBeforeTestClassCallbacks.evaluate(RunBeforeTes tClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements .RunAfterTestClassCallbacks.evaluate(RunAfterTestC lassCallbacks.java:71)
    at org.junit.runners.ParentRunner.run(ParentRunner.ja va:292)
    at org.springframework.test.context.junit4.SpringJUni t4ClassRunner.run(SpringJUnit4ClassRunner.java:174 )
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestR eference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecutio n.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:197)

    That's true for the JAR that I'm using
    Reply With Quote Reply With Quote
  5. Feb 10th, 2012, 08:53 AM #5
    jbbarquero
    jbbarquero is offline Senior Member
    Join Date
    May 2006
    Location
    Madrid
    Posts
    383

    Default

    Third attempt: failure

    SimpleNativeJdbcExtractor instead of OracleJdbc4NativeJdbcExtractor

    applicationContext.xml

    Code:
        <bean class="org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtractor" id="nativeJdbcExtractor" />
    ERROR
    org.springframework.dao.InvalidDataAccessApiUsageE xception: OracleLobCreator needs to work on [oracle.jdbc.OracleConnection], not on [org.apache.commons.dbcp.PoolableConnection]: specify a corresponding NativeJdbcExtractor; nested exception is java.lang.ClassCastException: org.apache.commons.dbcp.PoolableConnection incompatible with oracle.jdbc.OracleConnection
    at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.createLob(OracleLobHandler.ja va:505)
    at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.setBlobAsBytes(OracleLobHandl er.java:368)
    at com.malsolo.blobs.domain.Imagen$1.setValues(Imagen .java:129)
    at org.springframework.jdbc.core.support.AbstractLobC reatingPreparedStatementCallback.doInPreparedState ment(AbstractLobCreatingPreparedStatementCallback. java:72)
    at org.springframework.jdbc.core.support.AbstractLobC reatingPreparedStatementCallback.doInPreparedState ment(AbstractLobCreatingPreparedStatementCallback. java:1)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:587)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:615)
    at com.malsolo.blobs.domain.Imagen.save(Imagen.java:1 14)
    at com.malsolo.blobs.domain.ImagenIntegrationTest.tes tSaveImageForGi(ImagenIntegrationTest.java:87)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:79)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:618)
    at org.junit.runners.model.FrameworkMethod$1.runRefle ctiveCall(FrameworkMethod.java:44)
    at org.junit.internal.runners.model.ReflectiveCallabl e.run(ReflectiveCallable.java:15)
    at org.junit.runners.model.FrameworkMethod.invokeExpl osively(FrameworkMethod.java:41)
    at org.junit.internal.runners.statements.InvokeMethod .evaluate(InvokeMethod.java:20)
    at org.springframework.test.context.junit4.statements .RunBeforeTestMethodCallbacks.evaluate(RunBeforeTe stMethodCallbacks.java:74)
    at org.springframework.test.context.junit4.statements .RunAfterTestMethodCallbacks.evaluate(RunAfterTest MethodCallbacks.java:83)
    at org.springframework.test.context.junit4.statements .SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.springframework.test.context.junit4.SpringJUni t4ClassRunner.runChild(SpringJUnit4ClassRunner.jav a:231)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild( BlockJUnit4ClassRunner.java:48)
    at org.junit.runners.ParentRunner$3.run(ParentRunner. java:231)
    at org.junit.runners.ParentRunner$1.schedule(ParentRu nner.java:60)
    at org.junit.runners.ParentRunner.runChildren(ParentR unner.java:229)
    at org.junit.runners.ParentRunner.access$000(ParentRu nner.java:50)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRu nner.java:222)
    at org.springframework.test.context.junit4.statements .RunBeforeTestClassCallbacks.evaluate(RunBeforeTes tClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements .RunAfterTestClassCallbacks.evaluate(RunAfterTestC lassCallbacks.java:71)
    at org.junit.runners.ParentRunner.run(ParentRunner.ja va:292)
    at org.springframework.test.context.junit4.SpringJUni t4ClassRunner.run(SpringJUnit4ClassRunner.java:174 )
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestR eference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecutio n.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:197)
    Caused by: java.lang.ClassCastException: org.apache.commons.dbcp.PoolableConnection incompatible with oracle.jdbc.OracleConnection
    at oracle.jdbc.driver.OracleConnection.physicalConnec tionWithin(OracleConnection.java:5041)
    at oracle.sql.BLOB.createTemporary(BLOB.java:767)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:79)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:618)
    at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.prepareLob(OracleLobHandler.j ava:541)
    at org.springframework.jdbc.support.lob.OracleLobHand ler$OracleLobCreator.createLob(OracleLobHandler.ja va:488)
    ... 36 more
    Reply With Quote Reply With Quote
  6. Feb 10th, 2012, 09:13 AM #6
    jbbarquero
    jbbarquero is offline Senior Member
    Join Date
    May 2006
    Location
    Madrid
    Posts
    383

    Default

    Fourth attempt: success?

    applicationContext.xml
    Code:
    <bean class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor" id="nativeJdbcExtractor" />
    Bibliography:

    java2s.com: 28.43.1.Use Lob Handler To Handle Large Chunk of Data

    java2s.com: Insert Clob Data

    java2s.com: 28.43.2.Deal With Binary Stream With Lob Handler

    java2s.com: Insert Clob Data

    Spring framework reference: 13.7.2 Handling BLOB and CLOB objects
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules