BLOB + MyBatis

[vkrejcirik]

Hi, I have problem while getting BLOB from my Oracle database (10g).

Table:

CREATE TABLE concept (
job_id INTEGER NOT NULL,
context_id INTEGER NOT NULL,
name VARCHAR(100),
id INTEGER NOT NULL,
bytesA BLOB,
bytesB BLOB,
neighborsComputed INTEGER DEFAULT 0,
visited INTEGER DEFAULT 0,
isFactor INTEGER DEFAULT 0);

I insert data with this command:

<insert id="insertConcept" parameterType="Concept">
insert into
concept
(id,
name, context_id, bytesA, bytesB, job_id, neighborsComputed,
isFactor,
visited)
values (#{id}, #{name}, #{context_id}, #{bytesA,jdbcType=BLOB},
#{bytesB,jdbcType=BLOB},
#{job_id}, #{neighborsComputed,jdbcType=INTEGER},
#{isFactor,jdbcType=INTEGER}, #{visited,jdbcType=INTEGER})
</insert>

And select command:

<select id="selectConceptsByJobIDIntent" resultType="Concept"
resultMap="ConceptMap">
select id, context_id, name, bytesA, bytesB,
neighborsComputed from concept where
job_id = #{job_id} AND bytesB =
#{bytesB,jdbcType=BLOB}
</select>

Where map objects has attribute bytesB as byte[]

public class Concept {
int job_id;
int id;
int context_id;
String name;
byte[] bytesA;
byte[] bytesB;
int neighborsComputed;
int visited;
int isFactor;

...


I get this exception:

### Error querying database. Cause: java.sql.SQLException: ORA-00932:
inconsistent datatypes: expected - got BLOB

### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-00932: inconsistent datatypes:
expected - got BLOB

; bad SQL grammar []; nested exception is java.sql.SQLException:
ORA-00932: inconsistent datatypes: expected - got BLOB

at
org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:
98)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:
72)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:
80)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:
80)
at
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:
71)
at org.mybatis.spring.SqlSessionTemplate
$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:346)
at $Proxy6.selectList(Unknown Source)
at
org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:
189)
at
org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:
85)
at
org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:65)
at
org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:38)
at $Proxy33.selectConceptsByJobIDIntent(Unknown Source)
at
com.pikeelectronic.clan.core.DbWorker.computeNeighborsOfAConcept(DbWorker.java:
1244)
at
com.pikeelectronic.clan.core.jobs.BrowseFactorsWorker.run(BrowseFactorsWorker.java:
84)
Caused by: java.sql.SQLException: ORA-00932: inconsistent datatypes:
expected - got BLOB

at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
111)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:
330)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:
287)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:744)
at
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:
218)
at
oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:
812)
at
oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:
1048)
at
oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:
853)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:
1153)
at
oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:
3369)
at
oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:
3475)
at
org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:
172)
at
org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:
172)
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:
39)
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:
55)
at
org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:
41)
at
org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:
216)
at
org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:95)
at
org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:
72)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
75)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
69)
at sun.reflect.GeneratedMethodAccessor46.invoke(Unknown
Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.mybatis.spring.SqlSessionTemplate
$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:338)
... 8 more

Do you have any suggestions please?

Thanks.

[f a v]

I don't know if it works, but, did you try "AND
dbms_lob.compare(bytesB, #{bytesB,jdbcType=BLOB})"?

[Jeff Butler]

Most databases do not allow BLOB columns to be used in a where clause.
I don't know about Oracle specifically, but that may be the issue.

Jeff Butler

--
Sent from my mobile device

[vkrejcirik]

Thanks a lot, it works ;)