Map SQL-Type LVARCHAR(x) to JDBC-Type VARCHAR globally?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Map SQL-Type LVARCHAR(x) to JDBC-Type VARCHAR globally?

Frank Langelage
Hi,

third party application Camunda BPM uses this query to access data from tables in a generic way:
  <select id="selectTableData" parameterType="org.camunda.bpm.engine.impl.TablePageQueryImpl" resultType="map">
    select * from ${tableName}
   
<if test="order != null">
      order by ${order}
   
</if>
 
</select>

When running the testuie against an IBM Informix 12.1 Database with latest JDBC driver I get this error:
testPurgeDmnProcess(org.camunda.bpm.engine.test.standalone.db.entitymanager.PurgeDatabaseTest)  Time elapsed: 0.485 sec  <<< ERROR!
org
.camunda.bpm.engine.ProcessEngineException: Process engine persistence exception
    at org
.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.rethrow(CommandInvocationContext.java:148)
    at org
.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:171)
    at org
.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:113)
    at org
.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:66)
    at org
.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
    at org
.camunda.bpm.engine.impl.ManagementServiceImpl.purge(ManagementServiceImpl.java:186)
    at org
.camunda.bpm.engine.test.standalone.db.entitymanager.PurgeDatabaseTest.testPurgeDmnProcess(PurgeDatabaseTest.java:345)
    at sun
.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun
.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun
.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java
.lang.reflect.Method.invoke(Method.java:606)
    at org
.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org
.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org
.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org
.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org
.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org
.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org
.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
    at org
.camunda.bpm.engine.test.ProcessEngineRule$1.evaluate(ProcessEngineRule.java:165)
    at org
.junit.rules.RunRules.evaluate(RunRules.java:20)
    at org
.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    at org
.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
    at org
.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
    at org
.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org
.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org
.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org
.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org
.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org
.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org
.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:264)
    at org
.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:153)
    at org
.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:124)
    at org
.apache.maven.surefire.booter.ForkedBooter.invokeProviderInSameClassLoader(ForkedBooter.java:200)
    at org
.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:153)
    at org
.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:103)
Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: java.sql.SQLException: Can't convert to: binary stream
### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/TableData.xml
### The error may involve org.camunda.bpm.engine.impl.TablePageMap.selectTableData
### The error occurred while handling results
### SQL: select * from ACT_HI_DEC_IN
### Cause: java.sql.SQLException: Can't convert to: binary stream
    at org
.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)
    at org
.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111)
    at org
.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
    at org
.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectList(DbSqlSession.java:88)
    at org
.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectListWithRawParameter(DbEntityManager.java:168)
    at org
.camunda.bpm.engine.impl.cmd.PurgeDatabaseAndCacheCmd.purgeDatabase(PurgeDatabaseAndCacheCmd.java:91)
    at org
.camunda.bpm.engine.impl.cmd.PurgeDatabaseAndCacheCmd.execute(PurgeDatabaseAndCacheCmd.java:62)
    at org
.camunda.bpm.engine.impl.cmd.PurgeDatabaseAndCacheCmd.execute(PurgeDatabaseAndCacheCmd.java:46)
    at org
.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
    at org
.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:104)
   
... 32 more
Caused by: java.sql.SQLException: Can't convert to: binary stream
    at com.informix.util.IfxErrMsg.getSQLMinorException(Unknown Source)
    at com.informix.jdbc.IfxObject.toClob(Unknown Source)
    at com.informix.jdbc.IfxResultSet.getClob(Unknown Source)
    at com.informix.jdbc.IfxResultSet.getClob(Unknown Source)
    at org.apache.ibatis.type.ClobTypeHandler.getNullableResult(ClobTypeHandler.java:41)
    at org.apache.ibatis.type.ClobTypeHandler.getNullableResult(ClobTypeHandler.java:28)
    at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:55)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyAutomaticMappings(DefaultResultSetHandler.java:416)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:339)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:294)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:269)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:239)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:153)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:60)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)
    ... 40 more

