Обсуждение: 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/