arithmetic exception, numeric overflow, or string truncation
I use firebird as my database and mybatis to manipulate the data, but when I run a query with a keyword whose length is greater than 5, then the following errors happen:
### 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.
If it's length is lesser than 6, it runs fine. I update mybatis and firebird to latest version, and the result is the same thing.
Detailed sql is as below:
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
Followed by the error log:
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
And mybatis sql defination:
<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>
I did more test and discovered that title like is ok no matter how long the keyword,content like cause the error when the length is bigger than 5. title column is varchar(100),content is Blob sub_type 1 , but anyway it's totally fine in sql console which makes me so confused, error just happens when running in java
Any ideas? It's been a long time.
You are probably confronted with this bug: http://tracker.firebirdsql.org/browse/CORE-3353 which has been solved in Firebird 2.5.1. In this bug BLOB parameters in a LIKE -condition are described as a VARCHAR(30) parameter. In the case of UTF8 connection characterset this will actually be treated as VARCHAR(5) by Jaybird and due to specific behavior of Jaybird it will then allow upto 6 characters if it is a like pattern which ends in %.
So, upgrading to Firebird 2.5.1 should solve this problem.
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"
上一篇: Android：从JNI方法获取随机数
下一篇: 算术例外，数字溢出或字符串截断