Accessing the table data with a specific query like this one
  <resultMap id="historicDecisionInputInstanceResultMap" type="org.camunda.bpm.engine.impl.history.event.HistoricDecisionInputInstanceEntity">
   
<id property="id" column="ID_" jdbcType="VARCHAR" />
   
<result property="decisionInstanceId" column="DEC_INST_ID_" jdbcType="VARCHAR"/>
   
<result property="clauseId" column="CLAUSE_ID_" jdbcType="VARCHAR"/>
   
<result property="clauseName" column="CLAUSE_NAME_" jdbcType="VARCHAR"/>
   
<result property="serializerName" column="VAR_TYPE_" jdbcType="VARCHAR"/>
   
<result property="byteArrayValueId" column="BYTEARRAY_ID_" jdbcType="VARCHAR" />
   
<result property="doubleValue" column="DOUBLE_" jdbcType="DOUBLE" />
   
<result property="textValue" column="TEXT_" jdbcType="VARCHAR" />
   
<result property="textValue2" column="TEXT2_" jdbcType="VARCHAR" />
   
<result property="longValue" column="LONG_" jdbcType="BIGINT" />
   
<result property="tenantId" column="TENANT_ID_" jdbcType="VARCHAR" />
 
</resultMap>
works without any problems.
Using the specific query the jdbcType is set to VARCHAR whereas the generic query obviously causes the column to be handled as a CLOB column. SQL type of column is lvarchar(4000).

Is there a way to change the mapping to columns of SQL-Type LVARCHAR to JDBC-Type VARCHAR globally?

Regards, Frank

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Map SQL-Type LVARCHAR(x) to JDBC-Type VARCHAR globally?

Kazuki Shimizu
Probably, you can customize to use the `StringTypeHandler` for `LONGVARCHAR`.

If you use the mybatis-config.xml to configure mybatis configuration, please try adding type hander mapping as follows:

<typeHandlers>
 
<typeHandler javaType="string" jdbcType="LONGVARCHAR" handler="org.apache.ibatis.type.StringTypeHandler" />
</typeHandlers>

See http://www.mybatis.org/mybatis-3/configuration.html#typeHandlers.

Thanks.

On Saturday, January 7, 2017 at 4:25:40 AM UTC+9, Frank Langelage wrote:
Hi,

third party application Camunda BPM uses this query to access data from tables in a generic way:
  <select id="selectTableData" parameterType="org.camunda.bpm.engine.impl.TablePageQueryImpl" resultType="map">
    select * from ${tableName}
   
<if test="order != null">
      order by ${order}
   
</if>
 
</select>

When running the testuie against an IBM Informix 12.1 Database with latest JDBC driver I get this error:
testPurgeDmnProcess(org.camunda.bpm.engine.test.standalone.db.entitymanager.PurgeDatabaseTest)  Time elapsed: 0.485 sec  <<< ERROR!
org
.camunda.bpm.engine.ProcessEngineException: Process engine persistence exception
    at org
.camunda.bpm.engine.impl.interceptor.CommandInvocationContext.rethrow(CommandInvocationContext.java:148)
    at org
.camunda.bpm.engine.impl.interceptor.CommandContext.close(CommandContext.java:171)
    at org
.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:113)
    at org
.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:66)
    at org
.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
    at org
.camunda.bpm.engine.impl.ManagementServiceImpl.purge(ManagementServiceImpl.java:186)
    at org
.camunda.bpm.engine.test.standalone.db.entitymanager.PurgeDatabaseTest.testPurgeDmnProcess(PurgeDatabaseTest.java:345)
    at sun
.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun
.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun
.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java
.lang.reflect.Method.invoke(Method.java:606)
    at org
.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org
.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org
.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org
.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org
.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org
.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org
.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
    at org
.camunda.bpm.engine.test.ProcessEngineRule$1.evaluate(ProcessEngineRule.java:165)
    at org
