Search code examples
databasesybasesap-ase

Case sensitive sybase query: Invalid column name

Details:

  • 2 databases: sybase version 15 and sybase version 16
  • 1 table each (identical): AuthRole with columns id, rolename and description
  • Tried both jTDS and jconn drivers

Query:

SELECT t1.roleName FROM AuthRole t1;

Results:

  • Sybase 15: rows returned successfully. 'roleName' could be upper, lower or a mix of case, i.e. not case sensitive
  • Sybase 16: Invalid column name 'roleName'. It will only work with 'rolename' which is the exact case of the column. Anyone know why this would happen and how to resolve it?
Solution

  • If on ASE 15 both queries work - with "rolename" and "roleName" - that means the the sort order in this database is case insensitive.

    If on ASE 16 "rolename" is different than "roleName" - that means the the sort order in this database is case sensitive.

    You can check this by querying:

    if "a" = "A" print "Case insensitive" else print "Case sensitive"

    This setting is set and static for the whole server (and for all the databases that the server contains), but can be changed. Of course changing the sort order is a time consuming process, as it requires to rebuild all indexes based on character types.

    You can check the server sortorder setting:

    exec sp_configure 'sortorder id'

    The information about sort order should be visible in the ASE errorlog when the database server starts:

    00:0002:00000:00002:2017/07/04 16:49:26.35 server  ASE's default unicode sort order is 'binary'.
00:0002:00000:00002:2017/07/04 16:49:26.35 server  ASE's default sort order is:
00:0002:00000:00002:2017/07/04 16:49:26.35 server   'bin_iso_1' (ID = 50)
00:0002:00000:00002:2017/07/04 16:49:26.35 server  on top of default character set:
00:0002:00000:00002:2017/07/04 16:49:26.35 server   'iso_1' (ID = 1).

    In my example the sort order is binary - which is case sensitive.

    Information how to change the sort order for the server is in the ASE manual. Basicaly to change the sort order you need to:

    • add the new sort order using the charset program,
    • change the config parameter 'sortorder id'
    • reboot the ASE server (the server boots, rebuilds the disk devices and then it shuts down)
    • reboot the ASE server again
    • indexes that are build on character types are marked as invalid and need to be rebuild