What I want is an SQL command prompt allowing me to run SQL queries
against an Access .mdb.
I thought that Ruby/DBI included a sqlsh.rb script that essentially
did this, but no matter how I install Ruby/DBI I can’t find the SQL
shell that I want.
So - does Ruby/DBI even include an SQL shell anymore? Is there
another way to get an SQL prompt for MS Access in a similar way to
what you can do with mysql for example?
Thanks for any insight,
Paul
–
Paul S.
http://www.nomadicfun.co.uk
Paul S. wrote:
What I want is an SQL command prompt allowing me to run SQL queries
against an Access .mdb.I thought that Ruby/DBI included a sqlsh.rb script that essentially
did this, but no matter how I install Ruby/DBI I can’t find the SQL
shell that I want.So - does Ruby/DBI even include an SQL shell anymore? Is there
another way to get an SQL prompt for MS Access in a similar way to
what you can do with mysql for example?Thanks for any insight,
Paul
–
Paul S.
http://www.nomadicfun.co.uk
I’m not familiar with the mysql shell that you mentioned, but (assuming
you’re on Windows) you could probably tweak the AccessDb wrapper found
here…
…to meet your needs.
David
2009/10/27 Paul S. [email protected]:
What I want is an SQL command prompt allowing me to run SQL queries
against an Access .mdb.I thought that Ruby/DBI included a sqlsh.rb script that essentially
did this, but no matter how I install Ruby/DBI I can’t find the SQL
shell that I want.So - does Ruby/DBI even include an SQL shell anymore? Is there
another way to get an SQL prompt for MS Access in a similar way to
what you can do with mysql for example?
I doubt Ruby/DBI ever included what you need. In order to be able to
directly run SQL against an mdb file you need any DB engine. Ruby/DBI
is just an interfacing layer and does not include anything like a
complete DB engine.
Having said that, you can use OpenOffice to open mdb files and also
execute queries against it.
Kind regards
robert
On Tue, Oct 27, 2009 at 1:01 PM, Robert K.
[email protected] wrote:
what you can do with mysql for example?
I doubt Ruby/DBI ever included what you need. In order to be able to
directly run SQL against an mdb file you need any DB engine. Ruby/DBI
is just an interfacing layer and does not include anything like a
complete DB engine.
Well, to be more complete, Ruby/DBI using a ruby-odbc DBD to connect
out to an access database.
This cached page
http://209.85.229.132/search?q=cache:oks0dAPyNbQJ:doc.gnu-darwin.org/dbi/html/+ruby/dbi+sql+command+line&cd=9&hl=en&ct=clnk&gl=uk&client=firefox-a
suggests that sqlsh.rb did indeed once exist.
Page 137, Chapter 3 of “Accessing Databases with Ruby” also describes
what I want -
Having said that, you can use OpenOffice to open mdb files and also
execute queries against it.
Maybe this is the right way to go, I just really like the command
line, as I’m used to working with mysql, and there you can just fire
up mysql-client, get a mysql> prompt, and start typing things like
show tables to list all the tables in the db, desc table to describe
the layout of a given table.
Paul S.
http://www.nomadicfun.co.uk
2009/10/27 Paul S. [email protected]:
So - does Ruby/DBI even include an SQL shell anymore? Is there
another way to get an SQL prompt for MS Access in a similar way to
what you can do with mysql for example?I doubt Ruby/DBI ever included what you need. In order to be able to
directly run SQL against an mdb file you need any DB engine. Ruby/DBI
is just an interfacing layer and does not include anything like a
complete DB engine.Well, to be more complete, Ruby/DBI using a ruby-odbc DBD to connect
out to an access database.
Oh, OK, ODBC is a different story. I didn’t think of that.
This cached page
http://209.85.229.132/search?q=cache:oks0dAPyNbQJ:doc.gnu-darwin.org/dbi/html/+ruby/dbi+sql+command+line&cd=9&hl=en&ct=clnk&gl=uk&client=firefox-a
suggests that sqlsh.rb did indeed once exist.Page 137, Chapter 3 of “Accessing Databases with Ruby” also describes
what I want - Ruby Developers Guide - Syngress - Google Books
Having said that, you can use OpenOffice to open mdb files and also
execute queries against it.Maybe this is the right way to go, I just really like the command
line, as I’m used to working with mysql, and there you can just fire
up mysql-client, get a mysql> prompt, and start typing things like
show tables to list all the tables in the db, desc table to describe
the layout of a given table.
You could as well try a generic DB client (e.g. SQirreL) which should
also be capable of making use of Access’s ODBC driver via the JDBC
ODBC bridge (although that is not perfect as well). Maybe there’s
also a native Windows ODBC SQL client around that you could use.
I’m just trying to make working with MS Access feel more like working
with mysql
Seems like you are forced to work with MS Access. I would try to
avoid it if possible.
Kind regards
robert
Maybe this is the right way to go, I just really like the command
line, as I’m used to working with mysql, and there you can just fire
up mysql-client, get a mysql> prompt, and start typing things like
show tables to list all the tables in the db, desc table to describe
the layout of a given table.You could as well try a generic DB client (e.g. SQirreL) which should
also be capable of making use of Access’s ODBC driver via the JDBC
ODBC bridge (although that is not perfect as well). Maybe there’s
also a native Windows ODBC SQL client around that you could use.
I’ll keep on investigating. So close, but yet so far.
I’m just trying to make working with MS Access feel more like working
with mysqlSeems like you are forced to work with MS Access. I would try to
avoid it if possible.
I would too I’m maintaining a database as a bit of a side job at
work, and it’s currently in MS Access. There’s no real reason I guess
to keep it in MS Access, other than I may have to pass it on to
someone else at a later date, and they’re more likely to have some
Access knowledge than anything else.
If it was my main job I’d probably spend the effort to convert, but it
isn’t and I’m honestly trying to lose it to someone else.
Kind regards
robert
–
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
–
Paul S.
http://www.nomadicfun.co.uk
On Tue, Oct 27, 2009 at 4:31 PM, [email protected] wrote:
2009/10/27 Paul S. [email protected]:
So - does Ruby/DBI even include an SQL shell anymore? Is there
Page 137, Chapter 3 of “Accessing Databases with Ruby” also
‘sqlsh.rb’ was renamed to ‘dbi’. You should find a ‘dbi’ file in the same directory where ‘ruby’ is installed. Of course, there have been changes to ‘dbi’ since this change was made, but it’s a place to start.
Brilliant! Thanks so much for finding that, I had given up
Now I just need to get ruby-odbc working with Ruby 1.9 on windows…
or install Ruby 1.8.6. It’s looking like the latter is the better
option from here.
–
Paul S.
http://www.nomadicfun.co.uk
What I want is an SQL command prompt allowing me to run SQL
another way to get an SQL prompt for MS Access in a similar way
Well, to be more complete, Ruby/DBI using a ruby-odbc DBD to
describes
what I want -
Ruby Developers Guide - Syngress - Google Books
dq=ruby+ms+access+interactive+sql+client&source=bl&ots=SPgHkn7a76&s
ig=-Vk-Jpfm0f_LlS5z-
AaCKw67NGU&hl=en&ei=8MzmSpmeLZXSjAediLW1CA&sa=X&oi=book_result&ct=r
esult&resnum=1&ved=0CA4Q6AEwAA#v=onepage&q=&f=false
According to this page:
http://ruby-dbi.rubyforge.org/git?p=ruby-dbi.git;a=commit;h=556e08726d9716e5b06d558b8250eb9f0d06df9e
‘sqlsh.rb’ was renamed to ‘dbi’. You should find a ‘dbi’ file in the
same directory where ‘ruby’ is installed. Of course, there have been
changes to ‘dbi’ since this change was made, but it’s a place to start.
describe
the layout of a given table.I’m just trying to make working with MS Access feel more like
working
with mysqlPaul S.
http://www.nomadicfun.co.uk
Coey M.
I connected to an mdb (access db) in windows with the ‘win32ole’ gem. I
also got this class from a blog post by David M.:
which also has a guide on how to connect.
I modified the class on this page to return results as an array of
hashes, like ActiveRecord’s find_rows method, and added a “find” method
which just takes an sql string. So, it’s pretty like mysql (my desire
as well, i was amazed to discover that access has no sql command line).
If this has problems then blame me and not David Here’s my complete
class, see the blog post by David above on how to use it.
class AccessDb
attr_accessor :mdb, :connection, :data, :fields, :rows, :sql
def initialize(mdb=nil)
@mdb = mdb
@connection = nil
@data = nil
@fields = nil
@rows = []
self.open
end
def open
connection_string = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
connection_string << @mdb
@connection = WIN32OLE.new('ADODB.Connection')
@connection.Open(connection_string)
end
def query(sql, options = {})
self.sql = sql
puts "Querying: #{sql}" if options[:verbose]
recordset = WIN32OLE.new('ADODB.Recordset')
recordset.Open(sql, @connection)
@fields = []
@rows = []
recordset.Fields.each do |field|
@fields << field.Name
end
begin
@data = recordset.GetRows.transpose
@data.each do |row|
row_hash = {}
row.each_with_index do |value, i|
row_hash[@fields[i]] = value
end
@rows << row_hash
end
rescue
@data = []
end
recordset.Close
end
def find(sql, options = {})
self.sql = sql
self.query(sql, options)
self.rows
end
def execute(sql, options = {})
self.sql = sql
puts "Executing: #{sql}" if options[:verbose]
begin
@connection.Execute(sql)
rescue
raise "!!ERROR executing \n#{sql}\n: (#{@mdb.inspect}) #{$!}"
end
end
def close
@connection.Close
end
end
On Wed, Oct 28, 2009 at 2:26 PM, Max W.
[email protected] wrote:
If this has problems then blame me and not David
class, see the blog post by David above on how to use it.class AccessDb
Thanks Max! Making a painful job easier, one step at a time.
–
Paul S.
http://www.nomadicfun.co.uk
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.
Sponsor our Newsletter | Privacy Policy | Terms of Service | Remote Ruby Jobs