Re: COPY support in pgsql-jdbc driver

Michael,

Offhand, with out looking at the code, I would think that the copy
command ends up being a stream some how.

I would have a look at the psql code and see what it does with copy.

my best guess is that you would want to redirect the input/output to a
stream.

Dave
On Wed, 2002-06-19 at 17:33, Michael Adler wrote:
>
>
> I'm trying to add COPY support to the org.postgresql driver. This is my
> first work with pgsql below the "application" level.
>
> The first challenge is that the response to a COPY query is not a result
> set, so the assumptions of the QueryExecutor don't apply nicely here
> (execute() returns a ResultSet). I didn't investigate the possibility of
> making the copy output behave like a resultset, although I guess it's an
> option.
>
> The solution I came up with is to add two methods to the
> org.postgresql.Connection class and have them manage the entire transfer.
> This is not beautiful, but it's a start. Someone who is "intimate" with
> the project will no doubt have a better idea. (although the copy protocol
> seems to be the exception to the rest of the protocol, so perhaps the
> code would reflect that).
>
> The copyOut() method works well enough to read actual data from the
> backend.
>
> The copyIn() methods does not work. I'm not sure why.
>
> I suspect one problem is my mishandling of the pg_stream and the fe/be
> protocol.
>
> Two other shortcomings of the code - the lack of exception handling and
> the lack of synchronization of the pg_stream. I'm not sure if the later is
> a problem, but those can be addressed later.
>
>
> // in org.postgresql.Connection :
>
>
>
> // *****************
> // Postgres COPY handling
> // *****************
>
> /*
> * This will take the name of a table, construct a COPY OUT query, send the query
> * ( while bypassing QueryExecutor), receive the resulting bytes of data and return
> * a ByteArrayOutputStream.
> *
> */
>
> public ByteArrayOutputStream copyOut(String table) throws Exception
> {
> ByteArrayOutputStream out = new ByteArrayOutputStream();
>
> // duplicates statements in QueryExecutor.sendQuery
> pg_stream.SendChar('Q');
> pg_stream.Send(this.getEncoding().encode( "COPY " + table + " TO STDOUT" ));
> pg_stream.SendChar(0);
> pg_stream.flush();
>
> // check response from backend
> int response = pg_stream.ReceiveChar();
>
> if (response != 'H') {
> throw new Exception("Copy should receive H from backend, but instead received: " + (char)response );
> }
>
> // read input stream one char at a time, but always holding three
> int a = pg_stream.ReceiveChar();
> int b = pg_stream.ReceiveChar();
> int c = pg_stream.ReceiveChar();
>
> while (true) {
> if ( a == '\\' && b == '.' && c == '\n' ) {
> // this sequence of bytes means the copy is over
> break;
> }
>
> out.write(a);
>
> a = b;
> b = c;
> c = pg_stream.ReceiveChar();
> }
>
> String str = pg_stream.ReceiveString(this.getEncoding());
> System.out.println( "Received String " + str );
>
> return out;
> }
>
>
> /*
> * This will take the name of a table and a ByteArrayInputStream, construct a COPY IN query,
> * send the query ( while bypassing QueryExecutor), send the bytes of data and send the 3 bytes
> * that signify the end of the copy
> *
> */
>
> public void copyIn (String table, ByteArrayInputStream in) throws Exception
> {
> // duplicates statements in QueryExecutor.sendQuery
> pg_stream.SendChar('Q');
> pg_stream.Send(this.getEncoding().encode( "COPY " + table + " FROM STDIN " ));
> pg_stream.SendChar(0);
> pg_stream.flush();
>
> // check response from backend
> int response = pg_stream.ReceiveChar();
>
> if (response != 'G') {
> throw new Exception("Copy should receive G from backend, but instead received: " + (char)response );
> }
>
> // send the whole input stream
> int b = in.read();
> while (b != -1) {
> pg_stream.SendChar((char)b);
> b = in.read();
> }
>
> //send the special row
> pg_stream.Send( new byte[] { (byte)'\\', (byte)'.', (byte)'\n' } );
> pg_stream.flush();
>
> String str = pg_stream.ReceiveString(this.getEncoding());
> // str should be "COPY" ?
> System.out.println( "Received String " + str );
> }
>
>
>
> ############################################
> here's the class that's used to test the methods
> ############################################
>
> import java.sql.*;
> import java.util.*;
> import javax.sql.*;
> import java.net.*;
> import java.io.*;
>
> public class TestCopy {
>
> static {
> try {
> Class.forName("org.postgresql.Driver");
> }
> catch (Exception e) {
> e.printStackTrace();
> System.err.println(e);
> System.exit(1);
> }
> }
>
> public TestCopy () throws Exception {
> // nothing in constructor
> }
>
> public static void main (String array[] ) throws Exception {
>
> Connection local_con = DriverManager.getConnection("jdbc:postgresql://vision/sync_corp2", "eagle" , "c0ntr0l");
>
> // cast the connection so that you can access methods not available in java.sql.Connection
> org.postgresql.Connection con = (org.postgresql.Connection)local_con;
>
> // create a byte stream by copying out data from the source table
> ByteArrayOutputStream out = con.copyOut("source_table");
>
> // copy the byte stream into another table. in practice, you'd use COPY to
> // copy data from one database to another, not just one table to another
> con.copyIn("destination_table", new ByteArrayInputStream(out.toByteArray()));
>
> }
> }
>
>
>
>
>
>
>
>
>
> On 14 Jun 2002, Dave Cramer wrote:
>
> > Michael,
> >
> > You are likely going to have to look at the code in psql, and create a
> > stream to copy from.
> >
> > Dave
> > On Fri, 2002-06-14 at 14:44, Michael Adler wrote:
> > > On Fri, 14 Jun 2002, Sam Varshavchik wrote:
> > >
> > > > Date: Fri, 14 Jun 2002 14:02:40 -0400
> > > > From: Sam Varshavchik <mrsam(at)courier-mta(dot)com>
> > > > To: Michael Adler <adler(at)glimpser(dot)org>
> > > > Cc: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
> > > > Subject: Re: COPY support in pgsql-jdbc driver
> > > >
> > > > Michael Adler writes:
> > > >
> > > > >> > in the driver. I don't believe it is a jdbc standard though?
> > > > >> It's not. I'm quite happy with a separate API.
> > > > >
> > > > > Which API are you refering to? jxdbcon?
> > > >
> > > > The org.postgresql package.
> > >
> > > Sam,
> > >
> > > Is there any documentaion on how to use COPY with the org.postgresql
> > > package? I haven't found any.
> > >
> > > org.postgresql.core.QueryExecutor doesn't seem to support it. How do you
> > > get it to work?
> > >
> > > Thanks,
> > >
> > > Mike
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> > >
> > >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
> Mike
>
>
>
>