Communicating With SQL Server via HTML
[nobr]Hello.
I have recenty reintroduced myself to the Java world after having taken a class in college a few years back. I am trying to retrieve information from a SQL Server based on user input on an HTML form and display it back to HTML. From examples found online and an example from the class that connected to an access DB, I have put the following code together. I know it's not complete but I am stuck. I get a null pointer error on the line: stmt = con.createStatement(); I can't figure it out. I have verified that I can connect to the SQL Server through a command line program but I can't seem to apply it to this scenario. Can someone please help me?!
package coreservlets;
import java.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.util.*;
publicclass exiTrackingextends HttpServlet{
publicstaticfinal String url ="jdbc:microsoft:sqlserver://";
publicstaticfinal String serverName="server4";
publicstaticfinal String portNumber ="1433";
publicstaticfinal String databaseName="DB";
publicstaticfinal String userName ="user";
publicstaticfinal String password ="pass";
publicstaticfinal String selectMethod ="cursor";
publicstatic java.sql.Connection con;
publicstatic Statement stmt;
publicstatic String query ="select custid from tarcustomer";
/****************************************************************************
DOPOST - GATHERS AND POSTS
*****************************************************************************/
publicvoid doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException{
response.setContentType("text/html");
PrintWriter out = response.getWriter();
String so = request.getParameter("sono");//get info from html form
String po = request.getParameter("custpono");
String title ="Retrieval Result";
String validationResult ="";
String redirect ="";
String headTag ="<HTML><HEAD><TITLE>"+title+"</TITLE>";
String selectResult ="";
String endHeadTag ="</HEAD>";
String resultString ="invalid";
try{
con = this.getConnection();
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()){
String s = rs.getString("custid");
if(s.equalsIgnoreCase(so)){
resultString ="valid";//password is found
break;}
}
rs.close();
stmt.close();
}
catch (SQLException e){
System.err.println(e.getMessage());
}
if(selectResult =="valid")
{
redirect ="<META HTTP-EQUIV=\"Refresh\" CONTENT=\"1;URL=http://java.sun.com\">";
validationResult ="Successful query. Well done chap. <br>You will be re-directed to our homepage momentarily.";
}
else{
validationResult ="Unsuccessful Search. " +so+" " +po+"<br><Br>" +selectResult;
redirect ="<META HTTP-EQUIV=\"Refresh\" CONTENT=\"2;URL=/sotracking/html/index.html\">";
}
String outString = headTag + redirect + endHeadTag +
"<BODY BGCOLOR=\"#FDF5E6\">\n" +
"<H1 ALIGN=\"CENTER\">"+validationResult+"</H1>\n"+
"</BODY></HTML>";
out.println(outString);
try{
con.close();
}
catch (SQLException e){
System.err.println(e.getMessage());
}
}
/*********************************************************
GET CONNECTION
**********************************************************/
private java.sql.Connection getConnection(){
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
//if(con!=null);
}catch(Exception e){
e.printStackTrace();
System.out.println("Error Trace in getConnection() : " + e.getMessage());
}
return con;
}
/************************************************************
CONNECTION URL
***************************************************************/
privatestatic String getConnectionUrl(){
return url+serverName+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
}
}
[/nobr]
