I want to run multiple SQL statements in Mysql2::Client

I want to read the text file sql.txt with the ruby statement and run it in mysql2, but it doesn't work.

#DB Settings
db=Mysql2::Client.new(...)


# Loading sql Statements
sql = ' '
File.open("."/sql.txt", "r") do | f |
    sql = f.read
end

# execution
db.query(sql)

If you write one sql.txt in sql, it works, but if you write more than one, you will get an error.txt, you get an error.
Is there any good way?

Successful sql.txt

SELECT*
FROM`test`;

Failed sql.txt

SELECT*
FROM`test`;
SELECT*
FROM`test`;

ruby mysql

2022-09-30 17:06

2 Answers

You can execute multiple SQL statements by giving Mysql2::Client.new :flags=>Mysql2::Client::MULTI_STATEMENTS.To retrieve the results:

result=db.query(sql)
pp result.first
whiledb.next_result
  result=db.store_result
  pp result.first
end

Note: brianmario/mysql2:Multiple result sets

2022-09-30 17:06

Yasu's answer is correct if you have to execute a duplicate sentence in the program.

If you just want to run SQL stored in a file,

$mysqldbname<sql.txt

But I can.

As yasu's answer, it is troublesome to get results when dealing with multiple sentences, so you can read them from a file and run them in one SQL unit.

sql='"
IO.foreach("./sql.txt") do | line |
  sql+=line
  if sql=~/;\s*\z/# If a line appears ending in a semicolon, consider it a SQL break
    db.query(sql)
    sql = ' '
  end
end

2022-09-30 17:06

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656