区分大小写的sybase查询:无效的列名

Details:

细节:

2 databases: sybase version 15 and sybase version 16
2数据库:sybase版本15和sybase版本16
1 table each (identical): AuthRole with columns id, rolename and description
每个表(相同):AuthRole with column id, rolename和description
Tried both jTDS and jconn drivers
尝试了jTDS和jconn驱动程序

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 15:成功返回的行。“roleName”可以是大写、小写或大小写混合，即不区分大小写
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?
Sybase 16:无效的列名“roleName”。它只适用于“rolename”，这正是列的具体情况。有人知道为什么会发生这种情况吗?

3 个解决方案

#1

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

如果在ASE 15上，这两个查询都可以工作——使用“rolename”和“rolename”——这意味着这个数据库中的排序顺序不区分大小写。

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

如果在ASE 16上“rolename”与“rolename”不同——这意味着这个数据库中的排序顺序是区分大小写的。

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:

您可以查看服务器sortorder设置:

exec sp_configure 'sortorder id'

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

关于排序顺序的信息应该在数据库服务器启动时的ASE errorlog中显示:

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:

关于如何更改服务器的排序顺序的信息在ASE手册中。更改排序次序的基本步骤如下:

add the new sort order using the charset program,
使用charset程序添加新的排序顺序，
change the config parameter 'sortorder id'
更改配置参数'sortorder id'
reboot the ASE server (the server boots, rebuilds the disk devices and then it shuts down)
重新启动ASE服务器(服务器引导，重新构建磁盘设备，然后关闭)
reboot the ASE server again
重新启动ASE服务器
indexes that are build on character types are marked as invalid and need to be rebuild
基于字符类型构建的索引被标记为无效，需要重新构建

#2

Sounds like an issue with the sort order, eg:

听起来像是排序问题。

ASE 15 is configured with a case-insensitive sort order
ASE 15配置为不区分大小写的排序顺序
ASE 16 is configured with a case-sensitive sort order
ASE 16配置为区分大小写的排序顺序

You should be able to confirm the above by running sp_helpsort.

您应该能够通过运行sp_helpsort来确认上面的内容。

In ASE, case (in)sensitivity applies to data as well as identifiers (eg, table/column names).

在ASE中，case (In)敏感性适用于数据和标识符(如表/列名)。

To get ASE 16 to function like ASE 15, the DBA will need to change the sort order in the ASE 16 dataserver (I'd suggest they also verify the character set while they're at it).

要使ASE 16像ASE 15一样工作，DBA将需要更改ASE 16数据服务器中的排序顺序(我建议他们在处理时也验证字符集)。

Keep in mind that changing the sort order (and/or character set) is a dataserver-wide configuration and will require (at a minimum) a rebuild of all indexes and re-running of update index statistics. [For more info the DBA should refer to the ASE System Administration Guide, Chapter on Configuring Character Sets, Sort Orders and Languages.]

请记住，更改排序顺序(和/或字符集)是一个数据服务器范围的配置，需要(至少)重新构建所有索引并重新运行更新索引统计信息。[要了解更多信息，DBA应该参考ASE系统管理指南，关于配置字符集、排序命令和语言的章节。]

#3

Off the top of my head:

从我的头顶上:

In older versions of Sybase ASE you had to carefully set the case-sensitivity at server installation time. The installer defaults to case-sensitive. Maybe the admin who installed ASE15 noticed this (and changed the default to case-insensitive), whereas the admin who installed your ASE16 didn't.

在Sybase ASE的旧版本中，您必须在服务器安装时仔细设置大小写敏感性。安装程序默认为区分大小写。可能安装了ASE15的管理员注意到了这一点(并将默认设置为不区分大小写)，而安装了ASE16的管理员没有注意到这一点。

Yes, case-Sensitivity is a property of the Server. You can change it at a later time with sp_configure or ALTER DATABASE, or both (I don't remember and I don't have the time to look it up). You can also use a graphical admin tool to change the server default sort order.

是的，区分大小写是服务器的一个属性。您可以稍后使用sp_configure或ALTER DATABASE或者两者都修改它(我不记得了，我没有时间去查找)。您还可以使用图形管理工具来更改服务器的默认排序顺序。

In any case only databases created after that configuration change will be affected. Confusingly, older databases will still be case-sensitive, or lots of warnings will be issued. This is because in your older tables, all primary keys (PK) are implemented as indices, in assume case-sensitivity, and PKs and the PK indices cannot be changed by an installer or a config wizard. In fact, you have to drop and re-create the indices and run dbcc something (again I don't remember).

无论如何，只有在配置更改之后创建的数据库才会受到影响。令人困惑的是，旧的数据库仍然是大小写敏感的，或者会发出大量警告。这是因为在旧的表中，所有主键(PK)都是作为索引实现的，假设是区分大小写的，而PKs和PK索引不能被安装程序或配置向导更改。事实上，您必须删除并重新创建索引并运行dbcc之类的东西(我也不记得了)。

For small databases, this drop-and-recreate of indices can of cause be done (use a script or a database reengineering tool to do so). For larger databases this can take some time.

对于小的数据库，这种删除和重新创建索引的操作是可以完成的(使用脚本或数据库重构工具)。对于较大的数据库，这可能需要一些时间。

Maybe it's different for ASE16 -check the docuemntation

也许这是不同的，16 -检查文件记录。

#1