_The JDBC Client and Server_
by Andrew Wilson


Listing One
Driver  m_JDBCDriver;
String  m_JDBCUrl; 
Connection m_Connection; 

public boolean StartServices ()
    {
        try
        {   
            m_JDBCDriver  =(Driver) Class.forName 
              ("jdbc.odbc.JdbcOdbcDriver").newInstance();  // Loads JDBC driver
            if ( !m_JDBCDriver.jdbcCompliant () )
            {
                m_Log.addItem ( "Driver is not JDBC compliant");
               return false;
            }
            if (m_JDBCDriver.acceptsURL ( m_JDBCUrl ) ) 
                                   // Confirms that we can attach to this URL
            {
                m_Log.addItem ( "Trying to establish a 
                                                connection with "+m_JDBCUrl);
                m_Connection = DriverManager.getConnection ( m_JDBCUrl );
                return true;
            }               
        } catch (ClassNotFoundException e)
        {
            m_Log.addItem ( "Could not load JDBC-ODBC bridge, 
                                                   check your path env.");
            m_Log.addItem ( e.getMessage() );
            return false;
        }
        catch (SQLException db)
        {
            m_Log.addItem ( "Connection failed: " +db.getMessage());
        }
        catch (Exception e)
        {
            m_Log.addItem ( "Critical Fault in Starting DB services");
            m_Log.addItem ( e.getMessage() );
        }
        return false;
    }   

Listing Two
List m_Log;
Label m_TimeLabel;

int m_Port;

public void run()
    {
        StartServices();
        m_ServerThread = new ServerThread ( m_Port, m_Connection, m_Log ); 
        m_Log.addItem ( "Database driver and socket services are up" );
        while (true)
        {
                m_TimeLabel.setText( new Date().toString() );
        }
    }


Listing Three
protected ServerSocket m_Server = null;
protected Session m_Session = null;

protected void InitServer ()
{
    if (m_Server == null)
    {
        try
        {
            m_Server = new ServerSocket ( m_Port, 6 );
            m_Log.addItem ( "Socket server is up at port: " + m_Port );
            m_Log.makeVisible ( m_Log.countItems ()  - 1);
        } catch ( IOException e)
        {
            m_Log.addItem ( "Could not create socket:" + m_Port );
            m_Log.addItem ( e.getMessage() );
            m_Log.makeVisible ( m_Log.countItems ()  - 1);
            Stop ();
        }
    }
}
public void run ()
{
    InitServer (); 
    while (true)
    {
        try
        {
            m_Log.addItem ( "Waiting for Connect" );
            m_Session = new Session (m_Server.accept(), m_Connection, m_Log);
            m_Log.addItem ( "User has attached" );
            m_Log.makeVisible ( m_Log.countItems ()  - 1);
        } catch ( IOException e)
        {
            m_Log.addItem ( "Could not Accept" + m_Port );
            m_Log.addItem ( e.getMessage() );
            m_Log.makeVisible ( m_Log.countItems ()  - 1);
            Stop ();
        }
    }

}


Listing Four
protected Socket m_Socket = null;
protected Connection m_Connection;
protected DataInputStream m_InStream;
protected DataOutputStream m_OutStream;
List m_Log;

public Session ( Socket socket, Connection connection, List Log)
{                                
    m_Log = Log;
    if(socket != null)
    {
        try 
        {
            m_Socket = socket;
            m_OutStream = new DataOutputStream ( m_Socket.getOutputStream());
            m_InStream = new DataInputStream ( m_Socket.getInputStream());
        } catch ( IOException e)
        {
            addLogItem ( "Could not duplicate connection: "+e.getMessage());
        }
        m_Connection = connection;
            
        start();
    }
    else
    {
        stop();
    }
}


Listing Five
public void run ()
{        
    addLogItem ( "New Session Thread Started");
    while (true)
    {        
        if (manageConnection() == false)
            Stop();
    }
}
protected boolean manageConnection()
{
    int minutes = new Date().getMinutes();
    int ID = -1;

    try
    {
        Date date = new Date();

        if( date.getMinutes() != minutes)

        {
            minutes = date.getMinutes();                                     
            m_OutStream.writeBytes( TrackerData.buildHeartBeat());
        }
        if ( m_InStream.available() > 0)
        {
                        
            byte Stream[] = new byte[m_InStream.available()];
            m_InStream.readFully(Stream);
            String IncomingString = new String(Stream, 0);

            TrackerData TrackerPacket = new TrackerData(IncomingString);
            if( TrackerPacket.parsePacket() )
            {
                switch( TrackerPacket.getType() )
                {
                case TrackerPacket.QUERY:
                    ID = getDBItem(TrackerPacket);
                    TrackerPacket.setID(ID);

                    if(ID != -1)
                    {
                        addLogItem("User Queried");
                    }
                    else
                        addLogItem("User Query Failed");

                    sendReply(TrackerPacket, TrackerPacket.UPDATE);
                    break;

                case TrackerPacket.SET:
                    ID = addDBItem(TrackerPacket);
                    TrackerPacket.setID(ID);

                    if(ID != -1)
                        addLogItem("New DB Entry");
                    else
                        addLogItem("New DB Entry Attemp Failed");
                    sendReply(TrackerPacket, TrackerPacket.UPDATE);
                    break;
                case TrackerPacket.UPDATE:
                    ID = updateDBItem(TrackerPacket);
                    TrackerPacket.setID(ID);

                    if(ID != -1)
                        addLogItem("DB Entry Update");
                    else
                        addLogItem("DB Entry Update Failed");
                                                
                    sendReply(TrackerPacket, TrackerPacket.UPDATE);  
                    break;
                 }
             }
         }
     } catch (IOException f)

     {
         addLogItem ( f.getMessage());
         return false;
     }
     return true;
}


Listing Six
protected int addDBItem(TrackerData TrackerPacket)
{
    if((TrackerPacket.getFName().length() < 1) ||
      (TrackerPacket.getLName().length() < 1))
        return -1;
    Statement statement = attachDB();

    if(statement == null)
        return -1;

    String SQLStatement = buildRecordString(TrackerPacket);

    SQLStatement = "INSERT INTO Customers (ContactFirstName,"+
        "ContactLastName, CompanyName, PhoneNumber, Problem, Resolution)"+
        "VALUES " + SQLStatement;
    try
    {
        statement.executeUpdate(SQLStatement); 
        statement.close();
    } catch (SQLException e)
    {
        try
        {
            statement.close();
        } catch (SQLException f)
        {
            addLogItem(f.getMessage());
            return -1;
        }
        addLogItem(e.getMessage());
        return -1;
    }
    int ID = getID(TrackerPacket);
    return ID;
}
protected Statement attachDB () 
{
    try
    {
        Statement statement = m_Connection.createStatement();
        return statement;
    } catch (SQLException e)
    {
        addLogItem ( "Failed to get DB connection: "+e.getMessage() );
        return null;
    }

}
protected String buildRecordString(TrackerData TrackerPacket)
{
    String recordString = new String();
    recordString = "( '"+TrackerPacket.getFName()+"'"+
        ", '"+TrackerPacket.getLName()+"'"+
        ", '"+TrackerPacket.getCompany()+"'"+
        ", '"+TrackerPacket.getPhone()+"'"+
        ", '"+TrackerPacket.getProblem()+"'"+
        ", '"+TrackerPacket.getResolution()+"')";

    return recordString;
}

Listing Seven
protected int getID( TrackerData TrackerPacket)
{
    int ID = -1;

    Statement statement = attachDB();
    ResultSet rs;

    if(statement == null)
        return ID;
    try
    {
        String SQLStatement = new String();
        SQLStatement = "SELECT * FROM Customers WHERE ContactFirstName = '"
             + TrackerPacket.getFName() + "' AND ContactLastName = '" 
             + TrackerPacket.getLName()      + "' AND CompanyName = '"
             + TrackerPacket.getCompany() + "' AND   PhoneNumber = '"
             + TrackerPacket.getPhone() + "' AND Problem = '" 
             + TrackerPacket.getProblem()+ "' AND Resolution = '"
             + TrackerPacket.getResolution()+"'";
        rs = statement.executeQuery(SQLStatement);
        rs.next();
        ID = rs.getInt(1);
        rs.close();
        statement.close();
    } catch (SQLException e)
    {
        try
        {
            statement.close();
        } catch (SQLException f)
        {
            addLogItem(f.getMessage());
        }
        addLogItem(e.getMessage());
    }
    return ID;
}


Listing Eight

public boolean parsePacket()
{        
    if(Packet != null)
    {
        StringTokenizer ParsedPacket = new StringTokenizer(Packet, "-");
        int type;
        int len;

        while(ParsedPacket.hasMoreElements())
        {
            Type = type = getType (ParsedPacket.nextToken()); 
            switch(type)
            {
            case UPDATE:
            case SET:
                if (type == UPDATE)
                {
                    len = getLength(ParsedPacket.nextToken());
                    setID(Integer.parseInt(getString(ParsedPacket, len)));
                }
                    
                len = getLength(ParsedPacket.nextToken());
                setFName(getString(ParsedPacket, len));

                len = getLength(ParsedPacket.nextToken());
                setLName(getString(ParsedPacket, len));

                len = getLength(ParsedPacket.nextToken());
                setCompany(getString(ParsedPacket, len));

                len = getLength(ParsedPacket.nextToken());
                setPhone(getString(ParsedPacket, len));

                len = getLength(ParsedPacket.nextToken());
                setProblem(getString(ParsedPacket, len));

                len = getLength(ParsedPacket.nextToken());
                setResolution(getString(ParsedPacket,len)); 
                return true;

            case QUERY:
                len = getLength(ParsedPacket.nextToken());
                setID(Integer.parseInt(getString(ParsedPacket, len)));
                return true;
                
            case HEARTBEAT:
                len = getLength(ParsedPacket.nextToken());               
                getString(ParsedPacket,len);
                break;
            }
        }
    }
    return false; 
}


Listing Nine
public String buildPacket(int type)
{
    int FNameLen = FName.length();
    int LNameLen = LName.length();
    int CompanyLen = Company.length();
    int PhoneLen = Phone.length();
    int ProblemLen = Problem.length();
    int ResolutionLen = Resolution.length();

    String newPacket = new String();
    String ids = new String(Integer.toString(ID));

    switch (type)
    {
    case UPDATE:
    case SET:
        if (ID == -1)
        {
            type = SET;
            newPacket = type + "-"+FNameLen+"-"+FName+"-"
                +LNameLen+"-"+LName+"-"
                +CompanyLen+"-"+Company+"-"
                +PhoneLen+"-"+Phone+"-"
                +ProblemLen+"-"+Problem+"-"
                +ResolutionLen+"-"+Resolution;
        }
        else
        {
           type = UPDATE;

           newPacket = type +"-"+ids.length()+"-"+ids+
               "-"+FNameLen+"-"+FName+"-"
               +LNameLen+"-"+LName+"-"
               +CompanyLen+"-"+Company+"-"
               +PhoneLen+"-"+Phone+"-"
               +ProblemLen+"-"+Problem+"-"
               +ResolutionLen+"-"+Resolution;
        }
        break;
    case QUERY:
        newPacket = type +"-"+ids.length()+"-"+ids;
        break;
    }
    return newPacket;
}