CODEWITHSUNDEEP
rubyjdbcjrubysybase
jrochkind
jrochkind

Reputation: 23317

sybase from ruby: JDBC connection to Sybase

I need to connect to a fairly old Sybase database from Ruby.

I ended up using jRuby, only because I couldn't get any manner of connecting to Sybase from MRI to work; If there's a way that actually works and isn't crazy to set up a Sybase connection for an old-ish version of Sybase, that'd be great. But, I beat my head against the wall on that one for a while, I think there is not.

So I wound up in jRuby, in order to use JDBC. I do have a JDBC.jar file that works to connect to Sybase.

I know I can write raw JDBC code in jRuby, just as I would in Java, using the JDBC API. But the JDBC API is such a bear to work with, among other things being very poor at escaping/injection-protecting (or maybe I just don't understand how to use it right, see 'difficult to work with', at least for me).

Are there Ruby 'wrapper' libraries that will work in jRuby as a wrapper for an arbitrary JDBC? If I could get it to work with ActiveRecord, that might be cool, but not neccesarily actually required.

I had been using the Ruby 'DBI' package, which was actually working great. But the Ruby DBI package seems to be unmaintained and is becoming increasingly difficult for me to use; there are a bunch of pull requests (https://github.com/erikh/ruby-dbi/issues) related to making it work under 1.9.3, silencing deprecation warnings, etc. Which are not being attended to by committers. I emailed the github project owner to ask if there was any chance of them being attended to, and received a one-word answer "No", with no explanation. Not really sure what's going on.

So, what are my options for connecting to Sybase from Ruby, with jRuby JDBC or other?

(Per demand below, I'll add that I use RHEL 5 as an OS. But if the answer is "once you get FreeTDS installed, you can do X, Y, and Z like this", I can probably manage to get FreeTDS installed myself. In the past, I've gotten stumped even AFTER getting FreeTDS installed on the host).

Upvotes: 3

Views: 1751

Answers (4)

farooqsadiq
farooqsadiq

Reputation: 91

I put together a simple example of using FreeTDS/TinyTDS/Sequel with SyBase (we have Sybase ASE 15.3)

See the horizon-tds-example GitHub/repo for a Dockerfile

#<!-- language: rb -->
require 'dotenv/load'
require 'sequel'
require 'tiny_tds'
require 'awesome_print'

opts = {
  adapter: 'tinytds',
  login_timeout: 5,
  timeout: 10,
  tds_version: '50',   #42 or 50
  host:     ENV['APP_HZ_HOST'],
  port:     ENV['APP_HZ_PORT'],
  database: ENV['APP_HZ_DATABASE'],
  username: ENV['APP_HZ_USER'],
  password: ENV['APP_HZ_PASSWORD']
}
bib_id = Integer( ARGV[0] || 0 )
tag_num =  ARGV[1] || ''

sql = "SELECT  * FROM [bib] WHERE bib#=? AND tag=?"
DB = Sequel.connect opts
dataset = DB[sql, bib_id, tag_num]
dataset.each do |row|
  ap row
end

Upvotes: 0

nurettin
nurettin

Reputation: 11736

This question seems to be abandoned, but I'd like to log some success. I'd like to start with stating that this works on windows and linux.

I am currently using MSSQL v7 and MSSQL v8 from jruby with the JDBC driver distributables I downloaded from microsoft.

The catch is, JDBC 3.0 and 4.0 drivers are unable to connect to MSSQL v7. I was very lucky to find out JDBC 2.0 actually worked against it.

I'm not sure what the OP's concern was about SQL injection, but these drivers are capable of creating Prepared Statements (for partitioned SQL) and Callable Statements (for SPs) which can be used to escape parameters safely.

About the ugliness, yes you're right. That's why a bit of code is needed:

class Java::ComMicrosoftSqlserverJdbc::SQLServerResultSet
  def to_a
    #I used meta_data here to convert resultset to array of hashes 
  end
end

and to get multiple results from a single select statement:

yap= connection.create_statement
rowing= yap.execute sql
row_the_boat= []
while rowing
  row_the_boat<< yap.get_result_set.to_a
  rowing= yap.get_more_results
end

Upvotes: 1

arkadiy kraportov
arkadiy kraportov

Reputation: 3729

I used JTDS jar and activerecord-jdbc-adapter gem for my Rails 3/Sybase ASE 15 project.

The only problem I had is limit and offset for Sybase are not really supported. I end up writing my own visitors to make limit and offset work.

You may want to take a look here: https://github.com/arkadiyk/ar-sybase-jdbc-adapter

Upvotes: 2

the Tin Man
the Tin Man

Reputation: 160551

I have used FreeTDS and TinyTDS on Linux via Sequel to get to some of our MSSQL and Sybase DBMs.

It wasn't as elegant as using MySQL or Postgres, but was doable. And, Sequel is a very good tool for non-Rails DB access. It's very powerful and flexible.

Upvotes: 0

Related Questions