.junit.rules.RunRules.evaluate(RunRules.java:20)
    at org
.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    at org
.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
    at org
.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
    at org
.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org
.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org
.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org
.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org
.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org
.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org
.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:264)
    at org
.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:153)
    at org
.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:124)
    at org
.apache.maven.surefire.booter.ForkedBooter.invokeProviderInSameClassLoader(ForkedBooter.java:200)
    at org
.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:153)
    at org
.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:103)
Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: java.sql.SQLException: Can't convert to: binary stream
### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/TableData.xml
### The error may involve org.camunda.bpm.engine.impl.TablePageMap.selectTableData
### The error occurred while handling results
### SQL: select * from ACT_HI_DEC_IN
### Cause: java.sql.SQLException: Can't convert to: binary stream
    at org
.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)
    at org
.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111)
    at org
.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
    at org
.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectList(DbSqlSession.java:88)
    at org
.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectListWithRawParameter(DbEntityManager.java:168)
    at org
.camunda.bpm.engine.impl.cmd.PurgeDatabaseAndCacheCmd.purgeDatabase(PurgeDatabaseAndCacheCmd.java:91)
    at org
.camunda.bpm.engine.impl.cmd.PurgeDatabaseAndCacheCmd.execute(PurgeDatabaseAndCacheCmd.java:62)
    at org
.camunda.bpm.engine.impl.cmd.PurgeDatabaseAndCacheCmd.execute(PurgeDatabaseAndCacheCmd.java:46)
    at org
.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
    at org
.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:104)
   
... 32 more
Caused by: java.sql.SQLException: Can't convert to: binary stream
    at com.informix.util.IfxErrMsg.getSQLMinorException(Unknown Source)
    at com.informix.jdbc.IfxObject.toClob(Unknown Source)
    at com.informix.jdbc.IfxResultSet.getClob(Unknown Source)
    at com.informix.jdbc.IfxResultSet.getClob(Unknown Source)
    at org.apache.ibatis.type.ClobTypeHandler.getNullableResult(ClobTypeHandler.java:41)
    at org.apache.ibatis.type.ClobTypeHandler.getNullableResult(ClobTypeHandler.java:28)
    at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:55)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyAutomaticMappings(DefaultResultSetHandler.java:416)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:339)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:294)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:269)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:239)
    at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:153)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:60)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)
    ... 40 more

Accessing the table data with a specific query like this one
  <resultMap id="historicDecisionInputInstanceResultMap" type="org.camunda.bpm.engine.impl.history.event.HistoricDecisionInputInstanceEntity">
   
<id property="id" column="ID_" jdbcType="VARCHAR" />
   
<result property="decisionInstanceId" column="DEC_INST_ID_" jdbcType="VARCHAR"/>
   
<result property="clauseId" column="CLAUSE_ID_" jdbcType="VARCHAR"/>
   
<result property="clauseName" column="CLAUSE_NAME_" jdbcType="VARCHAR"/>
   
<result property="serializerName" column="VAR_TYPE_" jdbcType="VARCHAR"/>
   
<result property="byteArrayValueId" column="BYTEARRAY_ID_" jdbcType="VARCHAR" />
   
<result property="doubleValue" column="DOUBLE_" jdbcType="DOUBLE" />
   
<result property="textValue" column="TEXT_" jdbcType="VARCHAR" />
   
<result property="textValue2" column="TEXT2_" jdbcType="VARCHAR" />
   
<result property="longValue" column="LONG_" jdbcType="BIGINT" />
   
<result property="tenantId" column="TENANT_ID_" jdbcType="VARCHAR" />
 
</resultMap>
works without any problems.
Using the specific query the jdbcType is set to VARCHAR whereas the generic query obviously causes the column to be handled as a CLOB column. SQL type of column is lvarchar(4000).

Is there a way to change the mapping to columns of SQL-Type LVARCHAR to JDBC-Type VARCHAR globally?

Regards, Frank

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.