Subject | Cannot prepare IB_Query with this SQL... |
---|---|
Author | tibotaka |
Post date | 2002-02-19T10:18:42Z |
Sorry to trouble all of you, but this IB_query I can't prepare:
SELECT CUSTOMER.ID
FROM CUSTOMER
WHERE (CUSTOMER.CITY_CODE = :ACITY_CODE)
AND (((:ACITY_STREET_ID IS NULL) AND (CUSTOMER.CITY_STREET_ID IS
NULL)) OR (CUSTOMER.CITY_STREET_ID = :ACITY_STREET_ID))
AND (CUSTOMER.STREET_NUMBER_UPPER = :ASTREET_NUMBER) AND
(CUSTOMER.BUILDING_REG_UPPER = :ABUILDING_REG)
Error message:
ISC ERROR CODE:335544569
Dynamic SQL Error
SQL Error code = -804
Data type unknown
Critical part of SQL is "(:ACITY_STREET_ID IS NULL)" (without this
part I can prepare the IB_Query...)
The curiosity is that in database is declared stored procedure:
CREATE PROCEDURE SPS_CUSTOMER$ADDRESS_EXISTS
(ACITY_CODE VARCHAR(5),
ACITY_STREET_ID INTEGER,
ASTREET_NUMBER VARCHAR(10),
ABUILDING_REG VARCHAR(10))
RETURNS
(ARESULT SMALLINT)
AS
BEGIN
...
IF (EXISTS(SELECT * FROM CUSTOMER
WHERE (CUSTOMER.CITY_CODE = :ACITY_CODE)
AND (((:ACITY_STREET_ID IS NULL) AND (CUSTOMER.CITY_STREET_ID IS
NULL)) OR (CUSTOMER.CITY_STREET_ID = :ACITY_STREET_ID))
AND (CUSTOMER.STREET_NUMBER_UPPER = :ASTREET_NUMBER) AND
(CUSTOMER.BUILDING_REG_UPPER = :ABUILDING_REG)))
THEN
ARESULT = 1;
ELSE
ARESULT = 0;
...
END
with the same WHERE part of SQL which works fine...
I'm using IBObjects 4.2Fr (with patch for Lookup bug) and Firebird 1
RC2.
Sorry for long post, but can anybody help me, please?
Thanks in advance...
Tibor
SELECT CUSTOMER.ID
FROM CUSTOMER
WHERE (CUSTOMER.CITY_CODE = :ACITY_CODE)
AND (((:ACITY_STREET_ID IS NULL) AND (CUSTOMER.CITY_STREET_ID IS
NULL)) OR (CUSTOMER.CITY_STREET_ID = :ACITY_STREET_ID))
AND (CUSTOMER.STREET_NUMBER_UPPER = :ASTREET_NUMBER) AND
(CUSTOMER.BUILDING_REG_UPPER = :ABUILDING_REG)
Error message:
ISC ERROR CODE:335544569
Dynamic SQL Error
SQL Error code = -804
Data type unknown
Critical part of SQL is "(:ACITY_STREET_ID IS NULL)" (without this
part I can prepare the IB_Query...)
The curiosity is that in database is declared stored procedure:
CREATE PROCEDURE SPS_CUSTOMER$ADDRESS_EXISTS
(ACITY_CODE VARCHAR(5),
ACITY_STREET_ID INTEGER,
ASTREET_NUMBER VARCHAR(10),
ABUILDING_REG VARCHAR(10))
RETURNS
(ARESULT SMALLINT)
AS
BEGIN
...
IF (EXISTS(SELECT * FROM CUSTOMER
WHERE (CUSTOMER.CITY_CODE = :ACITY_CODE)
AND (((:ACITY_STREET_ID IS NULL) AND (CUSTOMER.CITY_STREET_ID IS
NULL)) OR (CUSTOMER.CITY_STREET_ID = :ACITY_STREET_ID))
AND (CUSTOMER.STREET_NUMBER_UPPER = :ASTREET_NUMBER) AND
(CUSTOMER.BUILDING_REG_UPPER = :ABUILDING_REG)))
THEN
ARESULT = 1;
ELSE
ARESULT = 0;
...
END
with the same WHERE part of SQL which works fine...
I'm using IBObjects 4.2Fr (with patch for Lookup bug) and Firebird 1
RC2.
Sorry for long post, but can anybody help me, please?
Thanks in advance...
Tibor