Thread: can't access database from servlet

can't access database from servlet

From
Sanjeev Rathore
Date:
Iam trying to access a table from PostgreSQL 7.1.3
from servlet.

These are the software that I am running.
Tomcat 4.0
PostgreSQL 7.1.3
RedHat 7.2

I am able to access PostgreSQL database from stand
alone Java program.
I am able to access meta data of PostgreSQL from
servlet, which means(i think) that JDBC driver must be
working. I am able to access database called 'auction'
and table called 'names' from a sql command prompt
which has one row of record. When servlet is executed
ResultSet returns null when it shoud return result of
SQL query.

I don't know where the problem could be but it seems
that Java code or some type of permission is
incorrect.  Here is my simple Servlet code that
compiles but gives ResultSet as null.

please help!
I am working on this problem for about 4 days now.

Thanks,
Sanjeev


  import java.sql.*;
  import java.io.*;
  import javax.servlet.*;
  import javax.servlet.http.*;
  import java.util.*;

  public class HelloServlet extends HttpServlet {
     Connection db;  //connection to database
     Statement  sql; //sql statement to query database
     DatabaseMetaData dbmd; //find out the drive
version
     String first_name = "blank";
     String last_name = " ";
     ResultSet result;

  public void init(ServletConfig config) throws
ServletException {
     super.init(config);
     String database = "auction";
     String username = "postgres";
     String password = "postgres";
     try{
       Class.forName("org.postgresql.Driver");
       db =
DriverManager.getConnection("jdbc:postgresql:" +
database, username, password);
       dbmd = db.getMetaData();
       sql = db.createStatement();
           String sqlText = "select first_name from
names";
           result = sql.executeQuery(sqlText);
           while(result.next()){
             first_name =
result.getString(first_name);
             last_name  = result.getString(last_name);
           }
     }catch(Exception e){
       e.printStackTrace();
       //Connection = null;
     }
  }

  public void doGet(HttpServletRequest request,
HttpServletResponse response)
     throws IOException {
     response.setContentType("text/html");
     PrintWriter out = response.getWriter();
     out.println("<html>");
     out.println("<body>");
     out.println("<h1>Hello Sanjeev! </h1>");
     //HelloServlet hs = new HelloServlet();

     out.println("<h4>" + last_name  + "</h4>");
     try{
     out.println("<h4>" +
dbmd.getDatabaseProductName() + "</h4>");
     out.println("<h2>" +
dbmd.getDatabaseProductVersion() + "</h2>");
     out.println("<h4>" + result + "</h4>");
     }catch(Exception e){
       System.out.println("bad things happened");
     }
     out.println("</body>");
     out.println("</html>");
  }
  } //end of HelloServlet


__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com

Re: can't access database from servlet

From
"Dave Cramer"
Date:
Sanjeev,

One thing right away is the url for the database connection should be
"jdbc:postgresql://hostname/dbname"
Where hostname could be localhost

What I would do if I were you is write a small non-servlet program to do
what you are trying to do below.

i.e. take the init code and put it into the main of a small class then
if that is running try to put it back into the servlet.


Dave





-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Sanjeev Rathore
Sent: Friday, December 28, 2001 11:47 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] can't access database from servlet


Iam trying to access a table from PostgreSQL 7.1.3
from servlet.

These are the software that I am running.
Tomcat 4.0
PostgreSQL 7.1.3
RedHat 7.2

I am able to access PostgreSQL database from stand
alone Java program.
I am able to access meta data of PostgreSQL from
servlet, which means(i think) that JDBC driver must be
working. I am able to access database called 'auction'
and table called 'names' from a sql command prompt
which has one row of record. When servlet is executed
ResultSet returns null when it shoud return result of
SQL query.

I don't know where the problem could be but it seems
that Java code or some type of permission is
incorrect.  Here is my simple Servlet code that
compiles but gives ResultSet as null.

please help!
I am working on this problem for about 4 days now.

Thanks,
Sanjeev


  import java.sql.*;
  import java.io.*;
  import javax.servlet.*;
  import javax.servlet.http.*;
  import java.util.*;

  public class HelloServlet extends HttpServlet {
     Connection db;  //connection to database
     Statement  sql; //sql statement to query database
     DatabaseMetaData dbmd; //find out the drive
version
     String first_name = "blank";
     String last_name = " ";
     ResultSet result;

  public void init(ServletConfig config) throws ServletException {
     super.init(config);
     String database = "auction";
     String username = "postgres";
     String password = "postgres";
     try{
       Class.forName("org.postgresql.Driver");
       db =
DriverManager.getConnection("jdbc:postgresql:" +
database, username, password);
       dbmd = db.getMetaData();
       sql = db.createStatement();
           String sqlText = "select first_name from
names";
           result = sql.executeQuery(sqlText);
           while(result.next()){
             first_name =
result.getString(first_name);
             last_name  = result.getString(last_name);
           }
     }catch(Exception e){
       e.printStackTrace();
       //Connection = null;
     }
  }

  public void doGet(HttpServletRequest request, HttpServletResponse
response)
     throws IOException {
     response.setContentType("text/html");
     PrintWriter out = response.getWriter();
     out.println("<html>");
     out.println("<body>");
     out.println("<h1>Hello Sanjeev! </h1>");
     //HelloServlet hs = new HelloServlet();

     out.println("<h4>" + last_name  + "</h4>");
     try{
     out.println("<h4>" +
dbmd.getDatabaseProductName() + "</h4>");
     out.println("<h2>" +
dbmd.getDatabaseProductVersion() + "</h2>");
     out.println("<h4>" + result + "</h4>");
     }catch(Exception e){
       System.out.println("bad things happened");
     }
     out.println("</body>");
     out.println("</html>");
  }
  } //end of HelloServlet


__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online! http://greetings.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly



Re: can't access database from servlet

From
"Nick Fankhauser"
Date:
Sanjeev-

At first glance, your code looks good. I suspect that the user that Tomcat
runs as has not been granted access to the "names" table at the SQL level.

You are probably running psql as the user who created the table, so you can
see it fine at the sql command prompt, but Tomcat is probably running as
user "tomcat" or "www-data".

I think "ps axu" would show you the user that tomcat is running as.

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/



Re: can't access database from servlet

From
"Nick Fankhauser"
Date:
Sanjeev-

Another hint that might help-

Since your stack trace is going to System.out when an exception is caught,
you won't see the stack trace in your browser screen- It's going to end up
in the tomcat log instead.

I'm not sure where the Tomcat log lives on RedHat Linux, but under Debian, I
find it in /var/log/tomcat/stdout.log -If your tomcat user does not have the
grants to select from name, the error message telling you this is probably
in this log file. (Or if I'm totally off base (quite possible this soon
after new years... <grin>), there might be some other message giving you a
hint about what is going wrong.)

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/


Re: can't access database from servlet

From
"Nick Fankhauser"
Date:
> One thing right away is the url for the database connection should be
> "jdbc:postgresql://hostname/dbname"
> Where hostname could be localhost

If hostname is omitted, localhost is assumed, so I don't think that's the
problem. (Also, he was able to succesfully get the dbmd = db.getMetaData();
statement to execute, so he must be connected.

-Nick