算术例外，数字溢出或字符串截断
我使用firebird作为我的数据库和mybatis来操作数据，但是当我使用长度大于5的关键字运行查询时，发生以下错误：
### Cause: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
No message for code 335544914 found.
如果它的长度小于6，则运行良好。 我更新mybatis和firebird到最新版本，结果是一样的。
详细的sql如下所示：
select b.*, bt.type_no as "type.id" , bt.type_dsc "type.name",
bt.show "type.show", bt.del "type.del"
from
book b,book_type bt
where
b.type_no=bt.type_no and bt.del=0
and b.del=0 and b.type_no in (74)
and (( lower(title) like '%aaaaaaaaa%' ) or ( lower(content) like '%aaaaaaaaa%' ))
order by bt.show desc,b.type_no,b.id
后面跟着错误日志：
2012-07-03 23:20:14 [DEBUG](org.apache.ibatis.logging.commons.JakartaCommonsLoggingImpl:42) ooo Using Connection [org.firebirdsql.jdbc.FBConnection@1389b3f]
2012-07-03 23:20:14 [DEBUG](org.apache.ibatis.logging.commons.JakartaCommonsLoggingImpl:42) ==> Preparing: select b.*, bt.type_no as "type.id" , bt.type_dsc "type.name", bt.show "type.show", bt.del "type.del" from book b,book_type bt where b.type_no=bt.type_no and bt.del=0 and b.del=0 and (( lower(title) like ? ) or ( lower(content) like ? )) order by bt.show desc,b.type_no,b.id
2012-07-03 23:20:14 [DEBUG](org.apache.ibatis.logging.commons.JakartaCommonsLoggingImpl:42) ==> Parameters: %aaaaaaaa%(String), %aaaaaaaa%(String)
2012-07-03 23:20:14 [ERROR](frame.FrmMainI:1018) org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
No message for code 335544914 found.
### The error may involve book.note.findByTypeTitleContent-Inline
### The error occurred while setting parameters
### Cause: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
No message for code 335544914 found.
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
No message for code 335544914 found.
### The error may involve book.note.findByTypeTitleContent-Inline
### The error occurred while setting parameters
### Cause: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
No message for code 335544914 found.
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)
at dao.AbsBaseIbatisDAO.selectList(AbsBaseIbatisDAO.java:96)
at dao.NoteDAO.findByTypeTitleContent(NoteDAO.java:19)
at bo.BookBO.findNoteByTypeTitleContent(BookBO.java:118)
at frame.FrmMainI.search(FrmMainI.java:1011)
at frame.FrmMainI$28.keyReleased(FrmMainI.java:839)
at java.awt.Component.processKeyEvent(Component.java:6249)
at javax.swing.JComponent.processKeyEvent(JComponent.java:2801)
at java.awt.Component.processEvent(Component.java:6065)
at java.awt.Container.processEvent(Container.java:2041)
at java.awt.Component.dispatchEventImpl(Component.java:4651)
at java.awt.Container.dispatchEventImpl(Container.java:2099)
at java.awt.Component.dispatchEvent(Component.java:4481)
at java.awt.KeyboardFocusManager.redispatchEvent(KeyboardFocusManager.java:1850)
at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(DefaultKeyboardFocusManager.java:712)
at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(DefaultKeyboardFocusManager.java:990)
at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(DefaultKeyboardFocusManager.java:855)
at java.awt.DefaultKeyboardFocusManager.dispatchEvent(DefaultKeyboardFocusManager.java:676)
at java.awt.Component.dispatchEventImpl(Component.java:4523)
at java.awt.Container.dispatchEventImpl(Container.java:2099)
at java.awt.Window.dispatchEventImpl(Window.java:2478)
at java.awt.Component.dispatchEvent(Component.java:4481)
at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:643)
at java.awt.EventQueue.access$000(EventQueue.java:84)
at java.awt.EventQueue$1.run(EventQueue.java:602)
at java.awt.EventQueue$1.run(EventQueue.java:600)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
at java.awt.EventQueue$2.run(EventQueue.java:616)
at java.awt.EventQueue$2.run(EventQueue.java:614)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:613)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
Caused by: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
No message for code 335544914 found.
at org.firebirdsql.jdbc.AbstractPreparedStatement.internalExecute(AbstractPreparedStatement.java:730)
at org.firebirdsql.jdbc.AbstractPreparedStatement.execute(AbstractPreparedStatement.java:663)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:58)
at $Proxy1.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:57)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:70)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:57)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:141)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:124)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:101)
... 40 more
和mybatis sql的定义：
<select id="findByTypeTitleContent" parameterType="map" resultMap="booknoteMap">
select
b.*,
bt.type_no as "type.id" ,
bt.type_dsc "type.name",
bt.show "type.show",
bt.del "type.del"
from
book b,book_type bt
where
b.type_no=bt.type_no
and bt.del=0
and b.del=0
<if test="typeNos!=null">
and b.type_no in (
<foreach collection="typeNos" item="t" separator=",">
#{t.id}
</foreach>
)
</if>
<if test="keys!=null">
and ((
<foreach collection="keys" item="key" separator="and" >
lower(title) like #{key}
</foreach>
) or (
<foreach collection="keys" item="key" separator="and" >
lower(content) like #{key}
</foreach>
))
</if>
<choose >
<when test="typeNo>0">
order by bt.show desc, b.id
</when>
<otherwise>
order by bt.show desc,b.type_no,b.id
</otherwise>
</choose>
</select>
我做了更多的测试，并发现无论关键字多长时间，标题都可以，当长度大于5时会引起错误Blob sub_type 1 column是varchar（100），content是Blob sub_type 1 ，但无论如何它完全没问题在SQL控制台，这让我很困惑，在java中运行时发生错误
有任何想法吗？ 已经很久了。
你可能遇到过这个bug：http://tracker.firebirdsql.org/browse/CORE-3353这已经在Firebird 2.5.1中解决了。 在这个bug中LIKE BLOB参数被描述为一个VARCHAR（30）参数。 在UTF8连接字符集的情况下，这实际上将被Jaybird当作VARCHAR（5），并且由于Jaybird的特定行为，如果它是以％结尾的类似模式，则它将允许多达6个字符。
所以，升级到Firebird 2.5.1应该可以解决这个问题。
select b.*, bt.type_no as "type.id" , bt.type_dsc "type.name",
bt.show "type.show", bt.del "type.del"
我认为在几个地方缺少“as”这个词
select b.*, bt.type_no as "type.id" , bt.type_dsc AS "type.name",
bt.show AS "type.show", bt.del AS "type.del"
上一篇: arithmetic exception, numeric overflow, or string truncation
下一篇: Cucumber: How to use the same regex in different transforms?