Re: COPY syntax in ColdFusion

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:

> 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(at)anime(dot)net
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>