1 Replies - 696 Views - Last Post: 23 April 2014 - 10:16 PM Rate Topic: -----

#1 jayburn00  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 118
  • Joined: 05-July 12

How do I get my access database file working(and do I need any fixes)?

Posted 23 April 2014 - 07:22 PM

I have written a script that is meant to provide a simple command line interface for accessing and interacting with an Access Database, but whenever I try to run it using the database (and with modifications that make it say why it is having trouble accessing the database provided by a professor, it outputs an error about the database not being found (or something like that) and OLEDB drives being missing (something like that anyway). The database file is in the same folder as the script, with the name of the table being accessed assigned the name items. I have been told that the commented out portion of my script should be able to work with the database, but for some reason it isn't. I checked the driver issue, reinstalling the drivers it referred to, but that didn't fix it, so I am inclined to think there is some tiny detail I'm missing in regards to my script. Here is my script, I block commented out most of the database portions, with mostly just test lines to see if functions were being called properly still in play, except for -o option, which I never got around to testing and retains the database use code associated with it. Any ideas how to fix it? I think all the option letters are working correctly (except if there is no database), but would also appreciate any tips on them if they aren't (since I can't entirely test them without the db).

#!/usr/bin/env ruby
begin
	require 'win32ole'
	require 'csv'
rescue LoadError
	print "Could not load required modules."
	exit 1
end


=begin
class inventorydb
	database="inventory.accdb"
	dbstring = 'Provider-Microsoft.ACE.OLEDB.12.0;Data Source='+database
		begin
			connection = WIN32OLE.new('ADODB.Connection')
			connection.Open(dbstring)
			connection.Open('Provider-Microsoft.ACE.OLEDB.12.0;Data Source=inventory.accdb')
			recordset = WIN32OLE.new('ADODB.Recordset')
			recordset.Open("SELECT * FROM items;", connection)
			data = recordset.GetRows.transpose
			recordset.Close
			connection.Close
		rescue
			print("Error accessing the database.")
			Process.exit(1)
		end
=end
def process_inventoryfile (filename)
	puts "Fetching DB"
end

def update_inventoryfile(filename)
	puts "Processing input"
	CSV.foreach (filename) do |col|
		puts col[0]
	end

end
	

def displayhelp
htext = puts <<-EOF
Usage: ruby inventory.rb [?|-h|help|[-u|-o|-z <infile>|[<outfile>]]]

Parameters:
    ?               displays this usage information
    -h              displays this usage information
    Help            displays this usage information
    -u <infile>     update the inventory using the file <infile>.
                    The filename <infile> must have a .csv
                    extension and it must be a text file in comma
                    separated value (CSV) format. Note that the
                    values must be in double quote.
    -z|-o [<outfile>] output either the entire content of the
                    database (-o) or only those records for which
                    the quantity is zero (-z). If no <outfile> is
                    specified then output on the console otherwise
                    output in the text file named <outfile>. The
                    output in both cases must be in a tab separated
                    value (tsv) format.
 EOF
end

unless ARGV.length > 0
	displayhelp
	exit
else
	case ARGV[0]
		when "-u","-U"
			if (ARGV[1] != nil)
				# user specified an input file
				filename = ARGV[1]
				update_inventoryfile(filename)
			else
				puts "Input file not specified - aborting"
				exit
			end
		when "-z"
			#options[:z] = ARGV[0]	
			print ("Printing records with quantity = 0")
			# Following line have been commented
=begin
			print(
			for recs in 0...data.length
				if data[recs][3] != 0
					print("Item Name:  ",data[recs][1],"\n")
					print("Item Category:  ",data[recs][2],"\n")
					print("Quantity:  ",data[recs][3],"\n")
					print("Price:  ",data[recs][4],"\n")
					print("Description:  ",data[recs][5],"\n")
					print("\n")
				else
					recordset.Close
					connection.Close
					Process.exit(0)
				end
			end
=end

		when "-o"
			#options[:o/>] = ARGV[0]
			if (ARGV[1] != nil)
				filename = ARGV[1]
				for recs in 0...data.length
					print("Item Name:  ",data[recs][1],"\n")
					print("Item Category:  ",data[recs][2],"\n")
					print("Quantity:  ",data[recs][3],"\n")
					print("Price:  ",data[recs][4],"\n")
					print("Description:  ",data[recs][5],"\n")
					print("\n")
				end
			end
			
		when "-h"
			if (ARGV[1] == nil)
				displayhelp
				exit
			end
		
		else
			displayhelp
			exit
		end
	end

	



Is This A Good Question/Topic? 0
  • +

Replies To: How do I get my access database file working(and do I need any fixes)?

#2 Lemur  Icon User is offline

  • Pragmatism over Dogma
  • member icon


Reputation: 1359
  • View blog
  • Posts: 3,425
  • Joined: 28-November 09

Re: How do I get my access database file working(and do I need any fixes)?

Posted 23 April 2014 - 10:16 PM

https://github.com/jeremyevans/sequel

Use something more meant for Ruby.

Now as per your code, let's clean that up a bit for a more Ruby style:
#!/usr/bin/env ruby
begin
  require 'win32ole'
  require 'csv'
rescue LoadError
  puts "Could not load required modules." and exit 1
end

# class inventorydb
#   database="inventory.accdb"
#   dbstring = 'Provider-Microsoft.ACE.OLEDB.12.0;Data Source='+database
#     begin
#       connection = WIN32OLE.new('ADODB.Connection')
#       connection.Open(dbstring)
#       connection.Open('Provider-Microsoft.ACE.OLEDB.12.0;Data Source=inventory.accdb')
#       recordset = WIN32OLE.new('ADODB.Recordset')
#       recordset.Open("SELECT * FROM items;", connection)
#       data = recordset.GetRows.transpose
#       recordset.Close
#       connection.Close
#     rescue
#       puts "Error accessing the database."
#       Process.exit(1)
#     end
# end

def process_inventoryfile (filename)
  puts "Fetching DB"
end

def update_inventoryfile(filename)
  puts "Processing input" and CSV.foreach (filename) { |col| puts col[0] }
end
  

def displayhelp
  puts <<-EOF
Usage: ruby inventory.rb [?|-h|help|[-u|-o|-z <infile>|[<outfile>]]]

Parameters:
    ?               displays this usage information
    -h              displays this usage information
    Help            displays this usage information
    -u <infile>     update the inventory using the file <infile>.
                    The filename <infile> must have a .csv
                    extension and it must be a text file in comma
                    separated value (CSV) format. Note that the
                    values must be in double quote.
    -z|-o [<outfile>] output either the entire content of the
                    database (-o) or only those records for which
                    the quantity is zero (-z). If no <outfile> is
                    specified then output on the console otherwise
                    output in the text file named <outfile>. The
                    output in both cases must be in a tab separated
                    value (tsv) format.
 EOF
end

if ARGV.length == 0
  displayhelp and exit
else
  case ARGV[0]
  when "-u","-U"
    if ARGV[1]
      update_inventoryfile(ARGV[1])
    else
      puts "Input file not specified - aborting" and exit
    end
  when "-z"
    puts "Printing records with quantity = 0"
    # Following line have been commented
    # data.each do |recs|
    #   if data[recs][3] != 0
    #     puts "Item Name:  #{data[recs][1]}"
    #     puts "Item Category:  #{data[recs][2]}"
    #     puts "Quantity:  #{data[recs][3]}"
    #     puts "Price:  #{data[recs][4]}"
    #     puts "Description:  #{data[recs][5]}"
    #   else
    #     recordset.Close
    #     connection.Close
    #     Process.exit(0)
    #   end
    # end
  when "-o"
    (filename = ARGV[1]) && data.each do |recs|
      puts "Item Name:  #{data[recs][1]}"
      puts "Item Category:  #{data[recs][2]}"
      puts "Quantity:  #{data[recs][3]}"
      puts "Price:  #{data[recs][4]}"
      puts "Description:  #{data[recs][5]}"
    end
  when "-h"
    displayhelp and exit unless ARGV[1]
  else
    displayhelp and exit
  end
end



It'll still need work from there, but I would use Sequel for the Database interaction and Option Parser for options instead of the odd case statements you have there.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1