Thread: When to close a Statement

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

After having had some problems with JDBC statements, as I mentioned in a
previous post, due to different behaviours between versions 7.x and 8.x
in the way Postgres deals with statements, I would like to know what is
the proper way of dealing with this.

I realized that the need to close the statement is implementaion
dependent, and now I'm a bit confused as I can't find anything in
Postgres docs about this issue... so the questions are:

- - must I close the statement after *every* executeXXX method call?

- - should I also close the connection as well, or may I leave it open
during the application execution?

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

iD8DBQFD8yaU90uzwjA1SJURAsyhAJ9twvK/oeZXrq/SEyoR9TaGr3ZNdQCgwaBC
H3eHaRZdHmZ3FuIhs/xgDIs=
=UI89
-----END PGP SIGNATURE-----

O Carlos Correia έγραψε στις Feb 15, 2006 :

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi,
>
> After having had some problems with JDBC statements, as I mentioned in a
> previous post, due to different behaviours between versions 7.x and 8.x
> in the way Postgres deals with statements, I would like to know what is
> the proper way of dealing with this.
>
> I realized that the need to close the statement is implementaion
> dependent, and now I'm a bit confused as I can't find anything in
> Postgres docs about this issue... so the questions are:
>
> - - must I close the statement after *every* executeXXX method call?
>
> - - should I also close the connection as well, or may I leave it open
> during the application execution?

