Sql 303 ошибка

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.

Вопрос больше из спортивного интереса.

Есть Firebird 2.0.3.12981 и табличка в базе данных созданная следующим запросом:

CREATE TABLE LANGUAGE (

    ID      INTEGER NOT NULL PRIMARY KEY,
    NAME    VARCHAR(3) NOT NULL UNIQUE COLLATE PXW_CYRL,
    BASIC   CHAR(1) DEFAULT '0' NOT NULL CHECK (BASIC IN ('0', '1')),
    DSCR    VARCHAR(100) NOT NULL COLLATE PXW_CYRL
);

И есть код обращения к этой табличке.

<?php

$res = false;

$db = ibase_pconnect("localhost:d:/cms/j/database/base.gdb", 'sysdba', 'masterkey');
$tr = ibase_trans(IBASE_WRITE | IBASE_COMMITTED | IBASE_WAIT | IBASE_REC_NO_VERSION, $db);

if ($query = ibase_query($db, $tr, "SELECT COUNT(*) FROM LANGUAGE WHERE NAME = ?", "english")) {

    $row = ibase_fetch_row($query);
    $res = $row[0] != 0;
    ibase_free_result($query);
}

ibase_close($db);

var_dump($res);

?>

И есть результат.

Dynamic SQL Error SQL error code = -303 arithmetic exception, numeric overflow, or string truncation

Почему если использовать параметризированный запрос ошибка есть:

ibase_query($db, $tr, «SELECT COUNT(*) FROM LANGUAGE WHERE NAME = ?», «english»)

А если параметр в запросе не использовать – ошибки нет.

