Case sensitivity on GET with Firebird

questions
1

Hi again everyone.

We have Firebird 2.5.7 with the ODBC driver (ISO8859_1, en_uk collation) as the back end. Still using C5PE.

We have a table of postcodes (suburb, state and postcode) which we have populated from our postal service. The fields are all in upper case, as is the text.

Today I stumbled across an unusual error.

Using the data in TPS when I perform a GET, regardless of the case, the GET() succeeds. When I attempt the same using ODBC and Firebird, the GET() succeeds when the text is upper case and fails for all other cases (CAPitalisation, lower case, camel case etc) and the failure returns no error() or fileerror()

with TPS files (works as expected):

attempting with: BURWOOD/VIC/3125
get succeeded
       returned: BURWOOD/VIC/3125

attempting with: Burwood/VIC/3125
get succeeded
       returned: BURWOOD/VIC/3125

attempting with: burwood/VIC/3125
get succeeded
       returned: BURWOOD/VIC/3125

attempting with: BURWOOD/vic/3125
get succeeded
       returned: BURWOOD/VIC/3125

attempting with: burwood/vic/3125
get succeeded
       returned: BURWOOD/VIC/3125

with ODBC/Firebird (fails):

attempting with: BURWOOD/VIC/3125
get succeeded
       returned: BURWOOD/VIC/3125

attempting with: Burwood/VIC/3125
get failed
   error: 0 -
   fileerror:  -
       returned: Burwood/VIC/3125

attempting with: burwood/VIC/3125
get failed
   error: 0 -
   fileerror:  -
       returned: burwood/VIC/3125

attempting with: BURWOOD/vic/3125
get failed
   error: 0 -
   fileerror:  -
       returned: BURWOOD/vic/3125

attempting with: burwood/vic/3125
get failed
   error: 0 -
   fileerror:  -
       returned: burwood/vic/3125

Now I suspect that FB is the issue, but the fact that I get no meaningful error, any error, concerns me.

I have checked my ODBC connection settings, nothing unusual.

Does anyone have any idea what has gone amiss, how I might fix it (with the exception of the obvious UPPER() command)?

FYI, the testing code used. debugmessage outputs text to a file.

POSTCODE:Suburb = 'BURWOOD'
POSTCODE:State_Territory = 'VIC'
POSTCODE:Postcode = '3125'
DO GetPostcode


POSTCODE:Suburb = 'Burwood'
POSTCODE:State_Territory = 'VIC'
POSTCODE:Postcode = '3125'
DO GetPostcode

POSTCODE:Suburb = 'burwood'
POSTCODE:State_Territory = 'VIC'
POSTCODE:Postcode = '3125'
DO GetPostcode                                                                                                   

POSTCODE:Suburb = 'BURWOOD'
POSTCODE:State_Territory = 'vic'
POSTCODE:Postcode = '3125'
DO GetPostcode

POSTCODE:Suburb = 'burwood'
POSTCODE:State_Territory = 'vic'
POSTCODE:Postcode = '3125'
DO GetPostcode


GetPostcode ROUTINE
debugmessage('   attempting with: ' & clip(left(POSTCODE:Suburb)) & '/' & CLIP(LEFT(POSTCODE:State_Territory)) & '/' & CLIP(LEFT(POSTCODE:Postcode)))
GET(POSTCODE,POSTCODE:POSTCODE_PK)
IF ERRORCODE()
    debugmessage('   get failed')
    debugmessage('      error: ' & errorcode() & ' - ' & clip(error()))
    debugmessage('      fileerror: ' & fileerrorcode() & ' - ' & clip(fileerror()))
ELSE
    debugmessage('   get succeeded')
END
debugmessage('          returned: ' & clip(left(POSTCODE:Suburb)) & '/' & CLIP(LEFT(POSTCODE:State_Territory)) & '/' & CLIP(LEFT(POSTCODE:Postcode)))
debugmessage('')

It’s a kooky one, (almost) any ideas or suggestions will be tested.

Cheers,
Andrew.

1 Like
2

EN_UK doesnt end with _CI_AI (Case insensitive, Accent insensitive) so i use ES_ES_CI_AI (spanish)

3

Can you show an excerpt of how you are calling the Get() and subsequent error checking?
It should at least be returning a not found error if it is a case sensitivity issue!

Have you looked at the output of the ODBC driver trace?

4

Hi brahn, code in original post.

I think Dirk might be on to something with _CA_AI collation.

Excerpt from TRACE.LOG