Close a statement when you dont need it any more.
e.g.
PreparedStatement neinsst=con.prepareStatement("insert into
sparecasesdets(scid,no,descr,qty_obd,qty_req,drgno,partno)
VALUES(?,?,?,?,?,?,?)");

java.util.Enumeration en = request.getParameterNames();

for (;en.hasMoreElements();) {

    String str_no=(String) en.nextElement();
    boolean isdascd = str_no.startsWith("no_dascdid");
    .....
    neinsst.setInt(1,Integer.parseInt(scid));
    .....
    int rc = neinsst.executeUpdate();
    ....
}
neinsst.close();



>
> 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
>
> iD8DBQFD8yaU90uzwjA1SJURAsyhAJ9twvK/oeZXrq/SEyoR9TaGr3ZNdQCgwaBC
> H3eHaRZdHmZ3FuIhs/xgDIs=
> =UI89
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

--
-Achilleus

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Achilleus Mantzios escreveu:
| O Carlos Correia έγραψε στις Feb 15, 2006 :
|
|
| Hi,
|
| After having had some problems with JDBC statements, as I mentioned in a
| previous post, due to different behaviours between versions 7.x and 8.x
| in the way Postgres deals with statements, I would like to know what is
| the proper way of dealing with this.
|
| I realized that the need to close the statement is implementaion
| dependent, and now I'm a bit confused as I can't find anything in
| Postgres docs about this issue... so the questions are:
|
| - must I close the statement after *every* executeXXX method call?
|
| - should I also close the connection as well, or may I leave it open
| during the application execution?
|
|
|> Close a statement when you dont need it any more.
|> e.g.
|> PreparedStatement neinsst=con.prepareStatement("insert into
|> sparecasesdets(scid,no,descr,qty_obd,qty_req,drgno,partno)
|> VALUES(?,?,?,?,?,?,?)");
|
|> java.util.Enumeration en = request.getParameterNames();
|
|> for (;en.hasMoreElements();) {
|
|>     String str_no=(String) en.nextElement();
|>     boolean isdascd = str_no.startsWith("no_dascdid");
|>     .....
|>     neinsst.setInt(1,Integer.parseInt(scid));
|>     .....
|>     int rc = neinsst.executeUpdate();
|>     ....
|> }
|> neinsst.close();
|

I haven't fully test it with PreparedStatement yet, but with normal
statements -- the ones you get after calling connection's
createStatement() -- after several executeXXX() calls I get an
SQLException saying "the connection has been closed" (this problem only
occurs with PostgreSQL v8.x in Windows -- I haven't been able to test in
Linux yet -- with v. 7.x I haven't notice such problems).

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

iD8DBQFD8y9490uzwjA1SJURAvixAKCuXSPDP3FPdxV9med+wGsh70QrBQCgr9ax
qDq8B8xu5D43aqFNaY1EA44=
=cqL5
-----END PGP SIGNATURE-----

O Fabrice.Sznajderman@devoteam.com έγραψε στις Feb 15, 2006 :

> Hello,
>
> According to you, What it better to use, the IN or EXISTS predicat?
>
> I saw on internet it's better to use EXITS . but I would like to have
> another point of view..
>
> Thanks in advance

Prior to 7.4 IN was a looser,
in 7.4 IN sometimes is by far a clear winner,
i dunno in 8.*

>
>
> Fabrice
>
> +---------------------------------------------------------------------+
> About Devoteam:
>
> Devoteam is an IT consulting group.
> Combining consulting and technology solutions offers enables
> Devoteam to provide its customers with independent advice and
> effective solutions to align their information system's technical
> infrastructure with their operational objectives.
> The group achieved a turnover of 199 million euros in 2005
> an operating margin of 7% and counts 2,100 employees through
> 12 European countries and the Middle East.
> Listed on the Euronext Paris Nouveau Marche and part of the Nexteconomy,
> IT CAC, IT CAC 50, SBF 250 index of Euronext Paris.
> www.devoteam.com
> +---------------------------------------------------------------------+
>

--
-Achilleus

O Fabrice.Sznajderman@devoteam.com έγραψε στις Feb 15, 2006 :

> Thank for your answer!
>
> I use 7.3. I think it the same between 7.4 and 7.3?

No in 7.3 exists wins.
Why are you so behind?

>
>
>
>
> Fabrice
>
>
>
>
>
> Achilleus Mantzios <achill@matrix.gatewaynet.com>
> EnvoyΓ© par : pgsql-jdbc-owner@postgresql.org
> 15/02/2006 16:57
>
> A
> Fabrice.Sznajderman@devoteam.com
> cc
> pgsql-jdbc@postgresql.org, <pgsql-jdbc-owner@postgresql.org>
> Objet
> Re: [JDBC] In or Exists?
>
>
>
>
>
>
> O Fabrice.Sznajderman@devoteam.com έγραψΡ στις Feb 15, 2006 :
>
> > Hello,
> >
> > According to you, What it better to use, the IN or EXISTS predicat?
> >
> > I saw on internet it's better to use EXITS . but I would like to have
> > another point of view..
> >
> > Thanks in advance
>
> Prior to 7.4 IN was a looser,
> in 7.4 IN sometimes is by far a clear winner,
> i dunno in 8.*
>
> >
> >
> > Fabrice
> >
> > +---------------------------------------------------------------------+
> > About Devoteam:
> >
> > Devoteam is an IT consulting group.
> > Combining consulting and technology solutions offers enables
> > Devoteam to provide its customers with independent advice and
> > effective solutions to align their information system's technical
> > infrastructure with their operational objectives.
> > The group achieved a turnover of 199 million euros in 2005
> > an operating margin of 7% and counts 2,100 employees through
> > 12 European countries and the Middle East.
> > Listed on the Euronext Paris Nouveau Marche and part of the Nexteconomy,
> > IT CAC, IT CAC 50, SBF 250 index of Euronext Paris.
> > www.devoteam.com
> > +---------------------------------------------------------------------+
> >
>
>

--
-Achilleus

On Wed, 2006-02-15 at 09:36, Fabrice.Sznajderman@devoteam.com wrote:
> I know I know I too behind!
> But for this moment my project manager wants to stay with this
> version...
>
> There is lot specific developpement around this version ... it not
> easy to change the version...
>
> I asked to my project manager : we must upgrade !! he answser me.. not
> for this moment!
>
> so!!.... :-(
>
> thank a lot for your answer!

Just make sure you're running the latest version of 7.3.x.  Earlier
versions of 7.3 had some data loss issues if I remember correctly.  It's
always a good practice to be running the latest patch level of your
version of PostgreSQL.

Hi, Fabrice,

Fabrice.Sznajderman@devoteam.com wrote:

> how I can know the version of my postgres DataBase ?
> I know is 7.3.x but I don't know for the 'x' number ..

use psql or another command line tool, and send the query
    SELECT VERSION();

HTH,
Markus



--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Fabrice.Sznajderman@devoteam.com wrote:
>
> thank you for your answer!
>
>
> how I can know the version of my postgres DataBase ?
> I know is 7.3.x but I don't know for the 'x' number ..

$ psql --version
psql (PostgreSQL) 8.1.2
...



or...

$ psql --version
psql (PostgreSQL) 7.4.5

(two different machines here)


this just shows the version of psql itself, which SHOULD be the same as
your server version.  if you want to confirm this,

$ psql -c "show server_version;"
  server_version
----------------
  7.4.5
(1 row)

On Wed, 2006-02-15 at 09:59, Fabrice.Sznajderman@devoteam.com wrote:
> thank you for your answer!
>
>
> how I can know the version of my postgres DataBase ?
> I know is 7.3.x but I don't know for the 'x' number ..

psql -V will tell you the client version.  or psql template1 -c "select
version();" will tell you the version of the backend server.

On Wed, 2006-02-15 at 10:27, Fabrice.Sznajderman@devoteam.com wrote:
> I tried select version();
>
> it gave me this:
>
> PostgreSQL 7.2.3-RH on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
> 3.2 20020903 (Red Hat Linux 8.0 3.2-7)

OK...  You're running a really really old version of PostgreSQL.

It has known data loss and crash inducing bugs.

Let your boss know that you should upgrade to, at a minimum, the latest
version of 7.2, which is 7.2.8.

7.2 is no longer supported.  It's very old.  It's creaky in fact.  I
would recommend upgrading to the latest 8.0 or 8.1 series, or 7.4 as a
minimum.

Many IT managers prefer to stick with older "known" versions of
software.  While I understand this, it's really mostly a comfort issue.

And nothing makes me more uncomfortable than a database error that means
all my data just disappeared.

Oh, and update your OS as well if you can. RH 8.0 is also no longer
supported.  It has many unpatched security bugs.  You are running an
unreliable, unstable version of postgresql on an unreliable, insecure
platform.  Expect no mercy from the gods or the members of this list
when things go wrong.  And they will.

Scott Marlowe <smarlowe@g2switchworks.com> writes:
> On Wed, 2006-02-15 at 10:27, Fabrice.Sznajderman@devoteam.com wrote:
>> PostgreSQL 7.2.3-RH on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
>> 3.2 20020903 (Red Hat Linux 8.0 3.2-7)

> OK...  You're running a really really old version of PostgreSQL.

In fact, it's so old that I can't even find any trace in Red Hat's
internal CVS of such a version having been released :-(.  AFAICT, RHL 8
shipped with Postgres 7.3.  I think that this very probably isn't an
official Red Hat RPM at all, but something that was built locally using
already-obsolete Postgres sources.

Upgrade *now*, don't wait to get burnt.  And I concur with Scott about
upgrading the whole OS while you're at it.

            regards, tom lane

On Wed, 15 Feb 2006, Fabrice.Sznajderman@devoteam.com wrote:

> According to you, What it better to use, the IN or EXISTS predicat?
>

This questino has nothing to do with Java or JDBC and is not appropriate
for this list.  Please send future questions of this sort to
pgsql-general@postgresql.org.  Also do not include the list owner email
address.  This address is only for problems with the list itself like
subscribing or unsubscribing, not for general questions.

Kris Jurka

> Oh, and update your OS as well if you can. RH 8.0 is also no longer
> supported.  It has many unpatched security bugs.  You are running an
> unreliable, unstable version of postgresql on an unreliable, insecure
> platform.

indeed.   The 'proper' upgrade is a clean install of a up to date
supported distribution, perhaps RHEL4 or CentOS4, or perhaps something
else, however I also know how hard this can be on a production system.

There is an alternative, the Fedora people have a 'fedora legacy'
project which is still supporting RH9 with critical fixes, you can
fairly easily update RH8 to RH9 (they are very similar systems), and
then get the latest updates for this platform.

This will give you some breathing room while you plan the major upgrade
to RHEL4 or something else...

I've done this on some production servers where there was lack of money
or resources to setup a whole new server (these are servers that belong
to 3rd parties for which noone is being paid to run, so there were no
resources available for a proper upgrade).

I would first dump backups of all your postgres databases(!!)  Then I'd
make backups of all your core file systems so you can revert if things
backfire on you for unexplained reasons...

then, follow these instructions...
http://www.fedora.us/wiki/LegacyRPMUpgrade

and then these...
http://www.fedoralegacy.org/docs/yum-rh9.php

then remove any and all traces of RH distributions of older postgres
versions (`rpm -qa | grep postgres`, then rpm -e xxxx), install the
latest postgres 8.1 from the RPMs on the postgres download pages, and
recreate your postgres users, and restore your postgres dump, and you
should be good to go.

Hi, Carlos,

Carlos Correia wrote:

> After having had some problems with JDBC statements, as I mentioned in a
> previous post, due to different behaviours between versions 7.x and 8.x
> in the way Postgres deals with statements, I would like to know what is
> the proper way of dealing with this.
>
> I realized that the need to close the statement is implementaion
> dependent, and now I'm a bit confused as I can't find anything in
> Postgres docs about this issue... so the questions are:
>
> - must I close the statement after *every* executeXXX method call?

No, only close it if you don't need it any more.

But reusing a statement closes the ResultSet from the last query used
with this statement. The same is for begin/abort/commit.

> - should I also close the connection as well, or may I leave it open
> during the application execution?

Keep it open as long as possible (or use a connection pool), because
reopening a connection is slow.

HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Hi, Fabrice,

Fabrice.Sznajderman@devoteam.com wrote:
>
> I tried select version();
>
> it gave me this:
>
> PostgreSQL 7.2.3-RH [...]

This tells us that you use a Redhat specific PostgreSQL version 7.2, dot
release 7.2.3.

7.2 is pretty old, and not supported by the PostgreSQL team any more
(since the 8.1 release, AFAIR.)

And even for 7.2, 7.2.8 is the newest dot release.

You can fetch it from
ftp://ftp.de.postgresql.org/mirror/postgresql/binary/OLD/
or other postgresql mirrors.



HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org