// JDBC Socket Connection -- sample call to the JDBC through Sockets

import java.net.*;
import java.io.*;
import java.sql.*;

public class JS extends Thread {

	private	Socket clientSocket;	// Client socket object
	private Connection con;		// Database connection object
	private	Statement stmt;		// SQL statement object
	private	ResultSet rs;		// SQL query results
	private	ObjectInputStream is;	// Input stream
	private ObjectOutputStream os;	// Output stream
    
	public JS (Socket clientSocket) {
		this.clientSocket = clientSocket;
	}

	/** Thread execution method */
	public void run() {
		String inputLine;

		rs = null;
		stmt = null;
		con = null;
	        try {
	            is = new ObjectInputStream(clientSocket.getInputStream());
	            os = new ObjectOutputStream(clientSocket.getOutputStream());
	            while (readCommand()) {
	            }

	        } catch (IOException e) {
	            e.printStackTrace();
	        }
	}

	/** Receive and process incoming command from client socket */
	public boolean readCommand() {
		String s = null;

		try {
			s = (String)is.readObject();
		} catch (Exception e) {
			s = null;
		}

		if (s == null) {
			closeSocket();
			return false;
		}

		// invoke the appropriate function based on the command 
		if (s.equals("open")) { openDatabase(); }
		else if (s.equals("srch")) { performSearch(); }
		else if (s.equals("fetch")) { getNextRow(); }
		else if (s.equals("close")) { closeDatabase(true); }
		else { sendError("Invalid command -> " + s); }

		return true;
	}

	/** Send a message back through the client socket */
	public void send(Object o) {

		try {
			os.writeObject(o);
			os.flush();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/** Send a pre-formatted error message to the client */
	public void sendError(String msg) {
		send("error:" + msg);
	}

	/** Open a database connection */
	public void openDatabase() {

		try {
			// Load the JDBC-ODBC bridge driver
			Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

			// Connect to the database
			con = DriverManager.getConnection("jdbc:odbc:Northwind", "admin", "");
			send("ok");
		} catch (Exception ex) {
			sendError(ex.toString());
		}
	}

	/** Issue a SQL query */
	public void performSearch() {

		String query;		// SQL select string

		// build the query command
		query = "SELECT EmployeeID, LastName, FirstName, Title " +
			"FROM Employees ";

		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			send("ok");
		} catch (Exception ex) {
			sendError(ex.toString());
		}
	}

	/** Fetch the next row in the result set, returning null when done */
	public void getNextRow() {
		String s = null;

		try {
			if (rs.next()) {
				s = rs.getInt("EmployeeId") + ", "
					+ rs.getString("FirstName") + " "
					+ rs.getString("LastName") + ", "
					+ rs.getString("Title");
			}

			else {
				rs.close();
				rs = null;
				stmt.close();
				stmt = null;
				s = null;
			}

			send(s);
		} catch (Exception ex) {
			sendError(ex.toString());
		}
	}

	/** Close the database connection 
	    ack = true if we want to send an acknowledgement
		  back to the client
	*/
	public void closeDatabase(boolean ack) {
		try {
			if (rs != null)
				rs.close();

			if (stmt != null)
				stmt.close();

			if (con != null)
				con.close();

			rs = null;
			stmt = null;
			con = null;

			if (ack)
				send("ok");

		} catch (Exception ex) {
			sendError(ex.toString());
		}
	}

	/** Close the client socket */
	public void closeSocket() {
		try {
			closeDatabase(false);
			os.close();
			is.close();
			clientSocket.close();
		} catch (Exception ex) {
			System.err.println(ex.toString());
		}
	}
}