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@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
>
>
>
>