Error in servlet

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:

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

\dt <table name> only seems to list Owner, Relation, and Type.

i am looking for something that returns the same info that is found in a
CREATE TABLE statement...column names, data types, etc. i haven't seen
anything like this in the documentation...

thanks
chris

-----Original Message-----
From: Len Morgan [mailto:len-morgan@crcom.net]
Sent: Friday, September 15, 2000 9:48 AM
To: cmarkiew@commnav.com
Subject: Re: [GENERAL] what is the equivalent of oracle's DESCRIBE
table?

in psql: \dt <table name>

-----Original Message-----
From: chris markiewicz <cmarkiew@commnav.com>
To: 'PostgreSQL General' <pgsql-general@postgresql.org>
Date: Friday, September 15, 2000 8:51 AM
Subject: [GENERAL] what is the equivalent of oracle's DESCRIBE table?

How about \d <table name>

I think that is the type of info oracle spits out for describe (sorry...
been 6 months since I touched oracle and it was for only about 4 months that
I did. :) )

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "chris markiewicz" <cmarkiew@commnav.com>
To: "'Len Morgan'" <len-morgan@crcom.net>
Cc: "'PostgreSQL General' (E-mail)" <pgsql-general@postgresql.org>
Sent: Friday, September 15, 2000 10:07 AM
Subject: RE: [GENERAL] what is the equivalent of oracle's DESCRIBE table?

chris markiewicz wrote:

\dt <table name> only seems to list Owner, Relation, and Type.

i am looking for something that returns the same info that is found in a
CREATE TABLE statement...column names, data types, etc. i haven't seen
anything like this in the documentation...

Check Chapter 37. Extending SQL: An Overview. This includes a section
on the PostgreSQL system catalogs. It's rather dense, but might get you
going. This subject comes up now and again, so I'm sure there's also
some info in the list archives.

This section also says "The Reference Manual gives a more detailed
explanation of these catalogs and their attributes." Where can one find
this Reference Manual?

--

Ron Peterson
Systems Manager
Wallace Floyd Design Group
273 Summer Street
Boston, MA 02210
617.350.7400 tel
617.350.0051 fax
rpeterson@wallacefloyd.com

use \d <tablename>

postgres@davis ~> psql demogdata
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.0 on sparc-sun-solaris2.6, compiled by /usr/local/bin/gcc
]

type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: demogdata

demogdata=> \d event_detail
Table    = event_detail
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| event_id                         | int4 not null default nextval('" |
4 |
| dt1eventid                       | int2                             |
2 |
| eventtypeid                      | int4                             |
4 |
| id                               | int4                             |
4 |
| date_effective                   | datetime                         |
8 |
| date_expires                     | datetime                         |
8 |
| expected                         | bool                             |
1 |
| title                            | varchar()                        |
150 |
| sourceid                         | int4                             |
4 |
| cost                             | float8                           |
8 |
| spec1                            | varchar()                        |
50 |
| spec2                            | varchar()                        |
50 |
| spec3                            | varchar()                        |
50 |
| spec4                            | varchar()                        |
50 |
| spec5                            | varchar()                        |
50 |
| spec6                            | varchar()                        |
50 |
| spec7                            | varchar()                        |
50 |
| spec8                            | varchar()                        |
50 |
| comments                         | text                             |
var |
| current_interests                | text                             |
var |
| updated                          | varchar()                        |
25 |
| override                         | varchar()                        |
25 |
| semester                         | varchar()                        |
15 |
| acadyear                         | varchar()                        |
12 |
| chair                            | int4                             |
4 |
| member1                          | int4                             |
4 |
| member2                          | int4                             |
4 |
| degree                           | varchar()                        |
5 |
+----------------------------------+----------------------------------+-------+
Indices:  date_effective_idx
          etid_detail_idx
          event_detail_event_id_key

---------------------------------------------------------------------
Andrew J. Perrin - aperrin@demog.berkeley.edu - NT/Unix Admin/Support
Department of Demography - University of California at Berkeley
2232 Piedmont Avenue #2120 - Berkeley, California, 94720-2120 USA
http://demog.berkeley.edu/~aperrin --------------------------SEIU1199

On Fri, 15 Sep 2000, chris markiewicz 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.

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

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/