Allocating Statement 2673ba8 on Connection 2671a20 Time Taken:0.00 secs
Getting columns for POSTCODE using Statement 2673ba8 Time Taken:0.00 secs
Binding Column 4 to C type CHAR for Statement 2673ba8 Time Taken:0.00 secs
Fetching Row from Statement 2673ba8 Return Code: 0 Time Taken:0.00 secs
Fetching Row from Statement 2673ba8 Return Code: 0 Time Taken:0.00 secs
Fetching Row from Statement 2673ba8 Return Code: 0 Time Taken:0.00 secs
Fetching Row from Statement 2673ba8 Return Code: 100 Time Taken:0.00 secs
Unbinding Columns Statement 2673ba8 Time Taken:0.00 secs
Closing Statement 2673ba8 Time Taken:0.00 secs
Closing Statement 2673ba8 Time Taken:0.00 secs
Setting Concurrency to Read Only for Statement 2673ba8 Time Taken:0.00 secs
Setting Cursor Type to Forward Only for Statement 2673ba8 Time Taken:0.00 secs
Resetting Parameters Statement 2673ba8 Time Taken:0.00 secs
Preparing Statement 2673ba8 : SELECT "SUBURB","STATE_TERRITORY","POSTCODE" FROM "POSTCODE" WHERE 0 = 1 Time Taken:0.00 secs
Describing Column 1 for Statement 2673ba8 Time Taken:0.00 secs
Getting Auto-increment state for Column 1 for Statement 2673ba8 Time Taken:0.00 secs
Getting Searchable state for Column 1 for Statement 2673ba8 Time Taken:0.00 secs
Describing Column 2 for Statement 2673ba8 Time Taken:0.00 secs
Getting Auto-increment state for Column 2 for Statement 2673ba8 Time Taken:0.00 secs
Getting Searchable state for Column 2 for Statement 2673ba8 Time Taken:0.00 secs
Describing Column 3 for Statement 2673ba8 Time Taken:0.00 secs
Getting Auto-increment state for Column 3 for Statement 2673ba8 Time Taken:0.00 secs
Getting Searchable state for Column 3 for Statement 2673ba8 Time Taken:0.00 secs
POSTCODE          FILE,DRIVER('ODBC'),OWNER(******),NAME('POSTCODE'),THREAD
POSTCODE:POSTCODE_PK KEY(+POSTCODE:SUBURB,+POSTCODE:STATE_TERRITORY,+POSTCODE:POSTCODE),OPT,NOCASE,PRIMARY
POSTCODE:POSTCODE_SUBURBSTATEKEY KEY(+POSTCODE:SUBURB,+POSTCODE:STATE_TERRITORY),DUP,OPT,NOCASE
POSTCODE:POSTCODE_POSTCODEKEY KEY(+POSTCODE:POSTCODE,+POSTCODE:SUBURB),DUP,OPT,NOCASE
POSTCODE:POSTCODE_STATESUBURBKEY KEY(+POSTCODE:STATE_TERRITORY,+POSTCODE:SUBURB),DUP,OPT,NOCASE
                    RECORD
POSTCODE:SUBURB       STRING(40)
POSTCODE:STATE_TERRITORY STRING(3)
POSTCODE:POSTCODE     STRING(4)
                    END
                  END

OPEN(POSTCODE 1)  Time Taken: 0.00 secs
Allocating Statement 2674088 on Connection 2671a20 Time Taken:0.00 secs
Setting Concurrency to Read Only for Statement 2674088 Time Taken:0.00 secs
Setting Cursor Type to Forward Only for Statement 2674088 Time Taken:0.00 secs
Resetting Parameters Statement 2674088 Time Taken:0.00 secs
Preparing Statement 2674088 : SELECT "SUBURB","STATE_TERRITORY","POSTCODE" FROM "POSTCODE" WHERE "SUBURB" = ? AND "STATE_TERRITORY" = ? AND "POSTCODE" = ?  Time Taken:0.00 secs
Setting number of rows to fetch to 1 for Statement 2674088 Time Taken:0.00 secs
Binding Column 1 to C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding Column 2 to C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding Column 3 to C type CHAR for Statement 2674088 Time Taken:0.00 secs
Closing Statement 2674088 Time Taken:0.00 secs
Closing Statement 2674088 Time Taken:0.00 secs
Resetting Parameters Statement 2674088 Time Taken:0.00 secs
Preparing Statement 2674088 : SELECT "SUBURB","STATE_TERRITORY","POSTCODE" FROM "POSTCODE" WHERE "SUBURB" = ? AND "STATE_TERRITORY" = ? AND "POSTCODE" = ?  Time Taken:0.00 secs
Setting number of rows to fetch to 1 for Statement 2674088 Time Taken:0.00 secs
Binding Column 1 to C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding Column 2 to C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding Column 3 to C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding ? 1 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding ? 2 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding ? 3 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Executing prepared Statement 2674088 Time Taken:0.00 secs
Fetching Row from Statement 2674088 Return Code: 0 Time Taken:0.00 secs
GETfilekey(POSTCODE,POSTCODE:POSTCODE_PK[0] 1) Time Taken: 0.00 secs
  POSTCODE:SUBURB   : 'BURWOOD'
  POSTCODE:STATE_TERRITORY: 'VIC'
  POSTCODE:POSTCODE : '3125'

