Does anyone have experience using the COPY command in ColdFusion to
populate a table? Doing it from the command line is easy, but I can't
seem to get the right syntax within a ColdFusion query. I have a table
called "category":
# \d categories
Table "categories"
Attribute | Type | Modifier
------------+----------+----------
productid | text |
category | text |
catid | smallint |
index | smallint |
And this is the ColdFusion code I'm using:
<CFQUERY NAME="populatetablequery" DATASOURCE="productdb" DBTYPE="ODBC">
COPY categories FROM STDIN ;
304 ALUM Aluminum 1 1
9415 Aluminum 1 2
324 Aluminum 1 3
\.
</CFQUERY>
If I cut-and-paste the query, it works. But within ColdFusion, I get
this PostgreSQL error:
Apr 27 00:42:05 localhost logger: ERROR: parser: parse error at or near "304"
Apr 27 00:42:05 localhost logger: pq_recvbuf: unexpected EOF on client connection
Apr 27 00:42:05 localhost logger: ERROR: parser: parse error at or near "304"
Apr 27 00:42:05 localhost logger: pq_flush: send() failed: Broken pipe
Apr 27 00:42:05 localhost logger: pq_recvbuf: unexpected EOF on client connection
This is with PostgreSQL 7.1. For the best performance and avoiding
server timeouts, I'd rather not insert one record at a time. Anyways,
thanks in advance for any tips.
--
Eugene Lee
eugene@anime.net
