将 Sybase ASE 12.5.4 与 jTDS 驱动程序与 JRuby 结合使用

发布于 2024-08-06 06:26:00 字数 2925 浏览 10 评论 0原文

问题

我正在尝试构建一个小型 ruby​​ 脚本(每天将使用 jruby 运行一次)来连接到 Sybase ASE 12.5.4 数据库并执行复杂的查询。

最终,我打算对数据进行一些处理,并将新数据插入 MySQL 表中,以便在 Rails 应用程序中使用。

环境

  • jruby v1.4.0
  • java v1.6.0_15
  • Ubuntu Karmic 上的

JRuby 安装的 Gems

  • activerecord-jdbc-adapter (0.9.1)
  • activerecord-2.3.4

Jruby Lib目录

  • jtds-1.2.5

查询

SET rowcount 10 
SELECT * FROM TEST_TABLE

代码片段

require 'java'
require 'jtds-1.2.5.jar'
require 'rubygems'
require 'active_record'

config = {
    :username => 'railstest',
    :password => 'railstest',
    :adapter  => 'jdbc',
    :dialect  => 'sybase',
    :host     => 'localhost',
    :database => 'railstest',
    :port     => '5000',
    :driver   => 'net.sourceforge.jtds.jdbc.Driver',
    :url      => 'jdbc:jtds:sybase://localhost:5000/railstest'
}
ActiveRecord::Base.establish_connection(config).connection.execute(-- QUERY --)

我可以确认它连接到数据库。尽管我在从数据库表中选择 10 行时遇到问题。

产生

对于execute方法:

/usr/local/bin/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract_adapter.rb:219:in `log': ActiveRecord::ActiveRecordError: The executeUpdate method must not return a result set.: SET rowcount 10 SELECT * FROM TEST_TABLE  (ActiveRecord::StatementInvalid)
        from /usr/local/bin/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.2/lib/active_record/connection_adapters/jdbc_adapter.rb:559:in `execute'
        from db-test.rb:21

对于select_rows方法:

/usr/local/bin/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract_adapter.rb:219:in `log': ActiveRecord::ActiveRecordError: The executeUpdate method must not return a result set.: SET rowcount 10 SELECT * FROM TEST_TABLE (ActiveRecord::StatementInvalid)
        from /usr/local/bin/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.2/lib/active_record/connection_adapters/jdbc_adapter.rb:559:in `execute'
        from /usr/local/bin/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.2/lib/active_record/connection_adapters/jdbc_adapter.rb:629:in `select'
        from /usr/local/bin/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.2/lib/active_record/connection_adapters/jdbc_adapter.rb:550:in `select_rows'
        from db-test.rb:21

错误指出我不应该返回结果集,但我使用哪种方法并不重要使用、执行、select_rows 等都不起作用。

关于查询的另一件事。我原来的查询相当复杂,我声明变量、删除临时表并创建临时表以及填充和从中选择。使用 Squirrel SQL,我可以执行一次并获得结果。使用 DBI,我无法在一次执行中完成此操作,有谁知道我是否可以只执行整个事情一次,还是必须将其拆分?

有人可以给我任何帮助吗?我是否正确使用 jTDS? 非常感谢。

Problem

I am trying to build a small ruby script - which will be run using jruby once a day - to connect to a Sybase ASE 12.5.4 database and perform a complex query.

Ultimately I intend to do some processing on the data and insert the new data in a MySQL table for use within a rails application.

Environment

  • jruby v1.4.0
  • java v1.6.0_15
  • on Ubuntu Karmic

JRuby Installed Gems

  • activerecord-jdbc-adapter (0.9.1)
  • activerecord-2.3.4

Jruby Lib Directory

  • jtds-1.2.5

Query

SET rowcount 10 
SELECT * FROM TEST_TABLE

Code Snippet

require 'java'
require 'jtds-1.2.5.jar'
require 'rubygems'
require 'active_record'

config = {
    :username => 'railstest',
    :password => 'railstest',
    :adapter  => 'jdbc',
    :dialect  => 'sybase',
    :host     => 'localhost',
    :database => 'railstest',
    :port     => '5000',
    :driver   => 'net.sourceforge.jtds.jdbc.Driver',
    :url      => 'jdbc:jtds:sybase://localhost:5000/railstest'
}
ActiveRecord::Base.establish_connection(config).connection.execute(-- QUERY --)