Closing Statement 2674088 Time Taken:0.00 secs
Closing Statement 2674088 Time Taken:0.00 secs
Resetting Parameters Statement 2674088 Time Taken:0.00 secs
Preparing Statement 2674088 : SELECT "SUBURB","STATE_TERRITORY","POSTCODE" FROM "POSTCODE" WHERE "SUBURB" = ? AND "STATE_TERRITORY" = ? AND "POSTCODE" = ?  Time Taken:0.00 secs
Setting number of rows to fetch to 1 for Statement 2674088 Time Taken:0.00 secs
Binding Column 1 to C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding Column 2 to C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding Column 3 to C type CHAR for Statement 2674088 Time Taken:0.00 secs
Closing Statement 2674088 Time Taken:0.00 secs
Closing Statement 2674088 Time Taken:0.00 secs
Resetting Parameters Statement 2674088 Time Taken:0.00 secs
Preparing Statement 2674088 : SELECT "SUBURB","STATE_TERRITORY","POSTCODE" FROM "POSTCODE" WHERE "SUBURB" = ? AND "STATE_TERRITORY" = ? AND "POSTCODE" = ?  Time Taken:0.00 secs
Setting number of rows to fetch to 1 for Statement 2674088 Time Taken:0.00 secs
Binding Column 1 to C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding Column 2 to C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding Column 3 to C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding ? 1 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding ? 2 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding ? 3 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Executing prepared Statement 2674088 Time Taken:0.00 secs
Fetching Row from Statement 2674088 Return Code: 100 Time Taken:0.00 secs
GETfilekey(POSTCODE,POSTCODE:POSTCODE_PK[0] 1) Error: Record Not Found Time Taken: 0.00 secs
  POSTCODE:SUBURB   : 'Burwood'
  POSTCODE:STATE_TERRITORY: 'VIC'
  POSTCODE:POSTCODE : '3125'

Closing Statement 2674088 Time Taken:0.00 secs
Binding ? 1 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding ? 2 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding ? 3 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Executing prepared Statement 2674088 Time Taken:0.00 secs
Fetching Row from Statement 2674088 Return Code: 100 Time Taken:0.00 secs
GETfilekey(POSTCODE,POSTCODE:POSTCODE_PK[0] 1) Error: Record Not Found Time Taken: 0.00 secs
  POSTCODE:SUBURB   : 'burwood'
  POSTCODE:STATE_TERRITORY: 'VIC'
  POSTCODE:POSTCODE : '3125'

Closing Statement 2674088 Time Taken:0.00 secs
Binding ? 1 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding ? 2 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding ? 3 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Executing prepared Statement 2674088 Time Taken:0.00 secs
Fetching Row from Statement 2674088 Return Code: 100 Time Taken:0.00 secs
GETfilekey(POSTCODE,POSTCODE:POSTCODE_PK[0] 1) Error: Record Not Found Time Taken: 0.00 secs
  POSTCODE:SUBURB   : 'BURWOOD'
  POSTCODE:STATE_TERRITORY: 'vic'
  POSTCODE:POSTCODE : '3125'

Closing Statement 2674088 Time Taken:0.00 secs
Binding ? 1 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding ? 2 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Binding ? 3 with C type CHAR for Statement 2674088 Time Taken:0.00 secs
Executing prepared Statement 2674088 Time Taken:0.00 secs
Fetching Row from Statement 2674088 Return Code: 100 Time Taken:0.00 secs
GETfilekey(POSTCODE,POSTCODE:POSTCODE_PK[0] 1) Error: Record Not Found Time Taken: 0.00 secs
  POSTCODE:SUBURB   : 'burwood'
  POSTCODE:STATE_TERRITORY: 'vic'
  POSTCODE:POSTCODE : '3125'
5

Isn’t that what you would expect with case sensitive backend?
The question is, why is that “record not found” error making it back to your error checking code?

6

'spose I would had it dawned on me.

Dirk is correct, there is no CI_AI collation for EN_UK so will have to consider how important such a thing is.

Guess I’ll have to UPPER() it for now.

Thanks.

1 Like