Re: COPY support in pgsql-jdbc driver

On 19 Jun 2002, Dave Cramer wrote:

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

I was thinking the same thing:

public ByteArrayOutputStream copyOut(String table) throws Exception
public void copyIn (String table, ByteArrayInputStream in) throws Exception


> 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@courier-mta.com>
> > > > > To: Michael Adler <adler@glimpser.org>
> > > > > Cc: "pgsql-jdbc@postgresql.org" <pgsql-jdbc@postgresql.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@postgresql.org
> > > >
> > > >
> > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
> > >
> >
> > Mike
> >
> >
> >
> >
>
>

Mike