How to Use Ruby Dbi's 'Select_All' VS 'Execute-Fetch/Each-Finish'

Ruby DBI fetch can't handle all-zero dates in MySQL

An alternative solution is to use fetch_hash. This fetches all data as strings, which is a little awkward as you need to convert to dates, integers, etc., but does give you the opportunity of trapping the error on the explicit conversion. This also makes it much easier to identify the bad record(s):

query = "select ETA from mydb.mytable where ID = #{someval}"
rows = dbh.execute(query)
while row = rows.fetch_hash() do
    begin
        eta = Date.parse row["ETA"]
        # Do something with the ETA
        ...
    rescue Exception => e
        puts "Error parsing date '#{row["ETA"]}': #{e}<br>\n"
        # Make do without the ETA
        ...
    end
end

Ruby dbi select statement returning BigDecimal?

DBI has probably determined that the underlying column can contain integers too large to fit in a regular int type, based on the data field. Or it may just use BigDecimal for all integer types to avoid worrying about it.

If you know that your values are all small enough to fit into a regular integer, you can convert the array to integers after you've populated it, like so:

1.9.3-p194 :014 > numberOfAccounts
=> [[#<BigDecimal:119cd90,'0.123E3',9(36)>], [#<BigDecimal:119cd18,'0.456E3',9(36)>]] 
1.9.3-p194 :015 > numberOfAccounts.flatten!.collect!(&:to_i)
=> [123, 456] 
1.9.3-p194 :016 > numberOfAccounts
=> [123, 456] 

Ruby DBI: How to add the execute arguments when use method in (?)

That's not how the IN clause works, you need to have the same amount of ? as the number of arguments, in your case, you should have:

sql = "select u.email, u.account_name, u.height, u.weight 
         from test_users 
         where id in (?,?,?)
         group by u.id
         order by u.id
         into outfile '/tmp/test.csv'
         fields terminated by ','
         enclosed by '\"'
         lines terminated by '\n'"

and then pass each value to the query.

Using Ruby DBI's prepare without a handle?

ruby-dbi does include an emulator for preparing statements when the dbd does not provide it. You can use it as follows:

require 'dbi'
st = DBI::SQL::PreparedStatement.new(nil, "Select * from table where x = ?")
st.bind(['test']) # => "Select * from table where x = test"

You can see the algorithm it uses here.

Edit: Based on the comments below a, dbi 0.2.2 version

require 'dbi'
class Quoter ; include DBI::SQL::BasicQuote ; end
st = DBI::SQL::PreparedStatement.new(Quoter.new, "Select * from table where x = ?")
st.bind(['test']) # => "Select * from table where x = 'test'"

Relevant source file is here.

---

Related Topics