ibase_query($db, $tr, «SELECT COUNT(*) FROM LANGUAGE WHERE NAME = ‘english'»)

Чем такое поведение можно объяснить?

Hi,

I am getting the error below when Adding a Child. The size of the EPIM FRee database is 7G = 7,086,788KB. I am not doing anything special. I am only adding a new child. Have a reach a limit of some sort? At the moment, I can still read current notes and stuff, delete etc. but can’t add. I’ve empty the trash, run optimize and still the same problem. I thought I can delete some, empty trash and then delete but it is the same error message. I am now trying to make a copy of the current file and then delete lots to size it down and se if that helps. Hopefully it does but then that means I am hitting some kind of limits somewhere, number of notes

Any advice much appreciated. Thanks in advance.

There was an error during the execution of this program.
Click «Send» to submit the report to the developer. Please also provide any steps necessary to reproduce the problem.

Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation.

——————————————————————————
Exception log with detailed tech info. Generated on 25/09/2014 2:05 p.m..
You may send it to the application vendor, helping him to understand what had happened.
Application title: [ Name of the EPIM file and the EPIM Executable = Essential PImportEssentialPIM.exe ]
Application: EssentialPIM Portable 5.82
——————————————————————————
Exception class: EIBInterBaseError
Exception message: Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation.
Exception address: 00874014
——————————————————————————
Main thread ID = 9664
Exception thread ID = 9664
——————————————————————————
Object Logs:
12839646 SetViewMode OK
12839521 SetViewMode 3
12837837 SetViewMode OK
12837649 SetViewMode 0
12823984 DBOpen

——————————————————————————
Active Controls:
12863873 ExceptionDialog TextMemo
12859240 NotePropDialog Panel1 NameEd
12857181 MainForm FormPan FormPan2 NotesForm TreesTC NotesVT
12855652 MainForm FormPan FormPan2 NotesForm RichEditFrame1 TabsTC RichViewEdit
12855137 AdvertForm HtmlViewer1 TPaintPanel ThtTabControl
12854903 MainForm FormPan FormPan2 NotesForm RichEditFrame1 TabsTC RichViewEdit
12837337 MainForm
12824186 PassDialog GroupBox1 PasswordEd

——————————————————————————
Exception stack
Stack list, generated 25/09/2014 2:05 p.m.
[0087400F] IB.IBDataBaseError (Line 420, «IB.pas» + 39) [0089804E] IBDatabase.TIBTransaction.Call (Line 1665, «IBDatabase.pas» + 11) [0087BB21] IBSQL.TIBSQL.Call (Line 2483, «IBSQL.pas» + 3) [0087BE04] IBSQL.TIBSQL.ExecQuery (Line 2556, «IBSQL.pas» + 50) [00D44166] SyncObj.TNote.StoreUserDataToSQL (Line 5310, «SyncObj.pas» + 7) [0054DB5F] Vcl.Controls.TControl.Click (Line 7340, «Vcl.Controls.pas» + 9)
——————————————————————————
Call stack for main thread
Stack list, generated 25/09/2014 2:05 p.m.
(000460F4) [775F70F4]
——————————————————————————
System : Windows 7 Professional, Version: 6.1, Build: 1DB1, «Service Pack 1»
Memory: 2993; free 1891
Display : 1920×1080 pixels, 32 bpp, 96 PPI
——————————————————————————

Submitted by: Łukasz Bączek (baczek00)

Hello Mark,

Sallis has written a small test in Java showing the problem:

Below you have the entire structure of the table:
__._,_.___

/******************************************************************************/
/* Generated by IBExpert 2013.10.8.1 2014-05-16 00:36:35 */
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE ‘:C:TEST.FDB’
USER ‘SYSDBA’ PASSWORD ‘masterkey’
PAGE_SIZE 16384
DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;

/******************************************************************************/
/* Domains */
/******************************************************************************/

CREATE DOMAIN CHECKBOX AS
SMALLINT
DEFAULT 0
CHECK (value is null or value in (0, 1));

/******************************************************************************/
/* Generators */
/******************************************************************************/

CREATE GENERATOR GEN_OSOBY_ID;
SET GENERATOR GEN_OSOBY_ID TO 138;

/******************************************************************************/
/* Tables */
/******************************************************************************/

CREATE TABLE OSOBY (
ID_OSOBY BIGINT NOT NULL,
IMIE VARCHAR(40) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI,
NAZWISKO VARCHAR(120) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI,
ADRES VARCHAR(150) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,
KOD VARCHAR(6) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,
MIASTO VARCHAR(30) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,
TELEFON VARCHAR(12) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,
EMAIL VARCHAR(60) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,
DYSTRYBUTOR CHECKBOX,
EKSPERT CHECKBOX,
PRZEDSTAWICIEL CHECKBOX,
WLASCICIEL CHECKBOX,
UWAGI VARCHAR(500) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI
);

INSERT INTO OSOBY (ID_OSOBY, IMIE, NAZWISKO, ADRES, KOD, MIASTO, TELEFON, EMAIL, DYSTRYBUTOR, EKSPERT, PRZEDSTAWICIEL, WLASCICIEL, UWAGI) VALUES (137, ‘TESTA’, ‘TESTb’, ‘xxx’, ’00-000′, ‘TestC’, ‘1234567’, ‘DEMO@DEMO.DEMO’, 0, 0, 0, 0, NULL);
INSERT INTO OSOBY (ID_OSOBY, IMIE, NAZWISKO, ADRES, KOD, MIASTO, TELEFON, EMAIL, DYSTRYBUTOR, EKSPERT, PRZEDSTAWICIEL, WLASCICIEL, UWAGI) VALUES (138, ‘Jan’, ‘Kowalski’, ‘AnioĹ‚Ăłw 5′, ’83-100’, ‘Warszawa’, ‘0123456’, ‘jan.kowalski@demo.demo’, 0, 0, 0, 0, NULL);

COMMIT WORK;

/******************************************************************************/
/* Primary Keys */
/******************************************************************************/

ALTER TABLE OSOBY ADD CONSTRAINT PK_OSOBY PRIMARY KEY (ID_OSOBY);

/******************************************************************************/
/* Triggers */
/******************************************************************************/

SET TERM ^ ;

/******************************************************************************/
/* Triggers for tables */
/******************************************************************************/

/* Trigger: OSOBY_BI */
CREATE TRIGGER OSOBY_BI FOR OSOBY
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (new.id_osoby is null) then
new.id_osoby = gen_id(gen_osoby_id,1);
end
^

SET TERM ; ^

And so reads the data:

*/
public static void main(String[] args) {
try {
FBWrappingDataSource pool = new FBWrappingDataSource();
pool.setMaxPoolSize(5);
pool.setMinPoolSize(2);
pool.setMaxStatements(2);
pool.setMaxIdleTime(30 * 60 * 60);
pool.setDatabase(«<//127.0.0.1/C:/TEST.FDB>»);
pool.setUserName(«SYSDBA»);
pool.setPassword(«masterkey»);
pool.setCharSet(«UTF8»);
pool.setEncoding(«UTF8»);
Connection connection = pool.getConnection();
try {
ResultSet result = null;
StringBuilder SBSQL = new StringBuilder();
SBSQL.append(«SELECT * FROM (SELECT id_osoby, imie, nazwisko, adres, kod, miasto, telefon ,email, COALESCE(przedstawiciel,0) AS przedstawiciel, COALESCE(wlasciciel,0) AS wlasciciel, COALESCE(ekspert,0) AS ekspert, COALESCE(dystrybutor,0) AS dystrybutor, uwagi FROM osoby ) WHERE imie LIKE ? OR nazwisko LIKE ? OR adres LIKE ? OR kod LIKE ? OR miasto LIKE ? OR telefon LIKE ?»);
PreparedStatement ps = connection.prepareStatement(SBSQL.toString());
ps.setString(1, «%TestA%»);
ps.setString(2, «%TestA%»);
ps.setString(3, «%TestA%»);
ps.setString(4, «%TestA%»);
ps.setString(5, «%TestA%»);
ps.setString(6, «%TestA%»);
result = ps.executeQuery();
while (result.next()) {
System.out.println(result.getInt(«IMIE»));
}
} finally {
connection.close();
}
} catch (SQLException ex) {
Logger.getLogger(TestConnectionPool.class.getName()).log(Level.SEVERE, null, ex);
}

SQL query which I used here works without a problem in IBExpert. I think that the problem lies in the driver Javabird and not as previously thought on the length of the field.

maj 16, 2014 12:51:02 AM testconnectionpool.TestConnectionPool main
SEVERE: null
org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
string right truncation
at org.firebirdsql.jdbc.AbstractPreparedStatement.internalExecute(AbstractPreparedStatement.java:801)
at org.firebirdsql.jdbc.AbstractPreparedStatement.executeQuery(AbstractPreparedStatement.java:182)
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:601)
at org.firebirdsql.pool.PooledPreparedStatementHandler.invoke(PooledPreparedStatementHandler.java:171)
at org.firebirdsql.pool.$Proxy1.executeQuery(Unknown Source)
at testconnectionpool.TestConnectionPool.main(TestConnectionPool.java:49)
Caused by: org.firebirdsql.gds.GDSException: Dynamic SQL Error
SQL error code = -303
arithmetic exception, numeric overflow, or string truncation
string right truncation
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.readStatusVector(AbstractJavaGDSImpl.java:2092)
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.receiveResponse(AbstractJavaGDSImpl.java:2042)
at org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl.iscDsqlExecute2(AbstractJavaGDSImpl.java:1149)
at org.firebirdsql.gds.impl.GDSHelper.executeStatement(GDSHelper.java:227)
at org.firebirdsql.jdbc.AbstractPreparedStatement.internalExecute(AbstractPreparedStatement.java:797)
… 8 more

My Firebird is: WI-6.3.0.26074 Firebird 2.5
My Javabird are: Jaybird-2.2.5-JDK_1.7

Regards