Advantage 数据库服务器 (ADS) CIChar 数据类型 ISNULL
不幸的是，我们使用的是 Advantage Database Server Torture Edition 版本 8.1。 当我完成我的项目后，我听说数据库被配置为区分大小写。因此我更改了表结构，将所有 Char 数据类型更改为 CIChar，这是一个大小写不敏感的 char 字段。但我在执行程序时遇到此错误：
Advantage.Data.Provider.AdsException：错误 7200：AQE 错误：状态 = HY000；本机错误 = 2214; [扩展系统][SQL 优势 Engine]无效强制：结果 表达式是一个不明确的字符 类型。
我发现 ISNULL(myciChar,'') 导致了这个问题，但我不明白，为什么？ 我该如何解决这个问题？ cichar 数据类型还有其他已知问题吗？
任何帮助将不胜感激。谢谢。
[更新]
我找到了此错误的原因。有两点需要澄清。
- 数据库的版本为 8.1，但数据架构师的版本为 7.1，并且在本地模式下，它采用架构师引擎版本 7.1。这意味着这是 v7.1 问题。
- isnull 函数中的第二个参数在版本 7.1 中默认区分大小写排序规则，而我的列 mytext 是 cichar，这是不明确的字符类型。因此，如果有人遇到同样的问题，它将在 v7.1 中工作，并带有整理声明：
works in v7.1:
select myid, isnull(mytext, '-' COLLATE ads_default_ci) as mytext from mytable
error in v7.1:
select myid, isnull(mytext, '-') as mytext from mytable
Unfortunately we are using the Advantage Database Server Torture Edition Version 8.1.
After I had finished my project, I heard that the data base is configured to be case sensitive. So I changed the table structure, all Char data types to CIChar, which is a case insensitive char field. But I get this error while executing my program:
Advantage.Data.Provider.AdsException:Error 7200: AQE Error: State =
HY000; NativeError = 2214;
[Extended Systems][Advantage SQL
Engine]Invalid coercion: Result of
expression is an ambiguous character
type.
I found that the ISNULL(myciChar,'') is causing this problem, but I don't understand, WHY?
How could I solve this problem? Is there other known issues with the cichar data type?
Any help will be appreciated. Thanks.
[update]
I found the reason for this error. There was two points to clarify.
- The data base has the version 8.1, but the data architect has the version 7.1 and in local mode it takes the architect engine version 7.1. That means it's a v7.1 issue.
- The 2nd parameter in the isnull function is in default case sensitive collation in version 7.1 while my column mytext is cichar and that's the ambiguous character type. So if someone has the same problem, it will work in v7.1 with the collate declaration:
works in v7.1:
select myid, isnull(mytext, '-' COLLATE ads_default_ci) as mytext from mytable
error in v7.1:
select myid, isnull(mytext, '-') as mytext from mytable
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
可能需要更多地查看您的表情才能告诉您为什么会出现此错误。它与 ISNULL 的关系不如与正在使用 ISNULL 的表达式的关系大。引擎可能遇到了比较，并且无法自动确定您是否希望比较区分大小写。请参阅文档，其中描述了不区分大小写的优先级和强迫。
如果没有在表达式中使用，它可能只是一个错误（我刚刚尝试使用 10.1 并且它有效），请验证您使用的是最新版本的 8.1，即 8.10.0.38
Probably need to see more of your expression to tell you why you are getting this error. It doesn't have as much to do with the ISNULL as it does the expression the ISNULL is being used in. The engine likely encountered a comparison and couldn't automatically determine if you wanted the comparison to be case sensitive or not. See the documentation which describes case insensitive precedence and coercion.
If not used in an expression, it might just be a bug (I just tried with 10.1 and it worked), verify you are using the latest version of 8.1, which is 8.10.0.38
我认为版本 7 不支持 cichar 数据类型，版本 6 当然不支持。我使用的是版本 6，直接转到版本 8，发现一切都可以与版本 6 客户端驱动程序配合使用，直到我使用 cichar 数据类型，然后 Advantage 就结束了。然后我必须将所有客户端更新到与服务器相同的版本。最好保持所有版本相同。
I don't think that version 7 supports the cichar data type, version 6 certainly doesn't. I was using version 6 and went straight to 8 and found that everything worked with the version 6 client driver, until I used the cichar data type and then it was game-over for Advantage. I then had to update all the clients to the same version as the server. It is best to keep all the versions the same.