Thread: Statement has been closed (only in Windows)

Statement has been closed (only in Windows)

From
Carlos Correia
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

While testing a Java app (that's been working for years with PostgreSQL
+ Linux) in a Windows box I get this error when inserting a record
whithin a transaction:

org.postgresql.util.PSQLException: This statement has been closed.
~        at
org.postgresql.jdbc2.AbstractJdbc2Statement.checkClosed(AbstractJdbc2Statement.java:2390)
~        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:329)
~        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:278)

What I was doing was to keep a single connection since the beginning
until the end of the session.

public class DbConnection
{
~  Statement statement = null;

~  public Statement getStatement() throws SQLException
~  {
~      if( statement == null )
~          statement = con.createStatement();
~      return statement;
~  }
}

As there was only one connection established with the database (fat
client), this seemed OK.

As the driver, when I ran the same app in Windos, was complaining that
the connection had been closed, I commented out the 'if' line above...
et voilá, problem solved.

New method:

~  public Statement getStatement() throws SQLException
~  {
    statement = con.createStatement();
~      return statement;
~  }

As I don't like this solution (either trusting the garbage collector to
do his job or closing every statement after have been used), what I ask
is: what is the proper way of handling this kind of problem?

Testing conditions:

Linux:
- - Debian, Ubuntu, Gentoo, Fedora, etc.
- - PostgreSQL 7.4.x
- - pg74.216.jdbc3.jar

Windows:
- - Windows 2000, XP
- - PostgreSQL 8.1
- - postgresql-8.0-314.jdbc3.jar

Thanks,

Carlos
- --
MEMÓRIA PERSISTENTE, Lda.
Tel.: 219 291 591 - GSM:  967 511 762
e-mail: geral@m16e.com - URL: http://www.m16e.com
AIM: m16e - ICQ: 257488263 - Jabber: m16e@amessage.de
Skype.com username (VoIP): m16e.com
GnuPG: wwwkeys.eu.pgp.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFD5k/X90uzwjA1SJURAsG+AJ0RYQalem4sTxzyQd8Z/ucKsP4YLACdGyU9
LPA+ToH9kBrgxM3kE2V1Zxg=
=v2Qt
-----END PGP SIGNATURE-----

Re: Statement has been closed (only in Windows)

From
pedro farinha
Date:
Hi Carlos, if you're problem is solved what is the question?
if you worry about creating a lot of statements, don't. But make sure you close them after use. Also the resultSet if any. Not a big brain on this end, but why would you want to keep the statement open?

P.



On Sun, 2006-02-05 at 19:19, Carlos Correia wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

While testing a Java app (that's been working for years with PostgreSQL
+ Linux) in a Windows box I get this error when inserting a record
whithin a transaction:

org.postgresql.util.PSQLException: This statement has been closed.
~        at
org.postgresql.jdbc2.AbstractJdbc2Statement.checkClosed(AbstractJdbc2Statement.java:2390)
~        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:329)
~        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:278)

What I was doing was to keep a single connection since the beginning
until the end of the session.

public class DbConnection
{
~  Statement statement = null;

~  public Statement getStatement() throws SQLException
~  {
~  	if( statement == null )
~  		statement = con.createStatement();
~  	return statement;
~  }
}

As there was only one connection established with the database (fat
client), this seemed OK.

As the driver, when I ran the same app in Windos, was complaining that
the connection had been closed, I commented out the 'if' line above...
et voilá, problem solved.

New method:

~  public Statement getStatement() throws SQLException
~  {statement = con.createStatement();
~  	return statement;
~  }

As I don't like this solution (either trusting the garbage collector to
do his job or closing every statement after have been used), what I ask
is: what is the proper way of handling this kind of problem?

Testing conditions:

Linux:
- - Debian, Ubuntu, Gentoo, Fedora, etc.
- - PostgreSQL 7.4.x
- - pg74.216.jdbc3.jar

Windows:
- - Windows 2000, XP
- - PostgreSQL 8.1
- - postgresql-8.0-314.jdbc3.jar

Thanks,

Carlos
- --
MEMÓRIA PERSISTENTE, Lda.
Tel.: 219 291 591 - GSM:  967 511 762
e-mail: geral@m16e.com - URL: http://www.m16e.com
AIM: m16e - ICQ: 257488263 - Jabber: m16e@amessage.de
Skype.com username (VoIP): m16e.com
GnuPG: wwwkeys.eu.pgp.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFD5k/X90uzwjA1SJURAsG+AJ0RYQalem4sTxzyQd8Z/ucKsP4YLACdGyU9
LPA+ToH9kBrgxM3kE2V1Zxg=
=v2Qt
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org

Re: Statement has been closed (only in Windows)

From
Carlos Correia
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

pedro farinha escreveu:
| Hi Carlos, if you're problem is solved what is the question?
| if you worry about creating a lot of statements, don't. But make sure
| you close them after use. Also the resultSet if any. Not a big brain on
| this end, but why would you want to keep the statement open?

Well, why not? It's a fat client, It establishes a connection at
application start up and closes it once the app finishes... what's the
problem about keeping the same statement (it's been working like that in
a production environment for years in Linux)?

Anyway, after I made the workaround I mentioned in a previous post
(creating a new statement per request), began getting errors in prepared
statements, which I'll try to reproduce (tomorrow?) and published them here.

The questions now are:
- - How stable is PostreSQL on a Windows environment, by now?

- - Are there any known problems with transactions, prepared statements or
storeed procedures in a Windows environment?

Thanks,

Carlos
- --
MEMÓRIA PERSISTENTE, Lda.
Tel.: 219 291 591 - GSM:  967 511 762
e-mail: geral@m16e.com - URL: http://www.m16e.com
AIM: m16e - ICQ: 257488263 - Jabber: m16e@amessage.de
Skype.com username (VoIP): m16e.com
GnuPG: wwwkeys.eu.pgp.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFD6Sio90uzwjA1SJURAu43AJ9t6YzRnncHCBbxsdbe111jFWVzWQCgo3T1
fnnTxAl/v+irDbYrRyq2Xt0=
=/OV6
-----END PGP SIGNATURE-----

Re: Statement has been closed (only in Windows)

From
Nelson Arape
Date:
El Martes, 7 de Febrero de 2006 19:09, Carlos Correia escribió:
> pedro farinha escreveu:
> | Hi Carlos, if you're problem is solved what is the question?
> | if you worry about creating a lot of statements, don't. But make sure
> | you close them after use. Also the resultSet if any. Not a big brain on
> | this end, but why would you want to keep the statement open?
>
> Well, why not? It's a fat client, It establishes a connection at
> application start up and closes it once the app finishes... what's the
> problem about keeping the same statement (it's been working like that in
> a production environment for years in Linux)?
>
Maybe your problem is more related to the transition from postgres (and its
JDBC driver) from 7.x to 8.x.

Why not you try your migration in steps

1.    Migrate to Postgres 8.x (and its JDBC driver) on Linux
2.    Migrate from linux to windows.

I think that the step to may be easer than step 1 due to changes related to
use network protocol version 3 and serverside prepare statement.

Nelson Arapé