COPY syntax in ColdFusion
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
I't probably because Cold Fusion and/or ODBC messes up the SQL, so it
isn't in a valid COPY format.
You can try to use insert statements, because 7.1 should have better
insert performance than 7.0.
If you really want to use the COPY command, the you can generate the SQL
and put it in a file (using CFFILE) and try to load it into PostgreSQL
using CFQUERY or the psql client.
Poul L. Christiansen
On Thu, 26 Apr 2001, Eugene Lee wrote:
Show quoted text
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 connectionThis 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---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?