Re: COPY support in pgsql-jdbc driver
От | Michael Adler |
---|---|
Тема | Re: COPY support in pgsql-jdbc driver |
Дата | |
Msg-id | Pine.NEB.4.44.0206192244150.16987-100000@reva.sixgirls.org обсуждение исходный текст |
Ответ на | Re: COPY support in pgsql-jdbc driver (Dave Cramer <Dave@micro-automation.net>) |
Список | pgsql-jdbc |
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
В списке pgsql-jdbc по дате отправления: