Hi,

Sorry, but this is support list for database stuff, only for JayBird
JDBC driver. Please ask your questions regarding SQL in IB-Support
list (http://groups.yahoo.com/group/IB-Support).

Your problem is most likely caused by the fact that you use CHAR
instead of VARCHAR. CHAR columns are always padded with spaces to the
full length (this is standard for all databases). Consider changing
definition from CHAR to VARCHAR.

Best regards,
Roman Rokytskyy

--- In Firebird-Java@yahoogroups.com, "zac29349" <zac29349@y...> wrote:
> Hi,
>
> Please accept my apologies for my shallow knowledge of SQL and
> Firebird but I have problem that per my understanding of how things
> are supposed to work make me believe there is a bug in the database
> itself.
>
> I am using Firebird-1.0.0.796-Win32 on Windows 2000 Professional.
> Normally database access is made through the JDBC driver but the
> problem occurs in ISQL also.
>
> The problem is that a trigger that I have defined considers 'TFIX_18'
> equal to 'TFIX_18 '. In this example I have used the single quote
> characters to delineate where spaces are. The single quotes are not
> part of my data, nor are the spaces. Or rather, a requirement of my
> application is that there should be no spaces in the end-user's data.
>
> Anyway, I have two tables named AIRSPACE_NODE and AIR_LINK. The key
> field in the AIRSPACE_NODE table is ASN_ID. ASN_ID is a varying char
> field of 35 characters which cannot be set to null.
>
> In AIR_LINK I have two fields called ASN_INITIAL_ID and ASN_FINAL_ID
> respectively. I shall only discuss the ASN_FINAL_ID field but the
> problem exists for the ASN_INITIAL_ID field also.
>
> As you may have guessed, ASN_INITIAL_ID and ASN_FINAL_ID are foreign
> keys of ASN_ID in the AIRSPACE_NODE table.
>
> I have purposefully avoided using the database's built in referential
> integrity capabilities via usage of the REFERENCES clause. I did this
> because the default error messages produced by the database were too
> technical for usage by my end users.
>
> Therefore I have implemented referential integrity via triggers
> instead. Thus, I can define within the database more meaningful error
> messages.
>
> The specific trigger that I have defined is as follows:
>
> CREATE EXCEPTION EXCEPT_296 'ASN_FINAL_ID not in AIRSPACE_NODE';
> SET TERM !! ;
> CREATE TRIGGER TRIG_156 FOR AIR_LINK
> BEFORE UPDATE POSITION 156 AS
> DECLARE VARIABLE CNT INTEGER ;
> BEGIN
> IF (NEW.ASN_FINAL_ID IS NOT NULL) THEN
> SELECT COUNT(*) FROM AIRSPACE_NODE
> WHERE ASN_ID=NEW.ASN_FINAL_ID INTO :CNT;
> IF (CNT=0) THEN EXCEPTION EXCEPT_295 ;
> END !!
> SET TERM ; !!
>
> Given the above trigger, could someone explain why the following
> occurs in ISQL (and essentially the same behavior in my proprietary
> database GUI editing system)?:
>
> >>>>>>>>>>>>>> START ISQL SESSION
>
> SQL> select ASN_ID from AIRSPACE_NODE;
>
> ASN_ID
> ===================================
>
> TFIX_36
> TASN_CAB_36
> TASN_CAB_18
> TFIX_18
>
> SQL> select ASN_FINAL_ID from AIR_LINK;
>
> ASN_FINAL_ID
> ===================================
>
> TFIX_36
> TASN_CAB_18
>
> SQL> update air_link set ASN_FINAL_ID='TFIX_36 ' where
> ASN_FINAL_ID='TFIX_36';
> SQL> update air_link set ASN_FINAL_ID='TFIX_36s' where
> ASN_FINAL_ID='TFIX_36';
> Statement failed, SQLCODE = -836
>
> exception 296
> -ASN_FINAL_ID not in AIRSPACE_NODE
> SQL> update air_link set ASN_FINAL_ID='TFIX_36 i' where
> ASN_FINAL_ID='TFIX_36';
> Statement failed, SQLCODE = -836
>
> exception 296
> -ASN_FINAL_ID not in AIRSPACE_NODE
>
> >>>>>>>>>>>>>> FINISH ISQL SESSION
>
> As you can see, the first update statement was happily accepted and
> executed. In my proprietary GUI I can determine that after executing
> the equivalent update statement the extra spaces have been placed in
> the ASN_FINAL_ID field.
>
> But, 'TFIX_36 ' is not equal to 'TFIX_36' where I come from, am I
> missing something?
>
> Thank you very much for your time.