SELECT语句中的列别名不适用于SQuirrel SQL + Firebird [英] Column aliasing in SELECT statements doesn't work with SQuirrel SQL + Firebird

问题描述

我尝试使用SQuirrel SQL 3.4 + Firebird 1.5 +驱动程序JDBC Jaybird 2.2.[0 | 1](JVM 1.7.0_03)在SELECT语句中添加列别名.别名列显示为列的原始名称.在另一个工具(DBExpert)中，相同的语句可以正常工作.

声明:SELECT column1 AS alias1, column2 FROM table;
SquirrelSQL中的结果列名称:column1 - column2
DBExpert中的结果列名称:alias1 - column2

尝试在别名名称周围添加'和'，但也无效.

有人有主意吗?

我将JDBC Jaybird驱动程序更新为2.2.1版本(9月30日发行)，问题仍然存在.

问题已解决！

问题是由Squirrel SQL的默认首选项引起的.

要修复此问题，请转到文件"->全局首选项"，在数据类型控件"选项卡中找到常规(适用于所有类型)"并标记组合框使用列标签而不是列名称"在结果标题中".

请参阅屏幕截图: http://i1-win.softpedia-static .com/screenshots/SQuirrel-SQL-Client_4.png?1345342622

享受.

解决方案

问题是由于JDBC规范(至少在历史上)混乱以及Jaybird在结果集中报告为columnNames和columnLabel的内容有所变化元数据.据我所知，在JDBC 2.0和更早版本中，columnName和columnLabel之间的区别没有很好地定义，因此在几乎所有情况下，getColumnName()返回的值都与getColumnLabel()相同(即，如果指定了AS别名).

在JDBC 3.0中，进行了更明显的区分:columnName是表中基础列的名称(如果有)，columnLabel是AS别名(如果已指定)，否则是columnName . Jaybird 2.1.6和更早版本曾经为getColumnLabel()和getColumnName()返回相同的值(即columnLabel)，这在Jaybird 2.2中已更改.

显然，Squirrel默认为columnName的非JDBC兼容选项，而不是columnLabel.正如您已经发现的那样:有一个选择使它可以使用columnLabel代替.您拥有的另一个选项是使用Jaybird 2.2.1或更高版本并添加连接属性columnLabelForName=true.

I tried add an column alias in SELECT statement using SQuirrel SQL 3.4 + Firebird 1.5 + Driver JDBC Jaybird 2.2.[0|1] (JVM 1.7.0_03), but doesn't work. The column aliased appears with the original name of column. In another tool (DBExpert) the same statement works fine.

Statement: SELECT column1 AS alias1, column2 FROM table;
Resultant columns name in SquirrelSQL: column1 - column2
Resultant columns name in DBExpert: alias1 - column2

Tried add ' and " around alias name, but also not work.

Someone have an idea?

Edit: I updated the JDBC Jaybird driver to 2.2.1 version (released Sept/30) and the problem remains.

Edit 2: Problem fixed!

The problenm is caused by default preferences of Squirrel SQL.

To fix it, go to "File" -> "Global Preferences", in tab "Data Type Controls" find "General (applies to all types)" and mark the combo-box "Use column labels instead of column names in result headers".

See the screenshot: http://i1-win.softpedia-static.com/screenshots/SQuirrel-SQL-Client_4.png?1345342622

Enjoy.

解决方案

The problem is due to confusion in the JDBC specs (at least historically) and a change in Jaybird with regard to what it reports as columnNames and columnLabels in the resultset metadata. As far as I am aware in JDBC 2.0 and earlier, the distinction between columnName and columnLabel was not well defined, so in almost all cases getColumnName() returned the same value as getColumnLabel() (ie the AS alias if specified).

With JDBC 3.0 a sharper distinction was made: the columnName was to be the name of the underlying column in a table (if any), the columnLabel is the AS-alias if specified and otherwise the columnName. Jaybird 2.1.6 and earlier used to return the same value for getColumnLabel() and getColumnName() (ie the columnLabel), this was changed in Jaybird 2.2.

Apparently Squirrel defaults to the non-JDBC compliant option of columnName, instead of columnLabel. As you already found out: there is an option to get it to use the columnLabel instead. The other option you have is to use Jaybird 2.2.1 or higher and add the connection property columnLabelForName=true.

这篇关于SELECT语句中的列别名不适用于SQuirrel SQL + Firebird的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！