2008年12月24日星期三

how to execute mutli-statements In Ruby/Mysql

there is a tip when you need to execute multi-statements in Ruby/Mysql.
the wrong version:

require 'mysql'
dbh=Mysql.real_connect('localhost','root','pass','db')
dbh.set_server_option( Mysql::OPTION_MULTI_STATEMENTS_ON )
stmt="select * from contents limit 1;\n" *10
puts stmt
dbh.query( stmt )
puts dbh.stat # got the CR_COMMANDS_OUT_OF_SYNC error.

....

the correct version:


require 'mysql'
dbh=Mysql.real_connect('localhost','root','pass','db')
dbh.set_server_option( Mysql::OPTION_MULTI_STATEMENTS_ON )
stmt="select * from contents limit 1;\n" *10
puts stmt
dbh.query( stmt ) { |res|
puts "number of rows is #{res.num_rows()}"
}
puts dbh.stat

what 's the differents?
Wow,
dbh.query( stmt ) { |res|

puts "number of rows is #{res.num_rows()}"
}

and

dbh.query( stmt )

when multi-statements was executed , a group of result set were present ,
even we need not the the data in the result set .
so we should iterate the result sets and clear it before the next mysql-related statement .

References :
1,http://www.tmtm.org/en/mysql/ruby/ , offical site of Ruby/Mysql
2,http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html , one query multiple statment using C API on Mysql.
3,http://www.kitebird.com/articles/ruby-mysql.html , a goog tutorial for ruby/Mysql

没有评论: