Hi,

I have written a jsp file to fetch data from a database table and display it in the form of a table in jsp. The idea is to generate a dynamic table. I have written the following code.

<%@page import="java.sql.*"%>
<html>
<head>
<title>The alert table</title>
</head>
<body>
<h1>Alerts</h1>
<%
    Connection conn = null;
    ResultSet rs1 = null;
    Statement stmt = null;
    ResultSetMetaData rsmd = null;
    String query="select * from alertdata";

    try {
      Class c = Class.forName("org.postgresql.driver");
      conn = DriverManager.getConnection("jdbc:postgresql://192.168.128.150:5432/thirdeye", "postgres", "postgres");
      stmt = conn.createStatement();
      rs1 = stmt.executeQuery(query);

     int columns=0;
       rsmd = rs1.getMetaData();
       columns = rsmd.getColumnCount();
     }
     catch (SQLException e) {
        System.out.println("Error occurred " + e);
     }
%>
<table width="90%" border="1">
    <tr>
        <% // for the header cells
     try {
        for (int i=1; i<=columns; i++) {
             out.write("<th>" + rsmd.getColumnLabel(i) + "</th>");
        }
  %>
    </tr>


    <% // for each row in the database table
     while (rs1.next()) {
         out.write("<tr>");
         for (int i=1; i<=columns; i++) {
           out.write("<td>" + rs1.getString(i) + "</td>");
         }
         out.write("</tr>");
      }

        // close the connection and the statement
        stmt.close();
        conn.close();
     } 
     catch (SQLException e) {
        System.out.println("Error " + e);
     }

   finally {
    try {
      if (stmt != null)
       stmt.close();
      }  catch (SQLException e) {}
      try {
       if (conn != null)
        conn.close();
       } catch (SQLException e) {}
   }

   %>
</table>
</body>
</html>

However this gives me an error, cannot resolve columns.

Please help.
Saswati

Edited 3 Years Ago by mike_2000_17: Fixed formatting

You have defined 'columns' locally inside a try-catch block and are attempting to use it in another block. Move the declaration outside the first block so that it is visible to the second.

You have defined 'columns' locally inside a try-catch block and are attempting to use it in another block. Move the declaration outside the first block so that it is visible to the second.

My code is working now. Thanks a lot for your help.

Saswati

You aware that accessing any database with JSP is not recommended and should be avoid. Servlets been design for this task...

Hi,

I am very new in java/jsp. Could you please explain me how to use servlets instead of jsp.

Thanks a lot in advance.

Saswati

This question has already been answered. Start a new discussion instead.