ECPG and COPY FROM STDIN

Hey List!
I have to do a Benchmark-Test in C (ECPG) with and without network.
After reading the Performance and COPY Documentation (not correctly), I
wanted to Copy a File over the Network by using "COPY FROM 'filename'".
After some curious Problems I knew why.
Now I read some about "COPY FROM STDIN. But I didn't find an Example.
Can someone please give me an Example for that ?

Or:

Is there another command to insert many tuples from a Client to a
Server in an adequate time ?

Niels

On Monday 20 November 2006 06:12, Niels Laakmann wrote:
> Hey List!
> I have to do a Benchmark-Test in C (ECPG) with and without network.
> After reading the Performance and COPY Documentation (not correctly), I
> wanted to Copy a File over the Network by using "COPY FROM 'filename'".
> After some curious Problems I knew why.
> Now I read some about "COPY FROM STDIN. But I didn't find an Example.
> Can someone please give me an Example for that ?
>
> Or:
>
> Is there another command to insert many tuples from a Client to a
> Server in an adequate time ?

How about here:

http://www.postgresql.org/docs/8.1/interactive/libpq-copy.html

Sean

Am heiligen Mo, 20 November 2006 06:42:23 -0500
schrieb Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>:

> On Monday 20 November 2006 06:12, Niels Laakmann wrote:
> > Hey List!
> > I have to do a Benchmark-Test in C (ECPG) with and without network.
> > After reading the Performance and COPY Documentation (not correctly), I
> > wanted to Copy a File over the Network by using "COPY FROM 'filename'".
> > After some curious Problems I knew why.
> > Now I read some about "COPY FROM STDIN. But I didn't find an Example.
> > Can someone please give me an Example for that ?
> >
> > Or:
> >
> > Is there another command to insert many tuples from a Client to a
> > Server in an adequate time ?
>
> How about here:
>
> http://www.postgresql.org/docs/8.1/interactive/libpq-copy.html

At first thanks for this Tipp. I rewrite my Code to use Libpq now. But
the next Problem comes up:

<snap>
char textbuffer[120];

result = PQexec(connect, "COPY branches (branchid, branchname, balance,
address) FROM STDIN WITH DELIMITER ',';");

if(PQresultStatus(result) != PGRES_COPY_IN)
shutdown_conn(1, connect); //my own error-handling
//function

for(n=0; n < count_tupel; n++) {
sprintf(textbuffer, "%d,text1,0,text2", n);
if(PQputCopyData(connect, textbuffer, strlen(textbuffer) + 1) != 1)
shutdown_conn(1, connect);
}
PQputCopyEnd(connect, NULL);
PQgetResult(connect);
</snap>

If count_tupel is 1, one Tupel is inserted to the relation. If
count_tupel is greater than 1. No Tupel is inserted and I got the
Error-Message: "extra data after last expected column".
What are we doing wrong ? - Is there something wrong as I discard the
buffer ? - A Null-Termination etc. ?

The other thing is, that I've looked for some libpq-copy - examples.
But neither in the postgresql-src nor on some postgres-related
internet-pages, I found one.

Can anyone help ?
- Thanks -

Niels

>
> Sean
>

Niels Laakmann wrote:
> Am heiligen Mo, 20 November 2006 06:42:23 -0500
> schrieb Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>:
>
>
>> On Monday 20 November 2006 06:12, Niels Laakmann wrote:
>>
>>> Hey List!
>>> I have to do a Benchmark-Test in C (ECPG) with and without network.
>>> After reading the Performance and COPY Documentation (not correctly), I
>>> wanted to Copy a File over the Network by using "COPY FROM 'filename'".
>>> After some curious Problems I knew why.
>>> Now I read some about "COPY FROM STDIN. But I didn't find an Example.
>>> Can someone please give me an Example for that ?
>>>
>>> Or:
>>>
>>> Is there another command to insert many tuples from a Client to a
>>> Server in an adequate time ?
>>>
>> How about here:
>>
>> http://www.postgresql.org/docs/8.1/interactive/libpq-copy.html
>>
>
> At first thanks for this Tipp. I rewrite my Code to use Libpq now. But
> the next Problem comes up:
>
> <snap>
> char textbuffer[120];
>
> result = PQexec(connect, "COPY branches (branchid, branchname, balance,
> address) FROM STDIN WITH DELIMITER ',';");
>
> if(PQresultStatus(result) != PGRES_COPY_IN)
> shutdown_conn(1, connect); //my own error-handling
> //function
>
> for(n=0; n < count_tupel; n++) {
> sprintf(textbuffer, "%d,text1,0,text2", n);
> if(PQputCopyData(connect, textbuffer, strlen(textbuffer) + 1) != 1)
> shutdown_conn(1, connect);
> }
> PQputCopyEnd(connect, NULL);
> PQgetResult(connect);
> </snap>
>
> If count_tupel is 1, one Tupel is inserted to the relation. If
> count_tupel is greater than 1. No Tupel is inserted and I got the
> Error-Message: "extra data after last expected column".
> What are we doing wrong ? - Is there something wrong as I discard the
> buffer ? - A Null-Termination etc. ?
>
You need to have each line end in "\n"; the line needs to be a line,
just like you would print to the screen.

Sean