Hello,
I use the 'pg' gem to send some data files to a postgresql database. I
use the 'COPY ... FROM ...' query, on STDIN. Looks like this :
db.exec("COPY t(a, b, c) FROM STDIN WITH DELIMITER '\t' csv QUOTE ''''")
file = File.open("filename", "r")
file.each_line { |line| db.put_copy_data(line) }
db.put_copy_end
It works. If I try to run a bad SQL query I get a PGError exception. If
I try to run it with bad data files, I get no exception but the data is
not (or not entirely) put in the database. The same data file with a
copy command runned in psql will fail and display an error.
Is there a way to know when the copy fails from the ruby program ? and
get the error message ?
Thanks
1. the COPY FROM runs in a transaction so you won't get
half-inserted data
2. the data-transfer part of the copy is inside a begin/rescue
that calls #put_copy_end with an error-message argument if
an error occurs, which forces the COPY to fail with the
given message.
From the relevant parts of the PostgreSQL API docs:
PQputCopyEnd
Sends end-of-data indication to the server during COPY_IN
state.
int PQputCopyEnd(PGconn *conn, const char *errormsg);
Ends the COPY_IN operation successfully if errormsg is NULL.
If errormsg is not NULL then the COPY is forced to fail,
with the string pointed to by errormsg used as the error
message.
···
On Apr 1, 2011, at 4:50 AM, Yoann M. wrote:
Hello,
I use the 'pg' gem to send some data files to a postgresql database. I
use the 'COPY ... FROM ...' query, on STDIN. Looks like this :
db.exec("COPY t(a, b, c) FROM STDIN WITH DELIMITER '\t' csv QUOTE ''''")
file = File.open("filename", "r")
file.each_line { |line| db.put_copy_data(line) }
db.put_copy_end
It works. If I try to run a bad SQL query I get a PGError exception. If
I try to run it with bad data files, I get no exception but the data is
not (or not entirely) put in the database. The same data file with a
copy command runned in psql will fail and display an error.
Is there a way to know when the copy fails from the ruby program ? and
get the error message ?
--
Michael Granger <ged@FaerieMUD.org>
Rubymage, Architect, Believer
The FaerieMUD Consortium <http://www.FaerieMUD.org/>
Sorry for replying so late, and thank you so much for your link it's
very helpful. With the example code, I can catch Postgresql status and
know if the COPY has failed or not (lines 73 and 74). I get a
PGRES_FATAL_ERROR when my datafiles are not ok, and a PGRES_COMMAND_OK
when everything went fine.
This solves my first issue, but my second issue still remains :
is there any way to get the postgresql error message about why the COPY
failed ? Inside psql, we get a very precise message including line
number, I'd like to display that.