JRruby、Sybase JDBC 和 DBI - 使用 AS 子句问题获取列名
我有一个使用 JRuby 解释器运行的 ruby 脚本。 该脚本使用 DBI 和 Sybase JDBC（jTDS3.jar 和 jconn3.jar）连接到 Sybase 数据库
我的问题是我有一个更改表列名称的选择查询。 例如：
SELECT
t.TRANSACTION as 'business_transaction',
t.TRADE_CURRENCY as 'currency',
t.CURRENCY as 'settlement_currency'
...etc...
FROM
TRADE t
...etc...
我的问题是直接使用 文档 中的示例
sth = dbh.execute(stmt)
printf "Number of rows: %d\n", rows.size
printf "Number of columns: %d\n", sth.column_names.size
sth.column_info.each_with_index do |info, i|
printf "--- Column %d (%s) ---\n", i, info["name"]
end
或简单地
sth = dbh.execute(stmt)
rows = sth.fetch_all
col_names = sth.column_names
sth.finish
DBI::Utils::TableFormatter.ascii(col_names, rows)
使用示例当我在查询中使用“as”子句设置名称时，并非所有名称都会出现。有些是原始字段名称，有些是我指定的名称。
例如，它们将列出如下：
--- Column 0 (TRANSACTION) ---
--- Column 1 (TRADE_CURRENCY) ---
--- Column 2 (settlement_currency) ---
或
TRANSACTION
TRADE_CURRENCY
settlement_currency
在 Squirrel SQL Client 中测试此列时，列的命名正确，那么这是 DBI 或 Sybase JDBC 驱动程序中的错误吗？或者我做错了什么？
任何帮助将不胜感激
I have a ruby script which I run using the JRuby Interpreter.
The script connects to a Sybase database using DBI and Sybase JDBC (jTDS3.jar and jconn3.jar)
My problem is that I have a select query that alters the column names of table.
For example:
SELECT
t.TRANSACTION as 'business_transaction',
t.TRADE_CURRENCY as 'currency',
t.CURRENCY as 'settlement_currency'
...etc...
FROM
TRADE t
...etc...
My problem is when using the examples directly from the documentation
sth = dbh.execute(stmt)
printf "Number of rows: %d\n", rows.size
printf "Number of columns: %d\n", sth.column_names.size
sth.column_info.each_with_index do |info, i|
printf "--- Column %d (%s) ---\n", i, info["name"]
end
or simply
sth = dbh.execute(stmt)
rows = sth.fetch_all
col_names = sth.column_names
sth.finish
DBI::Utils::TableFormatter.ascii(col_names, rows)
Not ALL the names come out as I set them using the 'as' clause in the query. Some are the original field names and some are the names I have specified.
For example they will list like:
--- Column 0 (TRANSACTION) ---
--- Column 1 (TRADE_CURRENCY) ---
--- Column 2 (settlement_currency) ---
or
TRANSACTION
TRADE_CURRENCY
settlement_currency
When testing this in Squirrel SQL Client the columns are correctly named so is this a bug in DBI or the Sybase JDBC drivers? or am I doing something wrong?
Any help would be greatly appreciated
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我猜想这是 DBI 中的一个错误，因为 JDBC 驱动程序可能已经投入使用多年了。您可能需要联系 DBI 人员，看看他们是否有建议。
I would guess that it's a bug in DBI, since the JDBC drivers have presumably been put through the mill for years. You may want to contact DBI folks to see if they have a suggestion.
我可以确认这是Sybase驱动程序。使用 jTDS (v1.2.5)
http://jtds.sourceforge.net/ 我可以获取在查询中正确定义的所有列名称，并且可以确认原始问题不是 DBI
如果关注此问题的任何人想知道我如何让 jtds 在 jRuby 下与 DBI 一起工作，请查看我以前的一个 问题 - 确实花了一些时间 - 指定 URL 使用时 DBI 有点有趣
请注意Jdbc 大写 J
我希望这可以节省很多时间;-)
I can confirm it is the Sybase drivers. Using the jTDS (v1.2.5) from
http://jtds.sourceforge.net/ I can get all the column names correctly defined in my query and can confirm that the original issue is NOT DBI
If anyone who is following this wondered how I got jtds working with DBI under jRuby please take a look at one of my former questions - it did take some time - and DBI is a little funny when specifying the URL use
Please note the capital J for Jdbc
I hope this saves someone a lot of time ;-)
Sybase 6.0 JDBC 驱动程序在处理别名方面有一些“有趣”的行为。如果定义了别名，则 resultSet.findColumn 方法将在表列名称查找中失败。
您可以在连接上设置一些属性来更改其中一些行为或仅使用 JTDS 驱动程序。
http://手册.sybase.com/onlinebooks/group-jc/jcg0600e/prjdbc/@Generic__BookTextView/1072;pt=1072;uf=0
Sybase 6.0 JDBC drivers has some "interesting" behavior dealing with aliases. The resultSet.findColumn method will fail on a table column name lookup if an alias is defined.
There are some properties you can set on the connect to change some of these behaviors or just use the JTDS drivers.
http://manuals.sybase.com/onlinebooks/group-jc/jcg0600e/prjdbc/@Generic__BookTextView/1072;pt=1072;uf=0