I can confirm this connects to the DB. Although I am having issues just selecting 10 rows from a database table.

Produces

For execute method:

/usr/local/bin/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract_adapter.rb:219:in `log': ActiveRecord::ActiveRecordError: The executeUpdate method must not return a result set.: SET rowcount 10 SELECT * FROM TEST_TABLE  (ActiveRecord::StatementInvalid)
        from /usr/local/bin/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.2/lib/active_record/connection_adapters/jdbc_adapter.rb:559:in `execute'
        from db-test.rb:21

For select_rows method:

/usr/local/bin/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract_adapter.rb:219:in `log': ActiveRecord::ActiveRecordError: The executeUpdate method must not return a result set.: SET rowcount 10 SELECT * FROM TEST_TABLE (ActiveRecord::StatementInvalid)
        from /usr/local/bin/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.2/lib/active_record/connection_adapters/jdbc_adapter.rb:559:in `execute'
        from /usr/local/bin/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.2/lib/active_record/connection_adapters/jdbc_adapter.rb:629:in `select'
        from /usr/local/bin/jruby-1.4.0/lib/ruby/gems/1.8/gems/activerecord-jdbc-adapter-0.9.2/lib/active_record/connection_adapters/jdbc_adapter.rb:550:in `select_rows'
        from db-test.rb:21

The error states that I should not return a results set but it doesn't matter which method I use, execute, select_rows etc nothing works.

One more thing regarding queries. My original query is rather complex, I decalre variables, drop temporary tables and create temporary tables as well as populate and select from them. Using Squirrel SQL I can execute once and gain a result. Using DBI I was unable to do this in one execution, does anyone know if I can just execute the whole thing once or will I have to split it up?

Would anyone be able to give me any assistance please? Am I using jTDS properly?
Many thanks in advance.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

黑白记忆 2024-08-13 06:26:00

我已经按照@lollipopman的建议使用Sybase驱动程序一段时间了,这对开始工作很有帮助,但是当我构建更复杂的查询时,我不断遇到问题,所以我尝试重新审视原来的问题,并用了大约一个小时的时间我开始工作。

此处查找开源 jTDS 驱动程序

require java
require jtds-1.2.5.jar
require rubygems
require dbi

dbh = DBI.connect('dbi:Jdbc:jtds:sybase://<host>:<port>/<db>', <username>, <password>, {'driver' => 'net.sourceforge.jtds.jdbc.Driver'} )

这就是使用 JRuby 连接到 Sybase 数据库所需的全部内容DBI

希望这对某人有帮助!

I had been using the Sybase drivers for sometime as suggested by @lollipopman which was helpful in getting going but as I built more complex queries I kept running into issues so I tried to revisit the original problem and with an hour or so I go it working.

Find the open source jTDS drivers here

require java
require jtds-1.2.5.jar
require rubygems
require dbi

dbh = DBI.connect('dbi:Jdbc:jtds:sybase://<host>:<port>/<db>', <username>, <password>, {'driver' => 'net.sourceforge.jtds.jdbc.Driver'} )

And that is all that is needed to connect to your Sybase Database with JRuby and DBI

Hope this helps someone!

云巢 2024-08-13 06:26:00

不完全相关,但这是使用 jruby、sybase jdbc 和 dbi 时所需要的:

require 'java'
require './jars/jTDS3.jar'
require './jars/jconn3.jar'
require "rubygems"
require "dbi"

dbh = DBI.connect('dbi:Jdbc:sybase:Tds:foobar:2460/testdb', 'sa', 'password',
  {'driver' => 'com.sybase.jdbc3.jdbc.SybDriver'} )

not entirely relevant, but this is what is required when using jruby, sybase jdbc and dbi:

require 'java'
require './jars/jTDS3.jar'
require './jars/jconn3.jar'
require "rubygems"
require "dbi"

dbh = DBI.connect('dbi:Jdbc:sybase:Tds:foobar:2460/testdb', 'sa', 'password',
  {'driver' => 'com.sybase.jdbc3.jdbc.SybDriver'} )
爱殇璃 2024-08-13 06:26:00

笔记 :
你说的是“设置行数”和“选择”。这是两个不同的语句 - 它们都会得到结果,即使它是“0 行”......所以你正在得到一个结果集。
尝试单独执行这些。

Note :
you are saying "set rowcount" and "select". These are two different statements - they both get results, even if it's "0 rows" ... So you ARE getting a resultset.
Try to execute those separately.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文