Run sql script from Ruby

Question

Using DBI::DatabaseHandle#execute or DBI::DatabaseHandle#prepare it's not possible to run an sql script (with mutiple sql statments). It fails with the following error :

ERROR: cannot insert multiple commands into a prepared statement

I tried to use the "unprepared" way using DBI::DatabaseHandle#do (the doc says it "goes straight to the DBD‘s implementation") but it keeps throwing the same error.

code snippet:

require 'dbd/pg'
require 'dbi'

DBI.connect("dbi:pg:database=dbname", db_user, db_password, db_params) do |dbh|
  schema = IO::read(schema_file)
  dbh.do(schema)
end

I'm using

ruby 1.8.6 (2007-09-24 patchlevel 111) [i386-mswin32]

dbi-0.4.3

dbd-pg-0.3.9

pg-0.9.0-x86-mswin32

Thank you!

Answer 1

Use either a function or just run multiple prepared queries.

Answer 2

Running multiple queries using DatabaseHandle#do is a missing feature in DBD-Pg. See Ruby/DBI feature 28001.

Note that Pg, the native postgresql Ruby driver on which DBD-Pg is based, allows running multiple queries.

Example:

require 'pg'
require 'dbd/pg'
require 'dbi'

# Pg Succeeds
PGconn.new({:host=>host,:user=>user,:password=>password,:dbname=>dbname}) do |conn|
  conn.exec("select 1; select 1;")
end

# DBD-Pg Fails with: ERROR: cannot insert multiple commands ...
DBI::connect("dbi:pg:database=#{dbname};host=#{host};", user, password) do |dbh|
  dbh.do("select 1; select 1;")
end