Thread: JDBC sources
Hi, Please can you tell me where I can download Postgres JDBC driver source files. Thanks. Tarik Choufa ZAQ Solutions Interactives 297 St-Paul Ouest, Bureau 105, Québec, Canada, H2Y 2A5 Tél. : 514-282-7073 x422 Fax : 514-282-8011 http://www.zaq.com ---------------------------------------------------------------------------------------- Ce message (et tout document annexé) est sujet à contenir des informations de nature confidentielle et/ou privilégiée etdoit être transmis exclusivement à l'attention de la personne ou de l'entité à qui il est adressé. Toute révision, retransmission,diffusion ou tout autre usage de l'information, ou toute prise de décision fondée sur ces informations, partoute personne ou entité autre que le destinataire à qui le message ou le document est adressé est interdit. Si vousrecevez par erreur ce message ou document, veuillez contacter l'expéditeur et détruire ou effacer toute copie papierou copie informatique des informations. Merci. The information transmitted (and all attachments) is intended only for the person or entity to which it is addressed andmay contain confidential and/or privileged material. Any review, retransmission, dissemination or other use, or takingof any action in reliance upon, of this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy or delete all paper material or data. Thankyou. ----------------------------------------------------------------------------------------
They come with the postgres source, or you can get them using cvs Dave On Thu, 2002-08-29 at 12:42, Tarik Choufa wrote: > Hi, > Please can you tell me where I can download Postgres JDBC driver source files. > Thanks. > > > Tarik Choufa > ZAQ Solutions Interactives > 297 St-Paul Ouest, Bureau 105, Québec, Canada, H2Y 2A5 > Tél.: 514-282-7073 x422 Fax : 514-282-8011 > http://www.zaq.com > > ---------------------------------------------------------------------------------------- > Ce message (et tout document annexé) est sujet à contenir des informations de nature confidentielle et/ou privilégiée etdoit être transmis exclusivement à l'attention de la personne ou de l'entité à qui il est adressé. Toute révision, retransmission,diffusion ou tout autre usage de l'information, ou toute prise de décision fondée sur ces informations, partoute personne ou entité autre que le destinataire à qui le message ou le document est adressé est interdit. Si vousrecevez par erreur ce message ou document, veuillez contacter l'expéditeur et détruire ou effacer toute copie papierou copie informatique des informations. Merci. > > The information transmitted (and all attachments) is intended only for the person or entity to which it is addressed andmay contain confidential and/or privileged material. Any review, retransmission, dissemination or other use, or takingof any action in reliance upon, of this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy or delete all paper material or data. Thankyou. > ---------------------------------------------------------------------------------------- > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
hi, i am planning to implement a pooling system for Prepared Statements. It will open a single connection during initialization and create many prepared statements using this connection. The connection will be always kept open so that the prepared statements are valid. To speed up things, there may be more than one prepared statement for the same query, ie, stmt1 = "select from table where field = ?"; stmt2 = "select from table where field = ?"; Will there be any issues when two statements are executed for the same table using the same connection? has anyone tried doing something like this or is there any open source tool which can do this? This will greatly improve the efficiency of the system as most of the time, the connections are used for executing the same statements. Pooling the statements instead of the connection will save the processing needed for creating the statement objects for every query. Regards, Nagarajan.
Hi! Im sorry but: 1 connection -> 1 resultset at time. You can have many statement associated to a connection but when you execute the statemente you can only have 1 resultset associated to the connection at time. You can execute 2 statement using the same connection but you have to: -> execute statement1 -> get the resultset1 and use it -> close resultset -> execute statement2 -> get the resultset2 and use it -> close resultset If you still using the resultset1 when you execute the statement2 you'll destroy it. This is why i told to close the resultset. Rule is : Every time you finish using a statement or a resultset close it. Exception: if you want to keep a cache of statements you only close them when you remove them from the cache or before closing the dbconnection. If think you are trying to make a statement cache. Take a look to the Enhydra (www.enhydra.org) DBLayer. They use preparedstatement cache for every dbconnection. Regards. João Paulo Ribeiro G.Nagarajan wrote: >hi, >i am planning to implement a pooling system for Prepared Statements. >It will open a single connection during initialization and create >many prepared statements using this connection. The connection will >be always kept open so that the prepared statements are valid. To >speed up things, there may be more than one prepared statement for >the same query, ie, > >stmt1 = "select from table where field = ?"; >stmt2 = "select from table where field = ?"; > >Will there be any issues when two statements are executed for the >same table using the same connection? has anyone tried doing something >like this or is there any open source tool which can do this? > >This will greatly improve the efficiency of the system as most of the time, >the connections are used for executing the same statements. Pooling the >statements instead of the connection will save the processing needed >for creating the statement objects for every query. > >Regards, >Nagarajan. > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > -- ---------------------------------------------------------------------------- MobiComp - Mobile Computing & Wireless Solutions phone: +351 253 305 250 fax: +351 253 305 251 web: http://www.mobicomp.com ----------------------------------------------------------------------------
hi,
Thanks for your reply. Better I knew this early, else i would have
spent hours trying to understand what went wrong!
I have some more questions.Is this a restriction of postgres,
the database driver or JDBC?
I also have lots of code like this:
sql = "select deptno, name from dept"
rs = stmt.executeQuery( sql );
while( rs.next() )
{
rs2 = "select empno, empname from emp where deptno = 1".
while( rs2.next() )
{
// sometimes rs3..
}
rs2.close()
}
rs.close()
they seem to work fine without any error messages. Here i am actually
having two resultsets open in the same connection, but on different
tables. Does it mean a bug waiting to occur at the right time?
So, i think for implementing the prepared statement cache, i have to
create the statements for each connection. something like
connection1 - statement1, statement2, statement3
connection2 - statement1, statement2, statement3
connection3 - statement1, statement2, statement3
Then wrap the connection in a class along with the prepared statement.
This would avoid the creation of the statement object but will still
use many connections.
Regards,
Nagarajan.
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of João Paulo Caldas
> Ribeiro
> Sent: Thursday, August 29, 2002 7:45 PM
> To: G.Nagarajan
> Cc: pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Pooling Prepared Statements
>
>
> Hi!
>
> Im sorry but: 1 connection -> 1 resultset at time.
> You can have many statement associated to a connection but when you
> execute the statemente you can only have 1 resultset associated to the
> connection at time.
> You can execute 2 statement using the same connection but you have to:
>
> -> execute statement1
> -> get the resultset1 and use it
> -> close resultset
> -> execute statement2
> -> get the resultset2 and use it
> -> close resultset
>
>
> If you still using the resultset1 when you execute the statement2 you'll
> destroy it.
> This is why i told to close the resultset.
> Rule is : Every time you finish using a statement or a resultset close it.
> Exception: if you want to keep a cache of statements you only close them
> when you remove them from the cache or before closing the dbconnection.
>
> If think you are trying to make a statement cache. Take a look to the
> Enhydra (www.enhydra.org) DBLayer. They use preparedstatement cache for
> every dbconnection.
>
> Regards.
> João Paulo Ribeiro
>
>
> G.Nagarajan wrote:
>
> >hi,
> >i am planning to implement a pooling system for Prepared Statements.
> >It will open a single connection during initialization and create
> >many prepared statements using this connection. The connection will
> >be always kept open so that the prepared statements are valid. To
> >speed up things, there may be more than one prepared statement for
> >the same query, ie,
> >
> >stmt1 = "select from table where field = ?";
> >stmt2 = "select from table where field = ?";
> >
> >Will there be any issues when two statements are executed for the
> >same table using the same connection? has anyone tried doing something
> >like this or is there any open source tool which can do this?
> >
> >This will greatly improve the efficiency of the system as most
> of the time,
> >the connections are used for executing the same statements. Pooling the
> >statements instead of the connection will save the processing needed
> >for creating the statement objects for every query.
> >
> >Regards,
> >Nagarajan.
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
> >
> >
>
>
> --
> ------------------------------------------------------------------
> ----------
> MobiComp - Mobile Computing & Wireless Solutions
> phone: +351 253 305 250 fax: +351 253 305 251
> web: http://www.mobicomp.com
> ------------------------------------------------------------------
> ----------
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
You are fortunate that this is an artifact of postgres.
the spec says
A ResultSet object is automatically closed when the Statement object
that generated it is closed, re-executed, or used to retrieve the next
result from a sequence of multiple results.
Dave
On Thu, 2002-08-29 at 14:52, G.Nagarajan wrote:
> hi,
> Thanks for your reply. Better I knew this early, else i would have
> spent hours trying to understand what went wrong!
>
> I have some more questions.Is this a restriction of postgres,
> the database driver or JDBC?
>
> I also have lots of code like this:
>
> sql = "select deptno, name from dept"
> rs = stmt.executeQuery( sql );
> while( rs.next() )
> {
> rs2 = "select empno, empname from emp where deptno = 1".
> while( rs2.next() )
> {
> // sometimes rs3..
> }
> rs2.close()
> }
> rs.close()
>
> they seem to work fine without any error messages. Here i am actually
> having two resultsets open in the same connection, but on different
> tables. Does it mean a bug waiting to occur at the right time?
>
> So, i think for implementing the prepared statement cache, i have to
> create the statements for each connection. something like
>
> connection1 - statement1, statement2, statement3
> connection2 - statement1, statement2, statement3
> connection3 - statement1, statement2, statement3
>
> Then wrap the connection in a class along with the prepared statement.
> This would avoid the creation of the statement object but will still
> use many connections.
>
> Regards,
> Nagarajan.
>
> > -----Original Message-----
> > From: pgsql-jdbc-owner@postgresql.org
> > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of João Paulo Caldas
> > Ribeiro
> > Sent: Thursday, August 29, 2002 7:45 PM
> > To: G.Nagarajan
> > Cc: pgsql-jdbc@postgresql.org
> > Subject: Re: [JDBC] Pooling Prepared Statements
> >
> >
> > Hi!
> >
> > Im sorry but: 1 connection -> 1 resultset at time.
> > You can have many statement associated to a connection but when you
> > execute the statemente you can only have 1 resultset associated to the
> > connection at time.
> > You can execute 2 statement using the same connection but you have to:
> >
> > -> execute statement1
> > -> get the resultset1 and use it
> > -> close resultset
> > -> execute statement2
> > -> get the resultset2 and use it
> > -> close resultset
> >
> >
> > If you still using the resultset1 when you execute the statement2 you'll
> > destroy it.
> > This is why i told to close the resultset.
> > Rule is : Every time you finish using a statement or a resultset close it.
> > Exception: if you want to keep a cache of statements you only close them
> > when you remove them from the cache or before closing the dbconnection.
> >
> > If think you are trying to make a statement cache. Take a look to the
> > Enhydra (www.enhydra.org) DBLayer. They use preparedstatement cache for
> > every dbconnection.
> >
> > Regards.
> > João Paulo Ribeiro
> >
> >
> > G.Nagarajan wrote:
> >
> > >hi,
> > >i am planning to implement a pooling system for Prepared Statements.
> > >It will open a single connection during initialization and create
> > >many prepared statements using this connection. The connection will
> > >be always kept open so that the prepared statements are valid. To
> > >speed up things, there may be more than one prepared statement for
> > >the same query, ie,
> > >
> > >stmt1 = "select from table where field = ?";
> > >stmt2 = "select from table where field = ?";
> > >
> > >Will there be any issues when two statements are executed for the
> > >same table using the same connection? has anyone tried doing something
> > >like this or is there any open source tool which can do this?
> > >
> > >This will greatly improve the efficiency of the system as most
> > of the time,
> > >the connections are used for executing the same statements. Pooling the
> > >statements instead of the connection will save the processing needed
> > >for creating the statement objects for every query.
> > >
> > >Regards,
> > >Nagarajan.
> > >
> > >
> > >---------------------------(end of broadcast)---------------------------
> > >TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > >
> > >
> > >
> > >
> >
> >
> > --
> > ------------------------------------------------------------------
> > ----------
> > MobiComp - Mobile Computing & Wireless Solutions
> > phone: +351 253 305 250 fax: +351 253 305 251
> > web: http://www.mobicomp.com
> > ------------------------------------------------------------------
> > ----------
> >
> >
> >
> >
> >
> > ---------------------------(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
>
>
João Paulo Caldas Ribeiro wrote: > Hi! > > Im sorry but: 1 connection -> 1 resultset at time. > You can have many statement associated to a connection but when you > execute the statemente you can only have 1 resultset associated to the > connection at time. > You can execute 2 statement using the same connection but you have to: As far as I know, that is not correct. At least if the documentation is correct: "You can use a single Statement instance as many times as you want. You could create one as soon as you open the connection and use it for the connection's lifetime. But you have to remember that only one ResultSet can exist per Statement or PreparedStatement at a given time." (http://www.postgresql.org/idocs/index.php?jdbc-query.html) So it's not 1 connection -> 1 resultset at a time, but 1 statement -> 1 resultset at a time. I would recommend reading the rest of the documention about JDBC (especially section 8.3.1) Regards, Michael Paesold
You are right. It's 1 statement -> 1 resultset at a time. As Dave said: "A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results. " My mistake. Thanks. JP Michael Paesold wrote: >João Paulo Caldas Ribeiro wrote: > > > >>Hi! >> >>Im sorry but: 1 connection -> 1 resultset at time. >>You can have many statement associated to a connection but when you >>execute the statemente you can only have 1 resultset associated to the >>connection at time. >>You can execute 2 statement using the same connection but you have to: >> >> > >As far as I know, that is not correct. At least if the documentation is >correct: > >"You can use a single Statement instance as many times as you want. You >could create one as soon as you open the connection and use it for the >connection's lifetime. But you have to remember that only one ResultSet can >exist per Statement or PreparedStatement at a given time." >(http://www.postgresql.org/idocs/index.php?jdbc-query.html) > >So it's not 1 connection -> 1 resultset at a time, but 1 statement -> 1 >resultset at a time. I would recommend reading the rest of the documention >about JDBC (especially section 8.3.1) > >Regards, >Michael Paesold > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly > > > > -- ---------------------------------------------------------------------------- MobiComp - Mobile Computing & Wireless Solutions phone: +351 253 305 250 fax: +351 253 305 251 web: http://www.mobicomp.com ----------------------------------------------------------------------------
Hi!
First at all. I apolagize because a made a mistake: it's 1 statement ->
1 resultset (thanks again Michael ;) )
Second. I you are correct when you said:
"Then wrap the connection in a class along with the prepared statement.
This would avoid the creation of the statement object but will still
use many connections."
I recommed you to see some code example about preparedstatement cache.
The usual is wrapping the connection and cache (usually a simple Hashtable) in MyConnection, for example.
You write all the methods that are usually used in a connection ( getPreparedStatement, executeQuery, etc).
But when someone try to execute a query, before you really execute it, you verify if its not in the cache.
Again, usally the cache is an Hashtable and the keys are SQL String (the query) and the values are the statements.
when you try to execute the query it will do something like this:
public synchronized PreparedStatement prepareStatement (String sql)
throws SQLException {
PreparedStatement preparedStmt;
preparedStmt = (PreparedStatement)preparedStmtCache.get(sql);
if (preparedStmt != null) {
preparedStmt.clearParameters();
}
else {
if (stmtCache.size() >= maxPreparedStmts) {
String key = (String)stmtCache.keys().nextElement();
((PreparedStatement) stmtCache.remove(key)).close();
}
preparedStmt = connection.prepareStatement(sql);
stmtCache.put(sql, preparedStmt);
}
return preparedStmt;
This is an example based in the preparedStatement cache in the Enhydra
com.lutris.appserver.server.sql.StandardDBConnection.java.
The tip to not use to much db conncetion it's to use connections pool.
When you need a connection you ask the pool to allocate one for you and when dont use connection anymore (you finished
processingthe result of your query ) you realese the conncetion. The release of the connection dont close it but give
itback to the pool.
The big advantages of using a pool:
- you need fewer conncetions because as soon as the connection is realesed, it's ready to be allocated by another
thread.
- you dont need to be allways creating and destroying connections: the pool manage it for you.
Almost all (may be all) the application Servers use database conncetion pooling because they usally have a lot o
threadsthat made very quick queries so they can easily share the connectins using the pool.
As an example, at the moment i have a site with 120 servlets (1400 active sessions) and they are using only 30
connectionsto the db.
Regards.
JP
G.Nagarajan wrote:
>hi,
>Thanks for your reply. Better I knew this early, else i would have
>spent hours trying to understand what went wrong!
>
>I have some more questions.Is this a restriction of postgres,
>the database driver or JDBC?
>
>I also have lots of code like this:
>
>sql = "select deptno, name from dept"
>rs = stmt.executeQuery( sql );
>while( rs.next() )
>{
> rs2 = "select empno, empname from emp where deptno = 1".
> while( rs2.next() )
> {
> // sometimes rs3..
> }
> rs2.close()
>}
>rs.close()
>
>they seem to work fine without any error messages. Here i am actually
>having two resultsets open in the same connection, but on different
>tables. Does it mean a bug waiting to occur at the right time?
>
> So, i think for implementing the prepared statement cache, i have to
>create the statements for each connection. something like
>
> connection1 - statement1, statement2, statement3
> connection2 - statement1, statement2, statement3
> connection3 - statement1, statement2, statement3
>
> Then wrap the connection in a class along with the prepared statement.
>This would avoid the creation of the statement object but will still
>use many connections.
>
>Regards,
>Nagarajan.
>
>
>
>>-----Original Message-----
>>From: pgsql-jdbc-owner@postgresql.org
>>[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of João Paulo Caldas
>>Ribeiro
>>Sent: Thursday, August 29, 2002 7:45 PM
>>To: G.Nagarajan
>>Cc: pgsql-jdbc@postgresql.org
>>Subject: Re: [JDBC] Pooling Prepared Statements
>>
>>
>>Hi!
>>
>>Im sorry but: 1 connection -> 1 resultset at time.
>>You can have many statement associated to a connection but when you
>>execute the statemente you can only have 1 resultset associated to the
>>connection at time.
>>You can execute 2 statement using the same connection but you have to:
>>
>>-> execute statement1
>>-> get the resultset1 and use it
>>-> close resultset
>>-> execute statement2
>>-> get the resultset2 and use it
>>-> close resultset
>>
>>
>>If you still using the resultset1 when you execute the statement2 you'll
>>destroy it.
>>This is why i told to close the resultset.
>>Rule is : Every time you finish using a statement or a resultset close it.
>>Exception: if you want to keep a cache of statements you only close them
>>when you remove them from the cache or before closing the dbconnection.
>>
>>If think you are trying to make a statement cache. Take a look to the
>>Enhydra (www.enhydra.org) DBLayer. They use preparedstatement cache for
>>every dbconnection.
>>
>>Regards.
>>João Paulo Ribeiro
>>
>>
>>G.Nagarajan wrote:
>>
>>
>>
>>>hi,
>>>i am planning to implement a pooling system for Prepared Statements.
>>>It will open a single connection during initialization and create
>>>many prepared statements using this connection. The connection will
>>>be always kept open so that the prepared statements are valid. To
>>>speed up things, there may be more than one prepared statement for
>>>the same query, ie,
>>>
>>>stmt1 = "select from table where field = ?";
>>>stmt2 = "select from table where field = ?";
>>>
>>>Will there be any issues when two statements are executed for the
>>>same table using the same connection? has anyone tried doing something
>>>like this or is there any open source tool which can do this?
>>>
>>>This will greatly improve the efficiency of the system as most
>>>
>>>
>>of the time,
>>
>>
>>>the connections are used for executing the same statements. Pooling the
>>>statements instead of the connection will save the processing needed
>>>for creating the statement objects for every query.
>>>
>>>Regards,
>>>Nagarajan.
>>>
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 2: you can get off all lists at once with the unregister command
>>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>>
>>>
>>>
>>>
>>>
>>>
>>--
>>------------------------------------------------------------------
>>----------
>>MobiComp - Mobile Computing & Wireless Solutions
>>phone: +351 253 305 250 fax: +351 253 305 251
>>web: http://www.mobicomp.com
>>------------------------------------------------------------------
>>----------
>>
>>
>>
>>
>>
>>---------------------------(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
>
>
>
>
--
----------------------------------------------------------------------------
MobiComp - Mobile Computing & Wireless Solutions
phone: +351 253 305 250 fax: +351 253 305 251
web: http://www.mobicomp.com
----------------------------------------------------------------------------
On Thu, 29 Aug 2002, Michael Paesold wrote: > So it's not 1 connection -> 1 resultset at a time, but 1 statement -> 1 > resultset at a time. Sorry, but there are many JDBC drivers out there that allow only one resultset on a connection at a time. You may get more than one per connection with some drivers, but that's not portable. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC
I forgot to add, the resultsets are from different
statements. the code is actually
getConnection()
open Rs1 using separate statement
while( Rs1.next() )
{
open Rs2 using another statement from same connection.
while( Rs2.next() )
{
...
}
}
This should give problems only when i reuse the same statement.
It means that my existing code will not give any problems.
Regards,
Nagarajan.
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> Sent: Thursday, August 29, 2002 10:25 PM
> To: G.Nagarajan
> Cc: João Paulo Caldas Ribeiro;
> Subject: Re: [JDBC] Pooling Prepared Statements
>
>
> You are fortunate that this is an artifact of postgres.
>
> the spec says
>
> A ResultSet object is automatically closed when the Statement object
> that generated it is closed, re-executed, or used to retrieve the next
> result from a sequence of multiple results.
>
> Dave
> On Thu, 2002-08-29 at 14:52, G.Nagarajan wrote:
> > hi,
> > Thanks for your reply. Better I knew this early, else i would have
> > spent hours trying to understand what went wrong!
> >
> > I have some more questions.Is this a restriction of postgres,
> > the database driver or JDBC?
> >
> > I also have lots of code like this:
> >
> > sql = "select deptno, name from dept"
> > rs = stmt.executeQuery( sql );
> > while( rs.next() )
> > {
> > rs2 = "select empno, empname from emp where deptno = 1".
> > while( rs2.next() )
> > {
> > // sometimes rs3..
> > }
> > rs2.close()
> > }
> > rs.close()
> >
> > they seem to work fine without any error messages. Here i am actually
> > having two resultsets open in the same connection, but on different
> > tables. Does it mean a bug waiting to occur at the right time?
> >
> > So, i think for implementing the prepared statement cache, i have to
> > create the statements for each connection. something like
> >
> > connection1 - statement1, statement2, statement3
> > connection2 - statement1, statement2, statement3
> > connection3 - statement1, statement2, statement3
> >
> > Then wrap the connection in a class along with the prepared statement.
> > This would avoid the creation of the statement object but will still
> > use many connections.
> >
> > Regards,
> > Nagarajan.
> >
> > > -----Original Message-----
> > > From: pgsql-jdbc-owner@postgresql.org
> > > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of João Paulo Caldas
> > > Ribeiro
> > > Sent: Thursday, August 29, 2002 7:45 PM
> > > To: G.Nagarajan
> > > Cc: pgsql-jdbc@postgresql.org
> > > Subject: Re: [JDBC] Pooling Prepared Statements
> > >
> > >
> > > Hi!
> > >
> > > Im sorry but: 1 connection -> 1 resultset at time.
> > > You can have many statement associated to a connection but when you
> > > execute the statemente you can only have 1 resultset associated to the
> > > connection at time.
> > > You can execute 2 statement using the same connection but you have to:
> > >
> > > -> execute statement1
> > > -> get the resultset1 and use it
> > > -> close resultset
> > > -> execute statement2
> > > -> get the resultset2 and use it
> > > -> close resultset
> > >
> > >
> > > If you still using the resultset1 when you execute the
> statement2 you'll
> > > destroy it.
> > > This is why i told to close the resultset.
> > > Rule is : Every time you finish using a statement or a
> resultset close it.
> > > Exception: if you want to keep a cache of statements you only
> close them
> > > when you remove them from the cache or before closing the
> dbconnection.
> > >
> > > If think you are trying to make a statement cache. Take a look to the
> > > Enhydra (www.enhydra.org) DBLayer. They use preparedstatement
> cache for
> > > every dbconnection.
> > >
> > > Regards.
> > > João Paulo Ribeiro
> > >
> > >
> > > G.Nagarajan wrote:
> > >
> > > >hi,
> > > >i am planning to implement a pooling system for Prepared Statements.
> > > >It will open a single connection during initialization and create
> > > >many prepared statements using this connection. The connection will
> > > >be always kept open so that the prepared statements are valid. To
> > > >speed up things, there may be more than one prepared statement for
> > > >the same query, ie,
> > > >
> > > >stmt1 = "select from table where field = ?";
> > > >stmt2 = "select from table where field = ?";
> > > >
> > > >Will there be any issues when two statements are executed for the
> > > >same table using the same connection? has anyone tried doing
> something
> > > >like this or is there any open source tool which can do this?
> > > >
> > > >This will greatly improve the efficiency of the system as most
> > > of the time,
> > > >the connections are used for executing the same statements.
> Pooling the
> > > >statements instead of the connection will save the processing needed
> > > >for creating the statement objects for every query.
> > > >
> > > >Regards,
> > > >Nagarajan.
> > > >
> > > >
> > > >---------------------------(end of
> broadcast)---------------------------
> > > >TIP 2: you can get off all lists at once with the unregister command
> > > > (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > > --
> > > ------------------------------------------------------------------
> > > ----------
> > > MobiComp - Mobile Computing & Wireless Solutions
> > > phone: +351 253 305 250 fax: +351 253 305 251
> > > web: http://www.mobicomp.com
> > > ------------------------------------------------------------------
> > > ----------
> > >
> > >
> > >
> > >
> > >
> > > ---------------------------(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 4: Don't 'kill -9' the postmaster
Connections/Statements/ResultSets (Was: Re: Pooling Prepared Statements)
From
"Michael Paesold"
Date:
Curt Sampson wrote: > On Thu, 29 Aug 2002, Michael Paesold wrote: > > > So it's not 1 connection -> 1 resultset at a time, but 1 statement -> 1 > > resultset at a time. > > Sorry, but there are many JDBC drivers out there that allow only > one resultset on a connection at a time. You may get more than one > per connection with some drivers, but that's not portable. > > cjs Does anyone of you know exactly, what the JDBC specification says about more than one result set per connection at a time? If not, can you give me a URL where I can have a look at the specs myself? Michael
On Fri, 30 Aug 2002, Michael Paesold wrote: > Does anyone of you know exactly, what the JDBC specification says about > more than one result set per connection at a time? The JDBC 2.1 specification says nothing about this, as far as I can tell. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC
The spec probably doesn't say anything but; with the way pg's driver is currently written you can have as many resultsets/statements open as you want. This is because the driver will read them all in when you do an executeXXX. There is a plan however to move to a cursor backed result set which will change the above statement. This is because a cursor must be executed within a transaction, and you can only have one transaction open per resultset. I suppose it's possible to still open a non-cursor based resultset while the transaction is open? Dave On Fri, 2002-08-30 at 04:34, Michael Paesold wrote: > Curt Sampson wrote: > > > On Thu, 29 Aug 2002, Michael Paesold wrote: > > > > > So it's not 1 connection -> 1 resultset at a time, but 1 statement -> 1 > > > resultset at a time. > > > > Sorry, but there are many JDBC drivers out there that allow only > > one resultset on a connection at a time. You may get more than one > > per connection with some drivers, but that's not portable. > > > > cjs > > > Does anyone of you know exactly, what the JDBC specification says about > more than one result set per connection at a time? > > If not, can you give me a URL where I can have a look at the specs myself? > > Michael > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
I dont know about the JDBC specs but i remember that i used a Oracle JDBC driver that only supported one 1 connection --> 1 resultset at time. I will try to find something about the specs. Regards. JP Dave Cramer wrote: >The spec probably doesn't say anything but; with the way pg's driver is >currently written you can have as many resultsets/statements open as you >want. This is because the driver will read them all in when you do an >executeXXX. There is a plan however to move to a cursor backed result >set which will change the above statement. This is because a cursor must >be executed within a transaction, and you can only have one transaction >open per resultset. I suppose it's possible to still open a non-cursor >based resultset while the transaction is open? > >Dave > >On Fri, 2002-08-30 at 04:34, Michael Paesold wrote: > > >>Curt Sampson wrote: >> >> >> >>>On Thu, 29 Aug 2002, Michael Paesold wrote: >>> >>> >>> >>>>So it's not 1 connection -> 1 resultset at a time, but 1 statement -> 1 >>>>resultset at a time. >>>> >>>> >>>Sorry, but there are many JDBC drivers out there that allow only >>>one resultset on a connection at a time. You may get more than one >>>per connection with some drivers, but that's not portable. >>> >>>cjs >>> >>> >>Does anyone of you know exactly, what the JDBC specification says about >>more than one result set per connection at a time? >> >>If not, can you give me a URL where I can have a look at the specs myself? >> >>Michael >> >> >>---------------------------(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 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > -- ---------------------------------------------------------------------------- MobiComp - Mobile Computing & Wireless Solutions phone: +351 253 305 250 fax: +351 253 305 251 web: http://www.mobicomp.com ----------------------------------------------------------------------------
Dave Cramer wrote: > The spec probably doesn't say anything but; with the way pg's driver is > currently written you can have as many resultsets/statements open as you > want. This is because the driver will read them all in when you do an > executeXXX. There is a plan however to move to a cursor backed result > set which will change the above statement. This is because a cursor must > be executed within a transaction, and you can only have one transaction > open per resultset. I suppose it's possible to still open a non-cursor > based resultset while the transaction is open? > > Dave Did you really want to say > ... you can only have one transaction open per resultset ? Shouldn't it be per connection? If so, there would be several scenarios, if I am correct. 1. connection.getAutoCommit is false Since it's possible to have several cursors within one transaction, one could also have more than one cursor based result set per connection. I don't know if this is a good idea, because if 2. connection.getAutoCommit is true then everything looks differnt. You would have to open a transaction for a cursor based result set. What happens to the auto commit state of the connection? Calling setAutoCommit while having a result set open would break the result set, since: "NOTE: If this method is called during a transaction, the transaction is committed." (J2SE 1.4 API on java.sql.Connection) > I suppose it's possible to still open a non-cursor > based resultset while the transaction is open? That would be nice! At least the non-cursor based resultset should not be removed from the driver. Have a look at createStatement (J2SE 1.4 API, don't have any older). This is the most specific prototype of createStatement: createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) I suppost the decision about cursor/non-cursor result set should be made based on these attributes (resultSetConcurrency, resultSetHoldability). Correct me, if I'm wrong. Best Regards, Michael Paesold
João Paulo Caldas Ribeiro wrote: > I dont know about the JDBC specs but i remember that i used a Oracle > JDBC driver that only supported one 1 connection --> 1 resultset at time. > I will try to find something about the specs. Probably the specs really don't specify this, but they will require that the methods getResultSetConcurrency(), getResultSetHoldability() and getResultSetType() return the correct values. Regards. Michael
I found something in http://java.sun.com/products/jdbc/driverdevs.html A.1.6 Support Multithreading ... Some drivers may allow more concurrent execution than others, but developers should be able to assume fully concurrent execution. If the driver requires some form of synchronization, then the driver should provide it. In this situation, the only difference visible to the developer should be that applications run with reduced concurrency. For example, two Statement objects on the same connection can be executed concurrently, and their ResultSets can be processed concurrently (from the perspective of the developer). Some drivers will provide this full concurrency. Others may execute one statement and wait until it completes before sending the next one. Regards. JP Michael Paesold wrote: >João Paulo Caldas Ribeiro wrote: > > > > >>I dont know about the JDBC specs but i remember that i used a Oracle >>JDBC driver that only supported one 1 connection --> 1 resultset at time. >>I will try to find something about the specs. >> >> > > >Probably the specs really don't specify this, but they will require that >the methods getResultSetConcurrency(), getResultSetHoldability() and >getResultSetType() return the correct values. > >Regards. >Michael > > > > > -- ---------------------------------------------------------------------------- MobiComp - Mobile Computing & Wireless Solutions phone: +351 253 305 250 fax: +351 253 305 251 web: http://www.mobicomp.com ----------------------------------------------------------------------------
Hi, João Paulo Caldas Ribeiro wrote: > I dont know about the JDBC specs but i remember that i used a Oracle > JDBC driver that only supported one 1 connection --> 1 resultset at time. I do not know what is your Oracle JDBC version, we useoracle on many prjects and do not have any problems with more resultset per connection. I think in the spec. exist nothink about it. Regards, Ivan. > I will try to find something about the specs. > > Regards. > JP > > Dave Cramer wrote: > > >The spec probably doesn't say anything but; with the way pg's driver is > >currently written you can have as many resultsets/statements open as you > >want. This is because the driver will read them all in when you do an > >executeXXX. There is a plan however to move to a cursor backed result > >set which will change the above statement. This is because a cursor must > >be executed within a transaction, and you can only have one transaction > >open per resultset. I suppose it's possible to still open a non-cursor > >based resultset while the transaction is open? > > > >Dave > > > >On Fri, 2002-08-30 at 04:34, Michael Paesold wrote: > > > > > >>Curt Sampson wrote: > >> > >> > >> > >>>On Thu, 29 Aug 2002, Michael Paesold wrote: > >>> > >>> > >>> > >>>>So it's not 1 connection -> 1 resultset at a time, but 1 statement -> 1 > >>>>resultset at a time. > >>>> > >>>> > >>>Sorry, but there are many JDBC drivers out there that allow only > >>>one resultset on a connection at a time. You may get more than one > >>>per connection with some drivers, but that's not portable. > >>> > >>>cjs > >>> > >>> > >>Does anyone of you know exactly, what the JDBC specification says about > >>more than one result set per connection at a time? > >> > >>If not, can you give me a URL where I can have a look at the specs myself? > >> > >>Michael > >> > >> > >>---------------------------(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 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > > > > > > -- > ---------------------------------------------------------------------------- > MobiComp - Mobile Computing & Wireless Solutions > phone: +351 253 305 250 fax: +351 253 305 251 > web: http://www.mobicomp.com > ---------------------------------------------------------------------------- > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
This was in a project 3 years ago. Dont give it to much importance. Regards. JP pginfo wrote: >Hi, > >João Paulo Caldas Ribeiro wrote: > > > >>I dont know about the JDBC specs but i remember that i used a Oracle >>JDBC driver that only supported one 1 connection --> 1 resultset at time. >> >> > >I do not know what is your Oracle JDBC version, we useoracle on many prjects and >do not have any problems with more resultset per connection. > >I think in the spec. exist nothink about it. > >Regards, >Ivan. > > > >>I will try to find something about the specs. >> >>Regards. >>JP >> >>Dave Cramer wrote: >> >> >> >>>The spec probably doesn't say anything but; with the way pg's driver is >>>currently written you can have as many resultsets/statements open as you >>>want. This is because the driver will read them all in when you do an >>>executeXXX. There is a plan however to move to a cursor backed result >>>set which will change the above statement. This is because a cursor must >>>be executed within a transaction, and you can only have one transaction >>>open per resultset. I suppose it's possible to still open a non-cursor >>>based resultset while the transaction is open? >>> >>>Dave >>> >>>On Fri, 2002-08-30 at 04:34, Michael Paesold wrote: >>> >>> >>> >>> >>>>Curt Sampson wrote: >>>> >>>> >>>> >>>> >>>> >>>>>On Thu, 29 Aug 2002, Michael Paesold wrote: >>>>> >>>>> >>>>> >>>>> >>>>> >>>>>>So it's not 1 connection -> 1 resultset at a time, but 1 statement -> 1 >>>>>>resultset at a time. >>>>>> >>>>>> >>>>>> >>>>>> >>>>>Sorry, but there are many JDBC drivers out there that allow only >>>>>one resultset on a connection at a time. You may get more than one >>>>>per connection with some drivers, but that's not portable. >>>>> >>>>>cjs >>>>> >>>>> >>>>> >>>>> >>>>Does anyone of you know exactly, what the JDBC specification says about >>>>more than one result set per connection at a time? >>>> >>>>If not, can you give me a URL where I can have a look at the specs myself? >>>> >>>>Michael >>>> >>>> >>>>---------------------------(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 2: you can get off all lists at once with the unregister command >>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >>> >>> >>> >>> >>> >>> >>-- >>---------------------------------------------------------------------------- >>MobiComp - Mobile Computing & Wireless Solutions >>phone: +351 253 305 250 fax: +351 253 305 251 >>web: http://www.mobicomp.com >>---------------------------------------------------------------------------- >> >>---------------------------(end of broadcast)--------------------------- >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >> >> > > > > > > > -- ---------------------------------------------------------------------------- MobiComp - Mobile Computing & Wireless Solutions phone: +351 253 305 250 fax: +351 253 305 251 web: http://www.mobicomp.com ----------------------------------------------------------------------------
Postgres doesn't support nested transactions test=# begin; LOG: query: begin; BEGIN test=# begin; LOG: query: begin; WARNING: BEGIN: already a transaction in progress BEGIN test=# On Fri, 2002-08-30 at 06:37, Michael Paesold wrote: > Dave Cramer wrote: > > > The spec probably doesn't say anything but; with the way pg's driver is > > currently written you can have as many resultsets/statements open as you > > want. This is because the driver will read them all in when you do an > > executeXXX. There is a plan however to move to a cursor backed result > > set which will change the above statement. This is because a cursor must > > be executed within a transaction, and you can only have one transaction > > open per resultset. I suppose it's possible to still open a non-cursor > > based resultset while the transaction is open? > > > > Dave > > Did you really want to say > > ... you can only have one transaction open per resultset > ? > Shouldn't it be per connection? > If so, there would be several scenarios, if I am correct. > > 1. connection.getAutoCommit is false > > Since it's possible to have several cursors within one transaction, one > could also have more than one cursor based result set per connection. > I don't know if this is a good idea, because if > > 2. connection.getAutoCommit is true > > then everything looks differnt. You would have to open a transaction for > a cursor based result set. What happens to the auto commit state of the > connection? Calling setAutoCommit while having a result set open would > break the result set, since: > "NOTE: If this method is called during a transaction, the transaction is > committed." > (J2SE 1.4 API on java.sql.Connection) yes, look at the code in the driver to see what it does when you call setAutoCommit(false); It actually issues a begin and end for every query > > > I suppose it's possible to still open a non-cursor > > based resultset while the transaction is open? > > That would be nice! At least the non-cursor based resultset should not > be removed from the driver. Have a look at createStatement > (J2SE 1.4 API, don't have any older). Yes, the way it will work is that if you call setFetchSize() then it will use cursors, otherwise no > > This is the most specific prototype of createStatement: > createStatement(int resultSetType, int resultSetConcurrency, int > resultSetHoldability) > > I suppost the decision about cursor/non-cursor result set should be made > based on these attributes (resultSetConcurrency, resultSetHoldability). > > Correct me, if I'm wrong. > > Best Regards, > Michael Paesold > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
>You are right. It's 1 statement -> 1 resultset at a time.
>
>As Dave said: "A ResultSet object is automatically closed when the
>Statement object
>that generated it is closed, re-executed, or used to retrieve the next
>result from a sequence of multiple results. "
>
>My mistake.
It is still a good idea to close result sets after use. If you
rely on automatically closing them you might have some wasted
resources lying around because you might execute a statement
and never reuse it.
It is also a good idea to explicitly close statements and
connections.
Best regards
Rainer Klute
Rainer Klute IT-Consulting GmbH
Dipl.-Inform.
Rainer Klute E-Mail: klute@rainer-klute.de
Körner Grund 24 Telefon: +49 172 2324824
D-44143 Dortmund Telefax: +49 231 5349423
This is what i found in the specs of the JDBC 3.0 ( http://java.sun.com/products/jdbc/download.html#corespec30) 10.1 Transaction Boundaries and Auto-commit ... For Insert, Update, Delete, and DDL statements, the statement is complete as soon as it has finished executing. For Select statements, the statement is complete when the associated result set is closed. The result set is closed as soon as one of the following occurs: - all of the rows have been retrieved - the associated Statement object is re-executed - another Statement object is executed on the same connection Looks like last phrase say that i can't have more than one resultSet per connection at time. I known that this is not the case with the PostgreSql JDBC driver ( i made some testes :) ). This is a feature of the PostgreSql JDBC driver? Regards. JP Michael Paesold wrote: >João Paulo Caldas Ribeiro wrote: > > > > >>I dont know about the JDBC specs but i remember that i used a Oracle >>JDBC driver that only supported one 1 connection --> 1 resultset at time. >>I will try to find something about the specs. >> >> > > >Probably the specs really don't specify this, but they will require that >the methods getResultSetConcurrency(), getResultSetHoldability() and >getResultSetType() return the correct values. > >Regards. >Michael > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > -- ---------------------------------------------------------------------------- MobiComp - Mobile Computing & Wireless Solutions phone: +351 253 305 250 fax: +351 253 305 251 web: http://www.mobicomp.com ----------------------------------------------------------------------------
Hmmm, the title is "Transaction Boundaries and Auto-commit", this seems to apply in the case when autocommit is on??? In an earlier mail you sent from the spec it clearly states that you can have multiple ResultSets open and processed concurrently on the same connection "A.1.6 Support Multi-threading". Hmmm. Confusing... I must RTFM and make up my own mind. In either case, it would be nice to have a nice big red flag waved on the list if the multiple ResultSet thing is going to go away. It will definitely break a lot of my code and I'm not so sure its so wrong. Tom. On Fri, 2002-08-30 at 23:41, João Paulo Caldas Ribeiro wrote: > This is what i found in the specs of the JDBC 3.0 ( > http://java.sun.com/products/jdbc/download.html#corespec30) > > 10.1 Transaction Boundaries and Auto-commit > ... > For Insert, Update, Delete, and DDL statements, the statement is > complete as soon > as it has finished executing. > For Select statements, the statement is complete when the associated > result set is > closed. The result set is closed as soon as one of the following occurs: > - all of the rows have been retrieved > - the associated Statement object is re-executed > - another Statement object is executed on the same connection > > Looks like last phrase say that i can't have more than one resultSet per > connection at time. > I known that this is not the case with the PostgreSql JDBC driver ( i > made some testes :) ). > > This is a feature of the PostgreSql JDBC driver? > > Regards. > JP > > > Michael Paesold wrote: > > >João Paulo Caldas Ribeiro wrote: > > > > > > > > > >>I dont know about the JDBC specs but i remember that i used a Oracle > >>JDBC driver that only supported one 1 connection --> 1 resultset at time. > >>I will try to find something about the specs. > >> > >> > > > > > >Probably the specs really don't specify this, but they will require that > >the methods getResultSetConcurrency(), getResultSetHoldability() and > >getResultSetType() return the correct values. > > > >Regards. > >Michael > > > > > >---------------------------(end of broadcast)--------------------------- > >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > > > > > > > -- > ---------------------------------------------------------------------------- > MobiComp - Mobile Computing & Wireless Solutions > phone: +351 253 305 250 fax: +351 253 305 251 > web: http://www.mobicomp.com > ---------------------------------------------------------------------------- > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Thomas O'Dowd. - Nooping - http://nooper.com tom@nooper.com - Testing - http://nooper.co.jp/labs
Hi! The rule is: Close any object that refer or use something remote (connections, statements, resultsets) as soon as possible. Because this kind of object are not automatically garbage collected and will remaind until you explecitly close them. The exception is: if this kind of object are pooled then never close them explicitly just release them back to the pool ( sometimes the method to release the object to the pool is called ".close()" but it dont explicitly close the object. It still juts give it back to the pool). The pool is reponsable to decide if it need more objects and create them or have to much (or to old objects?) and decide to destroy/close them. Regards. JP Rainer Klute wrote: >>You are right. It's 1 statement -> 1 resultset at a time. >> >>As Dave said: "A ResultSet object is automatically closed when the >>Statement object >>that generated it is closed, re-executed, or used to retrieve the next >>result from a sequence of multiple results. " >> >>My mistake. >> >> > >It is still a good idea to close result sets after use. If you >rely on automatically closing them you might have some wasted >resources lying around because you might execute a statement >and never reuse it. > >It is also a good idea to explicitly close statements and >connections. > >Best regards >Rainer Klute > > Rainer Klute IT-Consulting GmbH > Dipl.-Inform. > Rainer Klute E-Mail: klute@rainer-klute.de > Körner Grund 24 Telefon: +49 172 2324824 >D-44143 Dortmund Telefax: +49 231 5349423 > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly > > > > -- ---------------------------------------------------------------------------- MobiComp - Mobile Computing & Wireless Solutions phone: +351 253 305 250 fax: +351 253 305 251 web: http://www.mobicomp.com ----------------------------------------------------------------------------
I underestand you: I'm confused too. :) But at the moment i can tell you that postgresql support 1 connection --> more than 1 resultset at time. I have tested this case explicitly and it really work . I will try to put the question to the JDBC forum. I hope they can explain this. Regards. JP Thomas O'Dowd wrote: >Hmmm, the title is "Transaction Boundaries and Auto-commit", this seems >to apply in the case when autocommit is on??? In an earlier mail you >sent from the spec it clearly states that you can have multiple >ResultSets open and processed concurrently on the same connection "A.1.6 >Support Multi-threading". Hmmm. Confusing... I must RTFM and make up my >own mind. > >In either case, it would be nice to have a nice big red flag waved on >the list if the multiple ResultSet thing is going to go away. It will >definitely break a lot of my code and I'm not so sure its so wrong. > >Tom. > >On Fri, 2002-08-30 at 23:41, João Paulo Caldas Ribeiro wrote: > > >>This is what i found in the specs of the JDBC 3.0 ( >>http://java.sun.com/products/jdbc/download.html#corespec30) >> >>10.1 Transaction Boundaries and Auto-commit >>... >>For Insert, Update, Delete, and DDL statements, the statement is >>complete as soon >>as it has finished executing. >>For Select statements, the statement is complete when the associated >>result set is >>closed. The result set is closed as soon as one of the following occurs: >>- all of the rows have been retrieved >>- the associated Statement object is re-executed >>- another Statement object is executed on the same connection >> >>Looks like last phrase say that i can't have more than one resultSet per >>connection at time. >>I known that this is not the case with the PostgreSql JDBC driver ( i >>made some testes :) ). >> >>This is a feature of the PostgreSql JDBC driver? >> >>Regards. >>JP >> >> >>Michael Paesold wrote: >> >> >> >>>João Paulo Caldas Ribeiro wrote: >>> >>> >>> >>> >>> >>> >>>>I dont know about the JDBC specs but i remember that i used a Oracle >>>>JDBC driver that only supported one 1 connection --> 1 resultset at time. >>>>I will try to find something about the specs. >>>> >>>> >>>> >>>> >>>Probably the specs really don't specify this, but they will require that >>>the methods getResultSetConcurrency(), getResultSetHoldability() and >>>getResultSetType() return the correct values. >>> >>>Regards. >>>Michael >>> >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >>> >>> >>> >>> >>> >>> >>-- >>---------------------------------------------------------------------------- >>MobiComp - Mobile Computing & Wireless Solutions >>phone: +351 253 305 250 fax: +351 253 305 251 >>web: http://www.mobicomp.com >>---------------------------------------------------------------------------- >> >> >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >> >> -- ---------------------------------------------------------------------------- MobiComp - Mobile Computing & Wireless Solutions phone: +351 253 305 250 fax: +351 253 305 251 web: http://www.mobicomp.com ----------------------------------------------------------------------------
You can definately have more than one result set open per connection, as long as you don't have more than 1 result set per statement. If and when cursor backed result sets get implemented then you will still be able to. The cursor will only be used if you explicitly call setFetchSize Dave On Fri, 2002-08-30 at 11:35, João Paulo Caldas Ribeiro wrote: > I underestand you: I'm confused too. :) > > But at the moment i can tell you that postgresql support 1 connection > --> more than 1 resultset at time. > I have tested this case explicitly and it really work . > > I will try to put the question to the JDBC forum. I hope they can > explain this. > > Regards. > JP > > Thomas O'Dowd wrote: > > >Hmmm, the title is "Transaction Boundaries and Auto-commit", this seems > >to apply in the case when autocommit is on??? In an earlier mail you > >sent from the spec it clearly states that you can have multiple > >ResultSets open and processed concurrently on the same connection "A.1.6 > >Support Multi-threading". Hmmm. Confusing... I must RTFM and make up my > >own mind. > > > >In either case, it would be nice to have a nice big red flag waved on > >the list if the multiple ResultSet thing is going to go away. It will > >definitely break a lot of my code and I'm not so sure its so wrong. > > > >Tom. > > > >On Fri, 2002-08-30 at 23:41, João Paulo Caldas Ribeiro wrote: > > > > > >>This is what i found in the specs of the JDBC 3.0 ( > >>http://java.sun.com/products/jdbc/download.html#corespec30) > >> > >>10.1 Transaction Boundaries and Auto-commit > >>... > >>For Insert, Update, Delete, and DDL statements, the statement is > >>complete as soon > >>as it has finished executing. > >>For Select statements, the statement is complete when the associated > >>result set is > >>closed. The result set is closed as soon as one of the following occurs: > >>- all of the rows have been retrieved > >>- the associated Statement object is re-executed > >>- another Statement object is executed on the same connection > >> > >>Looks like last phrase say that i can't have more than one resultSet per > >>connection at time. > >>I known that this is not the case with the PostgreSql JDBC driver ( i > >>made some testes :) ). > >> > >>This is a feature of the PostgreSql JDBC driver? > >> > >>Regards. > >>JP > >> > >> > >>Michael Paesold wrote: > >> > >> > >> > >>>João Paulo Caldas Ribeiro wrote: > >>> > >>> > >>> > >>> > >>> > >>> > >>>>I dont know about the JDBC specs but i remember that i used a Oracle > >>>>JDBC driver that only supported one 1 connection --> 1 resultset at time. > >>>>I will try to find something about the specs. > >>>> > >>>> > >>>> > >>>> > >>>Probably the specs really don't specify this, but they will require that > >>>the methods getResultSetConcurrency(), getResultSetHoldability() and > >>>getResultSetType() return the correct values. > >>> > >>>Regards. > >>>Michael > >>> > >>> > >>>---------------------------(end of broadcast)--------------------------- > >>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >>> > >>> > >>> > >>> > >>> > >>> > >>-- > >>---------------------------------------------------------------------------- > >>MobiComp - Mobile Computing & Wireless Solutions > >>phone: +351 253 305 250 fax: +351 253 305 251 > >>web: http://www.mobicomp.com > >>---------------------------------------------------------------------------- > >> > >> > >> > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 2: you can get off all lists at once with the unregister command > >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >> > >> > > > -- > ---------------------------------------------------------------------------- > MobiComp - Mobile Computing & Wireless Solutions > phone: +351 253 305 250 fax: +351 253 305 251 > web: http://www.mobicomp.com > ---------------------------------------------------------------------------- > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > >
On 31 Aug 2002, Thomas O'Dowd wrote: > In either case, it would be nice to have a nice big red flag waved on > the list if the multiple ResultSet thing is going to go away. It will > definitely break a lot of my code and I'm not so sure its so wrong. It's not wrong, but it's not at all portable to assume that you can have more than one active ResultSet on a connection. So you may want to change your habit anyway, unless you're sure you'll never have to use another database. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC