MySQL Forums
Forum List  »  Ruby

differences between prepare(stmt) and query(stmt) results
Posted by: Donald Leslie
Date: August 13, 2012 10:48AM

I wanted to run a query where tested values changed in a loop.

Code was as follows:

query = 'SELECT meetings.meetings_key, meetings.meetings_date, meetings.title ' +
'FROM meetings, meeting_types ' +
'WHERE meetings.type_key = meeting_types.meeting_types_key ' +
"and meeting_types.meeting_type = ? " +
" AND year(meetings.meetings_date) >= ? " +
'ORDER BY meetings.meetings_key'

sth = @dbh.prepare(query)
type = 'Board'
year = '2012'
sth.execute(type,year)
row = sth.fetch
puts "#{sth} , #{row.class} - #{row}"
puts "#{row[0].class} , #{row[1].class} , #{row[2].class}"

This resulted in the following result:

#<Mysql::Stmt:0x00000000affff0> , Array - [6, #<Mysql::Time:2012-01-11 00:00:00>, "Jan board meeting"]
Fixnum , Mysql::Time , String

The result has the data typed as it will be used.
I wanted to be able to get the result as a hash, but fetch_hash was not available.
Looking further I found that fetch_hash is only available if I used query(stmt).

Running the following works:

query = 'SELECT meetings.meetings_key, meetings.meetings_date, meetings.title ' +
'FROM meetings, meeting_types ' +
'WHERE meetings.type_key = meeting_types.meeting_types_key ' +
"and meeting_types.meeting_type = \'Board\' " +
" AND year(meetings.meetings_date) >= \'2012\' " +
'ORDER BY meetings.meetings_key'

sth = @dbh.query(query)
row = sth.fetch_row
puts "#{sth} , #{row.class} - #{row}"
puts "#{row[0].class} , #{row[1].class} , #{row[2].class}"

But this returns :

#<Mysql::Result:0x00000000aff938> , Array - ["6", "2012-01-11", "Jan board meeting"]
String , String , String

Why the difference? I would really like to ba able to use fetch_hash with a
prepared statement or get the data in the form of the prepare results.

Options: ReplyQuote


Subject
Written By
Posted
differences between prepare(stmt) and query(stmt) results
August 13, 2012 10:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.