Thread: Error in servlet
Hello,
is there any known reason for the following behaviour:
I wrote a servlet with several database accesses via stored functions
and select statements. On a certain point of my servlet I get
the following:
[14/09/2000 16:31:51:216 CEST] Runtime.log: org.postgresql.jdbc1.ResultSet@80ab4ff
[14/09/2000 16:31:51:217 CEST] java.lang.NullPointerException:
at org.postgresql.jdbc1.ResultSet.getString(ResultSet.java)
at WebSQL.pGetGroupMemberByLocation(WebSQL.java:484)
at Web.DoWeb(Web.java:272)
at ehec.service(ehec.java:24)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:311)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:314)
at org.apache.jserv.JServConnection.run(JServConnection.java:188)
at java.lang.Thread.run(Thread.java)
I try to give a short description of the critical function in WebSQL.java:484
----- snip ----
public boolean pGetGroupMemberByLocation(Webdata data, int IdStandort,
String S_IdArbeitsgruppe, String S_IdMitarbeiter)
{
try {
query = "SELECT * FROM Mitarbeiter; " ;
query = "SELECT count(*) As FName FROM standort; " ;
query = "SELECT '<td class=\"MenuMain\"><a class=\"MenuMain\" href=\"' || '" +
project.GetServletName() + "' || '?" + "mid" +
"=' || i.IdMenuShow || '&" + "tid" +
"=' || i.IdWebSeite || '\">' || i.MenuItem || '</a></td>' " +
"AS getmenu FROM Menu m " +
"INNER JOIN MenuItem i ON i.IdMenu = m.IdMenu WHERE m.IdMenu = " + "54" +
" ORDER BY i.Sort ; " ;
rs = stmt.executeQuery(query);
if ( rs == null ) error.Common("Write message to logfile");
else error.Common("rs = " + rs); // This is the entry in Runtime.log
query = rs.getString(1);
error.Common("Are you living?");
---- snip ----
I tried different querys (see the strings "query"). First I reduced to
simple ones, later I used a more complex which worked inside the same
servlet some statements before and worked. They show all the same
behaviour to fail with the NullPointerException.
I'm using PostgreSQL 7.0.2, Apache, Jserv.
The behaviour is the same using
http://www.retep.org.uk/postgres/jdbc7.0-1.1.jar
http://www.retep.org.uk/postgres/jdbc7.0-1.2.jar
as JDBC driver.
I'm really in desperation with this stupid problem and tried
two days to solve it but failed. Any idea what might went wrong
here. It is a problems of threads od anything else. I can't see
any pointer which could be null here.
Really hoping for your help
Andreas.
could this be a servlet/thread issue? i cannot tell from the code snippet,
but remember that variables in a servlet with class scope are essentially
static. (i am guessing that query, rs, stmt, etc are all class scope. this
is very dangerous, in the programming sense of the word...) i've had
similar (but not the same) problems before. as a general rule, i NEVER put
a class scope variable in a servlet unless i really mean to.
chris
-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of Andreas Tille
Sent: Thursday, September 14, 2000 11:21 AM
To: PostgreSQL General
Subject: [GENERAL] Error in servlet
Hello,
is there any known reason for the following behaviour:
I wrote a servlet with several database accesses via stored functions
and select statements. On a certain point of my servlet I get
the following:
[14/09/2000 16:31:51:216 CEST] Runtime.log:
org.postgresql.jdbc1.ResultSet@80ab4ff
[14/09/2000 16:31:51:217 CEST] java.lang.NullPointerException:
at org.postgresql.jdbc1.ResultSet.getString(ResultSet.java)
at WebSQL.pGetGroupMemberByLocation(WebSQL.java:484)
at Web.DoWeb(Web.java:272)
at ehec.service(ehec.java:24)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:311)
at
org.apache.jserv.JServConnection.processRequest(JServConnection.java:314)
at org.apache.jserv.JServConnection.run(JServConnection.java:188)
at java.lang.Thread.run(Thread.java)
I try to give a short description of the critical function in
WebSQL.java:484
----- snip ----
public boolean pGetGroupMemberByLocation(Webdata data, int IdStandort,
String S_IdArbeitsgruppe, String
S_IdMitarbeiter)
{
try {
query = "SELECT * FROM Mitarbeiter; " ;
query = "SELECT count(*) As FName FROM standort; " ;
query = "SELECT '<td class=\"MenuMain\"><a class=\"MenuMain\" href=\"'
|| '" +
project.GetServletName() + "' || '?" + "mid" +
"=' || i.IdMenuShow || '&" + "tid" +
"=' || i.IdWebSeite || '\">' || i.MenuItem || '</a></td>' " +
"AS getmenu FROM Menu m " +
"INNER JOIN MenuItem i ON i.IdMenu = m.IdMenu WHERE m.IdMenu
= " + "54" +
" ORDER BY i.Sort ; " ;
rs = stmt.executeQuery(query);
if ( rs == null ) error.Common("Write message to logfile");
else error.Common("rs = " + rs); // This is the entry in Runtime.log
query = rs.getString(1);
error.Common("Are you living?");
---- snip ----
I tried different querys (see the strings "query"). First I reduced to
simple ones, later I used a more complex which worked inside the same
servlet some statements before and worked. They show all the same
behaviour to fail with the NullPointerException.
I'm using PostgreSQL 7.0.2, Apache, Jserv.
The behaviour is the same using
http://www.retep.org.uk/postgres/jdbc7.0-1.1.jar
http://www.retep.org.uk/postgres/jdbc7.0-1.2.jar
as JDBC driver.
I'm really in desperation with this stupid problem and tried
two days to solve it but failed. Any idea what might went wrong
here. It is a problems of threads od anything else. I can't see
any pointer which could be null here.
Really hoping for your help
Andreas.
On Thu, 14 Sep 2000, chris markiewicz wrote:
> could this be a servlet/thread issue? i cannot tell from the code snippet,
Solved. I really stupidly forgot an rs.next() :-(((.
> but remember that variables in a servlet with class scope are essentially
> static. (i am guessing that query, rs, stmt, etc are all class scope. this
> is very dangerous, in the programming sense of the word...) i've had
> similar (but not the same) problems before. as a general rule, i NEVER put
> a class scope variable in a servlet unless i really mean to.
Well, that might be true for query and rs and I'll change that, but
in my opinion
public class ServletSQLClass
{
private Connection con;
private Statement stmt;
...
con = DriverManager.getConnection(url,user,passwd);
stmt = con.createStatement();
...
}
con and stmt have to be class scope to hold the connection to the
database and don't have to reopen over and over. Or did I understand
something wrong?
Kind regards
Andreas.
Hi all, I'm quite new in postgres, and I can't find how can i do this. I have a txt file with all de data of one table, and I want to insert it into the database. in mysql is load data infile .... how is in postgres? thank you Fernando Caamaño Programador web Interweb Research www.interweb-research.net
You can use: COPY table_1 FROM '/usr/file.txt' USING DELIMITERS '/'; table_1 (f1 INT4, f2 VARCHAR(20)); file: 0/aaaa 1/bbbb Fernando Caamaño wrote: > > Hi all, I'm quite new in postgres, and I can't find how can i do this. > I have a txt file with all de data of one table, and I want to insert it > into the database. in mysql is load data infile .... > how is in postgres? > thank you > Fernando Caamaño > Programador web > Interweb Research > www.interweb-research.net
oh yeah, and you need to call rs.next()...
to you other point, yes and no. for any regular class (non-static and
non-servlet), this is true. the issue is this - there will only ever be one
copy of rs, con, and stmt. imagine the following very realistic scenario:
one user hits the servlet and runs a query...then a second user calls the
servlet while the first is using the rs...the first user's data will be
shot. he/she will either get nonsense data or a null ptr or something. the
second user will probably get good data. when writing my first servlet, i
chased down a similar problem for about a week (i didn't realize that it was
a problem until we started doing stress testing and people started getting
errors).
there are two obvious solutions to this problem...
1 (i DON'T recommend this one) declare the variables in a method and pass
them to other methods as necessary.
2 (i do recommend this one) many server side java programmers feel that
servlets should be a gateway and nothing else...essentially a servlet should
do little more than call your other classes. i.e. put all of your db
processing in a separate class and call it from your servlet.
chris
-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of Andreas Tille
Sent: Friday, September 15, 2000 4:29 AM
To: chris markiewicz
Cc: 'PostgreSQL General'
Subject: [GENERAL] RE: Error in servlet
On Thu, 14 Sep 2000, chris markiewicz wrote:
> could this be a servlet/thread issue? i cannot tell from the code
snippet,
Solved. I really stupidly forgot an rs.next() :-(((.
> but remember that variables in a servlet with class scope are essentially
> static. (i am guessing that query, rs, stmt, etc are all class scope.
this
> is very dangerous, in the programming sense of the word...) i've had
> similar (but not the same) problems before. as a general rule, i NEVER
put
> a class scope variable in a servlet unless i really mean to.
Well, that might be true for query and rs and I'll change that, but
in my opinion
public class ServletSQLClass
{
private Connection con;
private Statement stmt;
...
con = DriverManager.getConnection(url,user,passwd);
stmt = con.createStatement();
...
}
con and stmt have to be class scope to hold the connection to the
database and don't have to reopen over and over. Or did I understand
something wrong?
Kind regards
Andreas.
On Fri, 15 Sep 2000, chris markiewicz wrote:
> oh yeah, and you need to call rs.next()...
;-)))
> there are two obvious solutions to this problem...
>
> 1 (i DON'T recommend this one) declare the variables in a method and pass
> them to other methods as necessary.
>
> 2 (i do recommend this one) many server side java programmers feel that
> servlets should be a gateway and nothing else...essentially a servlet should
> do little more than call your other classes. i.e. put all of your db
> processing in a separate class and call it from your servlet.
In this case I'm perfectly all right. My servlet has just about 20 lines
and does call a class. This class instanciates a "Database-Accessing"-
Class (WebSQL) because I started with access to MS SQL server and now porting
to PostgreSQL. I just have to change WebSQL to use another database
engine. All the connections, statements etc are private in this
class. But I wanted to avoid to declare this stuff in every method
of this class.
Kind regards
Andreas.
hello. what is the postgres equivalent of oracle's command-line DESCRIBE <table> command? does there exist a sql92 command for this? thank you chris
The Statement variable here CANNOT be a class
variable unless you're taking other steps to
synchronize access. Otherwise, you risk having
two different threads trying to manipulate the
same statement object at the same time.
The Connection object is what holds the connection
to the database. Whether or not that can safely
be class scope depends on your particular JDBC driver
and how you're using it. This might work.
If you're not using auto-commit, this won't work,
since each connection is a single transaction
environment, and you'll have multiple transactions
interfering with one another.
Another ugly problem you'll encounter: many database
servers don't like long-lived connections, and will
spontaneously drop them after a few hours. At the very
least, you should timestamp when you opened the connection
(long timestamp = System.currentTimeMillis();)
and close/reopen it every 30 minutes or so. Also,
you'll want to be sure to ping the connection regularly
in case something goes down (like a bad network cable).
If you have a relatively low-traffic site, opening
one new connection for each request is not a real
problem. I've measured connection opens at around 0.1-0.2
seconds on local MySQL and networked Oracle, which isn't at
all prohibitive for a lot of applications. Plus, that
approach is easy to understand and very reliable.
If you have a higher-traffic site, look into connection
pooling. A good connection pool will cycle the connections,
open more if you need them, and can deal with a lot of other
issues as well.
- Tim Kientzle
> public class ServletSQLClass
> {
> private Connection con;
> private Statement stmt;
>
> ...
> con = DriverManager.getConnection(url,user,passwd);
> stmt = con.createStatement();
> ...
> }
>
> con and stmt have to be class scope to hold the connection to the
> database and don't have to reopen over and over. Or did I understand
> something wrong?
On Fri, 15 Sep 2000, Tim Kientzle wrote: > The Statement variable here CANNOT be a class > variable unless you're taking other steps to > synchronize access. Otherwise, you risk having > two different threads trying to manipulate the > same statement object at the same time. OK, I followed your hint and made statement and resultset local in all methods. > The Connection object is what holds the connection > to the database. Whether or not that can safely > be class scope depends on your particular JDBC driver > and how you're using it. This might work. Hmm, I'm using the driver shipped with Debian which is from ftp://ftp.postgresql.org/pub/postgresql.6.4.2.tar.gz What about this? Would it work. Would anybody recommend the driver from http://www.retep.org.uk/postgres/ ? > If you're not using auto-commit, this won't work, > since each connection is a single transaction > environment, and you'll have multiple transactions > interfering with one another. Could you explain this a little bit more detailed for a beginner or is there any information source about this topic? > Another ugly problem you'll encounter: many database > servers don't like long-lived connections, and will > spontaneously drop them after a few hours. At the very > least, you should timestamp when you opened the connection > (long timestamp = System.currentTimeMillis();) > and close/reopen it every 30 minutes or so. Also, > you'll want to be sure to ping the connection regularly > in case something goes down (like a bad network cable). You speak about "many database servers". What about PostgreSQL? > If you have a relatively low-traffic site, opening > one new connection for each request is not a real > problem. I've measured connection opens at around 0.1-0.2 > seconds on local MySQL and networked Oracle, which isn't at > all prohibitive for a lot of applications. Plus, that > approach is easy to understand and very reliable. I think in my case it would be best to hold the connection open while performing the about 10 requests of the servlet which are necessary to build my web-pages. > If you have a higher-traffic site, look into connection > pooling. A good connection pool will cycle the connections, > open more if you need them, and can deal with a lot of other > issues as well. Speaking about connection pooling I considered another problem: I'm using JServ and it seems to try to open more than one connection to the PostgreSQL server. Formerly I used MS SQL server and there where 5 open connections per servlet. How do I check the open connections of a servlet to the PostgreSQL server? How do I enforce connection pooling? Kind regards Andreas.
On Fri, 15 Sep 2000, Andreas Tille wrote:
> On Thu, 14 Sep 2000, chris markiewicz wrote:
>
> > could this be a servlet/thread issue? i cannot tell from the code snippet,
> Solved. I really stupidly forgot an rs.next() :-(((.
>
> > but remember that variables in a servlet with class scope are essentially
> > static. (i am guessing that query, rs, stmt, etc are all class scope. this
> > is very dangerous, in the programming sense of the word...) i've had
> > similar (but not the same) problems before. as a general rule, i NEVER put
> > a class scope variable in a servlet unless i really mean to.
> Well, that might be true for query and rs and I'll change that, but
> in my opinion
>
> public class ServletSQLClass
> {
> private Connection con;
> private Statement stmt;
>
> ...
> con = DriverManager.getConnection(url,user,passwd);
> stmt = con.createStatement();
> ...
> }
>
> con and stmt have to be class scope to hold the connection to the
> database and don't have to reopen over and over. Or did I understand
> something wrong?
IMHO, only Connection should be of class scope, as you can create as many
Statement/PreparedStatements as you want.
Peter
--
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/