Subject: Clarification reg ASCII files to PG classes and back

From: [email-masked]
Date: Sun, 10 Apr 1994 11:57:17 -0500 (CDT)

I am writing an application in C which will has to post data from an ASCII
file to Postgres classes. I have tried using the copy command
embedded in C program as follows in approach 1:

Approach 1.

#include <stdio.h>
#include "/home/coyote/postgres/src/backend/tmp/libpq.h"

main()
{
	PortalBuffer *p;
	int i, n;
	PQsetdb ("ftransferdb1");
	PQexec ("begin");
	PQexec ("copy LINE from "/home/coyote/postgres/sriramk/srcfile""); 
        PQputline(".\n");
        PQendcopy();
	PQexec ("end");
}

Each individual line in the srcfile needs to be copied as  an individual
tuple in the class called LINE. It worked fine when I used the copy command
at the Postgres prompt. But it didn't work in a C program as above.

So alternatively I tried out the following approach 2 which worked.

Approach 2

#include <stdio.h>

main()
{
	
     system ("monitor ftransferdb1 < tranferfile");
}

where transferfile is  the following:
                         
     copy LINE from "/home/coyote/postgres/sriramk/srcfile" \g

I feel Approach 1 is a better way of doing it (if it can be made to work)
rather than creating a number of command files (like transferfile above)

I had earlier addressed Postgres and tried out the alternative 1 in line
with suggestion from Mr Aoki, but I must be missing some point which
prevents the approach 1 from working.

A suitable and good solution is required as my project involves transferring
large volumes of data to and fro.

Looking forward to suggestions,

Thanks
Sriram Kal.

---

From: [email-masked] (Paul M. Aoki)
Date: Sun, 10 Apr 94 14:06:02 -0700

> 	PQexec ("copy LINE from "/home/coyote/postgres/sriramk/srcfile""); 

this last line isn't correct C, but i assume you know that..

> at the Postgres prompt. But it didn't work in a C program as above.

what someone was trying to say with the following (rather opaque)
statement in the reference manual:
  The copy command in POSTGRES has options to read from or write to the net-
  work connection used by LIBPQ.  Therefore, functions are necessary to
  access this network connection directly so applications may take full
  advantage of this capability.
is that PQgetline, PQputline and PQendcopy are for use with copy 
to/from stdin/stdout.  what isn't very clear is that they are ONLY
for use with copy to/from stdin/stdout.

if you're happy with copying from a particular file, you don't
need PQputline and PQendcopy.  i just ran the following program
and it works:

main()
{
PQsetdb("foo");
PQexec("create foo (a=int4)");
PQexec("copy foo from \"/home2/aoki/xxx\"");
}

although it doesn't do any error-checking, etc.

most people want to be able to generate instances from within
their program and send them directly to the backend (without
an intermediate file), which is what the PQputline stuff is for.

> with suggestion from Mr Aoki

hey, i didn't suggest *that*.  don't be puttin' words in my 
mouth.. :-)  i suggested you take a look at the example in 
the libpq section and at the source for monitor.

the reason i said to look at monitor is that there are some
real-world considerations with respect to error-checking,
buffer-handling, etc. that you won't see in a tiny sample 
program.
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki

---

From: Tom Cox <[email-masked]>
Date: Sun, 10 Apr 1994 23:16:38 -0700 (PDT)

> Looking forward to suggestions,

A approach that I used when transfering ASCII information to a pg 
database looks something like this.

void main(void)
{
  FILE *pp, *source;
  pp = popen("monitor grass", "w");
  source = fopen(/*source filename*/,"r");
  /*  fscanf(source) and fprintf(pp, "append class(field = %s) \g\q", 
  somestring) statements go here. */
  pclose(pp);
   
}

This approach worked the first time with no problems.  I redirected the
program output to a log file that I could later look at to verify every was
correctly copied.

-Tom Cox
[email-masked]