# ODBC Database Connectivity **URL:** \ **Category:** Ruby\ **Created:** [May 1, 2006, 10:25pm UTC](https://www.ruby-forum.com/t/odbc-database-connectivity/94357 "2006-05-01T22:25:11Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![alan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a87d85/32.png) [@alan](https://www.ruby-forum.com/u/alan)\ **Post date:** [May 1, 2006, 10:25pm UTC](https://www.ruby-forum.com/t/odbc-database-connectivity/94357/1 "2006-05-01T22:25:11Z")
Hi, I am having such hard time to connect to a ODBC data source. (Windows XP) I have C# and [ADO.NET](http://ADO.NET) background and I am shocked when I see there is so little database connectivity support for Ruby! Am I missing something or Ruby is not good for connecting to databases? Thanks, Alan ---
**Author:** ![alan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a87d85/32.png) [@alan](https://www.ruby-forum.com/u/alan)\ **Post date:** [May 2, 2006, 2:23pm UTC](https://www.ruby-forum.com/t/odbc-database-connectivity/94357/2 "2006-05-02T14:23:42Z")
Alan wrote: > Hi, > > I am having such hard time to connect to a ODBC data source. (Windows > XP) > > I have C# and [ADO.NET](http://ADO.NET) background and I am shocked when I see there is so > little database connectivity support for Ruby! > > Am I missing something or Ruby is not good for connecting to databases? > > Thanks, > Alan Hello Alan, I’m using Ruby DBI module with the ODBC package in windows. The best way to have it all is to get the ruby one-click installer ([http://rubyforge.org/projects/rubyinstaller/](http://rubyforge.org/projects/rubyinstaller/)) Then, all you have to do is (for example): require ‘DBI’ # make an ODBC connection conn = DBI.connect(‘DBI:ODBC:datasource’,‘your\_username’,‘your\_password’) # returns a list of the table names from your database conn.tables # returns an array with the resultset from your query rs = conn.select\_all(‘SELECT \* FROM TABLE’) You can google around to find more information about ruby’s DBI module, but here goes some links that can help you: [http://www.kitebird.com/articles/ruby-dbi.html](http://www.kitebird.com/articles/ruby-dbi.html) [http://ruby-dbi.rubyforge.org/](http://ruby-dbi.rubyforge.org/) > **[ConnectionStrings.com - Forgot that connection string? Get it here!](https://www.connectionstrings.com/)** > > All connection strings in one place. Find the syntax for your database connection using ADO.NET, ADO, ODBC, OLEDB, C#, VB, VB.NET, ASP.NET and more. ---
**Author:** ![alan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a87d85/32.png) [@alan](https://www.ruby-forum.com/u/alan)\ **Post date:** [May 3, 2006, 6:58pm UTC](https://www.ruby-forum.com/t/odbc-database-connectivity/94357/3 "2006-05-03T18:58:31Z")
On 5/1/06, Alan [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Hi, > > I am having such hard time to connect to a ODBC data source. (Windows > XP) > > I have C# and [ADO.NET](http://ADO.NET) background and I am shocked when I see there is so > little database connectivity support for Ruby! Really? I’ve never had a problem, using MySQL, MS Access, MS SQL Server, Oracle, and others… > Am I missing something or Ruby is not good for connecting to databases? [http://ruby-dbi.rubyforge.org/](http://ruby-dbi.rubyforge.org/) is the home page for Ruby DBI, which provides DB connectivity, ODBC should work for most DBs at the least I would think (If you are on Windows and can create a DSN for the data source, that is). -Michael ---
**Author:** ![alan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a87d85/32.png) [@alan](https://www.ruby-forum.com/u/alan)\ **Post date:** [May 3, 2006, 6:59pm UTC](https://www.ruby-forum.com/t/odbc-database-connectivity/94357/4 "2006-05-03T18:59:09Z")
Alan wrote: > Thanks, > Alan What Database is giving you trouble? (Currently I have a Ruby (non Rails) web app that speaks to MSSQL2k5 with out any problems) The client is currently interested in moving the web app to IIS and if an when this occurs, I will probably not use ODBC, but instead use ADO. ---
**Author:** ![alan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a87d85/32.png) [@alan](https://www.ruby-forum.com/u/alan)\ **Post date:** [May 3, 2006, 6:58pm UTC](https://www.ruby-forum.com/t/odbc-database-connectivity/94357/5 "2006-05-03T18:58:32Z")
On 5/1/06, Alan [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Hi, > > I am having such hard time to connect to a ODBC data source. (Windows > XP) > > I have C# and [ADO.NET](http://ADO.NET) background and I am shocked when I see there is so > little database connectivity support for Ruby! > > Am I missing something or Ruby is not good for connecting to databases? That is quite laughable actually. I suppose you have not heard of Ruby on Rails and it’s ActiveRecord database framework? In regards to your question, here is an example (a constructor for a database dumper) using the Ruby ODBC library ([ODBC Binding for Ruby](http://www.ch-werner.de/rubyodbc/)), which I believe is installed by default in the Windows One-Click installer: ``` def initialize(sql, datasource = DEFAULT_DS, pw = DEFAULT_PW) @sql, @datasource, @pw = sql, datasource, pw @output = '' ODBC::connect(datasource, 'sa', pw) do |dbc| stmt = dbc.run(sql) table = /from *(.*) */.match(sql)[1] prefix = "INSERT INTO #{table} (" cols = [] stmt.columns do |col| prefix << col.name << ', ' cols << col end prefix[-2..-1] = ") VALUES " stmt.each do |row| @output << prefix << '(' row.each_with_index do |field, i| @output << process_field(field, cols[i]) << ', ' end @output[-2..-1] = ")\n" end end end ``` Ryan ---
**Author:** ![alan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a87d85/32.png) [@alan](https://www.ruby-forum.com/u/alan)\ **Post date:** [January 9, 2007, 12:34pm UTC](https://www.ruby-forum.com/t/odbc-database-connectivity/94357/6 "2007-01-09T12:34:27Z")
Walter Kruse wrote: > I’m having trouble with ruby + db’s. I am running mysql for Mantis on > localhost (Windows XP using XAMPP). > > I installed mysql-2.7.1-mswin32.gem and cannot connect with > `\_get\_full\_driver’ error. > > I then created mantis as an ODBC DSN and I get: > > C:/ruby/lib/ruby/site\_ruby/1.8/DBD/ODBC/ODBC.rb:95:in `connect’: S1T00 > (1049) [MySQL][ODBC 3.51 Driver]Unknown database ’ mantis’ > (DBI::DatabaseError) > > I tested the connection, so I know it’s set up correctly and works. > > If you look closely at the last bit of the error, ’ mantis’, it looks > like there is a space between the first quote and the DSN name. Don’t > know if that’s significant ? Two things: First, mysql-2.7.1-mswin32.gem has nothing to do with ODBC. It will allow you to start from the default config/database.yml which references mysql directly; by changing the login details and database in this file you’ll get straight through. Configuring ODBC DSNs is orthogonal to using this gem. Alternatively, if you want to go via ODBC, there is the ODBC Adapter for ActiveRecord/Rails, at [http://odbc-rails.rubyforge.org/](http://odbc-rails.rubyforge.org/) . ---
**Author:** ![alan](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a87d85/32.png) [@alan](https://www.ruby-forum.com/u/alan)\ **Post date:** [July 14, 2006, 3:09pm UTC](https://www.ruby-forum.com/t/odbc-database-connectivity/94357/7 "2006-07-14T15:09:21Z")
Hi I’m having trouble with ruby + db’s. I am running mysql for Mantis on localhost (Windows XP using XAMPP). I installed mysql-2.7.1-mswin32.gem and cannot connect with `\_get\_full\_driver’ error. I then created mantis as an ODBC DSN and I get: C:/ruby/lib/ruby/site\_ruby/1.8/DBD/ODBC/ODBC.rb:95:in `connect’: S1T00 (1049) [MySQL][ODBC 3.51 Driver]Unknown database ’ mantis’ (DBI::DatabaseError) I tested the connection, so I know it’s set up correctly and works. If you look closely at the last bit of the error, ’ mantis’, it looks like there is a space between the first quote and the DSN name. Don’t know if that’s significant ?