Thread: streaming result sets: progress
I have made quite a bit of progress on the streaming result set implementation. Unfortunately, I found it too difficult to do without cleaning up the call path for creating result sets. My issue was that a streamed ResultSet has to have some way of re-initing itself with the new results. It also has to be able to run a new query. In the current code the ResultSet is a wrapper round an array of results, it doesn't know much about the database (tho it has a link to it's owning statement). In a streamed implementation the ResultSet has to become much more smart about knowing what it has (well, the logic to do that has to be somewhere, it seems sensble to put it either in the result set or in the statement). I found that there was quite a spread of methods which deal with generating results, responsibilities are shared between JdbcConnection, AbstractJdbcConnection, AbstractJdbcStatement and the result set impl and the QueryExecutor. My changes made all of these subtle interconnections unmanageable. I tried to draw some UML of it and failed: just too many classes. So what I'm seeking is guidance: should I submit a large patch with all the changes in? (including changes I've made to clean up the call path) Should I first submit patches to clean up the call path? (this is my preferred option) and then implement the streamed result sets on top of that? Should I make a tar available of the source of my changes? Should I work harder to make my changes fit in with the existing code? Nic
Nic, You should send us diff's of everything, we don't have to apply all of them at once, but we will be able to see where you are going. Dave On Tue, 2002-11-05 at 19:52, Nic Ferrier wrote: > I have made quite a bit of progress on the streaming result set > implementation. Unfortunately, I found it too difficult to do without > cleaning up the call path for creating result sets. > > My issue was that a streamed ResultSet has to have some way of > re-initing itself with the new results. It also has to be able to run > a new query. > > In the current code the ResultSet is a wrapper round an array of > results, it doesn't know much about the database (tho it has a link to > it's owning statement). > > In a streamed implementation the ResultSet has to become much more > smart about knowing what it has (well, the logic to do that has to be > somewhere, it seems sensble to put it either in the result set or in > the statement). > > > I found that there was quite a spread of methods which deal with > generating results, responsibilities are shared between > JdbcConnection, AbstractJdbcConnection, AbstractJdbcStatement and the > result set impl and the QueryExecutor. > > My changes made all of these subtle interconnections > unmanageable. I tried to draw some UML of it and failed: just too > many classes. > > > So what I'm seeking is guidance: should I submit a large patch with > all the changes in? (including changes I've made to clean up the call > path) > > Should I first submit patches to clean up the call path? (this is my > preferred option) and then implement the streamed result sets on top > of that? > > Should I make a tar available of the source of my changes? > > Should I work harder to make my changes fit in with the existing code? > > > > Nic > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Dave Cramer <Dave@micro-automation.net>
Nic Ferrier wrote: > So what I'm seeking is guidance: should I submit a large patch with > all the changes in? (including changes I've made to clean up the call > path) > > Should I first submit patches to clean up the call path? (this is my > preferred option) and then implement the streamed result sets on top > of that? > I agree that this is the best option. And I agree that there is plenty of room for this code to be cleaned up. > Should I make a tar available of the source of my changes? > I don't think that is necessary. > Should I work harder to make my changes fit in with the existing code? > No. Spend your time improving the code, don't waste it trying to hack the existing structure. I am looking forward to seeing what you have done. thanks, --Barry
Here's my first patch. This is very incomplete, but it does work with basic querys. An example program is attached. The patch also includes significant, but not comprehensive, refactoring of query paths. I should stress that the way it's done reflects only my own views and there are many other ways of achieving the same ends. Nic
Attachment
Hello Nic, I am used your patch for 7.3.b5 and I haven't interface PGResultSet Class AbstractJdbc1ResultSet implements PGResultSet and I have ResultSet in QueryExecutor.java (line 177) What is wrong ? regards Haris Peco On Wednesday 13 November 2002 12:27 am, Nic Ferrier wrote: > Here's my first patch. > > This is very incomplete, but it does work with basic querys. An > example program is attached. > > The patch also includes significant, but not comprehensive, > refactoring of query paths. I should stress that the way it's done > reflects only my own views and there are many other ways of achieving > the same ends.
Message-ID: <uof8t5sxf.fsf@tapsellferrier.co.uk> Lines: 15 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii --text follows this line-- snpe <snpe@snpe.co.yu> writes: > Hello Nic, > I am used your patch for 7.3.b5 and I haven't interface PGResultSet > Class AbstractJdbc1ResultSet implements PGResultSet and I have ResultSet in QueryExecutor.java (line 177) > > What is wrong ? The patch is incomplete /8-> It was purely illustrative at this stage, and I missed (accidentally as it happens) the extra classes. I'll send another patch tonight. Nic
nferrier@tapsellferrier.co.uk writes: > Message-ID: <uof8t5sxf.fsf@tapsellferrier.co.uk> > Lines: 15 > MIME-Version: 1.0 > Content-Type: text/plain; charset=us-ascii > --text follows this line-- > snpe <snpe@snpe.co.yu> writes: > > > Hello Nic, > > I am used your patch for 7.3.b5 and I haven't interface PGResultSet > > Class AbstractJdbc1ResultSet implements PGResultSet and I have ResultSet in QueryExecutor.java (line 177) > > > > What is wrong ? > > The patch is incomplete /8-> > > It was purely illustrative at this stage, and I missed (accidentally > as it happens) the extra classes. I'll send another patch tonight. So I don't have to run the diff again whilst including this file, here's the extra class. It goes in: src/interfaces/jdbc/org/postgresql/PGResultSet.java Nic
Attachment
On Wednesday 13 November 2002 11:57 pm, Nic Ferrier wrote: >... > > It was purely illustrative at this stage, and I missed (accidentally > > as it happens) the extra classes. I'll send another patch tonight. > > So I don't have to run the diff again whilst including this file, > here's the extra class. It goes in: > > src/interfaces/jdbc/org/postgresql/PGResultSet.java > .. I have tried this patch.Table have 1048576 rows and standard jdbc doesn't work (select * from tab) With Your patch it works.That is cool, but I have some problems : 1) I have made jdbc with jdk 1.4.1, but it is for jdbc2 (I guess) 2) method nic_execute create query like : BEGIN; DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100 FROM JDBC_CURS_1; I add ";" before FETCH 3) JDBC in cvs changed, but patch doesn't work with current cvs regards Haris Peco
Message-ID: <uu1iki0vx.fsf@tapsellferrier.co.uk> Lines: 35 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii --text follows this line-- snpe <snpe@snpe.co.yu> writes: > On Wednesday 13 November 2002 11:57 pm, Nic Ferrier wrote: > >... > > > It was purely illustrative at this stage, and I missed (accidentally > > > as it happens) the extra classes. I'll send another patch tonight. > > > > So I don't have to run the diff again whilst including this file, > > here's the extra class. It goes in: > > > > src/interfaces/jdbc/org/postgresql/PGResultSet.java > > .. > I have tried this patch.Table have 1048576 rows and standard jdbc doesn't work > (select * from tab) > With Your patch it works.That is cool, but I have some problems : > 1) I have made jdbc with jdk 1.4.1, but it is for jdbc2 (I guess) It doesn't work with 1.4.1? > 2) method nic_execute create query like : > BEGIN; DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100 FROM > JDBC_CURS_1; > I add ";" before FETCH Hmmm... that's odd. Does the statement you supply have a ";" on the end of it? My patch only works if it does. > 3) JDBC in cvs changed, but patch doesn't work with current cvs I'll do a resync and reissue the patch tonight. Nic
On Thursday 14 November 2002 04:14 pm, nferrier@tapsellferrier.co.uk wrote: > Message-ID: <uu1iki0vx.fsf@tapsellferrier.co.uk> > Lines: 35 > MIME-Version: 1.0 > Content-Type: text/plain; charset=us-ascii > --text follows this line-- > > snpe <snpe@snpe.co.yu> writes: > > On Wednesday 13 November 2002 11:57 pm, Nic Ferrier wrote: > > >... > > > > > > > It was purely illustrative at this stage, and I missed (accidentally > > > > as it happens) the extra classes. I'll send another patch tonight. > > > > > > So I don't have to run the diff again whilst including this file, > > > here's the extra class. It goes in: > > > > > > src/interfaces/jdbc/org/postgresql/PGResultSet.java > > > .. > > > > I have tried this patch.Table have 1048576 rows and standard jdbc doesn't > > work (select * from tab) > > With Your patch it works.That is cool, but I have some problems : > > 1) I have made jdbc with jdk 1.4.1, but it is for jdbc2 (I guess) > > It doesn't work with 1.4.1? I add AbstractJdbc3Statement missing method from AbstractJdbc2Statement and it work.I don't test complete, but simple command on table with 1 mil. rows work fine. > > > 2) method nic_execute create query like : > > BEGIN; DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100 > > FROM JDBC_CURS_1; > > I add ";" before FETCH > > Hmmm... that's odd. Does the statement you supply have a ";" on the > end of it? My patch only works if it does. You send this : BEGIN; DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100 FROM JDBC_CURS_1; I change and send : BEGIN; DECLARE JDBC_CURS_1 CURSOR FOR select * from tab; FETCH FORWARD 100 FROM JDBC_CURS_1; > > > 3) JDBC in cvs changed, but patch doesn't work with current cvs > > I'll do a resync and reissue the patch tonight. > > regards Haris Peco
snpe <snpe@snpe.co.yu> writes:
> I add AbstractJdbc3Statement missing method from AbstractJdbc2Statement and
> it work.I don't test complete, but simple command on table with 1 mil. rows
> work fine.
Ok.
> > > 2) method nic_execute create query like :
> > > BEGIN; DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100
> > > FROM JDBC_CURS_1;
> > > I add ";" before FETCH
> >
> > Hmmm... that's odd. Does the statement you supply have a ";" on the
> > end of it? My patch only works if it does.
>
> You send this :
> BEGIN; DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100
> FROM JDBC_CURS_1;
> I change and send :
> BEGIN; DECLARE JDBC_CURS_1 CURSOR FOR select * from tab; FETCH FORWARD 100
> FROM JDBC_CURS_1;
Yes. But the reason I send:
DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100 FROM JDBC_CURS_1;
is because the SQL statement you supply is _supposed_ to end with a
";". The code for the above is actually doing:
DECLARE JDBC_CURS_1 CURSOR FOR $userquery FETCH FORWARD 100 FROM JDBC_CURS_1;
where $userquery is what comes in from the client code, eg:
Statement st = con.createStatement();
ResultSet rs = con.executeQuery("select * from tab;");
then "select * from tab;" is inserted as $userquery. It must always
end with ";" because that's how PGSQL's normal query processing works.
So if your code does this:
Statement st = con.createStatement();
ResultSet rs = con.executeQuery("select * from tab");
it won't work. AFAIK it won't work with unpatched PGSQL JDBC either
(please correct me if I'm wrong).
Nic
On Thursday 14 November 2002 04:30 pm, nferrier@tapsellferrier.co.uk wrote:
> snpe <snpe@snpe.co.yu> writes:
> > I add AbstractJdbc3Statement missing method from AbstractJdbc2Statement
> > and it work.I don't test complete, but simple command on table with 1
> > mil. rows work fine.
>
> Ok.
>
> > > > 2) method nic_execute create query like :
> > > > BEGIN; DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD
> > > > 100 FROM JDBC_CURS_1;
> > > > I add ";" before FETCH
> > >
> > > Hmmm... that's odd. Does the statement you supply have a ";" on the
> > > end of it? My patch only works if it does.
> >
> > You send this :
> > BEGIN; DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100
> > FROM JDBC_CURS_1;
> > I change and send :
> > BEGIN; DECLARE JDBC_CURS_1 CURSOR FOR select * from tab; FETCH FORWARD
> > 100 FROM JDBC_CURS_1;
>
> Yes. But the reason I send:
>
> DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100 FROM
> JDBC_CURS_1;
>
> is because the SQL statement you supply is _supposed_ to end with a
> ";". The code for the above is actually doing:
>
> DECLARE JDBC_CURS_1 CURSOR FOR $userquery FETCH FORWARD 100 FROM
> JDBC_CURS_1;
>
> where $userquery is what comes in from the client code, eg:
>
> Statement st = con.createStatement();
> ResultSet rs = con.executeQuery("select * from tab;");
>
> then "select * from tab;" is inserted as $userquery. It must always
> end with ";" because that's how PGSQL's normal query processing works.
>
> So if your code does this:
>
> Statement st = con.createStatement();
> ResultSet rs = con.executeQuery("select * from tab");
>
> it won't work. AFAIK it won't work with unpatched PGSQL JDBC either
> (please correct me if I'm wrong).
>
I can't use it, because I haven't change query in 3pty software (btw)
In standard jdbc this is work :
Statement st = con.createStatement();
ResultSet rs = con.executeQuery("select * from tab");
It is same with Your patch if I use next
DECLARE JDBC_CURS_1 CURSOR FOR $userquery ; FETCH FORWARD 100 FROM
JDBC_CURS_1;
If is command like that query work unchanged in standard JDBC and with patch
Of course, Your patch works with large tables
regards
Haris Peco
Message-ID: <uof8shz6p.fsf@tapsellferrier.co.uk>
Lines: 18
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
--text follows this line--
snpe <snpe@snpe.co.yu> writes:
> I can't use it, because I haven't change query in 3pty software (btw)
> In standard jdbc this is work :
> Statement st = con.createStatement();
> ResultSet rs = con.executeQuery("select * from tab");
What do you mean "standard jdbc"?
If you use the unpatched postgresql driver you have to always specify
the ";".
This is different to Oracle but it isn't part of the JDBC standard.
Nic
On Thursday 14 November 2002 04:51 pm, nferrier@tapsellferrier.co.uk wrote:
> Message-ID: <uof8shz6p.fsf@tapsellferrier.co.uk>
> Lines: 18
> MIME-Version: 1.0
> Content-Type: text/plain; charset=us-ascii
> --text follows this line--
>
> snpe <snpe@snpe.co.yu> writes:
> > I can't use it, because I haven't change query in 3pty software (btw)
> > In standard jdbc this is work :
> > Statement st = con.createStatement();
> > ResultSet rs = con.executeQuery("select * from tab");
>
> What do you mean "standard jdbc"?
>
> If you use the unpatched postgresql driver you have to always specify
> the ";".
'Standard jdbc' is JDBC from cvs (7.3 STABLE tag)
I haven't specify ; - it is work with and without ;
If you add before FETCH and Your patch work with and without
regards
Haris Peco
nferrier@tapsellferrier.co.uk wrote:
> >I can't use it, because I haven't change query in 3pty software (btw)
> >In standard jdbc this is work :
> > Statement st = con.createStatement();
> > ResultSet rs = con.executeQuery("select * from tab");
>
>
> What do you mean "standard jdbc"?
>
> If you use the unpatched postgresql driver you have to always specify
> the ";".
I never specify the ";" and it works for me.
>
> This is different to Oracle but it isn't part of the JDBC standard.
Maybe not explicitly, but I definitely read that from the specification.
- None of the examples use semicolons.
- It's clearly intended for SQL to be portable between databases, with
the JDBC escapes, requiring drivers to support entry-level SQL-92
syntax, etc. Oracle requires statements to _not_ have semicolons at the
end. Any third-party tools will do things this way.
- JDBC is not intended for running multiple statements at one, except
through executeBatch(). And there's support for grabbing multiple result
sets from a single execution, but that's for callable statements that
have multiple ref cursors as output parameters. You definitely can't
retrieve both update counts and result sets. So without support for
multiple statements, adding statement separators is silly.
I'm really, really looking forward to your streaming result sets
(everything else in my application streams already!), but please don't
require me to do things in a way incompatible with the rest of the world
for no good reason.
Thanks,
Scott
nferrier@tapsellferrier.co.uk wrote:
> snpe <snpe@snpe.co.yu> writes:
>
> Yes. But the reason I send:
>
> DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100 FROM JDBC_CURS_1;
>
> is because the SQL statement you supply is _supposed_ to end with a
> ";". The code for the above is actually doing:
>
> DECLARE JDBC_CURS_1 CURSOR FOR $userquery FETCH FORWARD 100 FROM JDBC_CURS_1;
>
> where $userquery is what comes in from the client code, eg:
>
> Statement st = con.createStatement();
> ResultSet rs = con.executeQuery("select * from tab;");
>
> then "select * from tab;" is inserted as $userquery. It must always
> end with ";" because that's how PGSQL's normal query processing works.
>
The the ; is not required for the rest of the jdbc driver. In fact in
other areas of the code (like server prepared statements, batch updates)
the requirement is that the user supplied sql statement does *not* end
in a ;.
This is also consistent with other jdbc drivers. In fact oracle gives
you an error if a sql statement ends with a ;.
thanks,
--Barry
Barry Lind <blind@xythos.com> writes:
> nferrier@tapsellferrier.co.uk wrote:
> > snpe <snpe@snpe.co.yu> writes:
> >
> > Yes. But the reason I send:
> >
> > DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100 FROM JDBC_CURS_1;
> >
> > is because the SQL statement you supply is _supposed_ to end with a
> > ";". The code for the above is actually doing:
> >
> > DECLARE JDBC_CURS_1 CURSOR FOR $userquery FETCH FORWARD 100 FROM JDBC_CURS_1;
> >
> > where $userquery is what comes in from the client code, eg:
> >
> > Statement st = con.createStatement();
> > ResultSet rs = con.executeQuery("select * from tab;");
> >
> > then "select * from tab;" is inserted as $userquery. It must always
> > end with ";" because that's how PGSQL's normal query processing works.
> >
>
> The the ; is not required for the rest of the jdbc driver. In fact in
> other areas of the code (like server prepared statements, batch updates)
> the requirement is that the user supplied sql statement does *not* end
> in a ;.
>
> This is also consistent with other jdbc drivers. In fact oracle gives
> you an error if a sql statement ends with a ;.
Apologies to everyone paying attention to this thread... in my
experience postgres has always complained when I haven't supplied the
";".
I will investigate and fix the patch.
Nic
PostgreSQL/Oracle/MSSQL differences (was: streaming result sets: progress)
From
"David Hooker"
Date:
I've just recently updated my code, which has been using PostgreSQL
exclusively for a year, to make it able to run using MSSQL Server and
Oracle.
Some of the differences:
* MSSQL requires that ResultSet.getXXX methods, when used with column
names, are called in column order - PostgreSQL doesn't care. (BTW, the
JDBC javadoc suggests to do this).
* PostgreSQL and MSSQL both treat a trailing semicolon as optional.
Oracle requires that there be NO semicolon.
* Oracle uppercases table names in the ALL_TABLES view (analogous to
pg_tables in PostgreSQL), so for code to be portable all table names
should be created as uppercase. (I just went through my code and
uppercased all my SQL).
* Transactions in MSSQL are handled differently than PostgreSQL and
Oracle - there is no "BEGIN TRANSACTION" statement; instead you have to
toggle "SET IMPLICIT_TRANSACTIONS".
* Oracle doesn't have "text" or "bigint" datatypes.
* MSSQL can't perform string comparisons on "text" datatypes. (i.e.,
"select * from table where field='value'" won't work if field is a text
datatype).
Those are just the differences that bit me. Hope my trial helps you
guys.
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Nic Ferrier
Sent: Thursday, November 14, 2002 1:31 PM
To: Barry Lind
Cc: snpe; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] streaming result sets: progress
Barry Lind <blind@xythos.com> writes:
> nferrier@tapsellferrier.co.uk wrote:
> > snpe <snpe@snpe.co.yu> writes:
> >
> > Yes. But the reason I send:
> >
> > DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100
FROM JDBC_CURS_1;
> >
> > is because the SQL statement you supply is _supposed_ to end with a
> > ";". The code for the above is actually doing:
> >
> > DECLARE JDBC_CURS_1 CURSOR FOR $userquery FETCH FORWARD 100 FROM
JDBC_CURS_1;
> >
> > where $userquery is what comes in from the client code, eg:
> >
> > Statement st = con.createStatement();
> > ResultSet rs = con.executeQuery("select * from tab;");
> >
> > then "select * from tab;" is inserted as $userquery. It must always
> > end with ";" because that's how PGSQL's normal query processing
works.
> >
>
> The the ; is not required for the rest of the jdbc driver. In fact in
> other areas of the code (like server prepared statements, batch
updates)
> the requirement is that the user supplied sql statement does *not* end
> in a ;.
>
> This is also consistent with other jdbc drivers. In fact oracle gives
> you an error if a sql statement ends with a ;.
Apologies to everyone paying attention to this thread... in my
experience postgres has always complained when I haven't supplied the
";".
I will investigate and fix the patch.
Nic
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
David Hooker wrote:
> I've just recently updated my code, which has been using PostgreSQL
> exclusively for a year, to make it able to run using MSSQL Server and
> Oracle.
>
> Some of the differences:
[...]
> * Oracle uppercases table names in the ALL_TABLES view (analogous to
> pg_tables in PostgreSQL), so for code to be portable all table names
> should be created as uppercase. (I just went through my code and
> uppercased all my SQL).
Identifiers are, on PostgreSQL and Oracle:
- folded to the native case if unquoted
- left alone in quoted
(DatabaseMetaData.supportsMixedCaseQuotedIdentifiers() will tell you if
this is true for a specific database)
so all of my table/column names are in native case and I don't have to
do "TABLE_NAME" instead of table_name everywhere. That strikes me as a pain.
In literals, like all_tables, you could just native-case your paremeters
before sending them to the database (DatabaseMetaData will tell you
which is native) or make a stored procedure that does so. "select * from
all_tables where table_name = native_case('foo')". Or just use
DatabaseMetaData instead of directly accessing the (unportable already)
data dictionary; I think it takes care of all stuff like that for you.
On the Java side, ResultSet.getXXX(columnName) is case-insensitive, so
you don't need to worry about it. But ResultSetMetaDAta.getColumnName()
doesn't normalize case; you could toLower() it if your stuff cares.
> * Transactions in MSSQL are handled differently than PostgreSQL and
> Oracle - there is no "BEGIN TRANSACTION" statement; instead you have to
> toggle "SET IMPLICIT_TRANSACTIONS".
This shouldn't be a problem with JDBC - there are functions dealing with
transactions in a general way. An autocommit toggle, a commit/rollback
method, and control over transaction isolation levels. All in the
Connection interface.
> * Oracle doesn't have "text" or "bigint" datatypes.
PostgreSQL's "varchar" and "text" are the same, except that varchar
supports an _optional_ maximum length.
Oracle has clob and long. "clob" is newer and preferred.
Instead of bigint, you can use numeric(N,0), which is standard. It
exists on PostgreSQL and Oracle. I would assume MS SQL as well.
> * MSSQL can't perform string comparisons on "text" datatypes. (i.e.,
> "select * from table where field='value'" won't work if field is a text
> datatype).
Oracle's long has the same limitation. It makes sense, though, because
that would be really, really slow.
And here's a couple others I've hit:
- PostgreSQL supports selecting from no tables. Nothing else does. You
could add a "dual" table with one item for portability. Or use the JDBC
escapes instead of doing a select just to retrieve the result of a function.
- Oracle, previous to 9i, doesn't support SQL-92 syntax. This is really
annoying for outer joins. No way to have a single query that works on
both, except maybe a view. That's a major reason why my code that
handles libraries of SQL statements supports having different SQL for
different databases.
Scott
Barry Lind <blind@xythos.com> writes:
> >>The the ; is not required for the rest of the jdbc driver. In fact in
> >>other areas of the code (like server prepared statements, batch updates)
> >>the requirement is that the user supplied sql statement does *not* end
> >>in a ;.
> >>
> >>This is also consistent with other jdbc drivers. In fact oracle gives
> >>you an error if a sql statement ends with a ;.
> >
> >
> > Apologies to everyone paying attention to this thread... in my
> > experience postgres has always complained when I haven't supplied the
> > ";".
Here's the patch again. This time it includes fixes for jdbc3 (that I
haven't had a chance to test) and a fix for the ";" issue. The
handling of ";" is an attempt at intelligent semi-colon handling but
it's late and I'm tired /8->
PGResult still isn't included in this. I can't seem to get CVS to
listen to my instruction to put new files into the diff.
Nic
cvs server: Diffing .
cvs server: Diffing org
cvs server: Diffing org/postgresql
Index: org/postgresql/PGConnection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/PGConnection.java,v
retrieving revision 1.3
diff -r1.3 PGConnection.java
79a80,89
>
>
> // Added by Nic.
>
> /** Create a result set for the specified statement.
> * This is the factory method provided by the various
> * version specific implementations of the JDBC connection
> * classes.
> */
> public java.sql.ResultSet createResultSet (java.sql.Statement statement);
cvs server: Diffing org/postgresql/core
Index: org/postgresql/core/QueryExecutor.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java,v
retrieving revision 1.16
diff -r1.16 QueryExecutor.java
20a21,69
> /** nic version - statically sets up the QE correctly.
> */
> public static void execute (String[] p_sqlFrags,
> Object[] p_binds,
> java.sql.Statement statement,
> java.sql.ResultSet rs,
> PG_Stream pg_stream,
> java.sql.Connection con)
> throws SQLException
> {
> QueryExecutor qe = new QueryExecutor();
> // I think this should be a PGConnection and we should move
> // everything we need for that from AbstractJdbc1... into PGConnection.
> qe.connection = (org.postgresql.jdbc1.AbstractJdbc1Connection)con;
> qe.m_sqlFrags = p_sqlFrags;
> qe.m_binds = p_binds;
> qe.statement = statement;
> // Nic says: connection should wrap pg_stream.
> qe.pg_stream = pg_stream;
> if (statement != null)
> qe.maxRows = statement.getMaxRows();
> else
> qe.maxRows = 0;
> // The result set.
> qe.rs = rs;
> qe.execute();
> }
>
> // This is the result set used to wrap the results.
> // The type of this is whatever is passed into the static above.
> private java.sql.ResultSet rs;
>
> // cons for the static above.
> private QueryExecutor ()
> {
> }
>
>
>
>
> /*** pre-nic implementation ***/
>
>
> /// Nic has removed the final from ALL of these (to facilitate static method).
> private String[] m_sqlFrags;
> private Object[] m_binds;
> private java.sql.Statement statement;
> private PG_Stream pg_stream;
> private org.postgresql.jdbc1.AbstractJdbc1Connection connection;
22,26d70
< private final String[] m_sqlFrags;
< private final Object[] m_binds;
< private final java.sql.Statement statement;
< private final PG_Stream pg_stream;
< private final org.postgresql.jdbc1.AbstractJdbc1Connection connection;
36c80
< this.statement = statement;
---
> this.statement = statement;
45a90
>
53a99
>
55a102
> *
128c175,180
< return connection.getResultSet(statement, fields, tuples, status, update_count, insert_oid,
binaryCursor);
---
>
> // Nic changes.
> PGResultSet resSet = ((PGResultSet)rs);
> // System.out.println(getClass().getName() + " resSet=" + resSet);
> resSet.init(fields, tuples, status, update_count, insert_oid, binaryCursor);
> return rs;
cvs server: Diffing org/postgresql/fastpath
cvs server: Diffing org/postgresql/geometric
cvs server: Diffing org/postgresql/jdbc1
Index: org/postgresql/jdbc1/AbstractJdbc1Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java,v
retrieving revision 1.12
diff -r1.12 AbstractJdbc1Connection.java
353,354d352
< java.sql.ResultSet resultSet =
< ExecSQL("set datestyle to 'ISO'; select version(), " + encodingQuery + ";");
355a354,356
> java.sql.ResultSet resultSet
> = doQuery("set datestyle to 'ISO'; select version(), " + encodingQuery + ";");
>
376c377
< ExecSQL("set client_encoding = 'UNICODE'; show autocommit");
---
> doQuery("set client_encoding = 'UNICODE'; show autocommit");
391c392
< ExecSQL("set autocommit = on; commit;");
---
> doQuery("set autocommit = on; commit;");
412,418d412
< // These methods used to be in the main Connection implementation. As they
< // are common to all implementations (JDBC1 or 2), they are placed here.
< // This should make it easy to maintain the two specifications.
<
< public abstract java.sql.ResultSet getResultSet(Statement statement, org.postgresql.Field[] fields, Vector
tuples,String status, int updateCount, long insertOID, boolean binaryCursor) throws SQLException;
<
< public abstract java.sql.ResultSet getResultSet(Statement statement, org.postgresql.Field[] fields, Vector
tuples,String status, int updateCount) throws SQLException;
448,457c442
< /*
< * Send a query to the backend. Returns one of the ResultSet
< * objects.
< *
< * <B>Note:</B> there does not seem to be any method currently
< * in existance to return the update count.
< *
< * @param sql the SQL statement to be executed
< * @return a ResultSet holding the results
< * @exception SQLException if a database error occurs
---
> /** Simple query execution.
459c444
< public java.sql.ResultSet ExecSQL(String sql) throws SQLException
---
> public java.sql.ResultSet doQuery (String s) throws SQLException
461c446,450
< return ExecSQL(sql, null);
---
> final Object[] nullarr = new Object[0];
> java.sql.Statement stat = createStatement();
> java.sql.ResultSet rs = createResultSet(stat);
> execSQL(new String[] { s }, nullarr, stat, rs);
> return rs;
464,487c453
< /*
< * Send a query to the backend. Returns one of the ResultSet
< * objects.
< *
< * <B>Note:</B> there does not seem to be any method currently
< * in existance to return the update count.
< *
< * @param sql the SQL statement to be executed
< * @param stat The Statement associated with this query (may be null)
< * @return a ResultSet holding the results
< * @exception SQLException if a database error occurs
< */
< public java.sql.ResultSet ExecSQL(String sql, java.sql.Statement stat) throws SQLException
< {
< return new QueryExecutor(new String[] {sql}, EMPTY_OBJECT_ARRAY, stat, pg_stream,
(java.sql.Connection)this).execute();
< }
< private static final Object[] EMPTY_OBJECT_ARRAY = new Object[0];
<
< /*
< * Send a query to the backend. Returns one of the ResultSet
< * objects.
< *
< * <B>Note:</B> there does not seem to be any method currently
< * in existance to return the update count.
---
> /** Advanced query execution.
491,492c457,458
< * @param stat The Statement associated with this query (may be null)
< * @return a ResultSet holding the results
---
> * @param stat the statement associated with this query.
> * @param rs the ResultSet which will be initied for the results.
495,497c461,472
< public java.sql.ResultSet ExecSQL(String[] p_sqlFragments, Object[] p_binds, java.sql.Statement stat) throws
SQLException
< {
< return new QueryExecutor(p_sqlFragments, p_binds, stat, pg_stream, (java.sql.Connection)this).execute();
---
> public void execSQL(String[] p_sqlFragments,
> Object[] p_binds,
> java.sql.Statement stat,
> java.sql.ResultSet rs)
> throws SQLException
> {
> QueryExecutor.execute(p_sqlFragments,
> p_binds,
> stat,
> rs,
> pg_stream,
> (java.sql.Connection)this);
499a475
>
934c910
< ExecSQL("select 1; commit; set autocommit = on;");
---
> doQuery("select 1; commit; set autocommit = on;");
938c914
< ExecSQL("end");
---
> doQuery("end");
945c921
< ExecSQL("set autocommit = off; " + getIsolationLevelSQL());
---
> doQuery("set autocommit = off; " + getIsolationLevelSQL());
949c925
< ExecSQL("begin;" + getIsolationLevelSQL());
---
> doQuery("begin;" + getIsolationLevelSQL());
953,954c929,930
< ExecSQL("begin");
< ExecSQL(getIsolationLevelSQL());
---
> doQuery("begin");
> doQuery(getIsolationLevelSQL());
988c964
< ExecSQL("commit; " + getIsolationLevelSQL());
---
> doQuery("commit; " + getIsolationLevelSQL());
992c968
< ExecSQL("commit;begin;" + getIsolationLevelSQL());
---
> doQuery("commit;begin;" + getIsolationLevelSQL());
996,998c972,974
< ExecSQL("commit");
< ExecSQL("begin");
< ExecSQL(getIsolationLevelSQL());
---
> doQuery("commit");
> doQuery("begin");
> doQuery(getIsolationLevelSQL());
1019c995
< ExecSQL("rollback; " + getIsolationLevelSQL());
---
> doQuery("rollback; " + getIsolationLevelSQL());
1023c999
< ExecSQL("rollback; begin;" + getIsolationLevelSQL());
---
> doQuery("rollback; begin;" + getIsolationLevelSQL());
1027,1029c1003,1005
< ExecSQL("rollback");
< ExecSQL("begin");
< ExecSQL(getIsolationLevelSQL());
---
> doQuery("rollback");
> doQuery("begin");
> doQuery(getIsolationLevelSQL());
1044c1020
< ResultSet rs = ExecSQL(sql);
---
> ResultSet rs = doQuery(sql);
1051c1027
< ExecSQL(sql);
---
> doQuery(sql);
1116c1092
< ExecSQL(isolationLevelSQL);
---
> doQuery(isolationLevelSQL);
1259c1235
< ResultSet result = ExecSQL(sql);
---
> ResultSet result = doQuery(sql);
1300c1276
< ResultSet result = ExecSQL(sql);
---
> ResultSet result = doQuery(sql);
Index: org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java,v
retrieving revision 1.10
diff -r1.10 AbstractJdbc1DatabaseMetaData.java
1916c1916,1918
< return connection.getResultSet(null, f, v, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2202c2204,2207
< return connection.getResultSet(null, f, v, "OK", 1);
---
>
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2369c2374,2376
< return connection.getResultSet(null, f, v, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2481c2488,2491
< return connection.getResultSet(null, f, v, "OK", 1);
---
>
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2583c2593,2595
< return connection.getResultSet(null, f, v, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2776c2788,2791
< return connection.getResultSet(null, f, v, "OK", 1);
---
>
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2846c2861,2863
< return connection.getResultSet(null, f, v, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
3196c3213,3215
< return connection.getResultSet(null, f, tuples, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, tuples, "OK", 1, 0, false);
> return retRs;
3481c3500,3503
< return connection.getResultSet(null, f, v, "OK", 1);
---
>
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
Index: org/postgresql/jdbc1/AbstractJdbc1ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java,v
retrieving revision 1.7
diff -r1.7 AbstractJdbc1ResultSet.java
21c21
< public abstract class AbstractJdbc1ResultSet
---
> public abstract class AbstractJdbc1ResultSet implements org.postgresql.PGResultSet
45c45,51
< public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn, Statement statement, Field[] fields, Vector
tuples,String status, int updateCount, long insertOID, boolean binaryCursor)
---
> public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn,
> Statement statement,
> Field[] fields,
> Vector tuples,
> String status,
> int updateCount,
> long insertOID, boolean binaryCursor)
52a59,61
>
> System.out.println(getClass().getName() + " updateCount = " + updateCount);
>
59a69,97
> /*** nic constructor: called by superclass which is called from Jdbc1Connection.createResultSet ***/
> public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn,
> Statement statement)
> {
> this.connection = conn;
> this.statement = statement;
> }
>
> /*** nic initializer. ***/
> public void init (Field[] fields, Vector tuples, String status,
> int updateCount, long insertOID, boolean binaryCursor)
> {
> this.fields = fields;
>
> // on a reinit the size of this indicates how many we pulled
> // back. If it's 0 then the res set has ended.
> this.rows = tuples;
> this.status = status;
> this.updateCount = updateCount;
> this.insertOID = insertOID;
> this.this_row = null;
> this.current_row = -1;
> this.binaryCursor = binaryCursor;
> }
>
>
>
>
> // This slightly altered by nic.
66c104,130
< return false;
---
> {
> // Use the ref to the statement to get
> // the details we need to do another cursor
> // query - it will use reinit() to repopulate this
> // with the right data.
> String[] sql = new String[1];
> String[] binds = new String[0];
> // Is this the correct query???
> int fetchSize = ((AbstractJdbc1Statement)statement).nic_fetchSize;
> String cursorName = ((AbstractJdbc1Statement)statement).nic_statementName;
> sql[0] = "FETCH FORWARD " + fetchSize + " FROM " + cursorName + " ;";
>
> // We can safely throw the return value away, it reinits "this".
>
> ((AbstractJdbc1Connection)connection).execSQL(sql, binds, statement, (java.sql.ResultSet)this);
>
> // Test the new rows array.
> if (rows.size() == 0)
> {
> System.out.println("rows == 0 ending.");
> return false;
> }
>
> System.out.println("rows != 0 not ending");
> // Otherwise reset the counter and let it go on...
> current_row = 0;
> }
Index: org/postgresql/jdbc1/AbstractJdbc1Statement.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java,v
retrieving revision 1.12
diff -r1.12 AbstractJdbc1Statement.java
120c120
<
---
>
136c136
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
153c153
< this.execute();
---
> this.nic_execute();
178c178
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
222c222
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
320c320,322
< result = ((AbstractJdbc1Connection)connection).ExecSQL(m_sqlFragments, m_binds, (java.sql.Statement)this);
---
> result = ((AbstractJdbc1Connection)connection).createResultSet((java.sql.Statement)this);
> ((AbstractJdbc1Connection)connection).execSQL(m_sqlFragments, m_binds,
> (java.sql.Statement)this, result);
343a346,458
>
>
> // For now used to store the statement name.
> String nic_statementName = "";
>
> // For now stores the fetch size. There's no way of altering this with JDBC1.
> protected int nic_fetchSize = 0;
>
> /** version of execute which converts the query to a cursor.
> */
> public boolean nic_execute() throws SQLException
> {
> if (isFunction && !returnTypeSet)
> throw new PSQLException("postgresql.call.noreturntype");
> if (isFunction)
> { // set entry 1 to dummy entry..
> m_binds[0] = ""; // dummy entry which ensured that no one overrode
> m_bindTypes[0] = PG_TEXT;
> // and calls to setXXX (2,..) really went to first arg in a function call..
> }
>
> // New in 7.1, if we have a previous resultset then force it to close
> // This brings us nearer to compliance, and helps memory management.
> // Internal stuff will call ExecSQL directly, bypassing this.
> if (result != null)
> {
> java.sql.ResultSet rs = getResultSet();
> if (rs != null)
> rs.close();
> }
>
> // I've pretty much ignored server prepared statements... can declare and prepare be
> // used together?
> // It's trivial to change this: you just have to resolve this issue
> // of how to work out whether there's a function call. If there isn't then the first
> // element of the array must be the bit that you extend to become the cursor
> // decleration.
> // The last thing that can go wrong is when the user supplies a cursor statement
> // directly: the translation takes no account of that. I think we should just look
> // for declare and stop the translation if we find it.
>
> // The first thing to do is transform the statement text into the cursor form.
> String[] origSqlFragments = m_sqlFragments;
> if (origSqlFragments.length > 1)
> m_sqlFragments = new String[origSqlFragments.length + 1];
> else
> m_sqlFragments = new String[origSqlFragments.length];
> System.arraycopy(origSqlFragments, 0, m_sqlFragments, 0, origSqlFragments.length);
> // Pinch the prepared count for our own nefarious purposes.
> m_statementName = "JDBC_CURS_" + m_preparedCount++;
> nic_statementName = m_statementName;
> // The static bit to prepend to all querys.
> String cursDecl = "BEGIN; DECLARE " + m_statementName + " CURSOR FOR ";
> String endCurs = " FETCH FORWARD " + nic_fetchSize + " FROM " + m_statementName + ";";
>
> // Add the real query to the curs decleration.
> // This is the bit that really makes the presumption about
> // m_sqlFragments not being a function call.
> if (m_sqlFragments.length < 1)
> m_sqlFragments[0] = cursDecl + "SELECT NULL;";
>
> else if (m_sqlFragments.length < 2)
> {
> if (m_sqlFragments[0].endsWith(";"))
> m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + endCurs;
> else
> m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + ";" + endCurs;
> }
> else
> {
> m_sqlFragments[0] = cursDecl + m_sqlFragments[0];
> if (m_sqlFragments[m_sqlFragments.length - 2].endsWith(";"))
> m_sqlFragments[m_sqlFragments.length - 1] = endCurs;
> else
> m_sqlFragments[m_sqlFragments.length - 1] = ";" + endCurs;
> }
>
> // Make the call to the query executor.
> AbstractJdbc1Connection execr = (AbstractJdbc1Connection)connection;
> java.sql.Statement st = (java.sql.Statement)this;
> result = (java.sql.ResultSet) execr.createResultSet(st);
> // Nic says:
> // we don't need to collect the result here, rs is altered to
> // be the result set so "result = rs" would do ok after this call.
> execr.execSQL(m_sqlFragments, m_binds, st, result);
>
> //If we are executing a callable statement function set the return data
> if (isFunction)
> {
> if (!((AbstractJdbc1ResultSet)result).reallyResultSet())
> throw new PSQLException("postgresql.call.noreturnval");
> if (!result.next ())
> throw new PSQLException ("postgresql.call.noreturnval");
> callResult = result.getObject(1);
> int columnType = result.getMetaData().getColumnType(1);
> if (columnType != functionReturnType)
> {
> Object[] arr =
> { "java.sql.Types=" + columnType,
> "java.sql.Types=" + functionReturnType
> };
> throw new PSQLException ("postgresql.call.wrongrtntype",arr);
> }
> result.close ();
> return true;
> }
> else
> {
> return (result != null && ((AbstractJdbc1ResultSet)result).reallyResultSet());
> }
> }
>
>
596c711
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
1839c1954
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
Index: org/postgresql/jdbc1/Jdbc1Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Connection.java,v
retrieving revision 1.5
diff -r1.5 Jdbc1Connection.java
48a49,54
>
> public java.sql.ResultSet createResultSet (java.sql.Statement stat) throws SQLException
> {
> // This needs doing.
> return null;
> }
cvs server: Diffing org/postgresql/jdbc2
Index: org/postgresql/jdbc2/AbstractJdbc2Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java,v
retrieving revision 1.2
diff -r1.2 AbstractJdbc2Connection.java
8a9
>
20c21
<
---
>
Index: org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java,v
retrieving revision 1.10
diff -r1.10 AbstractJdbc2ResultSet.java
40a41,45
>
> AbstractJdbc2ResultSet (org.postgresql.PGConnection conn, Statement statement)
> {
> super(conn, statement);
> }
144a150,157
> else if (type.equals("refcursor"))
> {
> // We must return a ResultSet with the results packaged.
> // We should probably check that auto commit is turned off.
> String cursorName = getString(columnIndex);
> // return new RefCursorResultSet(cursorName);
> return null;
> }
757d769
<
790d801
<
Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v
retrieving revision 1.8
diff -r1.8 AbstractJdbc2Statement.java
133c133,135
< throw org.postgresql.Driver.notImplemented();
---
> // I don't think this should happen, since it's a hint it should just
> // fail quietly.
> // throw org.postgresql.Driver.notImplemented();
138c140,144
< throw org.postgresql.Driver.notImplemented();
---
> // I don't think this should happen, since it's a hint it should just
> // fail quietly.
> // throw org.postgresql.Driver.notImplemented();
>
> super.nic_fetchSize = rows;
Index: org/postgresql/jdbc2/Array.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Array.java,v
retrieving revision 1.18
diff -r1.18 Array.java
346c346,349
< return ((AbstractJdbc2Connection)conn).getResultSet(null, fields, rows, "OK", 1 );
---
> java.sql.Statement stat = ((AbstractJdbc2Connection)conn).createStatement();
> java.sql.ResultSet retRs = ((AbstractJdbc2Connection)conn).createResultSet(stat);
> ((AbstractJdbc2ResultSet)retRs).init(fields, rows, "OK", 1, 0, false);
> return retRs;
Index: org/postgresql/jdbc2/Jdbc2Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Connection.java,v
retrieving revision 1.5
diff -r1.5 Jdbc2Connection.java
48a49,80
>
>
> // the new method override which re-inits existing resultsets
> public java.sql.ResultSet getResultSet(java.sql.Statement stat,
> org.postgresql.jdbc1.AbstractJdbc1ResultSet rs,
> Field[] fields,
> Vector tuples,
> String status,
> int updateCount, long insertOID, boolean binaryCursor)
> throws SQLException
> {
> if (rs == null)
> return new Jdbc2ResultSet(this, stat, fields,
> tuples, status,
> updateCount, insertOID, binaryCursor);
> else
> {
> rs.init(fields, tuples, status, updateCount, insertOID, binaryCursor);
> return (java.sql.ResultSet) rs;
> }
> }
>
>
>
>
> /** new nic method **/
> public java.sql.ResultSet createResultSet (Statement statement)
> {
> return new Jdbc2ResultSet(this, statement);
> }
>
>
Index: org/postgresql/jdbc2/Jdbc2ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java,v
retrieving revision 1.6
diff -r1.6 Jdbc2ResultSet.java
15a16,21
> Jdbc2ResultSet (org.postgresql.PGConnection conn, Statement statement)
> {
> super (conn, statement);
> }
>
>
cvs server: Diffing org/postgresql/jdbc2/optional
cvs server: Diffing org/postgresql/jdbc3
Index: org/postgresql/jdbc3/AbstractJdbc3ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/AbstractJdbc3ResultSet.java,v
retrieving revision 1.2
diff -r1.2 AbstractJdbc3ResultSet.java
14a15,20
> AbstractJdbc3ResultSet (org.postgresql.PGConnection conn, Statement statement)
> {
> super(conn, statement);
> }
>
>
Index: org/postgresql/jdbc3/Jdbc3Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Connection.java,v
retrieving revision 1.2
diff -r1.2 Jdbc3Connection.java
49,52d48
< public java.sql.ResultSet getResultSet(Statement statement, Field[] fields, Vector tuples, String status, int
updateCount,long insertOID, boolean binaryCursor) throws SQLException
< {
< return new Jdbc3ResultSet(this, statement, fields, tuples, status, updateCount, insertOID, binaryCursor);
< }
54c50,56
< public java.sql.ResultSet getResultSet(Statement statement, Field[] fields, Vector tuples, String status, int
updateCount)throws SQLException
---
>
>
>
>
>
> /** new nic method **/
> public java.sql.ResultSet createResultSet (Statement statement)
56c58
< return new Jdbc3ResultSet(this, statement, fields, tuples, status, updateCount, 0, false);
---
> return new Jdbc3ResultSet(this, statement);
Index: org/postgresql/jdbc3/Jdbc3ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3ResultSet.java,v
retrieving revision 1.3
diff -r1.3 Jdbc3ResultSet.java
15a16,22
> Jdbc3ResultSet (org.postgresql.PGConnection conn, Statement statement)
> {
> super (conn, statement);
> }
>
>
>
cvs server: Diffing org/postgresql/largeobject
cvs server: Diffing org/postgresql/test
cvs server: Diffing org/postgresql/test/jdbc2
cvs server: Diffing org/postgresql/test/jdbc2/optional
cvs server: Diffing org/postgresql/test/jdbc3
cvs server: Diffing org/postgresql/test/util
cvs server: Diffing org/postgresql/util
Index: org/postgresql/util/Serialize.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/util/Serialize.java,v
retrieving revision 1.14
diff -r1.14 Serialize.java
408c408
< ResultSet rs = ((org.postgresql.jdbc1.AbstractJdbc1Connection)conn).ExecSQL(sb.toString());
---
> ResultSet rs = ((org.postgresql.jdbc1.AbstractJdbc1Connection)conn).doQuery(sb.toString());
On Thursday 14 November 2002 07:31 pm, Nic Ferrier wrote:
> Barry Lind <blind@xythos.com> writes:
> > nferrier@tapsellferrier.co.uk wrote:
> > > snpe <snpe@snpe.co.yu> writes:
> > >
> > > Yes. But the reason I send:
> > >
> > > DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100 FROM
> > > JDBC_CURS_1;
> > >
> > > is because the SQL statement you supply is _supposed_ to end with a
> > > ";". The code for the above is actually doing:
> > >
> > > DECLARE JDBC_CURS_1 CURSOR FOR $userquery FETCH FORWARD 100 FROM
> > > JDBC_CURS_1;
> > >
> > > where $userquery is what comes in from the client code, eg:
> > >
> > > Statement st = con.createStatement();
> > > ResultSet rs = con.executeQuery("select * from tab;");
> > >
> > > then "select * from tab;" is inserted as $userquery. It must always
> > > end with ";" because that's how PGSQL's normal query processing works.
> >
> > The the ; is not required for the rest of the jdbc driver. In fact in
> > other areas of the code (like server prepared statements, batch updates)
> > the requirement is that the user supplied sql statement does *not* end
> > in a ;.
> >
> > This is also consistent with other jdbc drivers. In fact oracle gives
> > you an error if a sql statement ends with a ;.
>
> Apologies to everyone paying attention to this thread... in my
> experience postgres has always complained when I haven't supplied the
> ";".
>
> I will investigate and fix the patch.
>
I work with patch and I have any problem yet
1) ResultSet.setFetchSize, Statement.getFetchSize and ResultSet.getFetchSize
don't work
2) any methods in DataBaseMetaData don't work
example, getBestRowIdentifier
getBestRowIdentifier create ResultSet without execSQL (direct) and
when we call ResultSet.next, after last row driverreturn error
(driver send command 'FETCH FORWARD 0 FROM ;'
3) if I have two or more query pgsql return warning 'BEGIN : already a
transaction in progress'
4) This is greatest problem :
If I create query and then want transaction without close query, I break
query.
example :
a) create statement for 'select ..' and fetch any rows
b) do any update,delete or update and commit or rollback
query is lost
I think that problem 4) can be make only with cursor outre of transaction
regards
Haris Peco
Thanks for testing all this Haris.
snpe <snpe@snpe.co.yu> writes:
> I work with patch and I have any problem yet
>
> 1) ResultSet.setFetchSize, Statement.getFetchSize and ResultSet.getFetchSize
> don't work
I've updated my patch to cover these methods. Note that
ResultSet.getFetchSize() should have worked for you. I haven't altered
it, right now the method returns the number of rows in the current
"batch" which seems like the right thing to do.
> 2) any methods in DataBaseMetaData don't work
> example, getBestRowIdentifier
> getBestRowIdentifier create ResultSet without execSQL (direct) and
> when we call ResultSet.next, after last row driverreturn error
> (driver send command 'FETCH FORWARD 0 FROM ;'
Ok... looks like the initing of the ResultSet isn't quite right
there. I'll take a look at that tonight.
> 3) if I have two or more query pgsql return warning 'BEGIN : already a
> transaction in progress'
the next one.
> 4) This is greatest problem :
> If I create query and then want transaction without close query, I break
> query. example :
>
> a) create statement for 'select ..' and fetch any rows
>
> b) do any update,delete or update and commit or rollback
>
> query is lost
> I think that problem 4) can be make only with cursor outre of
> transaction.
The problem is that my code needs to wrap the query in a transaction
so that the cursor can be used.
But when that's done other statements fail...
How do people handle this with their "manual" cursor based hacks? Is
the best thing to wrap _everything_ in an independant transaction, eg:
including inserts and updates? and do commits automatically but via
the java code. This will lower performance of course.
It's a tricky one.
Nic
Here's the latest patch (fixes the get/setFetchSize problems).
Index: org/postgresql/PGConnection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/PGConnection.java,v
retrieving revision 1.3
diff -r1.3 PGConnection.java
79a80,89
>
>
> // Added by Nic.
>
> /** Create a result set for the specified statement.
> * This is the factory method provided by the various
> * version specific implementations of the JDBC connection
> * classes.
> */
> public java.sql.ResultSet createResultSet (java.sql.Statement statement);
Index: org/postgresql/core/QueryExecutor.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java,v
retrieving revision 1.16.2.1
diff -r1.16.2.1 QueryExecutor.java
20a21,69
> /** nic version - statically sets up the QE correctly.
> */
> public static void execute (String[] p_sqlFrags,
> Object[] p_binds,
> java.sql.Statement statement,
> java.sql.ResultSet rs,
> PG_Stream pg_stream,
> java.sql.Connection con)
> throws SQLException
> {
> QueryExecutor qe = new QueryExecutor();
> // I think this should be a PGConnection and we should move
> // everything we need for that from AbstractJdbc1... into PGConnection.
> qe.connection = (org.postgresql.jdbc1.AbstractJdbc1Connection)con;
> qe.m_sqlFrags = p_sqlFrags;
> qe.m_binds = p_binds;
> qe.statement = statement;
> // Nic says: connection should wrap pg_stream.
> qe.pg_stream = pg_stream;
> if (statement != null)
> qe.maxRows = statement.getMaxRows();
> else
> qe.maxRows = 0;
> // The result set.
> qe.rs = rs;
> qe.execute();
> }
>
> // This is the result set used to wrap the results.
> // The type of this is whatever is passed into the static above.
> private java.sql.ResultSet rs;
>
> // cons for the static above.
> private QueryExecutor ()
> {
> }
>
>
>
>
> /*** pre-nic implementation ***/
>
>
> /// Nic has removed the final from ALL of these (to facilitate static method).
> private String[] m_sqlFrags;
> private Object[] m_binds;
> private java.sql.Statement statement;
> private PG_Stream pg_stream;
> private org.postgresql.jdbc1.AbstractJdbc1Connection connection;
22,26d70
< private final String[] m_sqlFrags;
< private final Object[] m_binds;
< private final java.sql.Statement statement;
< private final PG_Stream pg_stream;
< private final org.postgresql.jdbc1.AbstractJdbc1Connection connection;
36c80
< this.statement = statement;
---
> this.statement = statement;
45a90
>
53a99
>
55a102
> *
133c180,185
< return connection.getResultSet(statement, fields, tuples, status, update_count, insert_oid,
binaryCursor);
---
>
> // Nic changes.
> PGResultSet resSet = ((PGResultSet)rs);
> // System.out.println(getClass().getName() + " resSet=" + resSet);
> resSet.init(fields, tuples, status, update_count, insert_oid, binaryCursor);
> return rs;
Index: org/postgresql/jdbc1/AbstractJdbc1Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java,v
retrieving revision 1.12.2.1
diff -r1.12.2.1 AbstractJdbc1Connection.java
353,354d352
< java.sql.ResultSet resultSet =
< ExecSQL("set datestyle to 'ISO'; select version(), " + encodingQuery + ";");
355a354,356
> java.sql.ResultSet resultSet
> = doQuery("set datestyle to 'ISO'; select version(), " + encodingQuery + ";");
>
376c377
< ExecSQL("set client_encoding = 'UNICODE'; show autocommit");
---
> doQuery("set client_encoding = 'UNICODE'; show autocommit");
391c392
< ExecSQL("set autocommit = on; commit;");
---
> doQuery("set autocommit = on; commit;");
412,418d412
< // These methods used to be in the main Connection implementation. As they
< // are common to all implementations (JDBC1 or 2), they are placed here.
< // This should make it easy to maintain the two specifications.
<
< public abstract java.sql.ResultSet getResultSet(Statement statement, org.postgresql.Field[] fields, Vector
tuples,String status, int updateCount, long insertOID, boolean binaryCursor) throws SQLException;
<
< public abstract java.sql.ResultSet getResultSet(Statement statement, org.postgresql.Field[] fields, Vector
tuples,String status, int updateCount) throws SQLException;
448,457c442
< /*
< * Send a query to the backend. Returns one of the ResultSet
< * objects.
< *
< * <B>Note:</B> there does not seem to be any method currently
< * in existance to return the update count.
< *
< * @param sql the SQL statement to be executed
< * @return a ResultSet holding the results
< * @exception SQLException if a database error occurs
---
> /** Simple query execution.
459c444
< public java.sql.ResultSet ExecSQL(String sql) throws SQLException
---
> public java.sql.ResultSet doQuery (String s) throws SQLException
461c446,450
< return ExecSQL(sql, null);
---
> final Object[] nullarr = new Object[0];
> java.sql.Statement stat = createStatement();
> java.sql.ResultSet rs = createResultSet(stat);
> execSQL(new String[] { s }, nullarr, stat, rs);
> return rs;
464,491c453
< /*
< * Send a query to the backend. Returns one of the ResultSet
< * objects.
< *
< * <B>Note:</B> there does not seem to be any method currently
< * in existance to return the update count.
< *
< * @param sql the SQL statement to be executed
< * @param stat The Statement associated with this query (may be null)
< * @return a ResultSet holding the results
< * @exception SQLException if a database error occurs
< */
< public java.sql.ResultSet ExecSQL(String sql, java.sql.Statement stat) throws SQLException
< {
< if (isClosed())
< {
< throw new PSQLException("postgresql.con.closed");
< }
< return new QueryExecutor(new String[] {sql}, EMPTY_OBJECT_ARRAY, stat, pg_stream,
(java.sql.Connection)this).execute();
< }
< private static final Object[] EMPTY_OBJECT_ARRAY = new Object[0];
<
< /*
< * Send a query to the backend. Returns one of the ResultSet
< * objects.
< *
< * <B>Note:</B> there does not seem to be any method currently
< * in existance to return the update count.
---
> /** Advanced query execution.
495,496c457,458
< * @param stat The Statement associated with this query (may be null)
< * @return a ResultSet holding the results
---
> * @param stat the statement associated with this query.
> * @param rs the ResultSet which will be initied for the results.
499,505c461,472
< public java.sql.ResultSet ExecSQL(String[] p_sqlFragments, Object[] p_binds, java.sql.Statement stat) throws
SQLException
< {
< if (isClosed())
< {
< throw new PSQLException("postgresql.con.closed");
< }
< return new QueryExecutor(p_sqlFragments, p_binds, stat, pg_stream, (java.sql.Connection)this).execute();
---
> public void execSQL(String[] p_sqlFragments,
> Object[] p_binds,
> java.sql.Statement stat,
> java.sql.ResultSet rs)
> throws SQLException
> {
> QueryExecutor.execute(p_sqlFragments,
> p_binds,
> stat,
> rs,
> pg_stream,
> (java.sql.Connection)this);
507a475
>
942c910
< ExecSQL("select 1; commit; set autocommit = on;");
---
> doQuery("select 1; commit; set autocommit = on;");
946c914
< ExecSQL("end");
---
> doQuery("end");
953c921
< ExecSQL("set autocommit = off; " + getIsolationLevelSQL());
---
> doQuery("set autocommit = off; " + getIsolationLevelSQL());
957c925
< ExecSQL("begin;" + getIsolationLevelSQL());
---
> doQuery("begin;" + getIsolationLevelSQL());
961,962c929,930
< ExecSQL("begin");
< ExecSQL(getIsolationLevelSQL());
---
> doQuery("begin");
> doQuery(getIsolationLevelSQL());
996c964
< ExecSQL("commit; " + getIsolationLevelSQL());
---
> doQuery("commit; " + getIsolationLevelSQL());
1000c968
< ExecSQL("commit;begin;" + getIsolationLevelSQL());
---
> doQuery("commit;begin;" + getIsolationLevelSQL());
1004,1006c972,974
< ExecSQL("commit");
< ExecSQL("begin");
< ExecSQL(getIsolationLevelSQL());
---
> doQuery("commit");
> doQuery("begin");
> doQuery(getIsolationLevelSQL());
1027c995
< ExecSQL("rollback; " + getIsolationLevelSQL());
---
> doQuery("rollback; " + getIsolationLevelSQL());
1031c999
< ExecSQL("rollback; begin;" + getIsolationLevelSQL());
---
> doQuery("rollback; begin;" + getIsolationLevelSQL());
1035,1037c1003,1005
< ExecSQL("rollback");
< ExecSQL("begin");
< ExecSQL(getIsolationLevelSQL());
---
> doQuery("rollback");
> doQuery("begin");
> doQuery(getIsolationLevelSQL());
1052c1020
< ResultSet rs = ExecSQL(sql);
---
> ResultSet rs = doQuery(sql);
1059c1027
< ExecSQL(sql);
---
> doQuery(sql);
1124c1092
< ExecSQL(isolationLevelSQL);
---
> doQuery(isolationLevelSQL);
1267c1235
< ResultSet result = ExecSQL(sql);
---
> ResultSet result = doQuery(sql);
1308c1276
< ResultSet result = ExecSQL(sql);
---
> ResultSet result = doQuery(sql);
Index: org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java,v
retrieving revision 1.10.2.1
diff -r1.10.2.1 AbstractJdbc1DatabaseMetaData.java
1916c1916,1918
< return connection.getResultSet(null, f, v, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2202c2204,2207
< return connection.getResultSet(null, f, v, "OK", 1);
---
>
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2369c2374,2376
< return connection.getResultSet(null, f, v, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2481c2488,2491
< return connection.getResultSet(null, f, v, "OK", 1);
---
>
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2583c2593,2595
< return connection.getResultSet(null, f, v, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2776c2788,2791
< return connection.getResultSet(null, f, v, "OK", 1);
---
>
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2846c2861,2863
< return connection.getResultSet(null, f, v, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
3196c3213,3215
< return connection.getResultSet(null, f, tuples, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, tuples, "OK", 1, 0, false);
> return retRs;
3481c3500,3503
< return connection.getResultSet(null, f, v, "OK", 1);
---
>
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
Index: org/postgresql/jdbc1/AbstractJdbc1ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java,v
retrieving revision 1.7
diff -r1.7 AbstractJdbc1ResultSet.java
21c21
< public abstract class AbstractJdbc1ResultSet
---
> public abstract class AbstractJdbc1ResultSet implements org.postgresql.PGResultSet
45c45,51
< public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn, Statement statement, Field[] fields, Vector
tuples,String status, int updateCount, long insertOID, boolean binaryCursor)
---
> public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn,
> Statement statement,
> Field[] fields,
> Vector tuples,
> String status,
> int updateCount,
> long insertOID, boolean binaryCursor)
52a59,61
>
> System.out.println(getClass().getName() + " updateCount = " + updateCount);
>
59a69,97
> /*** nic constructor: called by superclass which is called from Jdbc1Connection.createResultSet ***/
> public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn,
> Statement statement)
> {
> this.connection = conn;
> this.statement = statement;
> }
>
> /*** nic initializer. ***/
> public void init (Field[] fields, Vector tuples, String status,
> int updateCount, long insertOID, boolean binaryCursor)
> {
> this.fields = fields;
>
> // on a reinit the size of this indicates how many we pulled
> // back. If it's 0 then the res set has ended.
> this.rows = tuples;
> this.status = status;
> this.updateCount = updateCount;
> this.insertOID = insertOID;
> this.this_row = null;
> this.current_row = -1;
> this.binaryCursor = binaryCursor;
> }
>
>
>
>
> // This slightly altered by nic.
66c104,130
< return false;
---
> {
> // Use the ref to the statement to get
> // the details we need to do another cursor
> // query - it will use reinit() to repopulate this
> // with the right data.
> String[] sql = new String[1];
> String[] binds = new String[0];
> // Is this the correct query???
> int fetchSize = ((AbstractJdbc1Statement)statement).fetchSize;
> String cursorName = ((AbstractJdbc1Statement)statement).m_statementName;
> sql[0] = "FETCH FORWARD " + fetchSize + " FROM " + cursorName + " ;";
>
> // We can safely throw the return value away, it reinits "this".
>
> ((AbstractJdbc1Connection)connection).execSQL(sql, binds, statement, (java.sql.ResultSet)this);
>
> // Test the new rows array.
> if (rows.size() == 0)
> {
> System.out.println("rows == 0 ending.");
> return false;
> }
>
> System.out.println("rows != 0 not ending");
> // Otherwise reset the counter and let it go on...
> current_row = 0;
> }
Index: org/postgresql/jdbc1/AbstractJdbc1Statement.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java,v
retrieving revision 1.12.2.1
diff -r1.12.2.1 AbstractJdbc1Statement.java
27a28,30
> /** Number of rows to get in a batch. */
> protected int fetchSize = 0;
>
51c54
< private String m_statementName = null;
---
> protected String m_statementName = null;
120c123
<
---
>
136c139
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
153c156
< this.execute();
---
> this.nic_execute();
178c181
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
222c225
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
320c323,325
< result = ((AbstractJdbc1Connection)connection).ExecSQL(m_sqlFragments, m_binds, (java.sql.Statement)this);
---
> result = ((AbstractJdbc1Connection)connection).createResultSet((java.sql.Statement)this);
> ((AbstractJdbc1Connection)connection).execSQL(m_sqlFragments, m_binds,
> (java.sql.Statement)this, result);
343a349,452
> /** version of execute which converts the query to a cursor.
> */
> public boolean nic_execute() throws SQLException
> {
> if (isFunction && !returnTypeSet)
> throw new PSQLException("postgresql.call.noreturntype");
> if (isFunction)
> { // set entry 1 to dummy entry..
> m_binds[0] = ""; // dummy entry which ensured that no one overrode
> m_bindTypes[0] = PG_TEXT;
> // and calls to setXXX (2,..) really went to first arg in a function call..
> }
>
> // New in 7.1, if we have a previous resultset then force it to close
> // This brings us nearer to compliance, and helps memory management.
> // Internal stuff will call ExecSQL directly, bypassing this.
> if (result != null)
> {
> java.sql.ResultSet rs = getResultSet();
> if (rs != null)
> rs.close();
> }
>
> // I've pretty much ignored server prepared statements... can declare and prepare be
> // used together?
> // It's trivial to change this: you just have to resolve this issue
> // of how to work out whether there's a function call. If there isn't then the first
> // element of the array must be the bit that you extend to become the cursor
> // decleration.
> // The last thing that can go wrong is when the user supplies a cursor statement
> // directly: the translation takes no account of that. I think we should just look
> // for declare and stop the translation if we find it.
>
> // The first thing to do is transform the statement text into the cursor form.
> String[] origSqlFragments = m_sqlFragments;
> if (origSqlFragments.length > 1)
> m_sqlFragments = new String[origSqlFragments.length + 1];
> else
> m_sqlFragments = new String[origSqlFragments.length];
> System.arraycopy(origSqlFragments, 0, m_sqlFragments, 0, origSqlFragments.length);
> // Pinch the prepared count for our own nefarious purposes.
> m_statementName = "JDBC_CURS_" + m_preparedCount++;
> // The static bit to prepend to all querys.
> String cursDecl = "BEGIN; DECLARE " + m_statementName + " CURSOR FOR ";
> String endCurs = " FETCH FORWARD " + fetchSize + " FROM " + m_statementName + ";";
>
> // Add the real query to the curs decleration.
> // This is the bit that really makes the presumption about
> // m_sqlFragments not being a function call.
> if (m_sqlFragments.length < 1)
> m_sqlFragments[0] = cursDecl + "SELECT NULL;";
>
> else if (m_sqlFragments.length < 2)
> {
> if (m_sqlFragments[0].endsWith(";"))
> m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + endCurs;
> else
> m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + ";" + endCurs;
> }
> else
> {
> m_sqlFragments[0] = cursDecl + m_sqlFragments[0];
> if (m_sqlFragments[m_sqlFragments.length - 2].endsWith(";"))
> m_sqlFragments[m_sqlFragments.length - 1] = endCurs;
> else
> m_sqlFragments[m_sqlFragments.length - 1] = ";" + endCurs;
> }
>
> // Make the call to the query executor.
> AbstractJdbc1Connection execr = (AbstractJdbc1Connection)connection;
> java.sql.Statement st = (java.sql.Statement)this;
> result = (java.sql.ResultSet) execr.createResultSet(st);
> // Nic says:
> // we don't need to collect the result here, rs is altered to
> // be the result set so "result = rs" would do ok after this call.
> execr.execSQL(m_sqlFragments, m_binds, st, result);
>
> //If we are executing a callable statement function set the return data
> if (isFunction)
> {
> if (!((AbstractJdbc1ResultSet)result).reallyResultSet())
> throw new PSQLException("postgresql.call.noreturnval");
> if (!result.next ())
> throw new PSQLException ("postgresql.call.noreturnval");
> callResult = result.getObject(1);
> int columnType = result.getMetaData().getColumnType(1);
> if (columnType != functionReturnType)
> {
> Object[] arr =
> { "java.sql.Types=" + columnType,
> "java.sql.Types=" + functionReturnType
> };
> throw new PSQLException ("postgresql.call.wrongrtntype",arr);
> }
> result.close ();
> return true;
> }
> else
> {
> return (result != null && ((AbstractJdbc1ResultSet)result).reallyResultSet());
> }
> }
>
>
596c705
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
1915c2024
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
Index: org/postgresql/jdbc1/Jdbc1Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Connection.java,v
retrieving revision 1.5
diff -r1.5 Jdbc1Connection.java
48a49,54
>
> public java.sql.ResultSet createResultSet (java.sql.Statement stat) throws SQLException
> {
> // This needs doing.
> return null;
> }
Index: org/postgresql/jdbc2/AbstractJdbc2Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java,v
retrieving revision 1.2
diff -r1.2 AbstractJdbc2Connection.java
8a9
>
20c21
<
---
>
Index: org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java,v
retrieving revision 1.10
diff -r1.10 AbstractJdbc2ResultSet.java
40a41,45
>
> AbstractJdbc2ResultSet (org.postgresql.PGConnection conn, Statement statement)
> {
> super(conn, statement);
> }
144a150,157
> else if (type.equals("refcursor"))
> {
> // We must return a ResultSet with the results packaged.
> // We should probably check that auto commit is turned off.
> String cursorName = getString(columnIndex);
> // return new RefCursorResultSet(cursorName);
> return null;
> }
369,371c382
< // In this implementation we return the entire result set, so
< // here return the number of rows we have. Sub-classes can return a proper
< // value
---
> // Returning the current batch size seems the right thing to do.
757d767
<
790d799
<
Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v
retrieving revision 1.8
diff -r1.8 AbstractJdbc2Statement.java
118c118
< return 0;
---
> return super.fetchSize;
133c133,135
< throw org.postgresql.Driver.notImplemented();
---
> // I don't think this should happen, since it's a hint it should just
> // fail quietly.
> // throw org.postgresql.Driver.notImplemented();
138c140
< throw org.postgresql.Driver.notImplemented();
---
> super.fetchSize = rows;
Index: org/postgresql/jdbc2/Array.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Array.java,v
retrieving revision 1.18
diff -r1.18 Array.java
346c346,349
< return ((AbstractJdbc2Connection)conn).getResultSet(null, fields, rows, "OK", 1 );
---
> java.sql.Statement stat = ((AbstractJdbc2Connection)conn).createStatement();
> java.sql.ResultSet retRs = ((AbstractJdbc2Connection)conn).createResultSet(stat);
> ((AbstractJdbc2ResultSet)retRs).init(fields, rows, "OK", 1, 0, false);
> return retRs;
Index: org/postgresql/jdbc2/Jdbc2Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Connection.java,v
retrieving revision 1.5
diff -r1.5 Jdbc2Connection.java
48a49,80
>
>
> // the new method override which re-inits existing resultsets
> public java.sql.ResultSet getResultSet(java.sql.Statement stat,
> org.postgresql.jdbc1.AbstractJdbc1ResultSet rs,
> Field[] fields,
> Vector tuples,
> String status,
> int updateCount, long insertOID, boolean binaryCursor)
> throws SQLException
> {
> if (rs == null)
> return new Jdbc2ResultSet(this, stat, fields,
> tuples, status,
> updateCount, insertOID, binaryCursor);
> else
> {
> rs.init(fields, tuples, status, updateCount, insertOID, binaryCursor);
> return (java.sql.ResultSet) rs;
> }
> }
>
>
>
>
> /** new nic method **/
> public java.sql.ResultSet createResultSet (Statement statement)
> {
> return new Jdbc2ResultSet(this, statement);
> }
>
>
Index: org/postgresql/jdbc2/Jdbc2ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java,v
retrieving revision 1.6
diff -r1.6 Jdbc2ResultSet.java
15a16,21
> Jdbc2ResultSet (org.postgresql.PGConnection conn, Statement statement)
> {
> super (conn, statement);
> }
>
>
Index: org/postgresql/jdbc3/AbstractJdbc3ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/AbstractJdbc3ResultSet.java,v
retrieving revision 1.2
diff -r1.2 AbstractJdbc3ResultSet.java
14a15,20
> AbstractJdbc3ResultSet (org.postgresql.PGConnection conn, Statement statement)
> {
> super(conn, statement);
> }
>
>
Index: org/postgresql/jdbc3/Jdbc3Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Connection.java,v
retrieving revision 1.2
diff -r1.2 Jdbc3Connection.java
49,52d48
< public java.sql.ResultSet getResultSet(Statement statement, Field[] fields, Vector tuples, String status, int
updateCount,long insertOID, boolean binaryCursor) throws SQLException
< {
< return new Jdbc3ResultSet(this, statement, fields, tuples, status, updateCount, insertOID, binaryCursor);
< }
54c50,56
< public java.sql.ResultSet getResultSet(Statement statement, Field[] fields, Vector tuples, String status, int
updateCount)throws SQLException
---
>
>
>
>
>
> /** new nic method **/
> public java.sql.ResultSet createResultSet (Statement statement)
56c58
< return new Jdbc3ResultSet(this, statement, fields, tuples, status, updateCount, 0, false);
---
> return new Jdbc3ResultSet(this, statement);
Index: org/postgresql/jdbc3/Jdbc3ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3ResultSet.java,v
retrieving revision 1.3
diff -r1.3 Jdbc3ResultSet.java
15a16,22
> Jdbc3ResultSet (org.postgresql.PGConnection conn, Statement statement)
> {
> super (conn, statement);
> }
>
>
>
Index: org/postgresql/util/Serialize.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/util/Serialize.java,v
retrieving revision 1.14
diff -r1.14 Serialize.java
408c408
< ResultSet rs = ((org.postgresql.jdbc1.AbstractJdbc1Connection)conn).ExecSQL(sb.toString());
---
> ResultSet rs = ((org.postgresql.jdbc1.AbstractJdbc1Connection)conn).doQuery(sb.toString());
First, my understanding is that cursors are only valid within the transaction in which they were created. Is this correct? If so, I can't use the cursor method exclusively. Some of my code needs to be able to iterate over a resultset and execute whole transactions based on it. With cursors, it would fail after the first iteration of the loop. From the archives, Barry Lind's plan at one point was to not use cursors unless setFetchSize() was used explicitly. [*] That would keep existing code working. Or otherwise, they should not be used if I specify ResultSet.HOLD_CURSORS_OVER_COMMIT; I could add that to my code where it is important. If/when the backend changes to support holding cursors over commit, then they could be used in all cases. Does this sound reasonable? Thanks, Scott [*] - <http://archives.postgresql.org/pgsql-jdbc/2002-07/msg00164.php>
Scott Lamb <slamb@slamb.org> writes: > First, my understanding is that cursors are only valid within the > transaction in which they were created. Is this correct? > > If so, I can't use the cursor method exclusively. Some of my code needs > to be able to iterate over a resultset and execute whole transactions > based on it. With cursors, it would fail after the first iteration of > the loop. > > From the archives, Barry Lind's plan at one point was to not use > cursors unless setFetchSize() was used explicitly. [*] That would keep > existing code working. Or otherwise, they should not be used if I > specify ResultSet.HOLD_CURSORS_OVER_COMMIT; I could add that to my code > where it is important. If/when the backend changes to support holding > cursors over commit, then they could be used in all cases. > > Does this sound reasonable? I've been thinking about it this morning... I think that we're going to have to drop the idea of keeping the cursor open and go with the idea suggested earlier (sorry, I forget by who) of issuing multiple SQL statements slightly hacked to limit the row set. Otherwise it's just not going to fly with multiple statements. Nic
One big difference that you should look for is case sensitivity in WHERE clauses. I know that SQL-SERVER defaults to case-insensitive and you have to go through hoops to do a case sensitive search. PostreSQL defaults to case sensitive and uses the non-standard "ILIKE" verb to generate a case-insensitive WHERE clause.
Re: PostgreSQL/Oracle/MSSQL differences (was: streaming result sets: progress)
From
Laszlo Hornyak
Date:
Hi!
<opinion>
If it makes your life difficult, you should use DAO pattern, or a persistence api such as castor.
</opinion>
Laszlo Hornyak
On Thu, Nov 14, 2002 at 01:42:00PM -0600, David Hooker wrote:
> I've just recently updated my code, which has been using PostgreSQL
> exclusively for a year, to make it able to run using MSSQL Server and
> Oracle.
>
> Some of the differences:
>
> * MSSQL requires that ResultSet.getXXX methods, when used with column
> names, are called in column order - PostgreSQL doesn't care. (BTW, the
> JDBC javadoc suggests to do this).
>
> * PostgreSQL and MSSQL both treat a trailing semicolon as optional.
> Oracle requires that there be NO semicolon.
>
> * Oracle uppercases table names in the ALL_TABLES view (analogous to
> pg_tables in PostgreSQL), so for code to be portable all table names
> should be created as uppercase. (I just went through my code and
> uppercased all my SQL).
>
> * Transactions in MSSQL are handled differently than PostgreSQL and
> Oracle - there is no "BEGIN TRANSACTION" statement; instead you have to
> toggle "SET IMPLICIT_TRANSACTIONS".
>
> * Oracle doesn't have "text" or "bigint" datatypes.
And doesn`t have boolean, and many more :)
>
> * MSSQL can't perform string comparisons on "text" datatypes. (i.e.,
> "select * from table where field='value'" won't work if field is a text
> datatype).
>
> Those are just the differences that bit me. Hope my trial helps you
> guys.
>
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Nic Ferrier
> Sent: Thursday, November 14, 2002 1:31 PM
> To: Barry Lind
> Cc: snpe; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] streaming result sets: progress
>
>
> Barry Lind <blind@xythos.com> writes:
>
> > nferrier@tapsellferrier.co.uk wrote:
> > > snpe <snpe@snpe.co.yu> writes:
> > >
> > > Yes. But the reason I send:
> > >
> > > DECLARE JDBC_CURS_1 CURSOR FOR select * from tab FETCH FORWARD 100
> FROM JDBC_CURS_1;
> > >
> > > is because the SQL statement you supply is _supposed_ to end with a
> > > ";". The code for the above is actually doing:
> > >
> > > DECLARE JDBC_CURS_1 CURSOR FOR $userquery FETCH FORWARD 100 FROM
> JDBC_CURS_1;
> > >
> > > where $userquery is what comes in from the client code, eg:
> > >
> > > Statement st = con.createStatement();
> > > ResultSet rs = con.executeQuery("select * from tab;");
> > >
> > > then "select * from tab;" is inserted as $userquery. It must always
> > > end with ";" because that's how PGSQL's normal query processing
> works.
> > >
> >
> > The the ; is not required for the rest of the jdbc driver. In fact in
> > other areas of the code (like server prepared statements, batch
> updates)
> > the requirement is that the user supplied sql statement does *not* end
> > in a ;.
> >
> > This is also consistent with other jdbc drivers. In fact oracle gives
> > you an error if a sql statement ends with a ;.
>
> Apologies to everyone paying attention to this thread... in my
> experience postgres has always complained when I haven't supplied the
> ";".
>
> I will investigate and fix the patch.
>
>
> Nic
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> One big difference that you should look for is case sensitivity in WHERE > clauses. I know that SQL-SERVER defaults to case-insensitive and you have > to go through hoops to do a case sensitive search. PostreSQL defaults to > case sensitive and uses the non-standard "ILIKE" verb to generate a > case-insensitive WHERE clause. You don't need to use ILIKE if you use: ... lower(attr) LIKE "abc" This works in Oracle and PG. David
Just ran into another one. PostgreSQL creates tables as all lowercase,
even if the SQL statement is in uppercase. Since I'm using the
pg_tables view to check for the existance of tables, this makes the case
in the WHERE clause significant. Oracle forces everything to uppercase.
PostgreSQL forces everything to lowercase. Argh!!
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Hale Pringle
Sent: Thursday, November 14, 2002 4:31 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] PostgreSQL/Oracle/MSSQL differences (was: streaming
result
One big difference that you should look for is case sensitivity in WHERE
clauses. I know that SQL-SERVER defaults to case-insensitive and you
have
to go through hoops to do a case sensitive search. PostreSQL defaults
to
case sensitive and uses the non-standard "ILIKE" verb to generate a
case-insensitive WHERE clause.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
This is the patch which should fix the meta data problem. It makes
the use of nic_execute (ie: the cursor based fetch) dependant on the
setting of fetch size.
I have come to the view that this has been a bit of a waste of
time. I don't think it's reasonable to put this into a release
because the block based querys will always be interfering with other
statements.
Basically, unless everything goes cursor based (not very promising
idea) or it's possible to declare a cursor in postgresql's default,
non-transactional mode (more promising idea, maybe it would be
possible to alter the backend to allow that?) I think this is a
non-starter.
It has been valuable in as much as I've had a good look at how it
might be possible to rip out some of the duplicate query execution
stuff. I believe the query execution stuff expressed by my patch is
now pretty good.
The other interesting thing is that a PGRefCursorResultSet (as
dreamed up by Barry) would be able to deal row selection, so one
could always call a proc if one wanted a batch return.
Anyway, here's the patch.
Nic
Index: org/postgresql/PGConnection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/PGConnection.java,v
retrieving revision 1.3
diff -r1.3 PGConnection.java
79a80,89
>
>
> // Added by Nic.
>
> /** Create a result set for the specified statement.
> * This is the factory method provided by the various
> * version specific implementations of the JDBC connection
> * classes.
> */
> public java.sql.ResultSet createResultSet (java.sql.Statement statement);
cvs server: Diffing org/postgresql/core
Index: org/postgresql/core/QueryExecutor.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java,v
retrieving revision 1.16.2.1
diff -r1.16.2.1 QueryExecutor.java
20a21,69
> /** nic version - statically sets up the QE correctly.
> */
> public static void execute (String[] p_sqlFrags,
> Object[] p_binds,
> java.sql.Statement statement,
> java.sql.ResultSet rs,
> PG_Stream pg_stream,
> java.sql.Connection con)
> throws SQLException
> {
> QueryExecutor qe = new QueryExecutor();
> // I think this should be a PGConnection and we should move
> // everything we need for that from AbstractJdbc1... into PGConnection.
> qe.connection = (org.postgresql.jdbc1.AbstractJdbc1Connection)con;
> qe.m_sqlFrags = p_sqlFrags;
> qe.m_binds = p_binds;
> qe.statement = statement;
> // Nic says: connection should wrap pg_stream.
> qe.pg_stream = pg_stream;
> if (statement != null)
> qe.maxRows = statement.getMaxRows();
> else
> qe.maxRows = 0;
> // The result set.
> qe.rs = rs;
> qe.execute();
> }
>
> // This is the result set used to wrap the results.
> // The type of this is whatever is passed into the static above.
> private java.sql.ResultSet rs;
>
> // cons for the static above.
> private QueryExecutor ()
> {
> }
>
>
>
>
> /*** pre-nic implementation ***/
>
>
> /// Nic has removed the final from ALL of these (to facilitate static method).
> private String[] m_sqlFrags;
> private Object[] m_binds;
> private java.sql.Statement statement;
> private PG_Stream pg_stream;
> private org.postgresql.jdbc1.AbstractJdbc1Connection connection;
22,26d70
< private final String[] m_sqlFrags;
< private final Object[] m_binds;
< private final java.sql.Statement statement;
< private final PG_Stream pg_stream;
< private final org.postgresql.jdbc1.AbstractJdbc1Connection connection;
36c80
< this.statement = statement;
---
> this.statement = statement;
45a90
>
53a99
>
55a102
> *
133c180,185
< return connection.getResultSet(statement, fields, tuples, status, update_count, insert_oid,
binaryCursor);
---
>
> // Nic changes.
> PGResultSet resSet = ((PGResultSet)rs);
> // System.out.println(getClass().getName() + " resSet=" + resSet);
> resSet.init(fields, tuples, status, update_count, insert_oid, binaryCursor);
> return rs;
Index: org/postgresql/jdbc1/AbstractJdbc1Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java,v
retrieving revision 1.12.2.1
diff -r1.12.2.1 AbstractJdbc1Connection.java
353,354d352
< java.sql.ResultSet resultSet =
< ExecSQL("set datestyle to 'ISO'; select version(), " + encodingQuery + ";");
355a354,356
> java.sql.ResultSet resultSet
> = doQuery("set datestyle to 'ISO'; select version(), " + encodingQuery + ";");
>
376c377
< ExecSQL("set client_encoding = 'UNICODE'; show autocommit");
---
> doQuery("set client_encoding = 'UNICODE'; show autocommit");
391c392
< ExecSQL("set autocommit = on; commit;");
---
> doQuery("set autocommit = on; commit;");
412,418d412
< // These methods used to be in the main Connection implementation. As they
< // are common to all implementations (JDBC1 or 2), they are placed here.
< // This should make it easy to maintain the two specifications.
<
< public abstract java.sql.ResultSet getResultSet(Statement statement, org.postgresql.Field[] fields, Vector
tuples,String status, int updateCount, long insertOID, boolean binaryCursor) throws SQLException;
<
< public abstract java.sql.ResultSet getResultSet(Statement statement, org.postgresql.Field[] fields, Vector
tuples,String status, int updateCount) throws SQLException;
448,457c442
< /*
< * Send a query to the backend. Returns one of the ResultSet
< * objects.
< *
< * <B>Note:</B> there does not seem to be any method currently
< * in existance to return the update count.
< *
< * @param sql the SQL statement to be executed
< * @return a ResultSet holding the results
< * @exception SQLException if a database error occurs
---
> /** Simple query execution.
459c444
< public java.sql.ResultSet ExecSQL(String sql) throws SQLException
---
> public java.sql.ResultSet doQuery (String s) throws SQLException
461c446,450
< return ExecSQL(sql, null);
---
> final Object[] nullarr = new Object[0];
> java.sql.Statement stat = createStatement();
> java.sql.ResultSet rs = createResultSet(stat);
> execSQL(new String[] { s }, nullarr, stat, rs);
> return rs;
464,491c453
< /*
< * Send a query to the backend. Returns one of the ResultSet
< * objects.
< *
< * <B>Note:</B> there does not seem to be any method currently
< * in existance to return the update count.
< *
< * @param sql the SQL statement to be executed
< * @param stat The Statement associated with this query (may be null)
< * @return a ResultSet holding the results
< * @exception SQLException if a database error occurs
< */
< public java.sql.ResultSet ExecSQL(String sql, java.sql.Statement stat) throws SQLException
< {
< if (isClosed())
< {
< throw new PSQLException("postgresql.con.closed");
< }
< return new QueryExecutor(new String[] {sql}, EMPTY_OBJECT_ARRAY, stat, pg_stream,
(java.sql.Connection)this).execute();
< }
< private static final Object[] EMPTY_OBJECT_ARRAY = new Object[0];
<
< /*
< * Send a query to the backend. Returns one of the ResultSet
< * objects.
< *
< * <B>Note:</B> there does not seem to be any method currently
< * in existance to return the update count.
---
> /** Advanced query execution.
495,496c457,458
< * @param stat The Statement associated with this query (may be null)
< * @return a ResultSet holding the results
---
> * @param stat the statement associated with this query.
> * @param rs the ResultSet which will be initied for the results.
499,505c461,472
< public java.sql.ResultSet ExecSQL(String[] p_sqlFragments, Object[] p_binds, java.sql.Statement stat) throws
SQLException
< {
< if (isClosed())
< {
< throw new PSQLException("postgresql.con.closed");
< }
< return new QueryExecutor(p_sqlFragments, p_binds, stat, pg_stream, (java.sql.Connection)this).execute();
---
> public void execSQL(String[] p_sqlFragments,
> Object[] p_binds,
> java.sql.Statement stat,
> java.sql.ResultSet rs)
> throws SQLException
> {
> QueryExecutor.execute(p_sqlFragments,
> p_binds,
> stat,
> rs,
> pg_stream,
> (java.sql.Connection)this);
507a475
>
942c910
< ExecSQL("select 1; commit; set autocommit = on;");
---
> doQuery("select 1; commit; set autocommit = on;");
946c914
< ExecSQL("end");
---
> doQuery("end");
953c921
< ExecSQL("set autocommit = off; " + getIsolationLevelSQL());
---
> doQuery("set autocommit = off; " + getIsolationLevelSQL());
957c925
< ExecSQL("begin;" + getIsolationLevelSQL());
---
> doQuery("begin;" + getIsolationLevelSQL());
961,962c929,930
< ExecSQL("begin");
< ExecSQL(getIsolationLevelSQL());
---
> doQuery("begin");
> doQuery(getIsolationLevelSQL());
996c964
< ExecSQL("commit; " + getIsolationLevelSQL());
---
> doQuery("commit; " + getIsolationLevelSQL());
1000c968
< ExecSQL("commit;begin;" + getIsolationLevelSQL());
---
> doQuery("commit;begin;" + getIsolationLevelSQL());
1004,1006c972,974
< ExecSQL("commit");
< ExecSQL("begin");
< ExecSQL(getIsolationLevelSQL());
---
> doQuery("commit");
> doQuery("begin");
> doQuery(getIsolationLevelSQL());
1027c995
< ExecSQL("rollback; " + getIsolationLevelSQL());
---
> doQuery("rollback; " + getIsolationLevelSQL());
1031c999
< ExecSQL("rollback; begin;" + getIsolationLevelSQL());
---
> doQuery("rollback; begin;" + getIsolationLevelSQL());
1035,1037c1003,1005
< ExecSQL("rollback");
< ExecSQL("begin");
< ExecSQL(getIsolationLevelSQL());
---
> doQuery("rollback");
> doQuery("begin");
> doQuery(getIsolationLevelSQL());
1052c1020
< ResultSet rs = ExecSQL(sql);
---
> ResultSet rs = doQuery(sql);
1059c1027
< ExecSQL(sql);
---
> doQuery(sql);
1124c1092
< ExecSQL(isolationLevelSQL);
---
> doQuery(isolationLevelSQL);
1267c1235
< ResultSet result = ExecSQL(sql);
---
> ResultSet result = doQuery(sql);
1308c1276
< ResultSet result = ExecSQL(sql);
---
> ResultSet result = doQuery(sql);
Index: org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java,v
retrieving revision 1.10.2.1
diff -r1.10.2.1 AbstractJdbc1DatabaseMetaData.java
1916c1916,1918
< return connection.getResultSet(null, f, v, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2202c2204,2207
< return connection.getResultSet(null, f, v, "OK", 1);
---
>
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2369c2374,2376
< return connection.getResultSet(null, f, v, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2481c2488,2491
< return connection.getResultSet(null, f, v, "OK", 1);
---
>
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2583c2593,2595
< return connection.getResultSet(null, f, v, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2776c2788,2791
< return connection.getResultSet(null, f, v, "OK", 1);
---
>
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
2846c2861,2863
< return connection.getResultSet(null, f, v, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
3196c3213,3215
< return connection.getResultSet(null, f, tuples, "OK", 1);
---
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, tuples, "OK", 1, 0, false);
> return retRs;
3481c3500,3503
< return connection.getResultSet(null, f, v, "OK", 1);
---
>
> java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
> ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> return retRs;
Index: org/postgresql/jdbc1/AbstractJdbc1ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java,v
retrieving revision 1.7
diff -r1.7 AbstractJdbc1ResultSet.java
21c21
< public abstract class AbstractJdbc1ResultSet
---
> public abstract class AbstractJdbc1ResultSet implements org.postgresql.PGResultSet
45c45,51
< public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn, Statement statement, Field[] fields, Vector
tuples,String status, int updateCount, long insertOID, boolean binaryCursor)
---
> public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn,
> Statement statement,
> Field[] fields,
> Vector tuples,
> String status,
> int updateCount,
> long insertOID, boolean binaryCursor)
52a59,61
>
> System.out.println(getClass().getName() + " updateCount = " + updateCount);
>
59a69,97
> /*** nic constructor: called by superclass which is called from Jdbc1Connection.createResultSet ***/
> public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn,
> Statement statement)
> {
> this.connection = conn;
> this.statement = statement;
> }
>
> /*** nic initializer. ***/
> public void init (Field[] fields, Vector tuples, String status,
> int updateCount, long insertOID, boolean binaryCursor)
> {
> this.fields = fields;
>
> // on a reinit the size of this indicates how many we pulled
> // back. If it's 0 then the res set has ended.
> this.rows = tuples;
> this.status = status;
> this.updateCount = updateCount;
> this.insertOID = insertOID;
> this.this_row = null;
> this.current_row = -1;
> this.binaryCursor = binaryCursor;
> }
>
>
>
>
> // This slightly altered by nic.
66c104,125
< return false;
---
> {
> int fetchSize = ((AbstractJdbc1Statement)statement).fetchSize;
> // Must be false if we weren't batching.
> if (fetchSize == 0)
> return false;
> // Use the ref to the statement to get
> // the details we need to do another cursor
> // query - it will use reinit() to repopulate this
> // with the right data.
> String[] sql = new String[1];
> String[] binds = new String[0];
> // Is this the correct query???
> String cursorName = ((AbstractJdbc1Statement)statement).m_statementName;
> sql[0] = "FETCH FORWARD " + fetchSize + " FROM " + cursorName + " ;";
> ((AbstractJdbc1Connection)connection).execSQL(sql, binds, statement, (java.sql.ResultSet)this);
>
> // Test the new rows array.
> if (rows.size() == 0)
> return false;
> // Otherwise reset the counter and let it go on...
> current_row = 0;
> }
Index: org/postgresql/jdbc1/AbstractJdbc1Statement.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java,v
retrieving revision 1.12.2.1
diff -r1.12.2.1 AbstractJdbc1Statement.java
27a28,30
> /** Number of rows to get in a batch. */
> protected int fetchSize = 0;
>
51c54
< private String m_statementName = null;
---
> protected String m_statementName = null;
120c123
<
---
>
136c139
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
153c156,159
< this.execute();
---
> if (fetchSize > 0)
> this.nic_execute();
> else
> this.execute();
178c184
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
222c228
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
320c326,328
< result = ((AbstractJdbc1Connection)connection).ExecSQL(m_sqlFragments, m_binds, (java.sql.Statement)this);
---
> result = ((AbstractJdbc1Connection)connection).createResultSet((java.sql.Statement)this);
> ((AbstractJdbc1Connection)connection).execSQL(m_sqlFragments, m_binds,
> (java.sql.Statement)this, result);
343a352,455
> /** version of execute which converts the query to a cursor.
> */
> public boolean nic_execute() throws SQLException
> {
> if (isFunction && !returnTypeSet)
> throw new PSQLException("postgresql.call.noreturntype");
> if (isFunction)
> { // set entry 1 to dummy entry..
> m_binds[0] = ""; // dummy entry which ensured that no one overrode
> m_bindTypes[0] = PG_TEXT;
> // and calls to setXXX (2,..) really went to first arg in a function call..
> }
>
> // New in 7.1, if we have a previous resultset then force it to close
> // This brings us nearer to compliance, and helps memory management.
> // Internal stuff will call ExecSQL directly, bypassing this.
> if (result != null)
> {
> java.sql.ResultSet rs = getResultSet();
> if (rs != null)
> rs.close();
> }
>
> // I've pretty much ignored server prepared statements... can declare and prepare be
> // used together?
> // It's trivial to change this: you just have to resolve this issue
> // of how to work out whether there's a function call. If there isn't then the first
> // element of the array must be the bit that you extend to become the cursor
> // decleration.
> // The last thing that can go wrong is when the user supplies a cursor statement
> // directly: the translation takes no account of that. I think we should just look
> // for declare and stop the translation if we find it.
>
> // The first thing to do is transform the statement text into the cursor form.
> String[] origSqlFragments = m_sqlFragments;
> if (origSqlFragments.length > 1)
> m_sqlFragments = new String[origSqlFragments.length + 1];
> else
> m_sqlFragments = new String[origSqlFragments.length];
> System.arraycopy(origSqlFragments, 0, m_sqlFragments, 0, origSqlFragments.length);
> // Pinch the prepared count for our own nefarious purposes.
> m_statementName = "JDBC_CURS_" + m_preparedCount++;
> // The static bit to prepend to all querys.
> String cursDecl = "BEGIN; DECLARE " + m_statementName + " CURSOR FOR ";
> String endCurs = " FETCH FORWARD " + fetchSize + " FROM " + m_statementName + ";";
>
> // Add the real query to the curs decleration.
> // This is the bit that really makes the presumption about
> // m_sqlFragments not being a function call.
> if (m_sqlFragments.length < 1)
> m_sqlFragments[0] = cursDecl + "SELECT NULL;";
>
> else if (m_sqlFragments.length < 2)
> {
> if (m_sqlFragments[0].endsWith(";"))
> m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + endCurs;
> else
> m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + ";" + endCurs;
> }
> else
> {
> m_sqlFragments[0] = cursDecl + m_sqlFragments[0];
> if (m_sqlFragments[m_sqlFragments.length - 2].endsWith(";"))
> m_sqlFragments[m_sqlFragments.length - 1] = endCurs;
> else
> m_sqlFragments[m_sqlFragments.length - 1] = ";" + endCurs;
> }
>
> // Make the call to the query executor.
> AbstractJdbc1Connection execr = (AbstractJdbc1Connection)connection;
> java.sql.Statement st = (java.sql.Statement)this;
> result = (java.sql.ResultSet) execr.createResultSet(st);
> // Nic says:
> // we don't need to collect the result here, rs is altered to
> // be the result set so "result = rs" would do ok after this call.
> execr.execSQL(m_sqlFragments, m_binds, st, result);
>
> //If we are executing a callable statement function set the return data
> if (isFunction)
> {
> if (!((AbstractJdbc1ResultSet)result).reallyResultSet())
> throw new PSQLException("postgresql.call.noreturnval");
> if (!result.next ())
> throw new PSQLException ("postgresql.call.noreturnval");
> callResult = result.getObject(1);
> int columnType = result.getMetaData().getColumnType(1);
> if (columnType != functionReturnType)
> {
> Object[] arr =
> { "java.sql.Types=" + columnType,
> "java.sql.Types=" + functionReturnType
> };
> throw new PSQLException ("postgresql.call.wrongrtntype",arr);
> }
> result.close ();
> return true;
> }
> else
> {
> return (result != null && ((AbstractJdbc1ResultSet)result).reallyResultSet());
> }
> }
>
>
596c708
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
1915c2027
< ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
---
> ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
Index: org/postgresql/jdbc1/Jdbc1Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Connection.java,v
retrieving revision 1.5
diff -r1.5 Jdbc1Connection.java
48a49,54
>
> public java.sql.ResultSet createResultSet (java.sql.Statement stat) throws SQLException
> {
> // This needs doing.
> return null;
> }
cvs server: Diffing org/postgresql/jdbc2
Index: org/postgresql/jdbc2/AbstractJdbc2Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java,v
retrieving revision 1.2
diff -r1.2 AbstractJdbc2Connection.java
8a9
>
20c21
<
---
>
Index: org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java,v
retrieving revision 1.10
diff -r1.10 AbstractJdbc2ResultSet.java
40a41,45
>
> AbstractJdbc2ResultSet (org.postgresql.PGConnection conn, Statement statement)
> {
> super(conn, statement);
> }
144a150,157
> else if (type.equals("refcursor"))
> {
> // We must return a ResultSet with the results packaged.
> // We should probably check that auto commit is turned off.
> String cursorName = getString(columnIndex);
> // return new RefCursorResultSet(cursorName);
> return null;
> }
369,371c382
< // In this implementation we return the entire result set, so
< // here return the number of rows we have. Sub-classes can return a proper
< // value
---
> // Returning the current batch size seems the right thing to do.
757d767
<
790d799
<
Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v
retrieving revision 1.8
diff -r1.8 AbstractJdbc2Statement.java
118c118
< return 0;
---
> return super.fetchSize;
133c133,135
< throw org.postgresql.Driver.notImplemented();
---
> // I don't think this should happen, since it's a hint it should just
> // fail quietly.
> // throw org.postgresql.Driver.notImplemented();
138c140
< throw org.postgresql.Driver.notImplemented();
---
> super.fetchSize = rows;
Index: org/postgresql/jdbc2/Array.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Array.java,v
retrieving revision 1.18
diff -r1.18 Array.java
346c346,349
< return ((AbstractJdbc2Connection)conn).getResultSet(null, fields, rows, "OK", 1 );
---
> java.sql.Statement stat = ((AbstractJdbc2Connection)conn).createStatement();
> java.sql.ResultSet retRs = ((AbstractJdbc2Connection)conn).createResultSet(stat);
> ((AbstractJdbc2ResultSet)retRs).init(fields, rows, "OK", 1, 0, false);
> return retRs;
Index: org/postgresql/jdbc2/Jdbc2Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Connection.java,v
retrieving revision 1.5
diff -r1.5 Jdbc2Connection.java
48a49,80
>
>
> // the new method override which re-inits existing resultsets
> public java.sql.ResultSet getResultSet(java.sql.Statement stat,
> org.postgresql.jdbc1.AbstractJdbc1ResultSet rs,
> Field[] fields,
> Vector tuples,
> String status,
> int updateCount, long insertOID, boolean binaryCursor)
> throws SQLException
> {
> if (rs == null)
> return new Jdbc2ResultSet(this, stat, fields,
> tuples, status,
> updateCount, insertOID, binaryCursor);
> else
> {
> rs.init(fields, tuples, status, updateCount, insertOID, binaryCursor);
> return (java.sql.ResultSet) rs;
> }
> }
>
>
>
>
> /** new nic method **/
> public java.sql.ResultSet createResultSet (Statement statement)
> {
> return new Jdbc2ResultSet(this, statement);
> }
>
>
Index: org/postgresql/jdbc2/Jdbc2ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java,v
retrieving revision 1.6
diff -r1.6 Jdbc2ResultSet.java
15a16,21
> Jdbc2ResultSet (org.postgresql.PGConnection conn, Statement statement)
> {
> super (conn, statement);
> }
>
>
Index: org/postgresql/jdbc3/AbstractJdbc3ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/AbstractJdbc3ResultSet.java,v
retrieving revision 1.2
diff -r1.2 AbstractJdbc3ResultSet.java
14a15,20
> AbstractJdbc3ResultSet (org.postgresql.PGConnection conn, Statement statement)
> {
> super(conn, statement);
> }
>
>
Index: org/postgresql/jdbc3/Jdbc3Connection.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Connection.java,v
retrieving revision 1.2
diff -r1.2 Jdbc3Connection.java
49,52d48
< public java.sql.ResultSet getResultSet(Statement statement, Field[] fields, Vector tuples, String status, int
updateCount,long insertOID, boolean binaryCursor) throws SQLException
< {
< return new Jdbc3ResultSet(this, statement, fields, tuples, status, updateCount, insertOID, binaryCursor);
< }
54c50,56
< public java.sql.ResultSet getResultSet(Statement statement, Field[] fields, Vector tuples, String status, int
updateCount)throws SQLException
---
>
>
>
>
>
> /** new nic method **/
> public java.sql.ResultSet createResultSet (Statement statement)
56c58
< return new Jdbc3ResultSet(this, statement, fields, tuples, status, updateCount, 0, false);
---
> return new Jdbc3ResultSet(this, statement);
Index: org/postgresql/jdbc3/Jdbc3ResultSet.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3ResultSet.java,v
retrieving revision 1.3
diff -r1.3 Jdbc3ResultSet.java
15a16,22
> Jdbc3ResultSet (org.postgresql.PGConnection conn, Statement statement)
> {
> super (conn, statement);
> }
>
>
>
Index: org/postgresql/util/Serialize.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/util/Serialize.java,v
retrieving revision 1.14
diff -r1.14 Serialize.java
408c408
< ResultSet rs = ((org.postgresql.jdbc1.AbstractJdbc1Connection)conn).ExecSQL(sb.toString());
---
> ResultSet rs = ((org.postgresql.jdbc1.AbstractJdbc1Connection)conn).doQuery(sb.toString());
On Friday 15 November 2002 10:30 am, nferrier@tapsellferrier.co.uk wrote: > Thanks for testing all this Haris. > You velcome > snpe <snpe@snpe.co.yu> writes: > > I work with patch and I have any problem yet > > > > 1) ResultSet.setFetchSize, Statement.getFetchSize and > > ResultSet.getFetchSize don't work > > I've updated my patch to cover these methods. Note that > ResultSet.getFetchSize() should have worked for you. I haven't altered > it, right now the method returns the number of rows in the current > "batch" which seems like the right thing to do. > > > 2) any methods in DataBaseMetaData don't work > > example, getBestRowIdentifier > > getBestRowIdentifier create ResultSet without execSQL (direct) and > > when we call ResultSet.next, after last row driverreturn error > > (driver send command 'FETCH FORWARD 0 FROM ;' > > Ok... looks like the initing of the ResultSet isn't quite right > there. I'll take a look at that tonight. > > > 3) if I have two or more query pgsql return warning 'BEGIN : already a > > transaction in progress' > > the next one. > > > 4) This is greatest problem : > > If I create query and then want transaction without close query, I break > > query. example : > > > > a) create statement for 'select ..' and fetch any rows > > > > b) do any update,delete or update and commit or rollback > > > > query is lost > > I think that problem 4) can be make only with cursor outre of > > transaction. > > The problem is that my code needs to wrap the query in a transaction > so that the cursor can be used. > > But when that's done other statements fail... > > How do people handle this with their "manual" cursor based hacks? Is > the best thing to wrap _everything_ in an independant transaction, eg: > including inserts and updates? and do commits automatically but via > the java code. This will lower performance of course. > > It's a tricky one. > I don't know. commit will always break open queries - we haven't savepoint I have yet another example : Simpe master detail screen (forms) in JAVA I do select in master and go on detail - I make update on detail and commit If I will return to master and continue query I must select again Solution is cursor out of transaction - it is in TODO lists for postgreSQL, but I don't know is it planed for 7.4 Another solution : I call old way query and save in memory only fetchSize rows When I call ResultSet.next out of fetchSize I call new query and save next fetchSize rows etc - this will lower performance, but if we set fetchSize big almost queries will make only first step and this is normal situation, but big qyery will work (slower, but work) - true solution is cursor out of transaction. Regards Haris Peco
Hello Nic,
Please call patch with ;
diff -Naur olddir newdir
and add in message like attach
This is add PGResultSet
I can't open this patch
Thanks
Haris Peco
On Friday 15 November 2002 11:50 pm, Nic Ferrier wrote:
> This is the patch which should fix the meta data problem. It makes
> the use of nic_execute (ie: the cursor based fetch) dependant on the
> setting of fetch size.
>
>
> I have come to the view that this has been a bit of a waste of
> time. I don't think it's reasonable to put this into a release
> because the block based querys will always be interfering with other
> statements.
>
> Basically, unless everything goes cursor based (not very promising
> idea) or it's possible to declare a cursor in postgresql's default,
> non-transactional mode (more promising idea, maybe it would be
> possible to alter the backend to allow that?) I think this is a
> non-starter.
>
>
> It has been valuable in as much as I've had a good look at how it
> might be possible to rip out some of the duplicate query execution
> stuff. I believe the query execution stuff expressed by my patch is
> now pretty good.
>
>
> The other interesting thing is that a PGRefCursorResultSet (as
> dreamed up by Barry) would be able to deal row selection, so one
> could always call a proc if one wanted a batch return.
>
>
>
> Anyway, here's the patch.
>
>
> Nic
>
>
> Index: org/postgresql/PGConnection.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/PGConnect
>ion.java,v retrieving revision 1.3
> diff -r1.3 PGConnection.java
> 79a80,89
>
> > // Added by Nic.
> >
> > /** Create a result set for the specified statement.
> > * This is the factory method provided by the various
> > * version specific implementations of the JDBC connection
> > * classes.
> > */
> > public java.sql.ResultSet createResultSet (java.sql.Statement
> > statement);
>
> cvs server: Diffing org/postgresql/core
> Index: org/postgresql/core/QueryExecutor.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/core/Quer
>yExecutor.java,v retrieving revision 1.16.2.1
> diff -r1.16.2.1 QueryExecutor.java
> 20a21,69
>
> > /** nic version - statically sets up the QE correctly.
> > */
> > public static void execute (String[] p_sqlFrags,
> > Object[] p_binds,
> > java.sql.Statement statement,
> > java.sql.ResultSet rs,
> > PG_Stream pg_stream,
> > java.sql.Connection con)
> > throws SQLException
> > {
> > QueryExecutor qe = new QueryExecutor();
> > // I think this should be a PGConnection and we should move
> > // everything we need for that from AbstractJdbc1... into PGConnection.
> > qe.connection = (org.postgresql.jdbc1.AbstractJdbc1Connection)con;
> > qe.m_sqlFrags = p_sqlFrags;
> > qe.m_binds = p_binds;
> > qe.statement = statement;
> > // Nic says: connection should wrap pg_stream.
> > qe.pg_stream = pg_stream;
> > if (statement != null)
> > qe.maxRows = statement.getMaxRows();
> > else
> > qe.maxRows = 0;
> > // The result set.
> > qe.rs = rs;
> > qe.execute();
> > }
> >
> > // This is the result set used to wrap the results.
> > // The type of this is whatever is passed into the static above.
> > private java.sql.ResultSet rs;
> >
> > // cons for the static above.
> > private QueryExecutor ()
> > {
> > }
> >
> >
> >
> >
> > /*** pre-nic implementation ***/
> >
> >
> > /// Nic has removed the final from ALL of these (to facilitate static
> > method). private String[] m_sqlFrags;
> > private Object[] m_binds;
> > private java.sql.Statement statement;
> > private PG_Stream pg_stream;
> > private org.postgresql.jdbc1.AbstractJdbc1Connection connection;
>
> 22,26d70
> < private final String[] m_sqlFrags;
> < private final Object[] m_binds;
> < private final java.sql.Statement statement;
> < private final PG_Stream pg_stream;
> < private final org.postgresql.jdbc1.AbstractJdbc1Connection connection;
> 36c80
> < this.statement = statement;
> ---
>
> > this.statement = statement;
>
> 45a90
>
> 53a99
>
> 55a102
>
> > *
>
> 133c180,185
> < return connection.getResultSet(statement, fields, tuples, status,
> update_count, insert_oid, binaryCursor); ---
>
> > // Nic changes.
> > PGResultSet resSet = ((PGResultSet)rs);
> > // System.out.println(getClass().getName() + " resSet=" + resSet);
> > resSet.init(fields, tuples, status, update_count, insert_oid,
> > binaryCursor); return rs;
>
> Index: org/postgresql/jdbc1/AbstractJdbc1Connection.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Abs
>tractJdbc1Connection.java,v retrieving revision 1.12.2.1
> diff -r1.12.2.1 AbstractJdbc1Connection.java
> 353,354d352
> < java.sql.ResultSet resultSet =
> < ExecSQL("set datestyle to 'ISO'; select version(), " + encodingQuery +
> ";"); 355a354,356
>
> > java.sql.ResultSet resultSet
> > = doQuery("set datestyle to 'ISO'; select version(), " + encodingQuery
> > + ";");
>
> 376c377
> < ExecSQL("set client_encoding = 'UNICODE'; show autocommit");
> ---
>
> > doQuery("set client_encoding = 'UNICODE'; show autocommit");
>
> 391c392
> < ExecSQL("set autocommit = on; commit;");
> ---
>
> > doQuery("set autocommit = on; commit;");
>
> 412,418d412
> < // These methods used to be in the main Connection implementation. As
> they < // are common to all implementations (JDBC1 or 2), they are placed
> here. < // This should make it easy to maintain the two specifications. <
> < public abstract java.sql.ResultSet getResultSet(Statement statement,
> org.postgresql.Field[] fields, Vector tuples, String status, int
> updateCount, long insertOID, boolean binaryCursor) throws SQLException; <
> < public abstract java.sql.ResultSet getResultSet(Statement statement,
> org.postgresql.Field[] fields, Vector tuples, String status, int
> updateCount) throws SQLException; 448,457c442
> < /*
> < * Send a query to the backend. Returns one of the ResultSet
> < * objects.
> < *
> < * <B>Note:</B> there does not seem to be any method currently
> < * in existance to return the update count.
> < *
> < * @param sql the SQL statement to be executed
> < * @return a ResultSet holding the results
> < * @exception SQLException if a database error occurs
> ---
>
> > /** Simple query execution.
>
> 459c444
> < public java.sql.ResultSet ExecSQL(String sql) throws SQLException
> ---
>
> > public java.sql.ResultSet doQuery (String s) throws SQLException
>
> 461c446,450
> < return ExecSQL(sql, null);
> ---
>
> > final Object[] nullarr = new Object[0];
> > java.sql.Statement stat = createStatement();
> > java.sql.ResultSet rs = createResultSet(stat);
> > execSQL(new String[] { s }, nullarr, stat, rs);
> > return rs;
>
> 464,491c453
> < /*
> < * Send a query to the backend. Returns one of the ResultSet
> < * objects.
> < *
> < * <B>Note:</B> there does not seem to be any method currently
> < * in existance to return the update count.
> < *
> < * @param sql the SQL statement to be executed
> < * @param stat The Statement associated with this query (may be null)
> < * @return a ResultSet holding the results
> < * @exception SQLException if a database error occurs
> < */
> < public java.sql.ResultSet ExecSQL(String sql, java.sql.Statement stat)
> throws SQLException < {
> < if (isClosed())
> < {
> < throw new PSQLException("postgresql.con.closed");
> < }
> < return new QueryExecutor(new String[] {sql}, EMPTY_OBJECT_ARRAY, stat,
> pg_stream, (java.sql.Connection)this).execute(); < }
> < private static final Object[] EMPTY_OBJECT_ARRAY = new Object[0];
> <
> < /*
> < * Send a query to the backend. Returns one of the ResultSet
> < * objects.
> < *
> < * <B>Note:</B> there does not seem to be any method currently
> < * in existance to return the update count.
> ---
>
> > /** Advanced query execution.
>
> 495,496c457,458
> < * @param stat The Statement associated with this query (may be null)
> < * @return a ResultSet holding the results
> ---
>
> > * @param stat the statement associated with this query.
> > * @param rs the ResultSet which will be initied for the results.
>
> 499,505c461,472
> < public java.sql.ResultSet ExecSQL(String[] p_sqlFragments, Object[]
> p_binds, java.sql.Statement stat) throws SQLException < {
> < if (isClosed())
> < {
> < throw new PSQLException("postgresql.con.closed");
> < }
> < return new QueryExecutor(p_sqlFragments, p_binds, stat, pg_stream,
> (java.sql.Connection)this).execute(); ---
>
> > public void execSQL(String[] p_sqlFragments,
> > Object[] p_binds,
> > java.sql.Statement stat,
> > java.sql.ResultSet rs)
> > throws SQLException
> > {
> > QueryExecutor.execute(p_sqlFragments,
> > p_binds,
> > stat,
> > rs,
> > pg_stream,
> > (java.sql.Connection)this);
>
> 507a475
>
> 942c910
> < ExecSQL("select 1; commit; set autocommit = on;");
> ---
>
> > doQuery("select 1; commit; set autocommit = on;");
>
> 946c914
> < ExecSQL("end");
> ---
>
> > doQuery("end");
>
> 953c921
> < ExecSQL("set autocommit = off; " + getIsolationLevelSQL());
> ---
>
> > doQuery("set autocommit = off; " + getIsolationLevelSQL());
>
> 957c925
> < ExecSQL("begin;" + getIsolationLevelSQL());
> ---
>
> > doQuery("begin;" + getIsolationLevelSQL());
>
> 961,962c929,930
> < ExecSQL("begin");
> < ExecSQL(getIsolationLevelSQL());
> ---
>
> > doQuery("begin");
> > doQuery(getIsolationLevelSQL());
>
> 996c964
> < ExecSQL("commit; " + getIsolationLevelSQL());
> ---
>
> > doQuery("commit; " + getIsolationLevelSQL());
>
> 1000c968
> < ExecSQL("commit;begin;" + getIsolationLevelSQL());
> ---
>
> > doQuery("commit;begin;" + getIsolationLevelSQL());
>
> 1004,1006c972,974
> < ExecSQL("commit");
> < ExecSQL("begin");
> < ExecSQL(getIsolationLevelSQL());
> ---
>
> > doQuery("commit");
> > doQuery("begin");
> > doQuery(getIsolationLevelSQL());
>
> 1027c995
> < ExecSQL("rollback; " + getIsolationLevelSQL());
> ---
>
> > doQuery("rollback; " + getIsolationLevelSQL());
>
> 1031c999
> < ExecSQL("rollback; begin;" + getIsolationLevelSQL());
> ---
>
> > doQuery("rollback; begin;" + getIsolationLevelSQL());
>
> 1035,1037c1003,1005
> < ExecSQL("rollback");
> < ExecSQL("begin");
> < ExecSQL(getIsolationLevelSQL());
> ---
>
> > doQuery("rollback");
> > doQuery("begin");
> > doQuery(getIsolationLevelSQL());
>
> 1052c1020
> < ResultSet rs = ExecSQL(sql);
> ---
>
> > ResultSet rs = doQuery(sql);
>
> 1059c1027
> < ExecSQL(sql);
> ---
>
> > doQuery(sql);
>
> 1124c1092
> < ExecSQL(isolationLevelSQL);
> ---
>
> > doQuery(isolationLevelSQL);
>
> 1267c1235
> < ResultSet result = ExecSQL(sql);
> ---
>
> > ResultSet result = doQuery(sql);
>
> 1308c1276
> < ResultSet result = ExecSQL(sql);
> ---
>
> > ResultSet result = doQuery(sql);
>
> Index: org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Abs
>tractJdbc1DatabaseMetaData.java,v retrieving revision 1.10.2.1
> diff -r1.10.2.1 AbstractJdbc1DatabaseMetaData.java
> 1916c1916,1918
> < return connection.getResultSet(null, f, v, "OK", 1);
> ---
>
> > java.sql.ResultSet retRs =
> > connection.createResultSet(connection.createStatement());
> > ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false); return
> > retRs;
>
> 2202c2204,2207
> < return connection.getResultSet(null, f, v, "OK", 1);
> ---
>
> > java.sql.ResultSet retRs =
> > connection.createResultSet(connection.createStatement());
> > ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false); return
> > retRs;
>
> 2369c2374,2376
> < return connection.getResultSet(null, f, v, "OK", 1);
> ---
>
> > java.sql.ResultSet retRs =
> > connection.createResultSet(connection.createStatement());
> > ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false); return
> > retRs;
>
> 2481c2488,2491
> < return connection.getResultSet(null, f, v, "OK", 1);
> ---
>
> > java.sql.ResultSet retRs =
> > connection.createResultSet(connection.createStatement());
> > ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false); return
> > retRs;
>
> 2583c2593,2595
> < return connection.getResultSet(null, f, v, "OK", 1);
> ---
>
> > java.sql.ResultSet retRs =
> > connection.createResultSet(connection.createStatement());
> > ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false); return
> > retRs;
>
> 2776c2788,2791
> < return connection.getResultSet(null, f, v, "OK", 1);
> ---
>
> > java.sql.ResultSet retRs =
> > connection.createResultSet(connection.createStatement());
> > ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false); return
> > retRs;
>
> 2846c2861,2863
> < return connection.getResultSet(null, f, v, "OK", 1);
> ---
>
> > java.sql.ResultSet retRs =
> > connection.createResultSet(connection.createStatement());
> > ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false); return
> > retRs;
>
> 3196c3213,3215
> < return connection.getResultSet(null, f, tuples, "OK", 1);
> ---
>
> > java.sql.ResultSet retRs =
> > connection.createResultSet(connection.createStatement());
> > ((AbstractJdbc1ResultSet)retRs).init(f, tuples, "OK", 1, 0, false);
> > return retRs;
>
> 3481c3500,3503
> < return connection.getResultSet(null, f, v, "OK", 1);
> ---
>
> > java.sql.ResultSet retRs =
> > connection.createResultSet(connection.createStatement());
> > ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false); return
> > retRs;
>
> Index: org/postgresql/jdbc1/AbstractJdbc1ResultSet.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Abs
>tractJdbc1ResultSet.java,v retrieving revision 1.7
> diff -r1.7 AbstractJdbc1ResultSet.java
> 21c21
> < public abstract class AbstractJdbc1ResultSet
> ---
>
> > public abstract class AbstractJdbc1ResultSet implements
> > org.postgresql.PGResultSet
>
> 45c45,51
> < public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn,
> Statement statement, Field[] fields, Vector tuples, String status, int
> updateCount, long insertOID, boolean binaryCursor) ---
>
> > public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn,
> > Statement statement,
> > Field[] fields,
> > Vector tuples,
> > String status,
> > int updateCount,
> > long insertOID, boolean binaryCursor)
>
> 52a59,61
>
> > System.out.println(getClass().getName() + " updateCount = " +
> > updateCount);
>
> 59a69,97
>
> > /*** nic constructor: called by superclass which is called from
> > Jdbc1Connection.createResultSet ***/ public
> > AbstractJdbc1ResultSet(org.postgresql.PGConnection conn, Statement
> > statement)
> > {
> > this.connection = conn;
> > this.statement = statement;
> > }
> >
> > /*** nic initializer. ***/
> > public void init (Field[] fields, Vector tuples, String status,
> > int updateCount, long insertOID, boolean binaryCursor)
> > {
> > this.fields = fields;
> >
> > // on a reinit the size of this indicates how many we pulled
> > // back. If it's 0 then the res set has ended.
> > this.rows = tuples;
> > this.status = status;
> > this.updateCount = updateCount;
> > this.insertOID = insertOID;
> > this.this_row = null;
> > this.current_row = -1;
> > this.binaryCursor = binaryCursor;
> > }
> >
> >
> >
> >
> > // This slightly altered by nic.
>
> 66c104,125
> < return false;
> ---
>
> > {
> > int fetchSize = ((AbstractJdbc1Statement)statement).fetchSize;
> > // Must be false if we weren't batching.
> > if (fetchSize == 0)
> > return false;
> > // Use the ref to the statement to get
> > // the details we need to do another cursor
> > // query - it will use reinit() to repopulate this
> > // with the right data.
> > String[] sql = new String[1];
> > String[] binds = new String[0];
> > // Is this the correct query???
> > String cursorName =
> > ((AbstractJdbc1Statement)statement).m_statementName; sql[0] = "FETCH
> > FORWARD " + fetchSize + " FROM " + cursorName + " ;";
> > ((AbstractJdbc1Connection)connection).execSQL(sql, binds, statement,
> > (java.sql.ResultSet)this);
> >
> > // Test the new rows array.
> > if (rows.size() == 0)
> > return false;
> > // Otherwise reset the counter and let it go on...
> > current_row = 0;
> > }
>
> Index: org/postgresql/jdbc1/AbstractJdbc1Statement.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Abs
>tractJdbc1Statement.java,v retrieving revision 1.12.2.1
> diff -r1.12.2.1 AbstractJdbc1Statement.java
> 27a28,30
>
> > /** Number of rows to get in a batch. */
> > protected int fetchSize = 0;
>
> 51c54
> < private String m_statementName = null;
> ---
>
> > protected String m_statementName = null;
>
> 120c123
> <
> ---
>
> 136c139
> < ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " +
> m_statementName); ---
>
> > ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " +
> > m_statementName);
>
> 153c156,159
> < this.execute();
> ---
>
> > if (fetchSize > 0)
> > this.nic_execute();
> > else
> > this.execute();
>
> 178c184
> < ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " +
> m_statementName); ---
>
> > ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " +
> > m_statementName);
>
> 222c228
> < ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " +
> m_statementName); ---
>
> > ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " +
> > m_statementName);
>
> 320c326,328
> < result = ((AbstractJdbc1Connection)connection).ExecSQL(m_sqlFragments,
> m_binds, (java.sql.Statement)this); ---
>
> > result =
> > ((AbstractJdbc1Connection)connection).createResultSet((java.sql.Statement
> >)this); ((AbstractJdbc1Connection)connection).execSQL(m_sqlFragments,
> > m_binds, (java.sql.Statement)this, result);
>
> 343a352,455
>
> > /** version of execute which converts the query to a cursor.
> > */
> > public boolean nic_execute() throws SQLException
> > {
> > if (isFunction && !returnTypeSet)
> > throw new PSQLException("postgresql.call.noreturntype");
> > if (isFunction)
> > { // set entry 1 to dummy entry..
> > m_binds[0] = ""; // dummy entry which ensured that no one overrode
> > m_bindTypes[0] = PG_TEXT;
> > // and calls to setXXX (2,..) really went to first arg in a function
> > call.. }
> >
> > // New in 7.1, if we have a previous resultset then force it to close
> > // This brings us nearer to compliance, and helps memory management.
> > // Internal stuff will call ExecSQL directly, bypassing this.
> > if (result != null)
> > {
> > java.sql.ResultSet rs = getResultSet();
> > if (rs != null)
> > rs.close();
> > }
> >
> > // I've pretty much ignored server prepared statements... can declare
> > and prepare be // used together?
> > // It's trivial to change this: you just have to resolve this issue
> > // of how to work out whether there's a function call. If there isn't
> > then the first // element of the array must be the bit that you extend to
> > become the cursor // decleration.
> > // The last thing that can go wrong is when the user supplies a cursor
> > statement // directly: the translation takes no account of that. I think
> > we should just look // for declare and stop the translation if we find
> > it.
> >
> > // The first thing to do is transform the statement text into the
> > cursor form. String[] origSqlFragments = m_sqlFragments;
> > if (origSqlFragments.length > 1)
> > m_sqlFragments = new String[origSqlFragments.length + 1];
> > else
> > m_sqlFragments = new String[origSqlFragments.length];
> > System.arraycopy(origSqlFragments, 0, m_sqlFragments, 0,
> > origSqlFragments.length); // Pinch the prepared count for our own
> > nefarious purposes.
> > m_statementName = "JDBC_CURS_" + m_preparedCount++;
> > // The static bit to prepend to all querys.
> > String cursDecl = "BEGIN; DECLARE " + m_statementName + " CURSOR FOR ";
> > String endCurs = " FETCH FORWARD " + fetchSize + " FROM " +
> > m_statementName + ";";
> >
> > // Add the real query to the curs decleration.
> > // This is the bit that really makes the presumption about
> > // m_sqlFragments not being a function call.
> > if (m_sqlFragments.length < 1)
> > m_sqlFragments[0] = cursDecl + "SELECT NULL;";
> >
> > else if (m_sqlFragments.length < 2)
> > {
> > if (m_sqlFragments[0].endsWith(";"))
> > m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + endCurs;
> > else
> > m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + ";" + endCurs;
> > }
> > else
> > {
> > m_sqlFragments[0] = cursDecl + m_sqlFragments[0];
> > if (m_sqlFragments[m_sqlFragments.length - 2].endsWith(";"))
> > m_sqlFragments[m_sqlFragments.length - 1] = endCurs;
> > else
> > m_sqlFragments[m_sqlFragments.length - 1] = ";" + endCurs;
> > }
> >
> > // Make the call to the query executor.
> > AbstractJdbc1Connection execr = (AbstractJdbc1Connection)connection;
> > java.sql.Statement st = (java.sql.Statement)this;
> > result = (java.sql.ResultSet) execr.createResultSet(st);
> > // Nic says:
> > // we don't need to collect the result here, rs is altered to
> > // be the result set so "result = rs" would do ok after this call.
> > execr.execSQL(m_sqlFragments, m_binds, st, result);
> >
> > //If we are executing a callable statement function set the return data
> > if (isFunction)
> > {
> > if (!((AbstractJdbc1ResultSet)result).reallyResultSet())
> > throw new PSQLException("postgresql.call.noreturnval");
> > if (!result.next ())
> > throw new PSQLException ("postgresql.call.noreturnval");
> > callResult = result.getObject(1);
> > int columnType = result.getMetaData().getColumnType(1);
> > if (columnType != functionReturnType)
> > {
> > Object[] arr =
> > { "java.sql.Types=" + columnType,
> > "java.sql.Types=" + functionReturnType
> > };
> > throw new PSQLException ("postgresql.call.wrongrtntype",arr);
> > }
> > result.close ();
> > return true;
> > }
> > else
> > {
> > return (result != null &&
> > ((AbstractJdbc1ResultSet)result).reallyResultSet()); }
> > }
>
> 596c708
> < ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " +
> m_statementName); ---
>
> > ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " +
> > m_statementName);
>
> 1915c2027
> < ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " +
> m_statementName); ---
>
> > ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " +
> > m_statementName);
>
> Index: org/postgresql/jdbc1/Jdbc1Connection.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Jdb
>c1Connection.java,v retrieving revision 1.5
> diff -r1.5 Jdbc1Connection.java
> 48a49,54
>
> > public java.sql.ResultSet createResultSet (java.sql.Statement stat)
> > throws SQLException {
> > // This needs doing.
> > return null;
> > }
>
> cvs server: Diffing org/postgresql/jdbc2
> Index: org/postgresql/jdbc2/AbstractJdbc2Connection.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Abs
>tractJdbc2Connection.java,v retrieving revision 1.2
> diff -r1.2 AbstractJdbc2Connection.java
> 8a9
>
> 20c21
> <
> ---
>
> Index: org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Abs
>tractJdbc2ResultSet.java,v retrieving revision 1.10
> diff -r1.10 AbstractJdbc2ResultSet.java
> 40a41,45
>
> > AbstractJdbc2ResultSet (org.postgresql.PGConnection conn, Statement
> > statement) {
> > super(conn, statement);
> > }
>
> 144a150,157
>
> > else if (type.equals("refcursor"))
> > {
> > // We must return a ResultSet with the results packaged.
> > // We should probably check that auto commit is turned off.
> > String cursorName = getString(columnIndex);
> > // return new RefCursorResultSet(cursorName);
> > return null;
> > }
>
> 369,371c382
> < // In this implementation we return the entire result set, so
> < // here return the number of rows we have. Sub-classes can return a
> proper < // value
> ---
>
> > // Returning the current batch size seems the right thing to do.
>
> 757d767
> <
> 790d799
> <
> Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Abs
>tractJdbc2Statement.java,v retrieving revision 1.8
> diff -r1.8 AbstractJdbc2Statement.java
> 118c118
> < return 0;
> ---
>
> > return super.fetchSize;
>
> 133c133,135
> < throw org.postgresql.Driver.notImplemented();
> ---
>
> > // I don't think this should happen, since it's a hint it should just
> > // fail quietly.
> > // throw org.postgresql.Driver.notImplemented();
>
> 138c140
> < throw org.postgresql.Driver.notImplemented();
> ---
>
> > super.fetchSize = rows;
>
> Index: org/postgresql/jdbc2/Array.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Arr
>ay.java,v retrieving revision 1.18
> diff -r1.18 Array.java
> 346c346,349
> < return ((AbstractJdbc2Connection)conn).getResultSet(null, fields, rows,
> "OK", 1 ); ---
>
> > java.sql.Statement stat =
> > ((AbstractJdbc2Connection)conn).createStatement(); java.sql.ResultSet
> > retRs = ((AbstractJdbc2Connection)conn).createResultSet(stat);
> > ((AbstractJdbc2ResultSet)retRs).init(fields, rows, "OK", 1, 0, false);
> > return retRs;
>
> Index: org/postgresql/jdbc2/Jdbc2Connection.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Jdb
>c2Connection.java,v retrieving revision 1.5
> diff -r1.5 Jdbc2Connection.java
> 48a49,80
>
> > // the new method override which re-inits existing resultsets
> > public java.sql.ResultSet getResultSet(java.sql.Statement stat,
> > org.postgresql.jdbc1.AbstractJdbc1ResultSet rs,
> > Field[] fields,
> > Vector tuples,
> > String status,
> > int updateCount, long insertOID, boolean binaryCursor)
> > throws SQLException
> > {
> > if (rs == null)
> > return new Jdbc2ResultSet(this, stat, fields,
> > tuples, status,
> > updateCount, insertOID, binaryCursor);
> > else
> > {
> > rs.init(fields, tuples, status, updateCount, insertOID, binaryCursor);
> > return (java.sql.ResultSet) rs;
> > }
> > }
> >
> >
> >
> >
> > /** new nic method **/
> > public java.sql.ResultSet createResultSet (Statement statement)
> > {
> > return new Jdbc2ResultSet(this, statement);
> > }
>
> Index: org/postgresql/jdbc2/Jdbc2ResultSet.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Jdb
>c2ResultSet.java,v retrieving revision 1.6
> diff -r1.6 Jdbc2ResultSet.java
> 15a16,21
>
> > Jdbc2ResultSet (org.postgresql.PGConnection conn, Statement statement)
> > {
> > super (conn, statement);
> > }
>
> Index: org/postgresql/jdbc3/AbstractJdbc3ResultSet.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/Abs
>tractJdbc3ResultSet.java,v retrieving revision 1.2
> diff -r1.2 AbstractJdbc3ResultSet.java
> 14a15,20
>
> > AbstractJdbc3ResultSet (org.postgresql.PGConnection conn, Statement
> > statement) {
> > super(conn, statement);
> > }
>
> Index: org/postgresql/jdbc3/Jdbc3Connection.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/Jdb
>c3Connection.java,v retrieving revision 1.2
> diff -r1.2 Jdbc3Connection.java
> 49,52d48
> < public java.sql.ResultSet getResultSet(Statement statement, Field[]
> fields, Vector tuples, String status, int updateCount, long insertOID,
> boolean binaryCursor) throws SQLException < {
> < return new Jdbc3ResultSet(this, statement, fields, tuples, status,
> updateCount, insertOID, binaryCursor); < }
> 54c50,56
> < public java.sql.ResultSet getResultSet(Statement statement, Field[]
> fields, Vector tuples, String status, int updateCount) throws SQLException
> ---
>
> > /** new nic method **/
> > public java.sql.ResultSet createResultSet (Statement statement)
>
> 56c58
> < return new Jdbc3ResultSet(this, statement, fields, tuples, status,
> updateCount, 0, false); ---
>
> > return new Jdbc3ResultSet(this, statement);
>
> Index: org/postgresql/jdbc3/Jdbc3ResultSet.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/Jdb
>c3ResultSet.java,v retrieving revision 1.3
> diff -r1.3 Jdbc3ResultSet.java
> 15a16,22
>
> > Jdbc3ResultSet (org.postgresql.PGConnection conn, Statement statement)
> > {
> > super (conn, statement);
> > }
>
> Index: org/postgresql/util/Serialize.java
> ===================================================================
> RCS file:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/util/Seri
>alize.java,v retrieving revision 1.14
> diff -r1.14 Serialize.java
> 408c408
> < ResultSet rs =
> ((org.postgresql.jdbc1.AbstractJdbc1Connection)conn).ExecSQL(sb.toString())
>; ---
>
> > ResultSet rs =
> > ((org.postgresql.jdbc1.AbstractJdbc1Connection)conn).doQuery(sb.toString(
> >));
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
Message-ID: <87heehjcv3.fsf@pooh-sticks-bridge.tapsellferrier.co.uk>
Lines: 19
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=-=-="
--text follows this line--
--=-=-=
snpe <snpe@snpe.co.yu> writes:
> Hello Nic,
> Please call patch with ;
> diff -Naur olddir newdir
> and add in message like attach
> This is add PGResultSet
> I can't open this patch
>
Here's a diff based patch.
--=-=-=
Content-Type: text/patch
Content-Disposition: attachment; filename=nic-diff
Content-Description: patch for query stuff.
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur currentcvs/src/interfaces/jdbc/org/postgresql/PGConnection.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/PGConnection.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/PGConnection.java Fri Sep 6 21:23:05 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/PGConnection.java Thu Nov 7 20:38:44 2002
@@ -77,5 +77,15 @@
public PGNotification[] getNotifications();
+
+
+ // Added by Nic.
+
+ /** Create a result set for the specified statement.
+ * This is the factory method provided by the various
+ * version specific implementations of the JDBC connection
+ * classes.
+ */
+ public java.sql.ResultSet createResultSet (java.sql.Statement statement);
}
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur currentcvs/src/interfaces/jdbc/org/postgresql/PGResultSet.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/PGResultSet.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/PGResultSet.java Thu Jan 1 00:00:00 1970
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/PGResultSet.java Sat Nov 2 01:18:43 2002
@@ -0,0 +1,16 @@
+package org.postgresql;
+
+
+import java.util.Vector;
+
+
+/** PG base rep of a Res Set.
+ */
+public interface PGResultSet
+{
+
+ /** The init method is what is used to put the data into a ResultSet.
+ */
+ public void init (Field[] fields, Vector tuples, String status,
+ int update_count, long insert_oid, boolean binaryCursor);
+}
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur currentcvs/src/interfaces/jdbc/org/postgresql/core/Encoding.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/core/Encoding.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/core/Encoding.java Sat Nov 16 22:24:28 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/core/Encoding.java Fri Nov 15 09:30:46 2002
@@ -8,7 +8,7 @@
/*
* Converts to and from the character encoding used by the backend.
*
- * $Id: Encoding.java,v 1.8 2002/11/14 05:35:45 barry Exp $
+ * $Id: Encoding.java,v 1.7.2.1 2002/11/14 05:54:39 barry Exp $
*/
public class Encoding
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java Sat Nov 16 22:24:28 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java Fri Nov 15 09:30:50 2002
@@ -13,17 +13,61 @@
* <p>The lifetime of a QueryExecutor object is from sending the query
* until the response has been received from the backend.
*
- * $Id: QueryExecutor.java,v 1.17 2002/11/14 05:35:45 barry Exp $
+ * $Id: QueryExecutor.java,v 1.16.2.1 2002/11/14 05:54:39 barry Exp $
*/
public class QueryExecutor
{
+ /** nic version - statically sets up the QE correctly.
+ */
+ public static void execute (String[] p_sqlFrags,
+ Object[] p_binds,
+ java.sql.Statement statement,
+ java.sql.ResultSet rs,
+ PG_Stream pg_stream,
+ java.sql.Connection con)
+ throws SQLException
+ {
+ QueryExecutor qe = new QueryExecutor();
+ // I think this should be a PGConnection and we should move
+ // everything we need for that from AbstractJdbc1... into PGConnection.
+ qe.connection = (org.postgresql.jdbc1.AbstractJdbc1Connection)con;
+ qe.m_sqlFrags = p_sqlFrags;
+ qe.m_binds = p_binds;
+ qe.statement = statement;
+ // Nic says: connection should wrap pg_stream.
+ qe.pg_stream = pg_stream;
+ if (statement != null)
+ qe.maxRows = statement.getMaxRows();
+ else
+ qe.maxRows = 0;
+ // The result set.
+ qe.rs = rs;
+ qe.execute();
+ }
+
+ // This is the result set used to wrap the results.
+ // The type of this is whatever is passed into the static above.
+ private java.sql.ResultSet rs;
+
+ // cons for the static above.
+ private QueryExecutor ()
+ {
+ }
+
+
+
+
+ /*** pre-nic implementation ***/
+
+
+ /// Nic has removed the final from ALL of these (to facilitate static method).
+ private String[] m_sqlFrags;
+ private Object[] m_binds;
+ private java.sql.Statement statement;
+ private PG_Stream pg_stream;
+ private org.postgresql.jdbc1.AbstractJdbc1Connection connection;
- private final String[] m_sqlFrags;
- private final Object[] m_binds;
- private final java.sql.Statement statement;
- private final PG_Stream pg_stream;
- private final org.postgresql.jdbc1.AbstractJdbc1Connection connection;
public QueryExecutor(String[] p_sqlFrags, Object[] p_binds,
java.sql.Statement statement,
@@ -33,7 +77,7 @@
{
this.m_sqlFrags = p_sqlFrags;
this.m_binds = p_binds;
- this.statement = statement;
+ this.statement = statement;
this.pg_stream = pg_stream;
this.connection = (org.postgresql.jdbc1.AbstractJdbc1Connection)connection;
@@ -43,6 +87,7 @@
maxRows = 0;
}
+
private Field[] fields = null;
private Vector tuples = new Vector();
private boolean binaryCursor = false;
@@ -51,8 +96,10 @@
private long insert_oid = 0;
private int maxRows;
+
/*
* Execute a query on the backend.
+ *
*/
public java.sql.ResultSet execute() throws SQLException
{
@@ -130,7 +177,12 @@
if ( errorMessage != null )
throw new SQLException( errorMessage.toString() );
- return connection.getResultSet(statement, fields, tuples, status, update_count, insert_oid, binaryCursor);
+
+ // Nic changes.
+ PGResultSet resSet = ((PGResultSet)rs);
+ // System.out.println(getClass().getName() + " resSet=" + resSet);
+ resSet.init(fields, tuples, status, update_count, insert_oid, binaryCursor);
+ return rs;
}
}
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java Sat Nov 16 22:24:29 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java Fri Nov 15 09:34:39 2002
@@ -14,7 +14,7 @@
import org.postgresql.util.*;
-/* $Header: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java,v
1.132002/11/14 05:35:45 barry Exp $
+/* $Header: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java,v
1.12.2.12002/11/14 05:54:39 barry Exp $
* This class defines methods of the jdbc1 specification. This class is
* extended by org.postgresql.jdbc2.AbstractJdbc2Connection which adds the jdbc2
* methods. The real Connection class (for jdbc1) is org.postgresql.jdbc1.Jdbc1Connection
@@ -350,9 +350,10 @@
// Set datestyle and fetch db encoding in a single call, to avoid making
// more than one round trip to the backend during connection startup.
- java.sql.ResultSet resultSet =
- ExecSQL("set datestyle to 'ISO'; select version(), " + encodingQuery + ";");
+ java.sql.ResultSet resultSet
+ = doQuery("set datestyle to 'ISO'; select version(), " + encodingQuery + ";");
+
if (! resultSet.next())
{
throw new PSQLException("postgresql.con.failed", "failed getting backend encoding");
@@ -373,7 +374,7 @@
if (haveMinimumServerVersion("7.3"))
{
java.sql.ResultSet acRset =
- ExecSQL("set client_encoding = 'UNICODE'; show autocommit");
+ doQuery("set client_encoding = 'UNICODE'; show autocommit");
//set encoding to be unicode
encoding = Encoding.getEncoding("UNICODE", null);
@@ -388,7 +389,7 @@
//to make the setting permanent
if (acRset.getString(1).equals("off"))
{
- ExecSQL("set autocommit = on; commit;");
+ doQuery("set autocommit = on; commit;");
}
}
@@ -409,13 +410,6 @@
return this_driver;
}
- // These methods used to be in the main Connection implementation. As they
- // are common to all implementations (JDBC1 or 2), they are placed here.
- // This should make it easy to maintain the two specifications.
-
- public abstract java.sql.ResultSet getResultSet(Statement statement, org.postgresql.Field[] fields, Vector tuples,
Stringstatus, int updateCount, long insertOID, boolean binaryCursor) throws SQLException;
-
- public abstract java.sql.ResultSet getResultSet(Statement statement, org.postgresql.Field[] fields, Vector tuples,
Stringstatus, int updateCount) throws SQLException;
/*
* This adds a warning to the warning chain.
@@ -445,66 +439,40 @@
//}
}
- /*
- * Send a query to the backend. Returns one of the ResultSet
- * objects.
- *
- * <B>Note:</B> there does not seem to be any method currently
- * in existance to return the update count.
- *
- * @param sql the SQL statement to be executed
- * @return a ResultSet holding the results
- * @exception SQLException if a database error occurs
+ /** Simple query execution.
*/
- public java.sql.ResultSet ExecSQL(String sql) throws SQLException
+ public java.sql.ResultSet doQuery (String s) throws SQLException
{
- return ExecSQL(sql, null);
+ final Object[] nullarr = new Object[0];
+ java.sql.Statement stat = createStatement();
+ java.sql.ResultSet rs = createResultSet(stat);
+ execSQL(new String[] { s }, nullarr, stat, rs);
+ return rs;
}
- /*
- * Send a query to the backend. Returns one of the ResultSet
- * objects.
- *
- * <B>Note:</B> there does not seem to be any method currently
- * in existance to return the update count.
- *
- * @param sql the SQL statement to be executed
- * @param stat The Statement associated with this query (may be null)
- * @return a ResultSet holding the results
- * @exception SQLException if a database error occurs
- */
- public java.sql.ResultSet ExecSQL(String sql, java.sql.Statement stat) throws SQLException
- {
- if (isClosed())
- {
- throw new PSQLException("postgresql.con.closed");
- }
- return new QueryExecutor(new String[] {sql}, EMPTY_OBJECT_ARRAY, stat, pg_stream,
(java.sql.Connection)this).execute();
- }
- private static final Object[] EMPTY_OBJECT_ARRAY = new Object[0];
-
- /*
- * Send a query to the backend. Returns one of the ResultSet
- * objects.
- *
- * <B>Note:</B> there does not seem to be any method currently
- * in existance to return the update count.
+ /** Advanced query execution.
*
* @param p_sqlFragmentss the SQL statement parts to be executed
* @param p_binds the SQL bind values
- * @param stat The Statement associated with this query (may be null)
- * @return a ResultSet holding the results
+ * @param stat the statement associated with this query.
+ * @param rs the ResultSet which will be initied for the results.
* @exception SQLException if a database error occurs
*/
- public java.sql.ResultSet ExecSQL(String[] p_sqlFragments, Object[] p_binds, java.sql.Statement stat) throws
SQLException
- {
- if (isClosed())
- {
- throw new PSQLException("postgresql.con.closed");
- }
- return new QueryExecutor(p_sqlFragments, p_binds, stat, pg_stream, (java.sql.Connection)this).execute();
+ public void execSQL(String[] p_sqlFragments,
+ Object[] p_binds,
+ java.sql.Statement stat,
+ java.sql.ResultSet rs)
+ throws SQLException
+ {
+ QueryExecutor.execute(p_sqlFragments,
+ p_binds,
+ stat,
+ rs,
+ pg_stream,
+ (java.sql.Connection)this);
}
+
/*
* In SQL, a result table can be retrieved through a cursor that
* is named. The current row of a result can be updated or deleted
@@ -939,27 +907,27 @@
//We do the select to ensure a transaction is in process
//before we do the commit to avoid warning messages
//from issuing a commit without a transaction in process
- ExecSQL("select 1; commit; set autocommit = on;");
+ doQuery("select 1; commit; set autocommit = on;");
}
else
{
- ExecSQL("end");
+ doQuery("end");
}
}
else
{
if (haveMinimumServerVersion("7.3"))
{
- ExecSQL("set autocommit = off; " + getIsolationLevelSQL());
+ doQuery("set autocommit = off; " + getIsolationLevelSQL());
}
else if (haveMinimumServerVersion("7.1"))
{
- ExecSQL("begin;" + getIsolationLevelSQL());
+ doQuery("begin;" + getIsolationLevelSQL());
}
else
{
- ExecSQL("begin");
- ExecSQL(getIsolationLevelSQL());
+ doQuery("begin");
+ doQuery(getIsolationLevelSQL());
}
}
this.autoCommit = autoCommit;
@@ -993,17 +961,17 @@
return ;
if (haveMinimumServerVersion("7.3"))
{
- ExecSQL("commit; " + getIsolationLevelSQL());
+ doQuery("commit; " + getIsolationLevelSQL());
}
else if (haveMinimumServerVersion("7.1"))
{
- ExecSQL("commit;begin;" + getIsolationLevelSQL());
+ doQuery("commit;begin;" + getIsolationLevelSQL());
}
else
{
- ExecSQL("commit");
- ExecSQL("begin");
- ExecSQL(getIsolationLevelSQL());
+ doQuery("commit");
+ doQuery("begin");
+ doQuery(getIsolationLevelSQL());
}
}
@@ -1024,17 +992,17 @@
//we don't automatically start a transaction
//but let the server functionality automatically start
//one when the first statement is executed
- ExecSQL("rollback; " + getIsolationLevelSQL());
+ doQuery("rollback; " + getIsolationLevelSQL());
}
else if (haveMinimumServerVersion("7.1"))
{
- ExecSQL("rollback; begin;" + getIsolationLevelSQL());
+ doQuery("rollback; begin;" + getIsolationLevelSQL());
}
else
{
- ExecSQL("rollback");
- ExecSQL("begin");
- ExecSQL(getIsolationLevelSQL());
+ doQuery("rollback");
+ doQuery("begin");
+ doQuery(getIsolationLevelSQL());
}
}
@@ -1049,14 +1017,14 @@
String sql = "show transaction isolation level";
String level = null;
if (haveMinimumServerVersion("7.3")) {
- ResultSet rs = ExecSQL(sql);
+ ResultSet rs = doQuery(sql);
if (rs.next()) {
level = rs.getString(1);
}
rs.close();
} else {
clearWarnings();
- ExecSQL(sql);
+ doQuery(sql);
SQLWarning warning = getWarnings();
if (warning != null)
{
@@ -1121,7 +1089,7 @@
new Integer(isolationLevel));
}
}
- ExecSQL(isolationLevelSQL);
+ doQuery(isolationLevelSQL);
}
/*
@@ -1264,7 +1232,7 @@
} else {
sql = "SELECT typname FROM pg_type WHERE oid = " +oid;
}
- ResultSet result = ExecSQL(sql);
+ ResultSet result = doQuery(sql);
if (((AbstractJdbc1ResultSet)result).getColumnCount() != 1 ||
((AbstractJdbc1ResultSet)result).getTupleCount()!= 1) {
throw new PSQLException("postgresql.unexpected");
}
@@ -1305,7 +1273,7 @@
} else {
sql = "SELECT oid FROM pg_type WHERE typname='" + typeName + "'";
}
- ResultSet result = ExecSQL(sql);
+ ResultSet result = doQuery(sql);
if (((AbstractJdbc1ResultSet)result).getColumnCount() != 1 ||
((AbstractJdbc1ResultSet)result).getTupleCount()!= 1)
throw new PSQLException("postgresql.unexpected");
result.next();
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java Mon Nov 11 07:11:12 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java Fri Nov 15 09:31:00 2002
@@ -1913,7 +1913,9 @@
}
rs.close();
- return connection.getResultSet(null, f, v, "OK", 1);
+ java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
+ ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
+ return retRs;
}
/*
@@ -2199,7 +2201,10 @@
tuple[0] = types[i].getBytes();
v.addElement(tuple);
}
- return connection.getResultSet(null, f, v, "OK", 1);
+
+ java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
+ ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
+ return retRs;
}
/*
@@ -2366,7 +2371,9 @@
}
rs.close();
- return connection.getResultSet(null, f, v, "OK", 1);
+ java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
+ ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
+ return retRs;
}
/*
@@ -2478,7 +2485,10 @@
}
}
rs.close();
- return connection.getResultSet(null, f, v, "OK", 1);
+
+ java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
+ ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
+ return retRs;
}
/*
@@ -2580,7 +2590,9 @@
}
rs.close();
- return connection.getResultSet(null, f, v, "OK", 1);
+ java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
+ ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
+ return retRs;
}
private static void sortStringArray(String s[]) {
@@ -2773,7 +2785,10 @@
tuple[7] = Integer.toString(java.sql.DatabaseMetaData.bestRowNotPseudo).getBytes();
v.addElement(tuple);
}
- return connection.getResultSet(null, f, v, "OK", 1);
+
+ java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
+ ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
+ return retRs;
}
/*
@@ -2843,7 +2858,9 @@
/* Perhaps we should check that the given
* catalog.schema.table actually exists. -KJ
*/
- return connection.getResultSet(null, f, v, "OK", 1);
+ java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
+ ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
+ return retRs;
}
/*
@@ -3193,7 +3210,9 @@
tuples.addElement(tuple);
}
- return connection.getResultSet(null, f, tuples, "OK", 1);
+ java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
+ ((AbstractJdbc1ResultSet)retRs).init(f, tuples, "OK", 1, 0, false);
+ return retRs;
}
/*
@@ -3478,7 +3497,10 @@
v.addElement(tuple);
}
rs.close();
- return connection.getResultSet(null, f, v, "OK", 1);
+
+ java.sql.ResultSet retRs = connection.createResultSet(connection.createStatement());
+ ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
+ return retRs;
}
/*
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java Sat Oct 19 22:10:36 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java Fri Nov 15 23:29:26 2002
@@ -18,7 +18,7 @@
* extended by org.postgresql.jdbc2.AbstractJdbc2ResultSet which adds the jdbc2
* methods. The real ResultSet class (for jdbc1) is org.postgresql.jdbc1.Jdbc1ResultSet
*/
-public abstract class AbstractJdbc1ResultSet
+public abstract class AbstractJdbc1ResultSet implements org.postgresql.PGResultSet
{
protected Vector rows; // The results
@@ -42,7 +42,13 @@
public byte[][] rowBuffer = null;
- public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn, Statement statement, Field[] fields, Vector
tuples,String status, int updateCount, long insertOID, boolean binaryCursor)
+ public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn,
+ Statement statement,
+ Field[] fields,
+ Vector tuples,
+ String status,
+ int updateCount,
+ long insertOID, boolean binaryCursor)
{
this.connection = conn;
this.statement = statement;
@@ -50,6 +56,9 @@
this.rows = tuples;
this.status = status;
this.updateCount = updateCount;
+
+ System.out.println(getClass().getName() + " updateCount = " + updateCount);
+
this.insertOID = insertOID;
this.this_row = null;
this.current_row = -1;
@@ -57,13 +66,63 @@
}
+ /*** nic constructor: called by superclass which is called from Jdbc1Connection.createResultSet ***/
+ public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn,
+ Statement statement)
+ {
+ this.connection = conn;
+ this.statement = statement;
+ }
+
+ /*** nic initializer. ***/
+ public void init (Field[] fields, Vector tuples, String status,
+ int updateCount, long insertOID, boolean binaryCursor)
+ {
+ this.fields = fields;
+
+ // on a reinit the size of this indicates how many we pulled
+ // back. If it's 0 then the res set has ended.
+ this.rows = tuples;
+ this.status = status;
+ this.updateCount = updateCount;
+ this.insertOID = insertOID;
+ this.this_row = null;
+ this.current_row = -1;
+ this.binaryCursor = binaryCursor;
+ }
+
+
+
+
+ // This slightly altered by nic.
public boolean next() throws SQLException
{
if (rows == null)
throw new PSQLException("postgresql.con.closed");
if (++current_row >= rows.size())
- return false;
+ {
+ int fetchSize = ((AbstractJdbc1Statement)statement).fetchSize;
+ // Must be false if we weren't batching.
+ if (fetchSize == 0)
+ return false;
+ // Use the ref to the statement to get
+ // the details we need to do another cursor
+ // query - it will use reinit() to repopulate this
+ // with the right data.
+ String[] sql = new String[1];
+ String[] binds = new String[0];
+ // Is this the correct query???
+ String cursorName = ((AbstractJdbc1Statement)statement).m_statementName;
+ sql[0] = "FETCH FORWARD " + fetchSize + " FROM " + cursorName + " ;";
+ ((AbstractJdbc1Connection)connection).execSQL(sql, binds, statement, (java.sql.ResultSet)this);
+
+ // Test the new rows array.
+ if (rows.size() == 0)
+ return false;
+ // Otherwise reset the counter and let it go on...
+ current_row = 0;
+ }
this_row = (byte [][])rows.elementAt(current_row);
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java Sat Nov 16 22:24:29 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java Fri Nov 15 23:12:53 2002
@@ -8,7 +8,7 @@
import org.postgresql.largeobject.*;
import org.postgresql.util.*;
-/* $Header: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java,v 1.13
2002/11/1405:35:45 barry Exp $
+/* $Header: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java,v
1.12.2.12002/11/14 05:54:39 barry Exp $
* This class defines methods of the jdbc1 specification. This class is
* extended by org.postgresql.jdbc2.AbstractJdbc2Statement which adds the jdbc2
* methods. The real Statement class (for jdbc1) is org.postgresql.jdbc1.Jdbc1Statement
@@ -25,6 +25,9 @@
/** Maximum number of rows to return, 0 = unlimited */
protected int maxrows = 0;
+ /** Number of rows to get in a batch. */
+ protected int fetchSize = 0;
+
/** Timeout (in seconds) for a query (not used) */
protected int timeout = 0;
@@ -48,7 +51,7 @@
private String[] m_executeSqlFragments;
protected Object[] m_binds = new Object[0];
private String[] m_bindTypes = new String[0];
- private String m_statementName = null;
+ protected String m_statementName = null;
private boolean m_useServerPrepare = false;
private static int m_preparedCount = 1;
@@ -117,7 +120,7 @@
}
-
+
/*
* Execute a SQL statement that retruns a single ResultSet
*
@@ -133,7 +136,7 @@
//If we have already created a server prepared statement, we need
//to deallocate the existing one
if (m_statementName != null) {
- ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
+ ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
m_statementName = null;
m_origSqlFragments = null;
m_executeSqlFragments = null;
@@ -150,7 +153,10 @@
*/
public java.sql.ResultSet executeQuery() throws SQLException
{
- this.execute();
+ if (fetchSize > 0)
+ this.nic_execute();
+ else
+ this.execute();
while (result != null && !((AbstractJdbc1ResultSet)result).reallyResultSet())
result = ((AbstractJdbc1ResultSet)result).getNext();
if (result == null)
@@ -175,7 +181,7 @@
//If we have already created a server prepared statement, we need
//to deallocate the existing one
if (m_statementName != null) {
- ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
+ ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
m_statementName = null;
m_origSqlFragments = null;
m_executeSqlFragments = null;
@@ -219,7 +225,7 @@
//If we have already created a server prepared statement, we need
//to deallocate the existing one
if (m_statementName != null) {
- ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
+ ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
m_statementName = null;
m_origSqlFragments = null;
m_executeSqlFragments = null;
@@ -317,7 +323,9 @@
}
// New in 7.1, pass Statement so that ExecSQL can customise to it
- result = ((AbstractJdbc1Connection)connection).ExecSQL(m_sqlFragments, m_binds, (java.sql.Statement)this);
+ result = ((AbstractJdbc1Connection)connection).createResultSet((java.sql.Statement)this);
+ ((AbstractJdbc1Connection)connection).execSQL(m_sqlFragments, m_binds,
+ (java.sql.Statement)this, result);
//If we are executing a callable statement function set the return data
if (isFunction)
@@ -341,6 +349,110 @@
}
}
+ /** version of execute which converts the query to a cursor.
+ */
+ public boolean nic_execute() throws SQLException
+ {
+ if (isFunction && !returnTypeSet)
+ throw new PSQLException("postgresql.call.noreturntype");
+ if (isFunction)
+ { // set entry 1 to dummy entry..
+ m_binds[0] = ""; // dummy entry which ensured that no one overrode
+ m_bindTypes[0] = PG_TEXT;
+ // and calls to setXXX (2,..) really went to first arg in a function call..
+ }
+
+ // New in 7.1, if we have a previous resultset then force it to close
+ // This brings us nearer to compliance, and helps memory management.
+ // Internal stuff will call ExecSQL directly, bypassing this.
+ if (result != null)
+ {
+ java.sql.ResultSet rs = getResultSet();
+ if (rs != null)
+ rs.close();
+ }
+
+ // I've pretty much ignored server prepared statements... can declare and prepare be
+ // used together?
+ // It's trivial to change this: you just have to resolve this issue
+ // of how to work out whether there's a function call. If there isn't then the first
+ // element of the array must be the bit that you extend to become the cursor
+ // decleration.
+ // The last thing that can go wrong is when the user supplies a cursor statement
+ // directly: the translation takes no account of that. I think we should just look
+ // for declare and stop the translation if we find it.
+
+ // The first thing to do is transform the statement text into the cursor form.
+ String[] origSqlFragments = m_sqlFragments;
+ if (origSqlFragments.length > 1)
+ m_sqlFragments = new String[origSqlFragments.length + 1];
+ else
+ m_sqlFragments = new String[origSqlFragments.length];
+ System.arraycopy(origSqlFragments, 0, m_sqlFragments, 0, origSqlFragments.length);
+ // Pinch the prepared count for our own nefarious purposes.
+ m_statementName = "JDBC_CURS_" + m_preparedCount++;
+ // The static bit to prepend to all querys.
+ String cursDecl = "BEGIN; DECLARE " + m_statementName + " CURSOR FOR ";
+ String endCurs = " FETCH FORWARD " + fetchSize + " FROM " + m_statementName + ";";
+
+ // Add the real query to the curs decleration.
+ // This is the bit that really makes the presumption about
+ // m_sqlFragments not being a function call.
+ if (m_sqlFragments.length < 1)
+ m_sqlFragments[0] = cursDecl + "SELECT NULL;";
+
+ else if (m_sqlFragments.length < 2)
+ {
+ if (m_sqlFragments[0].endsWith(";"))
+ m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + endCurs;
+ else
+ m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + ";" + endCurs;
+ }
+ else
+ {
+ m_sqlFragments[0] = cursDecl + m_sqlFragments[0];
+ if (m_sqlFragments[m_sqlFragments.length - 2].endsWith(";"))
+ m_sqlFragments[m_sqlFragments.length - 1] = endCurs;
+ else
+ m_sqlFragments[m_sqlFragments.length - 1] = ";" + endCurs;
+ }
+
+ // Make the call to the query executor.
+ AbstractJdbc1Connection execr = (AbstractJdbc1Connection)connection;
+ java.sql.Statement st = (java.sql.Statement)this;
+ result = (java.sql.ResultSet) execr.createResultSet(st);
+ // Nic says:
+ // we don't need to collect the result here, rs is altered to
+ // be the result set so "result = rs" would do ok after this call.
+ execr.execSQL(m_sqlFragments, m_binds, st, result);
+
+ //If we are executing a callable statement function set the return data
+ if (isFunction)
+ {
+ if (!((AbstractJdbc1ResultSet)result).reallyResultSet())
+ throw new PSQLException("postgresql.call.noreturnval");
+ if (!result.next ())
+ throw new PSQLException ("postgresql.call.noreturnval");
+ callResult = result.getObject(1);
+ int columnType = result.getMetaData().getColumnType(1);
+ if (columnType != functionReturnType)
+ {
+ Object[] arr =
+ { "java.sql.Types=" + columnType,
+ "java.sql.Types=" + functionReturnType
+ };
+ throw new PSQLException ("postgresql.call.wrongrtntype",arr);
+ }
+ result.close ();
+ return true;
+ }
+ else
+ {
+ return (result != null && ((AbstractJdbc1ResultSet)result).reallyResultSet());
+ }
+ }
+
+
/*
* setCursorName defines the SQL cursor name that will be used by
* subsequent execute methods. This name can then be used in SQL
@@ -593,7 +705,7 @@
// If using server prepared statements deallocate them
if (m_useServerPrepare && m_statementName != null) {
- ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
+ ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
}
// Disasociate it from us (For Garbage Collection)
@@ -1912,7 +2024,7 @@
//If turning server prepared statements off deallocate statement
//and reset statement name
if (m_useServerPrepare != flag && !flag)
- ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " + m_statementName);
+ ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " + m_statementName);
m_statementName = null;
m_useServerPrepare = flag;
} else {
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Connection.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Connection.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Connection.java Fri Sep 6 21:23:06 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Connection.java Thu Nov 7 22:27:15 2002
@@ -46,6 +46,12 @@
return new Jdbc1ResultSet(this, stat, fields, tuples, status, updateCount, 0, false);
}
+
+ public java.sql.ResultSet createResultSet (java.sql.Statement stat) throws SQLException
+ {
+ // This needs doing.
+ return null;
+ }
}
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java Fri Sep 6 21:23:06 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java Sat Oct 12 19:37:33 2002
@@ -6,6 +6,7 @@
import java.sql.*;
import org.postgresql.util.PSQLException;
+
/* $Header: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.java,v 1.2
2002/09/0621:23:06 momjian Exp $
* This class defines methods of the jdbc2 specification. This class extends
* org.postgresql.jdbc1.AbstractJdbc1Connection which provides the jdbc1
@@ -17,7 +18,7 @@
* The current type mappings
*/
protected java.util.Map typemap;
-
+
public java.sql.Statement createStatement() throws SQLException
{
// The spec says default of TYPE_FORWARD_ONLY but everyone is used to
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java Mon Nov 4 06:42:33 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java Fri Nov 15 09:52:10 2002
@@ -38,6 +38,11 @@
protected PreparedStatement deleteStatement = null;
private PreparedStatement selectStatement = null;
+
+ AbstractJdbc2ResultSet (org.postgresql.PGConnection conn, Statement statement)
+ {
+ super(conn, statement);
+ }
public AbstractJdbc2ResultSet(org.postgresql.PGConnection conn, Statement statement, Field[] fields, Vector
tuples,String status, int updateCount, long insertOID, boolean binaryCursor)
@@ -142,6 +147,14 @@
{
return getString(columnIndex);
}
+ else if (type.equals("refcursor"))
+ {
+ // We must return a ResultSet with the results packaged.
+ // We should probably check that auto commit is turned off.
+ String cursorName = getString(columnIndex);
+ // return new RefCursorResultSet(cursorName);
+ return null;
+ }
else
{
return connection.getObject(field.getPGType(), getString(columnIndex));
@@ -366,9 +379,7 @@
public int getFetchSize() throws SQLException
{
- // In this implementation we return the entire result set, so
- // here return the number of rows we have. Sub-classes can return a proper
- // value
+ // Returning the current batch size seems the right thing to do.
return rows.size();
}
@@ -754,7 +765,6 @@
}
updateValue(columnIndex, theData);
-
}
@@ -787,7 +797,6 @@
throw new PSQLException("postgresql.updateable.ioerror" + ie);
}
updateValue(columnIndex, theData);
-
}
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java Wed Oct 30 04:33:29 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java Fri Nov 15 09:47:27 2002
@@ -115,7 +115,7 @@
public int getFetchSize() throws SQLException
{
- return 0;
+ return super.fetchSize;
}
public int getResultSetConcurrency() throws SQLException
@@ -130,12 +130,14 @@
public void setFetchDirection(int direction) throws SQLException
{
- throw org.postgresql.Driver.notImplemented();
+ // I don't think this should happen, since it's a hint it should just
+ // fail quietly.
+ // throw org.postgresql.Driver.notImplemented();
}
public void setFetchSize(int rows) throws SQLException
{
- throw org.postgresql.Driver.notImplemented();
+ super.fetchSize = rows;
}
public void setResultSetConcurrency(int value) throws SQLException
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/Array.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/Array.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/Array.java Fri Sep 6 21:23:06 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/Array.java Thu Nov 7 23:32:30 2002
@@ -343,7 +343,10 @@
default:
throw org.postgresql.Driver.notImplemented();
}
- return ((AbstractJdbc2Connection)conn).getResultSet(null, fields, rows, "OK", 1 );
+ java.sql.Statement stat = ((AbstractJdbc2Connection)conn).createStatement();
+ java.sql.ResultSet retRs = ((AbstractJdbc2Connection)conn).createResultSet(stat);
+ ((AbstractJdbc2ResultSet)retRs).init(fields, rows, "OK", 1, 0, false);
+ return retRs;
}
public String toString()
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Connection.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Connection.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Connection.java Fri Sep 6 21:23:06 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Connection.java Thu Nov 7 23:29:13 2002
@@ -46,6 +46,38 @@
return metadata;
}
+
+
+ // the new method override which re-inits existing resultsets
+ public java.sql.ResultSet getResultSet(java.sql.Statement stat,
+ org.postgresql.jdbc1.AbstractJdbc1ResultSet rs,
+ Field[] fields,
+ Vector tuples,
+ String status,
+ int updateCount, long insertOID, boolean binaryCursor)
+ throws SQLException
+ {
+ if (rs == null)
+ return new Jdbc2ResultSet(this, stat, fields,
+ tuples, status,
+ updateCount, insertOID, binaryCursor);
+ else
+ {
+ rs.init(fields, tuples, status, updateCount, insertOID, binaryCursor);
+ return (java.sql.ResultSet) rs;
+ }
+ }
+
+
+
+
+ /** new nic method **/
+ public java.sql.ResultSet createResultSet (Statement statement)
+ {
+ return new Jdbc2ResultSet(this, statement);
+ }
+
+
public java.sql.ResultSet getResultSet(Statement statement, Field[] fields, Vector tuples, String status, int
updateCount,long insertOID, boolean binaryCursor) throws SQLException
{
return new Jdbc2ResultSet(this, statement, fields, tuples, status, updateCount, insertOID, binaryCursor);
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java Wed Sep 11 05:38:45 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java Wed Nov 6 12:19:43 2002
@@ -13,6 +13,12 @@
public class Jdbc2ResultSet extends org.postgresql.jdbc2.AbstractJdbc2ResultSet implements java.sql.ResultSet
{
+ Jdbc2ResultSet (org.postgresql.PGConnection conn, Statement statement)
+ {
+ super (conn, statement);
+ }
+
+
public Jdbc2ResultSet(Jdbc2Connection conn, Statement statement, Field[] fields, Vector tuples, String status, int
updateCount,long insertOID, boolean binaryCursor)
{
super(conn, statement, fields, tuples, status, updateCount, insertOID, binaryCursor);
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/jdbc3/AbstractJdbc3ResultSet.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc3/AbstractJdbc3ResultSet.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc3/AbstractJdbc3ResultSet.java Fri Sep 6 21:23:06 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc3/AbstractJdbc3ResultSet.java Thu Nov 14 23:14:05 2002
@@ -12,6 +12,12 @@
public abstract class AbstractJdbc3ResultSet extends org.postgresql.jdbc2.AbstractJdbc2ResultSet
{
+ AbstractJdbc3ResultSet (org.postgresql.PGConnection conn, Statement statement)
+ {
+ super(conn, statement);
+ }
+
+
public AbstractJdbc3ResultSet(org.postgresql.PGConnection conn, Statement statement, org.postgresql.Field[]
fields,Vector tuples, String status, int updateCount, long insertOID, boolean binaryCursor)
{
super (conn, statement, fields, tuples, status, updateCount, insertOID, binaryCursor);
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Connection.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Connection.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Connection.java Fri Sep 6 21:23:06 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Connection.java Thu Nov 14 23:12:57 2002
@@ -46,14 +46,16 @@
return metadata;
}
- public java.sql.ResultSet getResultSet(Statement statement, Field[] fields, Vector tuples, String status, int
updateCount,long insertOID, boolean binaryCursor) throws SQLException
- {
- return new Jdbc3ResultSet(this, statement, fields, tuples, status, updateCount, insertOID, binaryCursor);
- }
- public java.sql.ResultSet getResultSet(Statement statement, Field[] fields, Vector tuples, String status, int
updateCount)throws SQLException
+
+
+
+
+
+ /** new nic method **/
+ public java.sql.ResultSet createResultSet (Statement statement)
{
- return new Jdbc3ResultSet(this, statement, fields, tuples, status, updateCount, 0, false);
+ return new Jdbc3ResultSet(this, statement);
}
}
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3ResultSet.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3ResultSet.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3ResultSet.java Wed Sep 11 05:38:45 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3ResultSet.java Thu Nov 14 23:13:35 2002
@@ -13,6 +13,13 @@
public class Jdbc3ResultSet extends org.postgresql.jdbc3.AbstractJdbc3ResultSet implements java.sql.ResultSet
{
+ Jdbc3ResultSet (org.postgresql.PGConnection conn, Statement statement)
+ {
+ super (conn, statement);
+ }
+
+
+
public Jdbc3ResultSet(Jdbc3Connection conn, Statement statement, Field[] fields, Vector tuples, String status, int
updateCount,long insertOID, boolean binaryCursor)
{
super(conn, statement, fields, tuples, status, updateCount, insertOID, binaryCursor);
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur
currentcvs/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java Mon Nov 11 07:11:12 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTest.java Fri Nov 15 09:31:03 2002
@@ -9,7 +9,7 @@
*
* PS: Do you know how difficult it is to type on a train? ;-)
*
- * $Id: DatabaseMetaDataTest.java,v 1.16 2002/11/11 07:11:12 barry Exp $
+ * $Id: DatabaseMetaDataTest.java,v 1.15.2.1 2002/11/11 07:30:08 barry Exp $
*/
public class DatabaseMetaDataTest extends TestCase
diff --exclude .#* --exclude Makefile --exclude build.xml --exclude filelis* --exclude Driver.java --exclude CVS
--exclude*.jar --exclude *.class --exclude *~ -Naur currentcvs/src/interfaces/jdbc/org/postgresql/util/Serialize.java
pgsql-cvs/src/interfaces/jdbc/org/postgresql/util/Serialize.java
--- currentcvs/src/interfaces/jdbc/org/postgresql/util/Serialize.java Tue Oct 1 00:39:02 2002
+++ pgsql-cvs/src/interfaces/jdbc/org/postgresql/util/Serialize.java Thu Nov 7 23:29:29 2002
@@ -405,7 +405,7 @@
if (Driver.logDebug)
Driver.debug("Serialize.store: " + sb.toString() );
- ResultSet rs = ((org.postgresql.jdbc1.AbstractJdbc1Connection)conn).ExecSQL(sb.toString());
+ ResultSet rs = ((org.postgresql.jdbc1.AbstractJdbc1Connection)conn).doQuery(sb.toString());
// fetch the OID for returning
if (update)
--=-=-=
Nic
--=-=-=--
Message-ID: <87fztyexea.fsf@pooh-sticks-bridge.tapsellferrier.co.uk> Lines: 24 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii --text follows this line-- Haris Peco <snpe@snpe.co.yu> writes: > I have tried. > DatabaseMetaData is fine, but ResultSet.[get|set]FetchSize don't > work What error do you get? > Prepared command don't work, but my greatest problem are trasnaction > I hope that cursor in 7.4 will be out of a transaction I don't think there's much I can do about the cursor problem. > Can Yoy yet another : > set driver's flag btw jdbc:pgsql:...?cursor=yes > for use cursor or old way What does everyone else think? Is a system doing a different query each time worth looking into? Nic
Nic, Here are my thoughts on this topic. 1) Since the server doesn't support cursors across transactions, I don't think the driver should either. In fact in jdbc3 the DatabaseMetaData object has a supportsResultSetHoldability() method that explicitly lets the driver tell the application what is does/doesn't support in this area. I think running multiple sql statements to mimic this behavior is a very bad idea. Since the select statements will run at different times they will return different data (since they will pick up commited changes between runs), and if you don't include an order by the results are completely unpredictable. If someone wants this very unpredictable behavior they can issue the multiple statements themselves. 2) I think the use of cursors should be optional. In fact since most queries don't need them since most queries return a small number of rows , I think the use of cursors needs to be turned on. I think there should be two ways to do this: the first is by setting the fetchSize() and the second would be a jdbc url parameter. 3) I think the transaction characteristics of the current patch are just fine and conform to the jdbc specification. The code should automatically close the resultset when a commit occurs. One thing that will be confusing is that noncursor based result sets will work accross commits, but cursor based ones won't. But I think that is reasonable. thanks, --Barry Nic Ferrier wrote: > Message-ID: <87fztyexea.fsf@pooh-sticks-bridge.tapsellferrier.co.uk> > Lines: 24 > MIME-Version: 1.0 > Content-Type: text/plain; charset=us-ascii > --text follows this line-- > Haris Peco <snpe@snpe.co.yu> writes: > > >>I have tried. >>DatabaseMetaData is fine, but ResultSet.[get|set]FetchSize don't >>work > > > What error do you get? > > >>Prepared command don't work, but my greatest problem are trasnaction >>I hope that cursor in 7.4 will be out of a transaction > > > I don't think there's much I can do about the cursor problem. > > > > >>Can Yoy yet another : >>set driver's flag btw jdbc:pgsql:...?cursor=yes >>for use cursor or old way > > > What does everyone else think? Is a system doing a different query > each time worth looking into? > > > Nic > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
On Monday 18 November 2002 05:14 pm, Barry Lind wrote: > Nic, > > Here are my thoughts on this topic. > > 1) Since the server doesn't support cursors across transactions, I don't > think the driver should either. In fact in jdbc3 the DatabaseMetaData > object has a supportsResultSetHoldability() method that explicitly lets > the driver tell the application what is does/doesn't support in this area. > > I think running multiple sql statements to mimic this behavior is a very > bad idea. Since the select statements will run at different times they > will return different data (since they will pick up commited changes > between runs), and if you don't include an order by the results are > completely unpredictable. If someone wants this very unpredictable > behavior they can issue the multiple statements themselves. > > 2) I think the use of cursors should be optional. In fact since most > queries don't need them since most queries return a small number of rows > , I think the use of cursors needs to be turned on. I think there > should be two ways to do this: the first is by setting the fetchSize() > and the second would be a jdbc url parameter. > > 3) I think the transaction characteristics of the current patch are just > fine and conform to the jdbc specification. The code should > automatically close the resultset when a commit occurs. One thing that > will be confusing is that noncursor based result sets will work accross > commits, but cursor based ones won't. But I think that is reasonable. My problem : master-detail I select one from many rows master with cursor (big table and only this is possible) - In detail I do change and commit (or rollback) My select is lost. How can I do that ? > Nic Ferrier wrote: > > Message-ID: <87fztyexea.fsf@pooh-sticks-bridge.tapsellferrier.co.uk> > > Lines: 24 > > MIME-Version: 1.0 > > Content-Type: text/plain; charset=us-ascii > > --text follows this line-- > > > > Haris Peco <snpe@snpe.co.yu> writes: > >>I have tried. > >>DatabaseMetaData is fine, but ResultSet.[get|set]FetchSize don't > >>work > > > > What error do you get? > > > >>Prepared command don't work, but my greatest problem are trasnaction > >>I hope that cursor in 7.4 will be out of a transaction > > > > I don't think there's much I can do about the cursor problem. > > > >>Can Yoy yet another : > >>set driver's flag btw jdbc:pgsql:...?cursor=yes > >>for use cursor or old way > > > > What does everyone else think? Is a system doing a different query > > each time worth looking into? > > > > > > Nic > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Haris, I understand your problem. But unless the database supports cursors that span transactions, I don't see any solution for you, other than to issue multiple sql statements to mimic cross transaction queries in your application. --Barry Haris Peco wrote: > On Monday 18 November 2002 05:14 pm, Barry Lind wrote: > >>Nic, >> >>Here are my thoughts on this topic. >> >>1) Since the server doesn't support cursors across transactions, I don't >>think the driver should either. In fact in jdbc3 the DatabaseMetaData >>object has a supportsResultSetHoldability() method that explicitly lets >>the driver tell the application what is does/doesn't support in this area. >> >>I think running multiple sql statements to mimic this behavior is a very >>bad idea. Since the select statements will run at different times they >>will return different data (since they will pick up commited changes >>between runs), and if you don't include an order by the results are >>completely unpredictable. If someone wants this very unpredictable >>behavior they can issue the multiple statements themselves. >> >>2) I think the use of cursors should be optional. In fact since most >>queries don't need them since most queries return a small number of rows >>, I think the use of cursors needs to be turned on. I think there >>should be two ways to do this: the first is by setting the fetchSize() >>and the second would be a jdbc url parameter. >> >>3) I think the transaction characteristics of the current patch are just >>fine and conform to the jdbc specification. The code should >>automatically close the resultset when a commit occurs. One thing that >>will be confusing is that noncursor based result sets will work accross >>commits, but cursor based ones won't. But I think that is reasonable. > > > My problem : > master-detail > I select one from many rows master with cursor (big table and only this is possible) > - In detail I do change and commit (or rollback) > My select is lost. > How can I do that ? > > >>Nic Ferrier wrote: >> >>>Message-ID: <87fztyexea.fsf@pooh-sticks-bridge.tapsellferrier.co.uk> >>>Lines: 24 >>>MIME-Version: 1.0 >>>Content-Type: text/plain; charset=us-ascii >>>--text follows this line-- >>> >>>Haris Peco <snpe@snpe.co.yu> writes: >>> >>>>I have tried. >>>>DatabaseMetaData is fine, but ResultSet.[get|set]FetchSize don't >>>>work >>> >>>What error do you get? >>> >>> >>>>Prepared command don't work, but my greatest problem are trasnaction >>>>I hope that cursor in 7.4 will be out of a transaction >>> >>>I don't think there's much I can do about the cursor problem. >>> >>> >>>>Can Yoy yet another : >>>>set driver's flag btw jdbc:pgsql:...?cursor=yes >>>>for use cursor or old way >>> >>>What does everyone else think? Is a system doing a different query >>>each time worth looking into? >>> >>> >>>Nic >>> >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 5: Have you checked our extensive FAQ? >>> >>>http://www.postgresql.org/users-lounge/docs/faq.html >> >>---------------------------(end of broadcast)--------------------------- >>TIP 6: Have you searched our list archives? >> >>http://archives.postgresql.org > > >
Thanks Barry I work with Oracle JDeveloper and I can't mimic cross transaction (access to database work JDeveloper and JDev work with Oracle, DB2, MS SQL and any SQL 92 compliant database). I hope that cursor that span transaction will be soon regards Haris Peco On Monday 18 November 2002 05:54 pm, Barry Lind wrote: > Haris, > > I understand your problem. But unless the database supports cursors > that span transactions, I don't see any solution for you, other than to > issue multiple sql statements to mimic cross transaction queries in your > application. > > --Barry > > Haris Peco wrote: > > On Monday 18 November 2002 05:14 pm, Barry Lind wrote: > >>Nic, > >> > >>Here are my thoughts on this topic. > >> > >>1) Since the server doesn't support cursors across transactions, I don't > >>think the driver should either. In fact in jdbc3 the DatabaseMetaData > >>object has a supportsResultSetHoldability() method that explicitly lets > >>the driver tell the application what is does/doesn't support in this > >> area. > >> > >>I think running multiple sql statements to mimic this behavior is a very > >>bad idea. Since the select statements will run at different times they > >>will return different data (since they will pick up commited changes > >>between runs), and if you don't include an order by the results are > >>completely unpredictable. If someone wants this very unpredictable > >>behavior they can issue the multiple statements themselves. > >> > >>2) I think the use of cursors should be optional. In fact since most > >>queries don't need them since most queries return a small number of rows > >>, I think the use of cursors needs to be turned on. I think there > >>should be two ways to do this: the first is by setting the fetchSize() > >>and the second would be a jdbc url parameter. > >> > >>3) I think the transaction characteristics of the current patch are just > >>fine and conform to the jdbc specification. The code should > >>automatically close the resultset when a commit occurs. One thing that > >>will be confusing is that noncursor based result sets will work accross > >>commits, but cursor based ones won't. But I think that is reasonable. > > > > My problem : > > master-detail > > I select one from many rows master with cursor (big table and only this > > is possible) - In detail I do change and commit (or rollback) > > My select is lost. > > How can I do that ? > > > >>Nic Ferrier wrote: > >>>Message-ID: <87fztyexea.fsf@pooh-sticks-bridge.tapsellferrier.co.uk> > >>>Lines: 24 > >>>MIME-Version: 1.0 > >>>Content-Type: text/plain; charset=us-ascii > >>>--text follows this line-- > >>> > >>>Haris Peco <snpe@snpe.co.yu> writes: > >>>>I have tried. > >>>>DatabaseMetaData is fine, but ResultSet.[get|set]FetchSize don't > >>>>work > >>> > >>>What error do you get? > >>> > >>>>Prepared command don't work, but my greatest problem are trasnaction > >>>>I hope that cursor in 7.4 will be out of a transaction > >>> > >>>I don't think there's much I can do about the cursor problem. > >>> > >>>>Can Yoy yet another : > >>>>set driver's flag btw jdbc:pgsql:...?cursor=yes > >>>>for use cursor or old way > >>> > >>>What does everyone else think? Is a system doing a different query > >>>each time worth looking into? > >>> > >>> > >>>Nic > >>> > >>> > >>>---------------------------(end of broadcast)--------------------------- > >>>TIP 5: Have you checked our extensive FAQ? > >>> > >>>http://www.postgresql.org/users-lounge/docs/faq.html > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 6: Have you searched our list archives? > >> > >>http://archives.postgresql.org
Haris Peco <snpe@snpe.co.yu> writes: > Thanks Barry > I work with Oracle JDeveloper and I can't mimic cross transaction > (access to database work JDeveloper and JDev work with Oracle, DB2, > MS SQL and any SQL 92 compliant database). > I hope that cursor that span transaction will be soon > I think it's a good idea too. I'm interested in learning more about the pgsql internals (apart fromthe jdbc stuff I'm also working on a guile plug in for stored procs). I'm going to take a look at the cursor issue sometime before christmas and see if I can deal with it. Hopefully, if I fix it, the pgsql team might accept my patch. Nic
Barry Lind <blind@xythos.com> writes: > Nic, > > Here are my thoughts on this topic. > > 1) Since the server doesn't support cursors across transactions, I don't > think the driver should either. In fact in jdbc3 the DatabaseMetaData > object has a supportsResultSetHoldability() method that explicitly lets > the driver tell the application what is does/doesn't support in this area. > > I think running multiple sql statements to mimic this behavior is a very > bad idea. Since the select statements will run at different times they > will return different data (since they will pick up commited changes > between runs), and if you don't include an order by the results are > completely unpredictable. If someone wants this very unpredictable > behavior they can issue the multiple statements themselves. And app developers can do that themselves if they want to - it's how I prefer to deal with large result sets within web apps. > 2) I think the use of cursors should be optional. In fact since most > queries don't need them since most queries return a small number of rows > , I think the use of cursors needs to be turned on. I think there > should be two ways to do this: the first is by setting the fetchSize() > and the second would be a jdbc url parameter. Ok. I can do that. > 3) I think the transaction characteristics of the current patch are just > fine and conform to the jdbc specification. The code should > automatically close the resultset when a commit occurs. One thing that > will be confusing is that noncursor based result sets will work accross > commits, but cursor based ones won't. But I think that is > reasonable. And when we get non-transactional cursors we'll be cooking on wood (better than gas). So you think it's more or less ok? What about the changes to the execution path? The reason I haven't done the PGRefResultSet patch yet is that I saw a way, using my new execution path, to reduce the number of classes that I need to provide the new facility. If you (and everyone else who needs to) approve the new execution path stuff then I can do the PGRefResultSet patch as a patch to my patch /8-> Nic
On Tue, 2002-11-19 at 02:14, Barry Lind wrote: > Nic, > > Here are my thoughts on this topic. > > 1) Since the server doesn't support cursors across transactions, I don't > think the driver should either. In fact in jdbc3 the DatabaseMetaData > object has a supportsResultSetHoldability() method that explicitly lets > the driver tell the application what is does/doesn't support in this area. > > I think running multiple sql statements to mimic this behavior is a very > bad idea. Since the select statements will run at different times they > will return different data (since they will pick up commited changes > between runs), and if you don't include an order by the results are > completely unpredictable. If someone wants this very unpredictable > behavior they can issue the multiple statements themselves. I agree with Barry here. > 2) I think the use of cursors should be optional. In fact since most > queries don't need them since most queries return a small number of rows > , I think the use of cursors needs to be turned on. I think there > should be two ways to do this: the first is by setting the fetchSize() > and the second would be a jdbc url parameter. Yes. Optional also gets my vote. The majority of usage that I have is for queries that return few rows. I'm not interested in the overhead of cursors being added to each of my queries, but I do like the idea of being able to explicity ask for the functionality when needed. As such setFetchSize() sounds like the right way to do it. For those that need it on all the time, the url parameter sounds like a pretty good way of doing it. > 3) I think the transaction characteristics of the current patch are just > fine and conform to the jdbc specification. The code should > automatically close the resultset when a commit occurs. One thing that > will be confusing is that noncursor based result sets will work accross > commits, but cursor based ones won't. But I think that is reasonable. Sounds reasonable to me as long as its clear to the programmer what type they are using. I definitely don't want to see the noncursor based resultsets closed, but I can't see a better solution for cursor based ones... Tom. -- Thomas O'Dowd, CEO, Nooper.com - Mobile Services Inc., Tokyo, Japan i-mode & FOMA consulting, development, testing: http://nooper.co.jp/
I have tried.
DatabaseMetaData is fine, but ResultSet.[get|set]FetchSize don't work
Prepared command don't work, but my greatest problem are trasnaction
I hope that cursor in 7.4 will be out of a transaction
Can Yoy yet another :
set driver's flag btw jdbc:pgsql:...?cursor=yes
for use cursor or old way
regards
Haris Peco
On Saturday 16 November 2002 11:47 pm, Nic Ferrier wrote:
> Message-ID: <87heehjcv3.fsf@pooh-sticks-bridge.tapsellferrier.co.uk>
> Lines: 19
> MIME-Version: 1.0
> Content-Type: multipart/mixed; boundary="=-=-="
> --text follows this line--
> --=-=-=
>
> snpe <snpe@snpe.co.yu> writes:
> > Hello Nic,
> > Please call patch with ;
> > diff -Naur olddir newdir
> > and add in message like attach
> > This is add PGResultSet
> > I can't open this patch
>
> Here's a diff based patch.
>
>
>
> --=-=-=
> Content-Type: text/patch
> Content-Disposition: attachment; filename=nic-diff
> Content-Description: patch for query stuff.
>
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/PGConnection.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/PGConnection.java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/PGConnection.java Fri Sep 6
> 21:23:05 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/PGConnection.java Thu Nov 7
> 20:38:44 2002 @@ -77,5 +77,15 @@
> public PGNotification[] getNotifications();
>
>
> +
> +
> + // Added by Nic.
> +
> + /** Create a result set for the specified statement.
> + * This is the factory method provided by the various
> + * version specific implementations of the JDBC connection
> + * classes.
> + */
> + public java.sql.ResultSet createResultSet (java.sql.Statement statement);
> }
>
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/PGResultSet.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/PGResultSet.java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/PGResultSet.java Thu Jan 1
> 00:00:00 1970 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/PGResultSet.java Sat Nov 2
> 01:18:43 2002 @@ -0,0 +1,16 @@
> +package org.postgresql;
> +
> +
> +import java.util.Vector;
> +
> +
> +/** PG base rep of a Res Set.
> + */
> +public interface PGResultSet
> +{
> +
> + /** The init method is what is used to put the data into a ResultSet.
> + */
> + public void init (Field[] fields, Vector tuples, String status,
> + int update_count, long insert_oid, boolean binaryCursor);
> +}
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/core/Encoding.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/core/Encoding.java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/core/Encoding.java Sat Nov 16
> 22:24:28 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/core/Encoding.java Fri Nov 15
> 09:30:46 2002 @@ -8,7 +8,7 @@
> /*
> * Converts to and from the character encoding used by the backend.
> *
> - * $Id: Encoding.java,v 1.8 2002/11/14 05:35:45 barry Exp $
> + * $Id: Encoding.java,v 1.7.2.1 2002/11/14 05:54:39 barry Exp $
> */
>
> public class Encoding
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java Sat
> Nov 16 22:24:28 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java Fri
> Nov 15 09:30:50 2002 @@ -13,17 +13,61 @@
> * <p>The lifetime of a QueryExecutor object is from sending the query
> * until the response has been received from the backend.
> *
> - * $Id: QueryExecutor.java,v 1.17 2002/11/14 05:35:45 barry Exp $
> + * $Id: QueryExecutor.java,v 1.16.2.1 2002/11/14 05:54:39 barry Exp $
> */
>
> public class QueryExecutor
> {
> + /** nic version - statically sets up the QE correctly.
> + */
> + public static void execute (String[] p_sqlFrags,
> + Object[] p_binds,
> + java.sql.Statement statement,
> + java.sql.ResultSet rs,
> + PG_Stream pg_stream,
> + java.sql.Connection con)
> + throws SQLException
> + {
> + QueryExecutor qe = new QueryExecutor();
> + // I think this should be a PGConnection and we should move
> + // everything we need for that from AbstractJdbc1... into PGConnection.
> + qe.connection = (org.postgresql.jdbc1.AbstractJdbc1Connection)con;
> + qe.m_sqlFrags = p_sqlFrags;
> + qe.m_binds = p_binds;
> + qe.statement = statement;
> + // Nic says: connection should wrap pg_stream.
> + qe.pg_stream = pg_stream;
> + if (statement != null)
> + qe.maxRows = statement.getMaxRows();
> + else
> + qe.maxRows = 0;
> + // The result set.
> + qe.rs = rs;
> + qe.execute();
> + }
> +
> + // This is the result set used to wrap the results.
> + // The type of this is whatever is passed into the static above.
> + private java.sql.ResultSet rs;
> +
> + // cons for the static above.
> + private QueryExecutor ()
> + {
> + }
> +
> +
> +
> +
> + /*** pre-nic implementation ***/
> +
> +
> + /// Nic has removed the final from ALL of these (to facilitate static
> method). + private String[] m_sqlFrags;
> + private Object[] m_binds;
> + private java.sql.Statement statement;
> + private PG_Stream pg_stream;
> + private org.postgresql.jdbc1.AbstractJdbc1Connection connection;
>
> - private final String[] m_sqlFrags;
> - private final Object[] m_binds;
> - private final java.sql.Statement statement;
> - private final PG_Stream pg_stream;
> - private final org.postgresql.jdbc1.AbstractJdbc1Connection connection;
>
> public QueryExecutor(String[] p_sqlFrags, Object[] p_binds,
> java.sql.Statement statement,
> @@ -33,7 +77,7 @@
> {
> this.m_sqlFrags = p_sqlFrags;
> this.m_binds = p_binds;
> - this.statement = statement;
> + this.statement = statement;
> this.pg_stream = pg_stream;
> this.connection =
> (org.postgresql.jdbc1.AbstractJdbc1Connection)connection;
>
> @@ -43,6 +87,7 @@
> maxRows = 0;
> }
>
> +
> private Field[] fields = null;
> private Vector tuples = new Vector();
> private boolean binaryCursor = false;
> @@ -51,8 +96,10 @@
> private long insert_oid = 0;
> private int maxRows;
>
> +
> /*
> * Execute a query on the backend.
> + *
> */
> public java.sql.ResultSet execute() throws SQLException
> {
> @@ -130,7 +177,12 @@
> if ( errorMessage != null )
> throw new SQLException( errorMessage.toString() );
>
> - return connection.getResultSet(statement, fields, tuples, status,
> update_count, insert_oid, binaryCursor); +
> + // Nic changes.
> + PGResultSet resSet = ((PGResultSet)rs);
> + // System.out.println(getClass().getName() + " resSet=" + resSet);
> + resSet.init(fields, tuples, status, update_count, insert_oid,
> binaryCursor); + return rs;
> }
> }
>
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection
>.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.
>java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection
>.java Sat Nov 16 22:24:29 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.
>java Fri Nov 15 09:34:39 2002 @@ -14,7 +14,7 @@
> import org.postgresql.util.*;
>
>
> -/* $Header:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Abs
>tractJdbc1Connection.java,v 1.13 2002/11/14 05:35:45 barry Exp $ +/*
> $Header:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Abs
>tractJdbc1Connection.java,v 1.12.2.1 2002/11/14 05:54:39 barry Exp $ * This
> class defines methods of the jdbc1 specification. This class is * extended
> by org.postgresql.jdbc2.AbstractJdbc2Connection which adds the jdbc2 *
> methods. The real Connection class (for jdbc1) is
> org.postgresql.jdbc1.Jdbc1Connection @@ -350,9 +350,10 @@
> // Set datestyle and fetch db encoding in a single call, to avoid making
> // more than one round trip to the backend during connection startup.
>
> - java.sql.ResultSet resultSet =
> - ExecSQL("set datestyle to 'ISO'; select version(), " + encodingQuery +
> ";");
>
> + java.sql.ResultSet resultSet
> + = doQuery("set datestyle to 'ISO'; select version(), " + encodingQuery
> + ";"); +
> if (! resultSet.next())
> {
> throw new PSQLException("postgresql.con.failed", "failed getting
> backend encoding"); @@ -373,7 +374,7 @@
> if (haveMinimumServerVersion("7.3"))
> {
> java.sql.ResultSet acRset =
> - ExecSQL("set client_encoding = 'UNICODE'; show autocommit");
> + doQuery("set client_encoding = 'UNICODE'; show autocommit");
>
> //set encoding to be unicode
> encoding = Encoding.getEncoding("UNICODE", null);
> @@ -388,7 +389,7 @@
> //to make the setting permanent
> if (acRset.getString(1).equals("off"))
> {
> - ExecSQL("set autocommit = on; commit;");
> + doQuery("set autocommit = on; commit;");
> }
> }
>
> @@ -409,13 +410,6 @@
> return this_driver;
> }
>
> - // These methods used to be in the main Connection implementation. As
> they - // are common to all implementations (JDBC1 or 2), they are placed
> here. - // This should make it easy to maintain the two specifications.
> -
> - public abstract java.sql.ResultSet getResultSet(Statement statement,
> org.postgresql.Field[] fields, Vector tuples, String status, int
> updateCount, long insertOID, boolean binaryCursor) throws SQLException; -
> - public abstract java.sql.ResultSet getResultSet(Statement statement,
> org.postgresql.Field[] fields, Vector tuples, String status, int
> updateCount) throws SQLException;
>
> /*
> * This adds a warning to the warning chain.
> @@ -445,66 +439,40 @@
> //}
> }
>
> - /*
> - * Send a query to the backend. Returns one of the ResultSet
> - * objects.
> - *
> - * <B>Note:</B> there does not seem to be any method currently
> - * in existance to return the update count.
> - *
> - * @param sql the SQL statement to be executed
> - * @return a ResultSet holding the results
> - * @exception SQLException if a database error occurs
> + /** Simple query execution.
> */
> - public java.sql.ResultSet ExecSQL(String sql) throws SQLException
> + public java.sql.ResultSet doQuery (String s) throws SQLException
> {
> - return ExecSQL(sql, null);
> + final Object[] nullarr = new Object[0];
> + java.sql.Statement stat = createStatement();
> + java.sql.ResultSet rs = createResultSet(stat);
> + execSQL(new String[] { s }, nullarr, stat, rs);
> + return rs;
> }
>
> - /*
> - * Send a query to the backend. Returns one of the ResultSet
> - * objects.
> - *
> - * <B>Note:</B> there does not seem to be any method currently
> - * in existance to return the update count.
> - *
> - * @param sql the SQL statement to be executed
> - * @param stat The Statement associated with this query (may be null)
> - * @return a ResultSet holding the results
> - * @exception SQLException if a database error occurs
> - */
> - public java.sql.ResultSet ExecSQL(String sql, java.sql.Statement stat)
> throws SQLException - {
> - if (isClosed())
> - {
> - throw new PSQLException("postgresql.con.closed");
> - }
> - return new QueryExecutor(new String[] {sql}, EMPTY_OBJECT_ARRAY, stat,
> pg_stream, (java.sql.Connection)this).execute(); - }
> - private static final Object[] EMPTY_OBJECT_ARRAY = new Object[0];
> -
> - /*
> - * Send a query to the backend. Returns one of the ResultSet
> - * objects.
> - *
> - * <B>Note:</B> there does not seem to be any method currently
> - * in existance to return the update count.
> + /** Advanced query execution.
> *
> * @param p_sqlFragmentss the SQL statement parts to be executed
> * @param p_binds the SQL bind values
> - * @param stat The Statement associated with this query (may be null)
> - * @return a ResultSet holding the results
> + * @param stat the statement associated with this query.
> + * @param rs the ResultSet which will be initied for the results.
> * @exception SQLException if a database error occurs
> */
> - public java.sql.ResultSet ExecSQL(String[] p_sqlFragments, Object[]
> p_binds, java.sql.Statement stat) throws SQLException - {
> - if (isClosed())
> - {
> - throw new PSQLException("postgresql.con.closed");
> - }
> - return new QueryExecutor(p_sqlFragments, p_binds, stat, pg_stream,
> (java.sql.Connection)this).execute(); + public void execSQL(String[]
> p_sqlFragments,
> + Object[] p_binds,
> + java.sql.Statement stat,
> + java.sql.ResultSet rs)
> + throws SQLException
> + {
> + QueryExecutor.execute(p_sqlFragments,
> + p_binds,
> + stat,
> + rs,
> + pg_stream,
> + (java.sql.Connection)this);
> }
>
> +
> /*
> * In SQL, a result table can be retrieved through a cursor that
> * is named. The current row of a result can be updated or deleted
> @@ -939,27 +907,27 @@
> //We do the select to ensure a transaction is in process
> //before we do the commit to avoid warning messages
> //from issuing a commit without a transaction in process
> - ExecSQL("select 1; commit; set autocommit = on;");
> + doQuery("select 1; commit; set autocommit = on;");
> }
> else
> {
> - ExecSQL("end");
> + doQuery("end");
> }
> }
> else
> {
> if (haveMinimumServerVersion("7.3"))
> {
> - ExecSQL("set autocommit = off; " + getIsolationLevelSQL());
> + doQuery("set autocommit = off; " + getIsolationLevelSQL());
> }
> else if (haveMinimumServerVersion("7.1"))
> {
> - ExecSQL("begin;" + getIsolationLevelSQL());
> + doQuery("begin;" + getIsolationLevelSQL());
> }
> else
> {
> - ExecSQL("begin");
> - ExecSQL(getIsolationLevelSQL());
> + doQuery("begin");
> + doQuery(getIsolationLevelSQL());
> }
> }
> this.autoCommit = autoCommit;
> @@ -993,17 +961,17 @@
> return ;
> if (haveMinimumServerVersion("7.3"))
> {
> - ExecSQL("commit; " + getIsolationLevelSQL());
> + doQuery("commit; " + getIsolationLevelSQL());
> }
> else if (haveMinimumServerVersion("7.1"))
> {
> - ExecSQL("commit;begin;" + getIsolationLevelSQL());
> + doQuery("commit;begin;" + getIsolationLevelSQL());
> }
> else
> {
> - ExecSQL("commit");
> - ExecSQL("begin");
> - ExecSQL(getIsolationLevelSQL());
> + doQuery("commit");
> + doQuery("begin");
> + doQuery(getIsolationLevelSQL());
> }
> }
>
> @@ -1024,17 +992,17 @@
> //we don't automatically start a transaction
> //but let the server functionality automatically start
> //one when the first statement is executed
> - ExecSQL("rollback; " + getIsolationLevelSQL());
> + doQuery("rollback; " + getIsolationLevelSQL());
> }
> else if (haveMinimumServerVersion("7.1"))
> {
> - ExecSQL("rollback; begin;" + getIsolationLevelSQL());
> + doQuery("rollback; begin;" + getIsolationLevelSQL());
> }
> else
> {
> - ExecSQL("rollback");
> - ExecSQL("begin");
> - ExecSQL(getIsolationLevelSQL());
> + doQuery("rollback");
> + doQuery("begin");
> + doQuery(getIsolationLevelSQL());
> }
> }
>
> @@ -1049,14 +1017,14 @@
> String sql = "show transaction isolation level";
> String level = null;
> if (haveMinimumServerVersion("7.3")) {
> - ResultSet rs = ExecSQL(sql);
> + ResultSet rs = doQuery(sql);
> if (rs.next()) {
> level = rs.getString(1);
> }
> rs.close();
> } else {
> clearWarnings();
> - ExecSQL(sql);
> + doQuery(sql);
> SQLWarning warning = getWarnings();
> if (warning != null)
> {
> @@ -1121,7 +1089,7 @@
> new Integer(isolationLevel));
> }
> }
> - ExecSQL(isolationLevelSQL);
> + doQuery(isolationLevelSQL);
> }
>
> /*
> @@ -1264,7 +1232,7 @@
> } else {
> sql = "SELECT typname FROM pg_type WHERE oid = " +oid;
> }
> - ResultSet result = ExecSQL(sql);
> + ResultSet result = doQuery(sql);
> if (((AbstractJdbc1ResultSet)result).getColumnCount() != 1 ||
> ((AbstractJdbc1ResultSet)result).getTupleCount() != 1) { throw new
> PSQLException("postgresql.unexpected");
> }
> @@ -1305,7 +1273,7 @@
> } else {
> sql = "SELECT oid FROM pg_type WHERE typname='" + typeName + "'";
> }
> - ResultSet result = ExecSQL(sql);
> + ResultSet result = doQuery(sql);
> if (((AbstractJdbc1ResultSet)result).getColumnCount() != 1 ||
> ((AbstractJdbc1ResultSet)result).getTupleCount() != 1) throw new
> PSQLException("postgresql.unexpected");
> result.next();
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMe
>taData.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMet
>aData.java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMe
>taData.java Mon Nov 11 07:11:12 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMet
>aData.java Fri Nov 15 09:31:00 2002 @@ -1913,7 +1913,9 @@
> }
> rs.close();
>
> - return connection.getResultSet(null, f, v, "OK", 1);
> + java.sql.ResultSet retRs =
> connection.createResultSet(connection.createStatement());
> + ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> + return retRs;
> }
>
> /*
> @@ -2199,7 +2201,10 @@
> tuple[0] = types[i].getBytes();
> v.addElement(tuple);
> }
> - return connection.getResultSet(null, f, v, "OK", 1);
> +
> + java.sql.ResultSet retRs =
> connection.createResultSet(connection.createStatement());
> + ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> + return retRs;
> }
>
> /*
> @@ -2366,7 +2371,9 @@
> }
> rs.close();
>
> - return connection.getResultSet(null, f, v, "OK", 1);
> + java.sql.ResultSet retRs =
> connection.createResultSet(connection.createStatement());
> + ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> + return retRs;
> }
>
> /*
> @@ -2478,7 +2485,10 @@
> }
> }
> rs.close();
> - return connection.getResultSet(null, f, v, "OK", 1);
> +
> + java.sql.ResultSet retRs =
> connection.createResultSet(connection.createStatement());
> + ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> + return retRs;
> }
>
> /*
> @@ -2580,7 +2590,9 @@
> }
> rs.close();
>
> - return connection.getResultSet(null, f, v, "OK", 1);
> + java.sql.ResultSet retRs =
> connection.createResultSet(connection.createStatement());
> + ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> + return retRs;
> }
>
> private static void sortStringArray(String s[]) {
> @@ -2773,7 +2785,10 @@
> tuple[7] =
> Integer.toString(java.sql.DatabaseMetaData.bestRowNotPseudo).getBytes();
> v.addElement(tuple);
> }
> - return connection.getResultSet(null, f, v, "OK", 1);
> +
> + java.sql.ResultSet retRs =
> connection.createResultSet(connection.createStatement());
> + ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> + return retRs;
> }
>
> /*
> @@ -2843,7 +2858,9 @@
> /* Perhaps we should check that the given
> * catalog.schema.table actually exists. -KJ
> */
> - return connection.getResultSet(null, f, v, "OK", 1);
> + java.sql.ResultSet retRs =
> connection.createResultSet(connection.createStatement());
> + ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> + return retRs;
> }
>
> /*
> @@ -3193,7 +3210,9 @@
> tuples.addElement(tuple);
> }
>
> - return connection.getResultSet(null, f, tuples, "OK", 1);
> + java.sql.ResultSet retRs =
> connection.createResultSet(connection.createStatement());
> + ((AbstractJdbc1ResultSet)retRs).init(f, tuples, "OK", 1, 0, false);
> + return retRs;
> }
>
> /*
> @@ -3478,7 +3497,10 @@
> v.addElement(tuple);
> }
> rs.close();
> - return connection.getResultSet(null, f, v, "OK", 1);
> +
> + java.sql.ResultSet retRs =
> connection.createResultSet(connection.createStatement());
> + ((AbstractJdbc1ResultSet)retRs).init(f, v, "OK", 1, 0, false);
> + return retRs;
> }
>
> /*
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.
>java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.j
>ava ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.
>java Sat Oct 19 22:10:36 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.j
>ava Fri Nov 15 23:29:26 2002 @@ -18,7 +18,7 @@
> * extended by org.postgresql.jdbc2.AbstractJdbc2ResultSet which adds the
> jdbc2 * methods. The real ResultSet class (for jdbc1) is
> org.postgresql.jdbc1.Jdbc1ResultSet */
> -public abstract class AbstractJdbc1ResultSet
> +public abstract class AbstractJdbc1ResultSet implements
> org.postgresql.PGResultSet {
>
> protected Vector rows; // The results
> @@ -42,7 +42,13 @@
> public byte[][] rowBuffer = null;
>
>
> - public AbstractJdbc1ResultSet(org.postgresql.PGConnection conn, Statement
> statement, Field[] fields, Vector tuples, String status, int updateCount,
> long insertOID, boolean binaryCursor) + public
> AbstractJdbc1ResultSet(org.postgresql.PGConnection conn,
> + Statement statement,
> + Field[] fields,
> + Vector tuples,
> + String status,
> + int updateCount,
> + long insertOID, boolean binaryCursor)
> {
> this.connection = conn;
> this.statement = statement;
> @@ -50,6 +56,9 @@
> this.rows = tuples;
> this.status = status;
> this.updateCount = updateCount;
> +
> + System.out.println(getClass().getName() + " updateCount = " +
> updateCount); +
> this.insertOID = insertOID;
> this.this_row = null;
> this.current_row = -1;
> @@ -57,13 +66,63 @@
> }
>
>
> + /*** nic constructor: called by superclass which is called from
> Jdbc1Connection.createResultSet ***/ + public
> AbstractJdbc1ResultSet(org.postgresql.PGConnection conn,
> + Statement statement)
> + {
> + this.connection = conn;
> + this.statement = statement;
> + }
> +
> + /*** nic initializer. ***/
> + public void init (Field[] fields, Vector tuples, String status,
> + int updateCount, long insertOID, boolean binaryCursor)
> + {
> + this.fields = fields;
> +
> + // on a reinit the size of this indicates how many we pulled
> + // back. If it's 0 then the res set has ended.
> + this.rows = tuples;
> + this.status = status;
> + this.updateCount = updateCount;
> + this.insertOID = insertOID;
> + this.this_row = null;
> + this.current_row = -1;
> + this.binaryCursor = binaryCursor;
> + }
> +
> +
> +
> +
> + // This slightly altered by nic.
> public boolean next() throws SQLException
> {
> if (rows == null)
> throw new PSQLException("postgresql.con.closed");
>
> if (++current_row >= rows.size())
> - return false;
> + {
> + int fetchSize = ((AbstractJdbc1Statement)statement).fetchSize;
> + // Must be false if we weren't batching.
> + if (fetchSize == 0)
> + return false;
> + // Use the ref to the statement to get
> + // the details we need to do another cursor
> + // query - it will use reinit() to repopulate this
> + // with the right data.
> + String[] sql = new String[1];
> + String[] binds = new String[0];
> + // Is this the correct query???
> + String cursorName =
> ((AbstractJdbc1Statement)statement).m_statementName; + sql[0] = "FETCH
> FORWARD " + fetchSize + " FROM " + cursorName + " ;";
> + ((AbstractJdbc1Connection)connection).execSQL(sql, binds, statement,
> (java.sql.ResultSet)this); +
> + // Test the new rows array.
> + if (rows.size() == 0)
> + return false;
> + // Otherwise reset the counter and let it go on...
> + current_row = 0;
> + }
>
> this_row = (byte [][])rows.elementAt(current_row);
>
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.
>java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.j
>ava ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.
>java Sat Nov 16 22:24:29 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.j
>ava Fri Nov 15 23:12:53 2002 @@ -8,7 +8,7 @@
> import org.postgresql.largeobject.*;
> import org.postgresql.util.*;
>
> -/* $Header:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Abs
>tractJdbc1Statement.java,v 1.13 2002/11/14 05:35:45 barry Exp $ +/* $Header:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Abs
>tractJdbc1Statement.java,v 1.12.2.1 2002/11/14 05:54:39 barry Exp $ * This
> class defines methods of the jdbc1 specification. This class is * extended
> by org.postgresql.jdbc2.AbstractJdbc2Statement which adds the jdbc2 *
> methods. The real Statement class (for jdbc1) is
> org.postgresql.jdbc1.Jdbc1Statement @@ -25,6 +25,9 @@
> /** Maximum number of rows to return, 0 = unlimited */
> protected int maxrows = 0;
>
> + /** Number of rows to get in a batch. */
> + protected int fetchSize = 0;
> +
> /** Timeout (in seconds) for a query (not used) */
> protected int timeout = 0;
>
> @@ -48,7 +51,7 @@
> private String[] m_executeSqlFragments;
> protected Object[] m_binds = new Object[0];
> private String[] m_bindTypes = new String[0];
> - private String m_statementName = null;
> + protected String m_statementName = null;
> private boolean m_useServerPrepare = false;
> private static int m_preparedCount = 1;
>
> @@ -117,7 +120,7 @@
>
> }
>
> -
> +
> /*
> * Execute a SQL statement that retruns a single ResultSet
> *
> @@ -133,7 +136,7 @@
> //If we have already created a server prepared statement, we need
> //to deallocate the existing one
> if (m_statementName != null) {
> - ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " +
> m_statementName);
> + ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " +
> m_statementName); m_statementName = null;
> m_origSqlFragments = null;
> m_executeSqlFragments = null;
> @@ -150,7 +153,10 @@
> */
> public java.sql.ResultSet executeQuery() throws SQLException
> {
> - this.execute();
> + if (fetchSize > 0)
> + this.nic_execute();
> + else
> + this.execute();
> while (result != null &&
> !((AbstractJdbc1ResultSet)result).reallyResultSet()) result =
> ((AbstractJdbc1ResultSet)result).getNext();
> if (result == null)
> @@ -175,7 +181,7 @@
> //If we have already created a server prepared statement, we need
> //to deallocate the existing one
> if (m_statementName != null) {
> - ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " +
> m_statementName);
> + ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " +
> m_statementName); m_statementName = null;
> m_origSqlFragments = null;
> m_executeSqlFragments = null;
> @@ -219,7 +225,7 @@
> //If we have already created a server prepared statement, we need
> //to deallocate the existing one
> if (m_statementName != null) {
> - ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " +
> m_statementName);
> + ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " +
> m_statementName); m_statementName = null;
> m_origSqlFragments = null;
> m_executeSqlFragments = null;
> @@ -317,7 +323,9 @@
> }
>
> // New in 7.1, pass Statement so that ExecSQL can customise to it
> - result = ((AbstractJdbc1Connection)connection).ExecSQL(m_sqlFragments,
> m_binds, (java.sql.Statement)this); + result =
> ((AbstractJdbc1Connection)connection).createResultSet((java.sql.Statement)t
>his); + ((AbstractJdbc1Connection)connection).execSQL(m_sqlFragments,
> m_binds, + (java.sql.Statement)this, result);
>
> //If we are executing a callable statement function set the return data
> if (isFunction)
> @@ -341,6 +349,110 @@
> }
> }
>
> + /** version of execute which converts the query to a cursor.
> + */
> + public boolean nic_execute() throws SQLException
> + {
> + if (isFunction && !returnTypeSet)
> + throw new PSQLException("postgresql.call.noreturntype");
> + if (isFunction)
> + { // set entry 1 to dummy entry..
> + m_binds[0] = ""; // dummy entry which ensured that no one overrode
> + m_bindTypes[0] = PG_TEXT;
> + // and calls to setXXX (2,..) really went to first arg in a function
> call.. + }
> +
> + // New in 7.1, if we have a previous resultset then force it to close
> + // This brings us nearer to compliance, and helps memory management.
> + // Internal stuff will call ExecSQL directly, bypassing this.
> + if (result != null)
> + {
> + java.sql.ResultSet rs = getResultSet();
> + if (rs != null)
> + rs.close();
> + }
> +
> + // I've pretty much ignored server prepared statements... can declare
> and prepare be + // used together?
> + // It's trivial to change this: you just have to resolve this issue
> + // of how to work out whether there's a function call. If there isn't
> then the first + // element of the array must be the bit that you extend
> to become the cursor + // decleration.
> + // The last thing that can go wrong is when the user supplies a cursor
> statement + // directly: the translation takes no account of that. I think
> we should just look + // for declare and stop the translation if we find
> it.
> +
> + // The first thing to do is transform the statement text into the cursor
> form. + String[] origSqlFragments = m_sqlFragments;
> + if (origSqlFragments.length > 1)
> + m_sqlFragments = new String[origSqlFragments.length + 1];
> + else
> + m_sqlFragments = new String[origSqlFragments.length];
> + System.arraycopy(origSqlFragments, 0, m_sqlFragments, 0,
> origSqlFragments.length); + // Pinch the prepared count for our own
> nefarious purposes.
> + m_statementName = "JDBC_CURS_" + m_preparedCount++;
> + // The static bit to prepend to all querys.
> + String cursDecl = "BEGIN; DECLARE " + m_statementName + " CURSOR FOR ";
> + String endCurs = " FETCH FORWARD " + fetchSize + " FROM " +
> m_statementName + ";"; +
> + // Add the real query to the curs decleration.
> + // This is the bit that really makes the presumption about
> + // m_sqlFragments not being a function call.
> + if (m_sqlFragments.length < 1)
> + m_sqlFragments[0] = cursDecl + "SELECT NULL;";
> +
> + else if (m_sqlFragments.length < 2)
> + {
> + if (m_sqlFragments[0].endsWith(";"))
> + m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + endCurs;
> + else
> + m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + ";" + endCurs;
> + }
> + else
> + {
> + m_sqlFragments[0] = cursDecl + m_sqlFragments[0];
> + if (m_sqlFragments[m_sqlFragments.length - 2].endsWith(";"))
> + m_sqlFragments[m_sqlFragments.length - 1] = endCurs;
> + else
> + m_sqlFragments[m_sqlFragments.length - 1] = ";" + endCurs;
> + }
> +
> + // Make the call to the query executor.
> + AbstractJdbc1Connection execr = (AbstractJdbc1Connection)connection;
> + java.sql.Statement st = (java.sql.Statement)this;
> + result = (java.sql.ResultSet) execr.createResultSet(st);
> + // Nic says:
> + // we don't need to collect the result here, rs is altered to
> + // be the result set so "result = rs" would do ok after this call.
> + execr.execSQL(m_sqlFragments, m_binds, st, result);
> +
> + //If we are executing a callable statement function set the return data
> + if (isFunction)
> + {
> + if (!((AbstractJdbc1ResultSet)result).reallyResultSet())
> + throw new PSQLException("postgresql.call.noreturnval");
> + if (!result.next ())
> + throw new PSQLException ("postgresql.call.noreturnval");
> + callResult = result.getObject(1);
> + int columnType = result.getMetaData().getColumnType(1);
> + if (columnType != functionReturnType)
> + {
> + Object[] arr =
> + { "java.sql.Types=" + columnType,
> + "java.sql.Types=" + functionReturnType
> + };
> + throw new PSQLException ("postgresql.call.wrongrtntype",arr);
> + }
> + result.close ();
> + return true;
> + }
> + else
> + {
> + return (result != null &&
> ((AbstractJdbc1ResultSet)result).reallyResultSet()); + }
> + }
> +
> +
> /*
> * setCursorName defines the SQL cursor name that will be used by
> * subsequent execute methods. This name can then be used in SQL
> @@ -593,7 +705,7 @@
>
> // If using server prepared statements deallocate them
> if (m_useServerPrepare && m_statementName != null) {
> - ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " +
> m_statementName);
> + ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " +
> m_statementName); }
>
> // Disasociate it from us (For Garbage Collection)
> @@ -1912,7 +2024,7 @@
> //If turning server prepared statements off deallocate statement
> //and reset statement name
> if (m_useServerPrepare != flag && !flag)
> - ((AbstractJdbc1Connection)connection).ExecSQL("DEALLOCATE " +
> m_statementName);
> + ((AbstractJdbc1Connection)connection).doQuery("DEALLOCATE " +
> m_statementName); m_statementName = null;
> m_useServerPrepare = flag;
> } else {
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Connection.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Connection.java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Connection.java Fr
>i Sep 6 21:23:06 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Connection.java Thu
> Nov 7 22:27:15 2002 @@ -46,6 +46,12 @@
> return new Jdbc1ResultSet(this, stat, fields, tuples, status,
> updateCount, 0, false); }
>
> +
> + public java.sql.ResultSet createResultSet (java.sql.Statement stat)
> throws SQLException + {
> + // This needs doing.
> + return null;
> + }
> }
>
>
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection
>.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.
>java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection
>.java Fri Sep 6 21:23:06 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Connection.
>java Sat Oct 12 19:37:33 2002 @@ -6,6 +6,7 @@
> import java.sql.*;
> import org.postgresql.util.PSQLException;
>
> +
> /* $Header:
> /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Abs
>tractJdbc2Connection.java,v 1.2 2002/09/06 21:23:06 momjian Exp $ * This
> class defines methods of the jdbc2 specification. This class extends *
> org.postgresql.jdbc1.AbstractJdbc1Connection which provides the jdbc1 @@
> -17,7 +18,7 @@
> * The current type mappings
> */
> protected java.util.Map typemap;
> -
> +
> public java.sql.Statement createStatement() throws SQLException
> {
> // The spec says default of TYPE_FORWARD_ONLY but everyone is used to
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.
>java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.j
>ava ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.
>java Mon Nov 4 06:42:33 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.j
>ava Fri Nov 15 09:52:10 2002 @@ -38,6 +38,11 @@
> protected PreparedStatement deleteStatement = null;
> private PreparedStatement selectStatement = null;
>
> +
> + AbstractJdbc2ResultSet (org.postgresql.PGConnection conn, Statement
> statement) + {
> + super(conn, statement);
> + }
>
>
> public AbstractJdbc2ResultSet(org.postgresql.PGConnection conn, Statement
> statement, Field[] fields, Vector tuples, String status, int updateCount,
> long insertOID, boolean binaryCursor) @@ -142,6 +147,14 @@
> {
> return getString(columnIndex);
> }
> + else if (type.equals("refcursor"))
> + {
> + // We must return a ResultSet with the results packaged.
> + // We should probably check that auto commit is turned off.
> + String cursorName = getString(columnIndex);
> + // return new RefCursorResultSet(cursorName);
> + return null;
> + }
> else
> {
> return connection.getObject(field.getPGType(),
> getString(columnIndex)); @@ -366,9 +379,7 @@
>
> public int getFetchSize() throws SQLException
> {
> - // In this implementation we return the entire result set, so
> - // here return the number of rows we have. Sub-classes can return a
> proper - // value
> + // Returning the current batch size seems the right thing to do.
> return rows.size();
> }
>
> @@ -754,7 +765,6 @@
> }
>
> updateValue(columnIndex, theData);
> -
> }
>
>
> @@ -787,7 +797,6 @@
> throw new PSQLException("postgresql.updateable.ioerror" + ie);
> }
> updateValue(columnIndex, theData);
> -
> }
>
>
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.
>java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.j
>ava ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.
>java Wed Oct 30 04:33:29 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.j
>ava Fri Nov 15 09:47:27 2002 @@ -115,7 +115,7 @@
>
> public int getFetchSize() throws SQLException
> {
> - return 0;
> + return super.fetchSize;
> }
>
> public int getResultSetConcurrency() throws SQLException
> @@ -130,12 +130,14 @@
>
> public void setFetchDirection(int direction) throws SQLException
> {
> - throw org.postgresql.Driver.notImplemented();
> + // I don't think this should happen, since it's a hint it should just
> + // fail quietly.
> + // throw org.postgresql.Driver.notImplemented();
> }
>
> public void setFetchSize(int rows) throws SQLException
> {
> - throw org.postgresql.Driver.notImplemented();
> + super.fetchSize = rows;
> }
>
> public void setResultSetConcurrency(int value) throws SQLException
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/Array.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/Array.java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/Array.java Fri Sep 6
> 21:23:06 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/Array.java Thu Nov 7
> 23:32:30 2002 @@ -343,7 +343,10 @@
> default:
> throw org.postgresql.Driver.notImplemented();
> }
> - return ((AbstractJdbc2Connection)conn).getResultSet(null, fields, rows,
> "OK", 1 ); + java.sql.Statement stat =
> ((AbstractJdbc2Connection)conn).createStatement(); + java.sql.ResultSet
> retRs = ((AbstractJdbc2Connection)conn).createResultSet(stat);
> + ((AbstractJdbc2ResultSet)retRs).init(fields, rows, "OK", 1, 0, false);
> + return retRs;
> }
>
> public String toString()
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Connection.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Connection.java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Connection.java Fr
>i Sep 6 21:23:06 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Connection.java Thu
> Nov 7 23:29:13 2002 @@ -46,6 +46,38 @@
> return metadata;
> }
>
> +
> +
> + // the new method override which re-inits existing resultsets
> + public java.sql.ResultSet getResultSet(java.sql.Statement stat,
> + org.postgresql.jdbc1.AbstractJdbc1ResultSet rs,
> + Field[] fields,
> + Vector tuples,
> + String status,
> + int updateCount, long insertOID, boolean binaryCursor)
> + throws SQLException
> + {
> + if (rs == null)
> + return new Jdbc2ResultSet(this, stat, fields,
> + tuples, status,
> + updateCount, insertOID, binaryCursor);
> + else
> + {
> + rs.init(fields, tuples, status, updateCount, insertOID, binaryCursor);
> + return (java.sql.ResultSet) rs;
> + }
> + }
> +
> +
> +
> +
> + /** new nic method **/
> + public java.sql.ResultSet createResultSet (Statement statement)
> + {
> + return new Jdbc2ResultSet(this, statement);
> + }
> +
> +
> public java.sql.ResultSet getResultSet(Statement statement, Field[]
> fields, Vector tuples, String status, int updateCount, long insertOID,
> boolean binaryCursor) throws SQLException {
> return new Jdbc2ResultSet(this, statement, fields, tuples, status,
> updateCount, insertOID, binaryCursor); diff --exclude .#* --exclude
> Makefile --exclude build.xml --exclude filelis* --exclude Driver.java
> --exclude CVS --exclude *.jar --exclude *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java Wed
> Sep 11 05:38:45 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2ResultSet.java Wed
> Nov 6 12:19:43 2002 @@ -13,6 +13,12 @@
> public class Jdbc2ResultSet extends
> org.postgresql.jdbc2.AbstractJdbc2ResultSet implements java.sql.ResultSet {
>
> + Jdbc2ResultSet (org.postgresql.PGConnection conn, Statement statement)
> + {
> + super (conn, statement);
> + }
> +
> +
> public Jdbc2ResultSet(Jdbc2Connection conn, Statement statement, Field[]
> fields, Vector tuples, String status, int updateCount, long insertOID,
> boolean binaryCursor) {
> super(conn, statement, fields, tuples, status, updateCount, insertOID,
> binaryCursor); diff --exclude .#* --exclude Makefile --exclude build.xml
> --exclude filelis* --exclude Driver.java --exclude CVS --exclude *.jar
> --exclude *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc3/AbstractJdbc3ResultSet.
>java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc3/AbstractJdbc3ResultSet.j
>ava ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc3/AbstractJdbc3ResultSet.
>java Fri Sep 6 21:23:06 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc3/AbstractJdbc3ResultSet.j
>ava Thu Nov 14 23:14:05 2002 @@ -12,6 +12,12 @@
> public abstract class AbstractJdbc3ResultSet extends
> org.postgresql.jdbc2.AbstractJdbc2ResultSet {
>
> + AbstractJdbc3ResultSet (org.postgresql.PGConnection conn, Statement
> statement) + {
> + super(conn, statement);
> + }
> +
> +
> public AbstractJdbc3ResultSet(org.postgresql.PGConnection conn, Statement
> statement, org.postgresql.Field[] fields, Vector tuples, String status, int
> updateCount, long insertOID, boolean binaryCursor) {
> super (conn, statement, fields, tuples, status, updateCount, insertOID,
> binaryCursor); diff --exclude .#* --exclude Makefile --exclude build.xml
> --exclude filelis* --exclude Driver.java --exclude CVS --exclude *.jar
> --exclude *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Connection.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Connection.java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Connection.java Fr
>i Sep 6 21:23:06 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Connection.java Thu
> Nov 14 23:12:57 2002 @@ -46,14 +46,16 @@
> return metadata;
> }
>
> - public java.sql.ResultSet getResultSet(Statement statement, Field[]
> fields, Vector tuples, String status, int updateCount, long insertOID,
> boolean binaryCursor) throws SQLException - {
> - return new Jdbc3ResultSet(this, statement, fields, tuples, status,
> updateCount, insertOID, binaryCursor); - }
>
> - public java.sql.ResultSet getResultSet(Statement statement, Field[]
> fields, Vector tuples, String status, int updateCount) throws SQLException
> +
> +
> +
> +
> +
> + /** new nic method **/
> + public java.sql.ResultSet createResultSet (Statement statement)
> {
> - return new Jdbc3ResultSet(this, statement, fields, tuples, status,
> updateCount, 0, false); + return new Jdbc3ResultSet(this, statement);
> }
>
> }
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3ResultSet.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3ResultSet.java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3ResultSet.java Wed
> Sep 11 05:38:45 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3ResultSet.java Thu
> Nov 14 23:13:35 2002 @@ -13,6 +13,13 @@
> public class Jdbc3ResultSet extends
> org.postgresql.jdbc3.AbstractJdbc3ResultSet implements java.sql.ResultSet {
>
> + Jdbc3ResultSet (org.postgresql.PGConnection conn, Statement statement)
> + {
> + super (conn, statement);
> + }
> +
> +
> +
> public Jdbc3ResultSet(Jdbc3Connection conn, Statement statement, Field[]
> fields, Vector tuples, String status, int updateCount, long insertOID,
> boolean binaryCursor) {
> super(conn, statement, fields, tuples, status, updateCount, insertOID,
> binaryCursor); diff --exclude .#* --exclude Makefile --exclude build.xml
> --exclude filelis* --exclude Driver.java --exclude CVS --exclude *.jar
> --exclude *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTe
>st.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTes
>t.java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTe
>st.java Mon Nov 11 07:11:12 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/test/jdbc2/DatabaseMetaDataTes
>t.java Fri Nov 15 09:31:03 2002 @@ -9,7 +9,7 @@
> *
> * PS: Do you know how difficult it is to type on a train? ;-)
> *
> - * $Id: DatabaseMetaDataTest.java,v 1.16 2002/11/11 07:11:12 barry Exp $
> + * $Id: DatabaseMetaDataTest.java,v 1.15.2.1 2002/11/11 07:30:08 barry Exp
> $ */
>
> public class DatabaseMetaDataTest extends TestCase
> diff --exclude .#* --exclude Makefile --exclude build.xml --exclude
> filelis* --exclude Driver.java --exclude CVS --exclude *.jar --exclude
> *.class --exclude *~ -Naur
> currentcvs/src/interfaces/jdbc/org/postgresql/util/Serialize.java
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/util/Serialize.java ---
> currentcvs/src/interfaces/jdbc/org/postgresql/util/Serialize.java Tue Oct
> 1 00:39:02 2002 +++
> pgsql-cvs/src/interfaces/jdbc/org/postgresql/util/Serialize.java Thu Nov 7
> 23:29:29 2002 @@ -405,7 +405,7 @@
>
> if (Driver.logDebug)
> Driver.debug("Serialize.store: " + sb.toString() );
> - ResultSet rs =
> ((org.postgresql.jdbc1.AbstractJdbc1Connection)conn).ExecSQL(sb.toString())
>; + ResultSet rs =
> ((org.postgresql.jdbc1.AbstractJdbc1Connection)conn).doQuery(sb.toString())
>;
>
> // fetch the OID for returning
> if (update)
>
> --=-=-=
>
>
>
>
> Nic
>
> --=-=-=--
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Thomas O'Dowd <tom@nooper.com> writes: > > 3) I think the transaction characteristics of the current patch are just > > fine and conform to the jdbc specification. The code should > > automatically close the resultset when a commit occurs. One thing that > > will be confusing is that noncursor based result sets will work accross > > commits, but cursor based ones won't. But I think that is reasonable. > > Sounds reasonable to me as long as its clear to the programmer what type > they are using. I definitely don't want to see the noncursor based > resultsets closed, but I can't see a better solution for cursor based > ones... How can we make clear what type of ResultSet is being used? Nic
Nic Ferrier wrote: > Thomas O'Dowd writes: > > > >>3) I think the transaction characteristics of the current patch are just > >>fine and conform to the jdbc specification. The code should > >>automatically close the resultset when a commit occurs. One thing that > >>will be confusing is that noncursor based result sets will work accross > >>commits, but cursor based ones won't. But I think that is reasonable. > > > >Sounds reasonable to me as long as its clear to the programmer what type > >they are using. I definitely don't want to see the noncursor based > >resultsets closed, but I can't see a better solution for cursor based > >ones... > > > How can we make clear what type of ResultSet is being used? I suggest with ResultSet.CLOSE_CURSORS_AT_COMMIT (cursor method) vs ResultSet.HOLD_CURSORS_OVER_COMMIT (old method). You can both request a certain type when you create a Statement or PreparedStatement and get the type of the resultset from the Statement or PreparedStatement. Thanks, Scott
Scott Lamb <slamb@slamb.org> writes: > Nic Ferrier wrote: > > > Thomas O'Dowd writes: > > > > > > >>3) I think the transaction characteristics of the current patch are just > > >>fine and conform to the jdbc specification. The code should > > >>automatically close the resultset when a commit occurs. One thing that > > >>will be confusing is that noncursor based result sets will work accross > > >>commits, but cursor based ones won't. But I think that is reasonable. > > > > > >Sounds reasonable to me as long as its clear to the programmer what type > > >they are using. I definitely don't want to see the noncursor based > > >resultsets closed, but I can't see a better solution for cursor based > > >ones... > > > > > > How can we make clear what type of ResultSet is being used? > > I suggest with ResultSet.CLOSE_CURSORS_AT_COMMIT (cursor method) vs > ResultSet.HOLD_CURSORS_OVER_COMMIT (old method). You can both request a > certain type when you create a Statement or PreparedStatement and get > the type of the resultset from the Statement or PreparedStatement. Sounds good. I'll bung that into the latest patch which will be released tommorow (I'll be setting up a website soon /8->) Tommorow's patch will also include code to handle ResultSets coming back from procs (via cursors). This is based on Barry and my conversations from earlier this autumn. Nic
Hello Barry, What think You about next idea I have study C interface and it is easy solve my problem without cursor. JDBC load complete qyery result in method QueryExecutor.execute I think that method QueryExecutor.execute work like now except for query (select) - for select this method should do something like PQExec method in C (this is compatible with ResultSet in JDBC specification) When we call ResultSet.next (or like method) we should be initialize fetchSize rows and load rows with method like C method PQgetvalue - consecutive call ResultSet.next should scroll row window (length for window is fetchSize) and if need call new fetchSize rows with method like PQgetvalue 'OutOfmemory' error for large table is not because Postgresql do so than JDBC QueryExecutor.execute method load all rows. If this work in C without cursor then no reason that work in Java I have tried more queries with large table in C and it work fine without cursors. regards Haris Peco On Monday 18 November 2002 05:54 pm, Barry Lind wrote: > Haris, > > I understand your problem. But unless the database supports cursors > that span transactions, I don't see any solution for you, other than to > issue multiple sql statements to mimic cross transaction queries in your > application. > > --Barry > > Haris Peco wrote: > > On Monday 18 November 2002 05:14 pm, Barry Lind wrote: > >>Nic, > >> > >>Here are my thoughts on this topic. > >> > >>1) Since the server doesn't support cursors across transactions, I don't > >>think the driver should either. In fact in jdbc3 the DatabaseMetaData > >>object has a supportsResultSetHoldability() method that explicitly lets > >>the driver tell the application what is does/doesn't support in this > >> area. > >> > >>I think running multiple sql statements to mimic this behavior is a very > >>bad idea. Since the select statements will run at different times they > >>will return different data (since they will pick up commited changes > >>between runs), and if you don't include an order by the results are > >>completely unpredictable. If someone wants this very unpredictable > >>behavior they can issue the multiple statements themselves. > >> > >>2) I think the use of cursors should be optional. In fact since most > >>queries don't need them since most queries return a small number of rows > >>, I think the use of cursors needs to be turned on. I think there > >>should be two ways to do this: the first is by setting the fetchSize() > >>and the second would be a jdbc url parameter. > >> > >>3) I think the transaction characteristics of the current patch are just > >>fine and conform to the jdbc specification. The code should > >>automatically close the resultset when a commit occurs. One thing that > >>will be confusing is that noncursor based result sets will work accross > >>commits, but cursor based ones won't. But I think that is reasonable. > > > > My problem : > > master-detail > > I select one from many rows master with cursor (big table and only this > > is possible) - In detail I do change and commit (or rollback) > > My select is lost. > > How can I do that ? > > > >>Nic Ferrier wrote: > >>>Message-ID: <87fztyexea.fsf@pooh-sticks-bridge.tapsellferrier.co.uk> > >>>Lines: 24 > >>>MIME-Version: 1.0 > >>>Content-Type: text/plain; charset=us-ascii > >>>--text follows this line-- > >>> > >>>Haris Peco <snpe@snpe.co.yu> writes: > >>>>I have tried. > >>>>DatabaseMetaData is fine, but ResultSet.[get|set]FetchSize don't > >>>>work > >>> > >>>What error do you get? > >>> > >>>>Prepared command don't work, but my greatest problem are trasnaction > >>>>I hope that cursor in 7.4 will be out of a transaction > >>> > >>>I don't think there's much I can do about the cursor problem. > >>> > >>>>Can Yoy yet another : > >>>>set driver's flag btw jdbc:pgsql:...?cursor=yes > >>>>for use cursor or old way > >>> > >>>What does everyone else think? Is a system doing a different query > >>>each time worth looking into? > >>> > >>> > >>>Nic > >>> > >>> > >>>---------------------------(end of broadcast)--------------------------- > >>>TIP 5: Have you checked our extensive FAQ? > >>> > >>>http://www.postgresql.org/users-lounge/docs/faq.html > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 6: Have you searched our list archives? > >> > >>http://archives.postgresql.org
Haris, This is not possible on the client. The protocol that clients use to talk to the server (the front end/back end protocol (FE/BE)) only allows one method for getting the results of a sql statement. It is only possible via the FE/BE protocol to get the entire result. Which is why the code works the way it does. The work Nic is doing works around this limitation by issuing multiple sql statements (declare cursor, fetch etc.) but it is still the case that the entire result of each statement must be read before any additional calls can be made. What you are pointing out here is the C interface on the server, which is not limited by the capabilities of the FE/BE protocol. But I still beleive that even using the server side C interface, cursors are limited to a single transaction, thus your original problem still exists. thanks, --Barry Haris Peco wrote: > Hello Barry, > What think You about next idea > I have study C interface and it is easy solve my problem without cursor. > JDBC load complete qyery result in method QueryExecutor.execute > I think that method QueryExecutor.execute work like now except for query > (select) - for select this method should do something like PQExec method > in C (this is compatible with ResultSet in JDBC specification) > When we call ResultSet.next (or like method) we should be initialize > fetchSize rows and load rows with method like C method PQgetvalue - > consecutive call ResultSet.next should scroll row window (length for window is fetchSize) > and if need call new fetchSize rows with method like PQgetvalue > 'OutOfmemory' error for large table is not because Postgresql do so than > JDBC QueryExecutor.execute method load all rows. > If this work in C without cursor then no reason that work in Java > I have tried more queries with large table in C and it work fine without cursors. > > regards > Haris Peco > On Monday 18 November 2002 05:54 pm, Barry Lind wrote: > >>Haris, >> >>I understand your problem. But unless the database supports cursors >>that span transactions, I don't see any solution for you, other than to >>issue multiple sql statements to mimic cross transaction queries in your >>application. >> >>--Barry >> >>Haris Peco wrote: >> >>>On Monday 18 November 2002 05:14 pm, Barry Lind wrote: >>> >>>>Nic, >>>> >>>>Here are my thoughts on this topic. >>>> >>>>1) Since the server doesn't support cursors across transactions, I don't >>>>think the driver should either. In fact in jdbc3 the DatabaseMetaData >>>>object has a supportsResultSetHoldability() method that explicitly lets >>>>the driver tell the application what is does/doesn't support in this >>>>area. >>>> >>>>I think running multiple sql statements to mimic this behavior is a very >>>>bad idea. Since the select statements will run at different times they >>>>will return different data (since they will pick up commited changes >>>>between runs), and if you don't include an order by the results are >>>>completely unpredictable. If someone wants this very unpredictable >>>>behavior they can issue the multiple statements themselves. >>>> >>>>2) I think the use of cursors should be optional. In fact since most >>>>queries don't need them since most queries return a small number of rows >>>>, I think the use of cursors needs to be turned on. I think there >>>>should be two ways to do this: the first is by setting the fetchSize() >>>>and the second would be a jdbc url parameter. >>>> >>>>3) I think the transaction characteristics of the current patch are just >>>>fine and conform to the jdbc specification. The code should >>>>automatically close the resultset when a commit occurs. One thing that >>>>will be confusing is that noncursor based result sets will work accross >>>>commits, but cursor based ones won't. But I think that is reasonable. >>> >>>My problem : >>>master-detail >>>I select one from many rows master with cursor (big table and only this >>>is possible) - In detail I do change and commit (or rollback) >>>My select is lost. >>>How can I do that ? >>> >>> >>>>Nic Ferrier wrote: >>>> >>>>>Message-ID: <87fztyexea.fsf@pooh-sticks-bridge.tapsellferrier.co.uk> >>>>>Lines: 24 >>>>>MIME-Version: 1.0 >>>>>Content-Type: text/plain; charset=us-ascii >>>>>--text follows this line-- >>>>> >>>>>Haris Peco <snpe@snpe.co.yu> writes: >>>>> >>>>>>I have tried. >>>>>>DatabaseMetaData is fine, but ResultSet.[get|set]FetchSize don't >>>>>>work >>>>> >>>>>What error do you get? >>>>> >>>>> >>>>>>Prepared command don't work, but my greatest problem are trasnaction >>>>>>I hope that cursor in 7.4 will be out of a transaction >>>>> >>>>>I don't think there's much I can do about the cursor problem. >>>>> >>>>> >>>>>>Can Yoy yet another : >>>>>>set driver's flag btw jdbc:pgsql:...?cursor=yes >>>>>>for use cursor or old way >>>>> >>>>>What does everyone else think? Is a system doing a different query >>>>>each time worth looking into? >>>>> >>>>> >>>>>Nic >>>>> >>>>> >>>>>---------------------------(end of broadcast)--------------------------- >>>>>TIP 5: Have you checked our extensive FAQ? >>>>> >>>>>http://www.postgresql.org/users-lounge/docs/faq.html >>>> >>>>---------------------------(end of broadcast)--------------------------- >>>>TIP 6: Have you searched our list archives? >>>> >>>>http://archives.postgresql.org >>> > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Yes, proccess increase with result from server and diferent is that C request less memory - in C we can execute big qyery than Java I can't believe that we must complete query in memory, but it is true Excuse me Thanks On Wednesday 20 November 2002 05:51 pm, Barry Lind wrote: > Haris, > > This is not possible on the client. The protocol that clients use to > talk to the server (the front end/back end protocol (FE/BE)) only allows > one method for getting the results of a sql statement. It is only > possible via the FE/BE protocol to get the entire result. Which is why > the code works the way it does. The work Nic is doing works around this > limitation by issuing multiple sql statements (declare cursor, fetch > etc.) but it is still the case that the entire result of each statement > must be read before any additional calls can be made. > > What you are pointing out here is the C interface on the server, which > is not limited by the capabilities of the FE/BE protocol. But I still > beleive that even using the server side C interface, cursors are limited > to a single transaction, thus your original problem still exists. > > thanks, > --Barry > > Haris Peco wrote: > > Hello Barry, > > What think You about next idea > > I have study C interface and it is easy solve my problem without cursor. > > JDBC load complete qyery result in method QueryExecutor.execute > > I think that method QueryExecutor.execute work like now except for query > > (select) - for select this method should do something like PQExec method > > in C (this is compatible with ResultSet in JDBC specification) > > When we call ResultSet.next (or like method) we should be initialize > > fetchSize rows and load rows with method like C method PQgetvalue - > > consecutive call ResultSet.next should scroll row window (length for > > window is fetchSize) and if need call new fetchSize rows with method like > > PQgetvalue > > 'OutOfmemory' error for large table is not because Postgresql do so than > > JDBC QueryExecutor.execute method load all rows. > > If this work in C without cursor then no reason that work in Java > > I have tried more queries with large table in C and it work fine without > > cursors. > > > > regards > > Haris Peco > > > > On Monday 18 November 2002 05:54 pm, Barry Lind wrote: > >>Haris, > >> > >>I understand your problem. But unless the database supports cursors > >>that span transactions, I don't see any solution for you, other than to > >>issue multiple sql statements to mimic cross transaction queries in your > >>application. > >> > >>--Barry > >> > >>Haris Peco wrote: > >>>On Monday 18 November 2002 05:14 pm, Barry Lind wrote: > >>>>Nic, > >>>> > >>>>Here are my thoughts on this topic. > >>>> > >>>>1) Since the server doesn't support cursors across transactions, I > >>>> don't think the driver should either. In fact in jdbc3 the > >>>> DatabaseMetaData object has a supportsResultSetHoldability() method > >>>> that explicitly lets the driver tell the application what is > >>>> does/doesn't support in this area. > >>>> > >>>>I think running multiple sql statements to mimic this behavior is a > >>>> very bad idea. Since the select statements will run at different > >>>> times they will return different data (since they will pick up > >>>> commited changes between runs), and if you don't include an order by > >>>> the results are completely unpredictable. If someone wants this very > >>>> unpredictable behavior they can issue the multiple statements > >>>> themselves. > >>>> > >>>>2) I think the use of cursors should be optional. In fact since most > >>>>queries don't need them since most queries return a small number of > >>>> rows , I think the use of cursors needs to be turned on. I think > >>>> there should be two ways to do this: the first is by setting the > >>>> fetchSize() and the second would be a jdbc url parameter. > >>>> > >>>>3) I think the transaction characteristics of the current patch are > >>>> just fine and conform to the jdbc specification. The code should > >>>>automatically close the resultset when a commit occurs. One thing that > >>>>will be confusing is that noncursor based result sets will work accross > >>>>commits, but cursor based ones won't. But I think that is reasonable. > >>> > >>>My problem : > >>>master-detail > >>>I select one from many rows master with cursor (big table and only this > >>>is possible) - In detail I do change and commit (or rollback) > >>>My select is lost. > >>>How can I do that ? > >>> > >>>>Nic Ferrier wrote: > >>>>>Message-ID: <87fztyexea.fsf@pooh-sticks-bridge.tapsellferrier.co.uk> > >>>>>Lines: 24 > >>>>>MIME-Version: 1.0 > >>>>>Content-Type: text/plain; charset=us-ascii > >>>>>--text follows this line-- > >>>>> > >>>>>Haris Peco <snpe@snpe.co.yu> writes: > >>>>>>I have tried. > >>>>>>DatabaseMetaData is fine, but ResultSet.[get|set]FetchSize don't > >>>>>>work > >>>>> > >>>>>What error do you get? > >>>>> > >>>>>>Prepared command don't work, but my greatest problem are trasnaction > >>>>>>I hope that cursor in 7.4 will be out of a transaction > >>>>> > >>>>>I don't think there's much I can do about the cursor problem. > >>>>> > >>>>>>Can Yoy yet another : > >>>>>>set driver's flag btw jdbc:pgsql:...?cursor=yes > >>>>>>for use cursor or old way > >>>>> > >>>>>What does everyone else think? Is a system doing a different query > >>>>>each time worth looking into? > >>>>> > >>>>> > >>>>>Nic > >>>>> > >>>>> > >>>>>---------------------------(end of > >>>>> broadcast)--------------------------- TIP 5: Have you checked our > >>>>> extensive FAQ? > >>>>> > >>>>>http://www.postgresql.org/users-lounge/docs/faq.html > >>>> > >>>>---------------------------(end of > >>>> broadcast)--------------------------- TIP 6: Have you searched our > >>>> list archives? > >>>> > >>>>http://archives.postgresql.org > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Haris Peco <snpe@snpe.co.yu> writes: > Yes, proccess increase with result from server and diferent is that > C request less memory - in C we can execute big qyery than Java > I can't believe that we must complete query in memory, but it is true > Excuse me If you look at the implementation of the libpq library you'll see that it's exactly the same as the java one: the query is done and then all the rows are retrieved and kept in memory. If you're able to do it in C it's because C has slightly more efficient memory handling than Java does. It must also mean that your machine has just too little memory for your Java app, if I were you I'd just buy some more RAM as a quick fix to your problem. Another alternative is to create 2 connections and use a cursor in one. Or to package your update operations as stored procs operating over the large results. Nic
On Wednesday 20 November 2002 08:08 pm, Nic Ferrier wrote: > Haris Peco <snpe@snpe.co.yu> writes: > > Yes, proccess increase with result from server and diferent is that > > C request less memory - in C we can execute big qyery than Java > > I can't believe that we must complete query in memory, but it is true > > Excuse me > > If you look at the implementation of the libpq library you'll see > that it's exactly the same as the java one: the query is done and > then all the rows are retrieved and kept in memory. > > If you're able to do it in C it's because C has slightly more > efficient memory handling than Java does. It must also mean that your > machine has just too little memory for your Java app, if I were you > I'd just buy some more RAM as a quick fix to your problem. > > Another alternative is to create 2 connections and use a cursor in > one. Or to package your update operations as stored procs operating > over the large results. > Hello Nic yes, for me only alternative is cursor out of transaction and I think that it is not big request.Oracle, db2, sql server, sybase, informix etc have this Your work with cursor is great, but we have to it out of transaction thanks
Well, considering that cursors *ONLY* operate within transactions then this is a huge request. Dave On Wed, 2002-11-20 at 16:00, Haris Peco wrote: > On Wednesday 20 November 2002 08:08 pm, Nic Ferrier wrote: > > Haris Peco <snpe@snpe.co.yu> writes: > > > Yes, proccess increase with result from server and diferent is that > > > C request less memory - in C we can execute big qyery than Java > > > I can't believe that we must complete query in memory, but it is true > > > Excuse me > > > > If you look at the implementation of the libpq library you'll see > > that it's exactly the same as the java one: the query is done and > > then all the rows are retrieved and kept in memory. > > > > If you're able to do it in C it's because C has slightly more > > efficient memory handling than Java does. It must also mean that your > > machine has just too little memory for your Java app, if I were you > > I'd just buy some more RAM as a quick fix to your problem. > > > > Another alternative is to create 2 connections and use a cursor in > > one. Or to package your update operations as stored procs operating > > over the large results. > > > > Hello Nic > yes, for me only alternative is cursor out of transaction and I think that it > is not big request.Oracle, db2, sql server, sybase, informix etc have this > Your work with cursor is great, but we have to it out of transaction > > thanks > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Dave Cramer <Dave@micro-automation.net>
Whoa! big time over run... anyway, getting back to this:
Scott Lamb <slamb@slamb.org> writes:
> Nic Ferrier wrote:
>
> > Thomas O'Dowd writes:
> >
> >
> > >>3) I think the transaction characteristics of the current patch are just
> > >>fine and conform to the jdbc specification. The code should
> > >>automatically close the resultset when a commit occurs. One thing that
> > >>will be confusing is that noncursor based result sets will work accross
> > >>commits, but cursor based ones won't. But I think that is reasonable.
> > >
> > >Sounds reasonable to me as long as its clear to the programmer what type
> > >they are using. I definitely don't want to see the noncursor based
> > >resultsets closed, but I can't see a better solution for cursor based
> > >ones...
> >
> >
> > How can we make clear what type of ResultSet is being used?
>
> I suggest with ResultSet.CLOSE_CURSORS_AT_COMMIT (cursor method) vs
> ResultSet.HOLD_CURSORS_OVER_COMMIT (old method). You can both request a
> certain type when you create a Statement or PreparedStatement and get
> the type of the resultset from the Statement or PreparedStatement.
So what you're saying is that this code:
Statement st
= connection.createStatement(ResultSet.CLOSE_CURSORS_AT_COMMIT,
ResulSet.CONCUR_READ_ONLY);
ResultSet rs = st.executeQuery("select * from table");
would produce a cursor based res set whereas:
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery("select * from table");
would not.
That would mean that we didn't need the fetch size signal. Or we
could use the fetchSize signal as well.
Note also that CLOSE_CURSORS_AT_COMMIT is not actually a result set
type so it _might_ break other code.
Anyone else have any opinion on this?
Nic
On Friday 22 November 2002 03:15 pm, Nic Ferrier wrote:
> Whoa! big time over run... anyway, getting back to this:
>
> Scott Lamb <slamb@slamb.org> writes:
> > Nic Ferrier wrote:
> > > Thomas O'Dowd writes:
> > > >>3) I think the transaction characteristics of the current patch are
> > > >> just fine and conform to the jdbc specification. The code should
> > > >> automatically close the resultset when a commit occurs. One thing
> > > >> that will be confusing is that noncursor based result sets will work
> > > >> accross commits, but cursor based ones won't. But I think that is
> > > >> reasonable.
> > > >
> > > >Sounds reasonable to me as long as its clear to the programmer what
> > > > type they are using. I definitely don't want to see the noncursor
> > > > based resultsets closed, but I can't see a better solution for cursor
> > > > based ones...
> > >
> > > How can we make clear what type of ResultSet is being used?
> >
> > I suggest with ResultSet.CLOSE_CURSORS_AT_COMMIT (cursor method) vs
> > ResultSet.HOLD_CURSORS_OVER_COMMIT (old method). You can both request a
> > certain type when you create a Statement or PreparedStatement and get
> > the type of the resultset from the Statement or PreparedStatement.
>
> So what you're saying is that this code:
>
> Statement st
> = connection.createStatement(ResultSet.CLOSE_CURSORS_AT_COMMIT,
> ResulSet.CONCUR_READ_ONLY);
> ResultSet rs = st.executeQuery("select * from table");
>
>
> would produce a cursor based res set whereas:
>
> Statement st = connection.createStatement();
> ResultSet rs = st.executeQuery("select * from table");
>
> would not.
>
>
> That would mean that we didn't need the fetch size signal. Or we
> could use the fetchSize signal as well.
>
>
> Note also that CLOSE_CURSORS_AT_COMMIT is not actually a result set
> type so it _might_ break other code.
>
>
> Anyone else have any opinion on this?
>
I think that if fetchSize > 0 then user cursor, if fetchSize = 0 then use old way
regards
Haris Peco
Yet another sugestion :
When make createStatement, we haven't to do fetch - command is same except
begin; declare xxx cursor (I think that and begin will not be required soon)
When we call first ResultSet.next (or like) we call fetch if don't rows in memory.
It is way in another databases : execute is prepare and bind (without fetch) and then is fetch
JDBC specification tell same - execute don't nothing with row
regards
Haris Peco
On Friday 22 November 2002 03:15 pm, Nic Ferrier wrote:
> Whoa! big time over run... anyway, getting back to this:
>
> Scott Lamb <slamb@slamb.org> writes:
> > Nic Ferrier wrote:
> > > Thomas O'Dowd writes:
> > > >>3) I think the transaction characteristics of the current patch are
> > > >> just fine and conform to the jdbc specification. The code should
> > > >> automatically close the resultset when a commit occurs. One thing
> > > >> that will be confusing is that noncursor based result sets will work
> > > >> accross commits, but cursor based ones won't. But I think that is
> > > >> reasonable.
> > > >
> > > >Sounds reasonable to me as long as its clear to the programmer what
> > > > type they are using. I definitely don't want to see the noncursor
> > > > based resultsets closed, but I can't see a better solution for cursor
> > > > based ones...
> > >
> > > How can we make clear what type of ResultSet is being used?
> >
> > I suggest with ResultSet.CLOSE_CURSORS_AT_COMMIT (cursor method) vs
> > ResultSet.HOLD_CURSORS_OVER_COMMIT (old method). You can both request a
> > certain type when you create a Statement or PreparedStatement and get
> > the type of the resultset from the Statement or PreparedStatement.
>
> So what you're saying is that this code:
>
> Statement st
> = connection.createStatement(ResultSet.CLOSE_CURSORS_AT_COMMIT,
> ResulSet.CONCUR_READ_ONLY);
> ResultSet rs = st.executeQuery("select * from table");
>
>
> would produce a cursor based res set whereas:
>
> Statement st = connection.createStatement();
> ResultSet rs = st.executeQuery("select * from table");
>
> would not.
>
>
> That would mean that we didn't need the fetch size signal. Or we
> could use the fetchSize signal as well.
>
>
> Note also that CLOSE_CURSORS_AT_COMMIT is not actually a result set
> type so it _might_ break other code.
>
>
> Anyone else have any opinion on this?
>
>
>
> Nic
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
snpe <snpe@snpe.co.yu> writes: > Yet another sugestion : > > When make createStatement, we haven't to do fetch - command is same except > begin; declare xxx cursor (I think that and begin will not be required soon) > When we call first ResultSet.next (or like) we call fetch if don't rows in memory. > It is way in another databases : execute is prepare and bind (without fetch) and then is fetch > JDBC specification tell same - execute don't nothing with row JDBC spec doesn't require any particular behaviour... what we've got kinda works. Nic
On Friday 22 November 2002 07:16 pm, Nic Ferrier wrote: > snpe <snpe@snpe.co.yu> writes: > > Yet another sugestion : > > > > When make createStatement, we haven't to do fetch - command is same > > except begin; declare xxx cursor (I think that and begin will not be > > required soon) When we call first ResultSet.next (or like) we call fetch > > if don't rows in memory. It is way in another databases : execute is > > prepare and bind (without fetch) and then is fetch JDBC specification > > tell same - execute don't nothing with row > > JDBC spec doesn't require any particular behaviour... what we've got > kinda works. > JDBC spec requires that after executeStatement there is nothing in ResultSet. Execute and fetch are different command - only in PostgreSQL they are one command. PostgreSQL with cursors work execute and fetch , too regards Haris Peco regrads
Message-ID: <87of8h5fdc.fsf@pooh-sticks-bridge.tapsellferrier.co.uk> Lines: 27 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii --text follows this line-- snpe <snpe@snpe.co.yu> writes: > On Friday 22 November 2002 07:16 pm, Nic Ferrier wrote: > > snpe <snpe@snpe.co.yu> writes: > > > Yet another sugestion : > > > > > > When make createStatement, we haven't to do fetch - command is same > > > except begin; declare xxx cursor (I think that and begin will not be > > > required soon) When we call first ResultSet.next (or like) we call fetch > > > if don't rows in memory. It is way in another databases : execute is > > > prepare and bind (without fetch) and then is fetch JDBC specification > > > tell same - execute don't nothing with row > > > > JDBC spec doesn't require any particular behaviour... what we've got > > kinda works. > > > > JDBC spec requires that after executeStatement there is nothing in > ResultSet. No it doesn't. It requires that the result set is not positioned until after the first call to next(). Postgresql's behaviour is quite legitimate. Nic
On Friday 22 November 2002 11:55 pm, Nic Ferrier wrote: > Message-ID: <87of8h5fdc.fsf@pooh-sticks-bridge.tapsellferrier.co.uk> > Lines: 27 > MIME-Version: 1.0 > Content-Type: text/plain; charset=us-ascii > --text follows this line-- > > snpe <snpe@snpe.co.yu> writes: > > On Friday 22 November 2002 07:16 pm, Nic Ferrier wrote: > > > snpe <snpe@snpe.co.yu> writes: > > > > Yet another sugestion : > > > > > > > > When make createStatement, we haven't to do fetch - command is same > > > > except begin; declare xxx cursor (I think that and begin will not be > > > > required soon) When we call first ResultSet.next (or like) we call > > > > fetch if don't rows in memory. It is way in another databases : > > > > execute is prepare and bind (without fetch) and then is fetch JDBC > > > > specification tell same - execute don't nothing with row > > > > > > JDBC spec doesn't require any particular behaviour... what we've got > > > kinda works. > > > > JDBC spec requires that after executeStatement there is nothing in > > ResultSet. > > No it doesn't. It requires that the result set is not positioned > until after the first call to next(). > > Postgresql's behaviour is quite legitimate. > Yes, it is legitime, but execute and fetch are separated command. There isn't good reason for doing fetch with execute - maybe user never call fetch. regards Haris Peco
snpe wrote: > Yes, it is legitime, but execute and fetch are separated command. > There isn't good reason for doing fetch with execute - maybe user never call fetch. Network efficiency. By batching the commands together, it avoids a round trip to the database server. Scott
Scott Lamb <slamb@slamb.org> writes: > snpe wrote: > > Yes, it is legitime, but execute and fetch are separated command. > > There isn't good reason for doing fetch with execute - maybe user never call fetch. > > Network efficiency. By batching the commands together, it avoids a round > trip to the database server. And it's very likely that once a ResultSet is got, the user will want to position it. The number of times that this doesn't happen is, well, I've never done it. Nic
On Saturday 23 November 2002 03:20 pm, Scott Lamb wrote: > snpe wrote: > > Yes, it is legitime, but execute and fetch are separated command. > > There isn't good reason for doing fetch with execute - maybe user never > > call fetch. > > Network efficiency. By batching the commands together, it avoids a round > trip to the database server. > Are you sure ? When PostgreSQL work with cursors - execute and fetch are two command for backend I know that other database work separatly and PostgreSQL work together without cursors and we will choose regards Haris Peco
Nic Ferrier wrote:
> Scott Lamb <slamb@slamb.org> writes:
>>I suggest with ResultSet.CLOSE_CURSORS_AT_COMMIT (cursor method) vs
>>ResultSet.HOLD_CURSORS_OVER_COMMIT (old method). You can both request a
>>certain type when you create a Statement or PreparedStatement and get
>>the type of the resultset from the Statement or PreparedStatement.
>
>
> So what you're saying is that this code:
>
> Statement st
> = connection.createStatement(ResultSet.CLOSE_CURSORS_AT_COMMIT,
> ResulSet.CONCUR_READ_ONLY);
> ResultSet rs = st.executeQuery("select * from table");
>
>
> would produce a cursor based res set whereas:
>
> Statement st = connection.createStatement();
> ResultSet rs = st.executeQuery("select * from table");
>
> would not.
Yup.
> That would mean that we didn't need the fetch size signal. Or we
> could use the fetchSize signal as well.
I'm not sure that would work anyway. It's ResultSet.setFetchSize(),
which means you must already have a resultset when to change it. So it
seems like it'd be a little late for deciding how to execute the query.
> Note also that CLOSE_CURSORS_AT_COMMIT is not actually a result set
> type so it _might_ break other code.
Not sure what you mean. If client code is using CLOSE_CURSORS_AT_COMMIT
and expecting them to work beyond commit, it's their bug and an
easily-fixed one at that. And I don't see any other negative
consequences to using cursors, just the advantage of being able to
handle large queries.
Thanks,
Scott
Scott Lamb <slamb@slamb.org> writes:
> Nic said:
> > That would mean that we didn't need the fetch size signal. Or we
> > could use the fetchSize signal as well.
>
> I'm not sure that would work anyway. It's ResultSet.setFetchSize(),
> which means you must already have a resultset when to change it. So it
> seems like it'd be a little late for deciding how to execute the
> query.
We don't use that "setFetchSize" we use Statement.setFetchSize.
The setFetchSize in ResultSet has an effect, but as you say, not on
the type of the ResultSet /8->
> > Note also that CLOSE_CURSORS_AT_COMMIT is not actually a result set
> > type so it _might_ break other code.
>
> Not sure what you mean. If client code is using CLOSE_CURSORS_AT_COMMIT
> and expecting them to work beyond commit, it's their bug and an
> easily-fixed one at that. And I don't see any other negative
> consequences to using cursors, just the advantage of being able to
> handle large queries.
That's not what I was saying.
It's easier if I express this in pseudo-C:
enum resultset_types
{
TYPE_FORWARD_ONLY
TYPE_SCROLL_INSENSITIVE
TYPE_SCROLL_SENSITIVE
};
So CLOSE_CURSORS_AT_COMMIT is not a _normal_ value for the ResultSet
_type_ type.
The real problem with this is that Java does not have enums, witness
the code from GNU Classpath's java.sql implementation:
>>>>
public static final int TYPE_FORWARD_ONLY = 0;
public static final int TYPE_SCROLL_INSENSITIVE = 1;
public static final int TYPE_SCROLL_SENSITIVE = 1;
public static final int CONCUR_READ_ONLY = 0;
public static final int CONCUR_UPDATABLE = 1;
<<<<
Oops. Not sure how we'll get round that if we follow your suggestion.
Any ideas?
Nic
Nic Ferrier wrote:
> Scott Lamb writes:
> >I'm not sure that would work anyway. It's ResultSet.setFetchSize(),
> >which means you must already have a resultset when to change it. So it
> >seems like it'd be a little late for deciding how to execute the
> >query.
>
>
> We don't use that "setFetchSize" we use Statement.setFetchSize.
Ahh. Didn't realize there were two. That should work, though I still
prefer specifying the required holdability because that's what defines
whether or not you're able to use the (otherwise superior) cursor method.
> >>Note also that CLOSE_CURSORS_AT_COMMIT is not actually a result set
> >>type so it _might_ break other code.
> >
> >Not sure what you mean. If client code is using CLOSE_CURSORS_AT_COMMIT
> >and expecting them to work beyond commit, it's their bug and an
> >easily-fixed one at that. And I don't see any other negative
> >consequences to using cursors, just the advantage of being able to
> >handle large queries.
> [...]
> So CLOSE_CURSORS_AT_COMMIT is not a _normal_ value for the ResultSet
> _type_ type.
Okay, I understand now. The confusion is, you wrote this:
Statement st = connection.createStatement(
ResultSet.CLOSE_CURSORS_AT_COMMIT,
ResultSet.CONCUR_READ_ONLY);
but evil gnomes changed the bits in my copy of the email to what I
expected, so I didn't notice a difference. ;) I meant this:
Statement st = connection.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY,
ResultSet.CLOSE_CURSORS_AT_COMMIT);
Type, concurrency, and holdability are all orthogonal parameters to
createStatement and prepareStatement.
Thanks,
Scott
I'm trying to put a bytea into a callable statement (a plpgsql function) using current CVS drivers. Has anybody done such a thing? I can't seem to get it to work - before I start fixing it (/8->) I want to know whether there is some dead chicken I have to wave over it... Nic
slamb writes: > Okay, I understand now. The confusion is, you wrote this: > > Statement st = connection.createStatement( > ResultSet.CLOSE_CURSORS_AT_COMMIT, > ResultSet.CONCUR_READ_ONLY); > > but evil gnomes changed the bits in my copy of the email to what I > expected, so I didn't notice a difference. ;) I meant this: > > Statement st = connection.createStatement( > ResultSet.TYPE_FORWARD_ONLY, > ResultSet.CONCUR_READ_ONLY, > ResultSet.CLOSE_CURSORS_AT_COMMIT); > > Type, concurrency, and holdability are all orthogonal parameters to > createStatement and prepareStatement. I've looked into this a bit more... I'm not gonna do this right now because this call is only JDBC3 compatible. We'll have to stick with the fetch size method I think. Nic
It seems that the check functions for binary objects are wrong. byte[] getBytes() checks only for VARBINARY and not BINARY as well. Sun's javadoc says the following: >>>> byte[] getBytes() Gets the value of a JDBC BINARY or VARBINARY parameter as an array of byte values in the Java programming language. <<<< I've got a fix that I will submit... trouble is it's all wrapped up in the "nic patch" that contains the stuff for dealing with result sets from functions and the stuff for fetch size result sets (and the general re-working of the pgsql execution path). I now aim to get this up for tommorow (wednesday) night. Nic