SQL Shell for MS Access

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

[email protected]

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

[email protected]

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…

http://rubyonwindows.blogspot.com/2007/06/using-ruby-ado-to-work-with-ms-access.html

…to meet your needs.

David

http://rubyonwindows.blogspot.com
http://rubyonwindows.blogspot.com/search/label/access

2009/10/27 Paul S. [email protected]lid:

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 -
http://books.google.co.uk/books?id=SvTuCXEKBvkC&pg=PA137&lpg=PA137&dq=ruby+ms+access+interactive+sql+client&source=bl&ots=SPgHkn7a76&sig=-Vk-Jpfm0f_LlS5z-AaCKw67NGU&hl=en&ei=8MzmSpmeLZXSjAediLW1CA&sa=X&oi=book_result&ct=result&resnum=1&ved=0CA4Q6AEwAA#v=onepage&q=&f=false

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.

I’m just trying to make working with MS Access feel more like working
with mysql :slight_smile:

Paul S.
http://www.nomadicfun.co.uk

[email protected]

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 - http://books.google.co.uk/books?id=SvTuCXEKBvkC&pg=PA137&lpg=PA137&dq=ruby+ms+access+interactive+sql+client&source=bl&ots=SPgHkn7a76&sig=-Vk-Jpfm0f_LlS5z-AaCKw67NGU&hl=en&ei=8MzmSpmeLZXSjAediLW1CA&sa=X&oi=book_result&ct=result&resnum=1&ved=0CA4Q6AEwAA#v=onepage&q=&f=false

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 :slight_smile:

Seems like you are forced to work with MS Access. I would try to
avoid it if possible. :slight_smile:

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 mysql :slight_smile:

Seems like you are forced to work with MS Access. I would try to
avoid it if possible. :slight_smile:

I would too :slight_smile: 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

[email protected]

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 :slight_smile:

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

[email protected]

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 -
http://books.google.co.uk/books?id=SvTuCXEKBvkC&pg=PA137&lpg=PA137&
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 mysql :slight_smile:

Paul S.
http://www.nomadicfun.co.uk

[email protected]

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.:
http://rubyonwindows.blogspot.com/2007/06/using-ruby-ado-to-work-with-ms-access.html

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 :slight_smile: 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 :slight_smile: Here’s my complete
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

[email protected]

You can try to use libmdb

http://www.automatthew.com/2008/11/ruby-ffi-example-with-libmdb.html

and

http://www.automatthew.com/2007/04/activemdb-for-that-not-so-fresh-feeling.html

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs