php ibase_fetch_assoc(): Dynamic SQL Error SQL error code = -303 in long SQL

Question

PHP Version 5.5.12

Firebird/InterBase Support dynamic Compile-time Client Library Version Firebird API version 25 Run-time Client Library Version LI-V6.3.2.26539 Firebird 2.5

When I run long sql it's end with error ibase_fetch_assoc(): Dynamic SQL Error SQL error code = -303 arithmetic exception, numeric overflow, or string truncation string right truncation

The same select run correctly in flamerobin.

Any idea what is wrong on PHP side?

SELECT COUNT(*) 
FROM EST_ORDERS_ITEMS 
LEFT JOIN EST_ORDERS AS "order" ON EST_ORDERS_ITEMS.ORDER_ID = "order".ORDER_ID 
LEFT JOIN EST_PORTS AS PORT_OF_LOADING ON "order".PORT_OF_LOADING_ID = PORT_OF_LOADING.PORT_ID 
LEFT JOIN EST_PORTS AS PORT_OF_DISCHARGE ON "order".PORT_OF_DISCHARGE_ID = PORT_OF_DISCHARGE.PORT_ID
LEFT JOIN EST_SUBJECTS AS "delivery" ON EST_ORDERS_ITEMS.DELIVERY_ID = "delivery".SUBJECT_ID 
LEFT JOIN EST_SUBJECTS AS "pickup" ON EST_ORDERS_ITEMS.PICKUP_ID = "pickup".SUBJECT_ID 
LEFT JOIN EST_SUBJECTS AS "shipper" ON EST_ORDERS_ITEMS.SHIPPER_ID = "shipper".SUBJECT_ID 
LEFT JOIN EST_SUBJECTS AS "consignee" ON EST_ORDERS_ITEMS.CONSIGNEE_ID = "consignee".SUBJECT_ID 
LEFT JOIN EST_TAB_AIRPORTS AS AIRPORT_ORIGIN ON "order".AIRPORT_ORIGIN_ID =
AIRPORT_ORIGIN.AIRPORT_ID 
LEFT JOIN EST_TAB_AIRPORTS AS AIRPORT_DEST ON "order".AIRPORT_DEST_ID = AIRPORT_DEST.AIRPORT_ID 
WHERE (EST_ORDERS_ITEMS.DELETED = 0 AND "order".DELETED = 0) AND (("order".PARTNER_ID IN (
SELECT SOURCE_ID 
FROM EST_SUBJECTS_USERS 
WHERE USER_ID = 1375))) AND (CLIENT_REF CONTAINING ('Fakultní nemocnice Hradec Králové') OR CLIENT_REF_2 CONTAINING ('Fakultní nemocnice Hradec Králové') OR
CLIENT_REF_3 CONTAINING ('Fakultní nemocnice Hradec Králové') OR HBL CONTAINING ('Fakultní nemocnice Hradec Králové') OR CNTR_NR CONTAINING ('Fakultní nemocnice Hradec Králové') OR JSD CONTAINING ('Fakultní nemocnice Hradec Králové') OR
"order".NUMBER CONTAINING ('Fakultní nemocnice Hradec Králové') OR PORT_OF_LOADING.NAME CONTAINING ('Fakultní nemocnice Hradec Králové') OR PORT_OF_DISCHARGE.NAME
CONTAINING ('Fakultní nemocnice Hradec Králové') OR "delivery".NAME CONTAINING ('Fakultní nemocnice Hradec Králové') OR "delivery".IDENT_2 CONTAINING ('Fakultní nemocnice Hradec Králové') OR
"pickup".NAME CONTAINING ('Fakultní nemocnice Hradec Králové') OR "pickup".IDENT_2 CONTAINING ('Fakultní nemocnice Hradec Králové') OR "shipper".NAME CONTAINING ('Fakultní nemocnice Hradec Králové') OR
"consignee".NAME CONTAINING ('Fakultní nemocnice Hradec Králové') OR AIRPORT_ORIGIN.IDENT||' - '||AIRPORT_ORIGIN.CITY CONTAINING ('Fakultní nemocnice Hradec Králové')
OR AIRPORT_DEST.IDENT||' - '||AIRPORT_DEST.CITY CONTAINING ('Fakultní nemocnice Hradec Králové'))

What is your connection character set, and what are the definitions (types + lengths) of the fields you are using in the WHERE clauses? Have you tried removing elements from your query to find out what triggers the error? — Mark Rotteveel
– Mark Rotteveel, Commented Sep 19, 2014 at 14:03
It was the length of the field. But still don't understand why in EMS it runs correctly :( — Stenly.k
– Stenly.k, Commented Sep 23, 2014 at 11:22
Was the above query the actual query, or did you use parameters? Literals usually don't cause this error (as the actual length is used), but parameters are limited to the declared length of the field. — Mark Rotteveel
– Mark Rotteveel, Commented Sep 23, 2014 at 14:22

Chloris · Accepted Answer · 2014-09-23 11:25:52Z

2

Recently i ran into the same problem. I solved it by CASTING the search field to BLOB SUB_TYPE 1

So change all CONTAINING occurencies like:

CLIENT_REF CONTAINING ('Fakultní nemocnice Hradec Králové')

to:

CAST(CLIENT_REF AS BLOB SUB_TYPE 1) CONTAINING ('Fakultní nemocnice Hradec Králové')

It's because there is something wrong with php ibase extension and it happens only when you bind your search text as ? (question mark) parameter. When you type it directly to your SQL, it works.

Hope that helps.

answered Sep 23, 2014 at 11:25

Chloris

363 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

php ibase_fetch_assoc(): Dynamic SQL Error SQL error code = -303 in long SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related