EVAL EVAL - 2 months ago 16
MySQL Question

How to distinguish between exceptions

I am attempting to create a simple script in Ruby that accesses a MySQL database and runs a query based on user input.

If a user gives bad login information or attempts to

SELECT
FROM
a table or column that does not exist I would like the user to re-enter only the information that is causing an issue instead of having to re-enter all information.

I wish for this code to raise an exception based on what part of the query threw an error:

#!/usr/local/bin/ruby
require "mysql2"
require "watir"
require "csv"

Mysql2::Client.default_query_options.merge!(:as => :array)
mysql = Mysql2::Client.new(:host => "1.2.3.4", :username => "usr", :password => "pass123", :database => "db")

db = "db"

puts "Please enter all `table`.`column` pairs you wish to select from; separated by a comma and space."
tCP = gets.chomp.split(", ")

dynQ = "SELECT "
tCP.each {|pair| dynQ << (db + "." + pair + ", ") }

puts "Please enter the `table` you wish to select from:"
tF = db + "." + gets.chomp

dynQ = dynQ.chop!.chop! + " FROM " + tF + " LIMIT 10;"

report = mysql.query(dynQ)

begin
report = mysql.query(dynQ)
report.each {|row| puts row}

puts "Query Successful!"

rescue Mysql2::Error => e
puts e.errno
puts e.error

puts "Please re-nter all `table`.`column` pairs separated by, ', '."
tCP = gets.chomp!.split(", ")

dynQ = "SELECT "
tCP.each {|pair| dynQ << (db + "." + pair + ", ") }

tF = db + "."
puts "Now re-enter the `table` to select `FROM`:"
tF << gets.chomp!

dynQ = dynQ.chop!.chop! + " FROM " + tF + " LIMIT 10;"

retry
end


For example, if I
SELECT
from a table or column that doesn't exist, I get this:

/Users/guy/.rvm/gems/ruby-2.3.0/gems/mysql2-0.4.2/lib/mysql2/client.rb:107:in '_query': Table 'db.table_dne' doesn't exist (Mysql2::Error)
from /Users/guy/.rvm/gems/ruby-2.3.0/gems/mysql2-0.4.2/lib/mysql2/client.rb:107:in 'block in query'
from /Users/guy/.rvm/gems/ruby-2.3.0/gems/mysql2-0.4.2/lib/mysql2/client.rb:106:in 'handle_interrupt'
from /Users/guy/.rvm/gems/ruby-2.3.0/gems/mysql2-0.4.2/lib/mysql2/client.rb:106:in 'query'
from file.rb:23:in '<main>'


Yet if I am taking that selection from a table that doesn't exist, I get the same exact thing:

/Users/guy/.rvm/gems/ruby-2.3.0/gems/mysql2-0.4.2/lib/mysql2/client.rb:107:in '_query': Unknown column 'db.table_dne' doesn't exist (Mysql2::Error)
from /Users/guy/.rvm/gems/ruby-2.3.0/gems/mysql2-0.4.2/lib/mysql2/client.rb:107:in 'block in query'
from /Users/guy/.rvm/gems/ruby-2.3.0/gems/mysql2-0.4.2/lib/mysql2/client.rb:106:in 'handle_interrupt'
from /Users/guy/.rvm/gems/ruby-2.3.0/gems/mysql2-0.4.2/lib/mysql2/client.rb:106:in 'query'
from file.rb:23:in '<main>'


Ideally, I want to
rescue
based on what exception appears in the console and have the user re-enter either
tCP
or
tF
instead of just being given a more general exception.

I looked through the MySQL2 documentation without luck.

Answer

Let's break down your attempt:

rescue Mysql2::Error => e
  puts e.errno
  puts e.error
  1. rescue keeps your script running
  2. Mysql2::Error => e takes from the Error class and stores in e
  3. puts e.errno puts the error number to the console
  4. puts e.error puts the error description to the console

And with that, the following begin loop should work for you:

begin
  report = mysql.query(dynQ)
  report.each {|row| puts row}

  puts "Query Successful!"

rescue Mysql2::Error => e

  if e.errno === 1146
    puts e.error

    tF = db + "."
    puts "Now re-enter the `table` to select `FROM`:"
    tF << gets.chomp!
  elsif e.errno === 1054
    puts e.error

    puts "Please re-nter all `table`.`column` pairs separated by, ', '."
    tCP = gets.chomp!.split(", ")

    dynQ = "SELECT "
    tCP.each {|pair| dynQ << (db + "." + pair + ", ") }
  else
#   puts e.errno
    puts e.error

        puts "Please re-nter all `table`.`column` pairs separated by, ', '."
    tCP = gets.chomp!.split(", ")

    dynQ = "SELECT "
    tCP.each {|pair| dynQ << (db + "." + pair + ", ") }

    tF = db + "."
    puts "Now re-enter the `table` to select `FROM`:"
    tF << gets.chomp!
  end

  dynQ = dynQ.chop!.chop! + " FROM " + tF + " LIMIT 10;"

  retry
end