Using Sybase ASE 12.5.4 with jTDS drivers with JRu

Question

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.

Answer 1

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.

Answer 2

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!

Answer 3

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'} )