import java.sql.*;
import java.net.*;
import java.io.*;
import java.lang.*;
import java.util.*;
import sun.server.http.*;
import java.servlet.*;
import java.servlet.http.*;
public class dbAdminServlet extends HttpServlet {
private Hashtable hDBLIST = new Hashtable(10); // hashtable for dblist
//private Vector vConnection = new Vector(10);
private void homePage(HttpServletRequest req,HttpServletResponse res){
PrintStream out = new PrintStream(res.getOutputStream());
out.println(" WebAdmin Applet");
out.println("");
out.println("");
out.println("");
out.println("");
out.flush();
}
private void handleDBList(HttpServletRequest req,HttpServletResponse res){
Connection con;
DataOutputStream sout;
StringBuffer sbuf;
String s="";
try {
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
con = connect("jdbc:odbc:workpgdb","udayan","udayan");
//connect to the database
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from dbMaster");
// retrieve the dbMaster records
boolean dbexist=false;
sbuf = new StringBuffer();
while(rs.next()){
System.out.println("inside while");
String dbname = rs.getString("dbname");
String dbtype = rs.getString("dbtype");
String url = rs.getString("url");
String machine = rs.getString("machine");
System.out.println("dbname :"+dbname);
System.out.println("dbname :"+url);
System.out.println("dbname :"+machine);
System.out.println("dbname :"+dbtype);
sbuf.append("dbname="+dbname+"&machine="+machine+"&url="+url+"&dbtype="+dbtype);
hDBLIST.put(dbname,new String("dbname="+dbname+"&machine="+machine+"&url="+url+"&dbtype="+dbtype));
sbuf.append("\r\n");
}
//send it to the applet
//res.setContentType("text/html");
sout = new DataOutputStream(res.getOutputStream());
// get the outputstream to send titles
sout.writeBytes(new String(sbuf));
disconnect(con);
sout.flush();
sout.close();
}
catch(SQLException e){
e.printStackTrace();
}
catch(Exception e){
e.printStackTrace();
}
}
private void handleTableList(HttpServletRequest req,HttpServletResponse res, String q){
DataInputStream inStream;
DataOutputStream outStream;
DataOutputStream sout;
Socket sock;
String currLine="";
String machine="";
int port =8080;
Hashtable ht = get_hashed(q);
String dbselect = (String)ht.get("dbname");
String url = (String)ht.get("url");
String dbtype = (String)ht.get("dbtype");
System.out.println("Selected database for connect: "+dbselect);
if(hDBLIST.containsKey(dbselect)){
String s = (String) hDBLIST.get(dbselect);
ht = get_hashed(s);
machine = (String)ht.get("machine");
System.out.println("Machine is :"+machine);
if(machine.startsWith("osprey7")){
port = 8110;
System.out.println("port set to "+port);
}
// open socket and contact machine
try {
sock = new Socket(machine,port);
inStream = new DataInputStream(sock.getInputStream());
outStream = new DataOutputStream(sock.getOutputStream());
//sout.writeBytes("send");
outStream.writeBytes("GET "+ "/servlet/localSer?GETTABLES=yes&url="+url+"&dbtype="+dbtype+" HTTP/1.0 \r\n\r\n");
// connect first time and expect the connecion id back and data related to
// tables
while ((currLine = inStream.readLine()) != null) {
if (currLine.length() == 0) break;
if (currLine.toLowerCase().startsWith("content-length:")) {
try {
int length = Integer.valueOf(currLine.substring(15)).intValue();
}catch (Exception ignoreMe) {
}
}
System.out.println(currLine);
}
// write table details back to the applet
// which is formatted as :
// conid
// table=tablename
// field=XXX...
// table=tablename...
res.setContentType("text/html");
sout = new DataOutputStream(res.getOutputStream());
currLine = inStream.readLine();
System.out.println("### Connection in dbadmin "+ currLine);
sout.writeBytes(currLine+"\r\n");
sout.flush();
System.out.println("writing Connection o applet"+ currLine);
while (!((currLine = inStream.readLine()).startsWith("END"))){
System.out.println("In dbadmin :"+currLine);
sout.writeBytes(currLine+"\r\n");
System.out.println("In dbadmin after writing :"+currLine);
sout.flush();
}
System.out.println("finished sending to the applet");
outStream.flush();
outStream.close();
sout.flush();
sout.close();
inStream.close();
sock.close();
}
catch(IOException e){
e.printStackTrace();
}
}
else {
System.out.println("dbname :"+dbselect +"not found in dbMaster");
}
}
private void handleDeleteTable(HttpServletRequest req,HttpServletResponse res,String q) {
DataInputStream inStream;
DataOutputStream outStream;
DataOutputStream sout;
Socket sock;
String currLine="";
String machine="";
int port=8080;
Hashtable ht = get_hashed(q);
String dbselect = (String)ht.get("dbname");
String conid = (String)ht.get("conid");
String table = (String)ht.get("table");
System.out.println("Selected database for connect: "+dbselect);
if(hDBLIST.containsKey(dbselect)){
String s = (String) hDBLIST.get(dbselect);
ht= get_hashed(s);
machine = (String)ht.get("machine");
if(machine.startsWith("osprey7")){
port = 8110;
System.out.println("port set to "+port);
}
// open socket and contact machine
try {
sock = new Socket(machine,port);
inStream = new DataInputStream(sock.getInputStream());
outStream = new DataOutputStream(sock.getOutputStream());
sout = new DataOutputStream(res.getOutputStream());
String query = "table="+table+"&conid="+conid;
outStream.writeBytes("GET "+ "/servlet/localSer?DELETETABLE=yes&"+query+" HTTP/1.0 \r\n\r\n");
// connect first time and expect the connecion id back and data related to
// tables
while ((currLine = inStream.readLine()) != null) {
if (currLine.length() == 0) break;
if (currLine.toLowerCase().startsWith("content-length:")) {
try {
int length = Integer.valueOf(currLine.substring(15)).intValue();
}catch (Exception ignoreMe) {
}
}
}
// write table details back to the applet
currLine = inStream.readLine();
System.out.println("result after delete in dbAdmin"+currLine);
sout.writeBytes(currLine+"\r\n");
sout.flush();
outStream.flush();
sout.close();
outStream.close();
inStream.close();
sock.close();
}
catch(IOException e){
e.printStackTrace();
}
}
else {
System.out.println("dbname :"+dbselect +"not found in dbMaster");
}
}
private void handleCreateTable(HttpServletRequest req,HttpServletResponse res){
DataInputStream din;
DataOutputStream dout;
DataInputStream inStream;
DataOutputStream outStream;
Socket sock;
Hashtable ht;
String machine="";
try {
din = new DataInputStream(req.getInputStream());
dout = new DataOutputStream(res.getOutputStream());
// read the dbname
String currLine;
currLine = din.readLine();
ht = get_hashed(currLine);
String conid = (String)ht.get("conid");
String table = (String)ht.get("table");
String dbname = (String)ht.get("dbname");
// now read in details regarding the table creation
currLine = "";
StringBuffer sbuf = new StringBuffer();
while ((currLine = din.readLine()) != null){
sbuf.append(currLine);
}
String args = new String(sbuf);
int content_len = args.length();
if(hDBLIST.contains(dbname)){
String s = (String) hDBLIST.get(dbname);
ht = get_hashed(s);
machine = (String)ht.get("machine");
// open socket and contact machine
sock = new Socket(machine,8110);
inStream = new DataInputStream(sock.getInputStream());
outStream = new DataOutputStream(sock.getOutputStream());
String query = "table="+table+"&conid="+conid;
outStream.writeBytes("POST "+ "/servlet/localSer?"+query+" HTTP/1.0\r\n");
outStream.writeBytes("Content-type: application/octet-stream\r\n");
outStream.writeBytes("Content-length: " + content_len +"\r\n\r\n");
System.out.println("After doing post");
while ((currLine = inStream.readLine()) != null) {
if (currLine.length() == 0) break;
if (currLine.toLowerCase().startsWith("content-length:")) {
try {
int length = Integer.valueOf(currLine.substring(15)).intValue();
}catch (Exception ignoreMe) {
}
}
}
// write table details back to the applet which will be status
// whether it was succeful or not
// depending upon which update the applet
while ((currLine = inStream.readLine()) != null){
dout.writeBytes(currLine);
}
outStream.flush();
dout.flush();
outStream.close();
dout.close();
inStream.close();
din.close();
sock.close();
}
else {
System.out.println("dbname :"+dbname +"not found in dbMaster");
}
}
catch(IOException e){
e.printStackTrace();
}
}
// checks whether the string is null
private boolean isNull(String s){
if(s.equalsIgnoreCase(""))
return true;
else
return false;
}
// gets the value for key from a hashtable
private Hashtable get_hashed(String s){
HttpUtils smutils;
Hashtable h;
try {
smutils = new HttpUtils();
System.out.println("in get_hashed "+s);
h=smutils.parseQueryString(s);
return h;
}
catch(Exception e){
e.printStackTrace();
return null;
}
}
// Encodes special characters so that there are no ambiguitie while sending data
private String getEncoded(String str) {
String per = "%25";
String amp = "%26";
String equ = "%3d";
String newline = "+\n";
String st = str.replace(' ','+');
String finstr = "";
StringBuffer sbuf = new StringBuffer();
for (int i = 0; i < st.length(); i++) {
char c = st.charAt(i);
switch(c) {
case '%':
sbuf.append(per);
break;
case '&':
sbuf.append(amp);
break;
case '=':
sbuf.append(equ);
break;
case '\n':
sbuf.append(newline);
break;
default :
sbuf.append(c);
break;
}
}
return (new String(sbuf));
}
// given resultset and a datatype returns the field value specified by the col
private String getvalue(ResultSet rs,int datatype,int col)
throws SQLException{
String value="";
int tp = 1;
switch(datatype) {
case Types.DATE:
java.sql.Date date = rs.getDate(col);
value = date.toString();
break;
case Types.TIME:
java.sql.Time time = rs.getTime(col);
value = time.toString();
break;
case Types.TIMESTAMP:
java.sql.Timestamp timestamp = rs.getTimestamp(col);
value = timestamp.toString();
break;
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
value = rs.getString(col);
break;
case Types.BIT:
boolean bit = rs.getBoolean(col);
value = (new Boolean(bit)).toString();
break;
case Types.INTEGER:
int integer = rs.getInt(col);
Integer ind = new Integer(integer);
value = ind.toString();
break;
case Types.BIGINT:
long bigint = rs.getLong(col);
Long lng = new Long (bigint);
value = lng.toString();
break;
case Types.REAL:
float real = rs.getFloat(col);
value = (new Float(real)).toString();
break;
case Types.FLOAT:
case Types.DOUBLE:
double longreal = rs.getDouble(col);
value = (new Double(longreal)).toString();
break;
}
if (value == null ) {
value = "";
return value;
}else {
String fin = getEncoded(value);
return fin;
}
}
// Connect to a database given url,user,password
private Connection connect(String url,String user, String passwd) throws SQLException {
Connection conn;
try {
// Attempt to connect to a driver. Each one
// of the registered drivers will be loaded until
// one is found that can process this URL
conn = DriverManager.getConnection (url, user, passwd);
System.out.println("After successful connection");
return conn;
}
catch (SQLException ex) {
// A SQLException was generated. Catch it and
// display the error information. Note that there
// could be multiple error objects chained
// together
System.out.println ("\n*** SQLException caught ***\n");
while (ex != null) {
System.out.println ("SQLState: " +
ex.getSQLState ());
System.out.println ("Message: " +
ex.getMessage ());
System.out.println ("Vendor: " +
ex.getErrorCode ());
ex = ex.getNextException ();
System.out.println ("");
}
throw ex; // rethrow the exception
}
catch (java.lang.Exception ex) {
// Got some other type of exception. Dump it.
ex.printStackTrace ();
}
return null;
}
private boolean disconnect(Connection c) throws SQLException {
boolean rc= true;
try{
c.close();
System.out.println("Connection closed");
}
catch(SQLException ex){
// A SQLException was generated. Catch it and
// display the error information. Note that there
// could be multiple error objects chained
// together
System.out.println ("\n*** SQLException caught ***\n");
while (ex != null) {
System.out.println ("SQLState: " +
ex.getSQLState ());
System.out.println ("Message: " +
ex.getMessage ());
System.out.println ("Vendor: " +
ex.getErrorCode ());
ex = ex.getNextException ();
System.out.println ("");
}
throw ex; // rethrow the exception
}
return rc;
}
//-------------------------------------------------------------------
// checkForWarning
// Checks for and displays warnings. Returns true if a warning
// existed
//-------------------------------------------------------------------
private static boolean checkForWarning (SQLWarning warn) throws SQLException {
boolean rc = false;
// If a SQLWarning object was given, display the
// warning messages. Note that there could be
// multiple warnings chained together
if (warn != null) {
System.out.println ("\n *** Warning ***\n");
rc = true;
while (warn != null) {
System.out.println ("SQLState: " +
warn.getSQLState ());
System.out.println ("Message: " +
warn.getMessage ());
System.out.println ("Vendor: " +
warn.getErrorCode ());
System.out.println ("");
warn = warn.getNextWarning ();
}
}
return rc;
}
public void service(HttpServletRequest req,HttpServletResponse res) throws ServletException, IOException {
String qStr;
qStr = req.getQueryString();
if((req.getMethod()).equals(new String("POST"))){
System.out.println("In dbAdminServlet POST");
if(qStr.equalsIgnoreCase("CREATETABLE")) {
handleCreateTable(req,res);
}
}
else { // method is GET , display applet on the page
if (qStr == null ) { // main page
System.out.println("The query String is not there ");
homePage(req,res);
}
else if(qStr.startsWith("GETDBLIST")) {
System.out.println("The queryString is "+qStr);
handleDBList(req,res);
}
else if(qStr.startsWith("GETTABLELIST")) {
System.out.println("The queryString is "+qStr);
handleTableList(req,res,qStr);
}
else if(qStr.startsWith("DELETETABLE")) {
System.out.println("The queryString is "+qStr);
handleDeleteTable(req,res,qStr);
}
else {
System.out.println("The queryString is "+qStr);
PrintStream out = new PrintStream(res.getOutputStream());
out.println(" Error Page ");
out.println("");
out.println("");
out.println("Wrong Option or Bad request !
");
out.println("Please try again.");
out.println("");
out.flush();
}
}
}
} // end of dbAdminServlet class