<%@page import="java.sql.Connection,java.sql.DriverManager,java.sql.PreparedStatement,java.sql.SQLException,java.sql.ResultSet"%><%!

// server response codes
String licenseValid   = "601";
String licenseInvalid = "602";

// JDBC driver name
String driverName = "com.mysql.jdbc.Driver";

// MySql database URL:  jdbc:mysql://host_name:port/dbname
String dbUrl = "jdbc:mysql://localhost:3306/mydb";

// database username and password
String username = "root";
String password = "root123";

//client information table & serial number column
String tblName = "clients";
String colName = "serial_no";

%><%

// Variables POSTed by Advanced Installer serial validation tool to this web page: "sn", "languageid".
// get the serial number entered by the installing user in the "UserRegistrationDlg" dialog 
String sn = request.getParameter("sn");

// get the system language ID of the user's machine
// (you can use this parameter to display a localized error message taken from your database)
String languageId = request.getParameter("languageid");

// check if serial no. is in the database
boolean valid = checkIfSerialNoExists(sn);

//server HTTP response to the query issued by Advanced Installer serial validation tool
serverResponse(valid,sn,languageId,out);

%><%!

public Connection getConnection(){

  Connection con = null;
  
  try{
    // load the JDBC driver and return the database connection
    Class.forName(driverName).newInstance();
    con = DriverManager.getConnection(dbUrl, username, password);
  }catch(Exception e){
    // free resources
    if(con != null){
      try{
        con.close();
      }catch(java.sql.SQLException ex){}
    }
  }
  
  return con;
}

public boolean checkIfSerialNoExists(String sn){
    
  if(sn == null && sn.length() == 0) return false;
  
  boolean valid = false;
  Connection con = null;
  PreparedStatement stmt = null;
  ResultSet rs = null;
  
  try{
    // connect to database
    con = getConnection();
    
    // prepare SQL statement
    String query = "SELECT `" + colName + "` FROM `" + tblName + "` WHERE `" + colName + "` = ?";
    stmt = con.prepareStatement(query);
    stmt.setString(1, sn);   
    
    // execute query
    rs = stmt.executeQuery();
    
    // result set has at least one item
    valid = rs.next();
  }catch(Exception e){  
  }finally{
    // free resources
    if(rs != null){
      try{
        rs.close();
      }catch(SQLException ex){}
    }
    if(stmt != null){
      try{
        stmt.close();
      }catch(SQLException ex){}
    }
    if(con != null){
      try{
        con.close();
      }catch(SQLException ex){}
    }
  }
  
  return valid;
}

public void serverResponse(boolean valid,String sn,String languageId,JspWriter out) throws java.io.IOException{

  // load error messages from your database, using "$lang_id" for localization (optional)

  // serial number is missing or is empty
  if(sn == null || sn.length() == 0){
    out.print(licenseInvalid);
    out.print("\n");
    out.print("Missing Serial Number!");
  }else{
    // serial number NOT found in database => issue error response
    if(!valid){
      out.print(licenseInvalid);
      out.print("\n");
      out.print("Serial Number: " + sn + " is invalid!");
    }else{
      // serial number was found in database => issue SUCCESS response
      out.print(licenseValid);
    }
  }
}

%>