/***************************************************************************
* FILE NAME:  NameSrch.java     TITLE: Perform phonetic search
*
* AUTHOR:  K. E. North II, Ken North Computing
*
* from Java Database Magic with Java (Prentice-Hall PTR, 1998)
****************************************************************************
*
* Copyright (c) Kendall E. North II, 1998. All rights reserved. Reproduction
* or translation of this work beyond that permitted in Section 117 of the
* United States Copyright Act without express written permission of the
* copyright owner is unlawful. The purchaser may make backup copies for
* his/her own use only and not for distribution or resale. The Author and
* Publisher assumes no responsibility errors, omissions or damages caused
* by the use of these programs or from use of the information contained
* herein.
*
****************************************************************************
* Synopsis:
*
* Demonstrate server-side Java using JDBC. Use Sybase runtime environment.
* Install this class in asademo, the Adaptive Server sample database.   
*      
* This class executes  SOUNDEX search of the employee table in the
* Adaptive Server Anywhere sample database.
***************************************************************************/

   // import JDBC classes
import java.sql.*;

public class NameSrch {

    // data members
    public String sqlQry;
	
    private Connection conn;
    private Statement stmt;
    private ResultSet rs;
    private boolean isMore;

    public NameSrch(String lname ) throws SQLException {
 
        // use internal connection with server-side JDBC driver
        conn = DriverManager.getConnection(³²);
        stmt= conn.createStatement();

        sqlQry = ³select emp_lname, emp_fname ³;
        sqlQry = sqlQry + ³from employee ³;
        sqlQry = sqlQry + ³where SOUNDEX(emp_lname) = ³;
        sqlQry = sqlQry + ³SOUNDEX(\¹² + lname + ³\¹)²;

        // echo class and query to Adaptive Server status window
        String msg1 = ³Class NameSrch: ³;
        msg1 = msg1 + sqlQry;
        System.out.println (msg1);

        // execute the query
        rs = stmt.executeQuery(sqlQry);

        // position the cursor at the first row of the ResultSet 
        isMore = rs.next();
    }

    public String getName() throws SQLException {
        String fName = ³²;
        String lName = ³²;
        String eName = ³²;

        if ( isMore ) 
            {
            lName = rs.getString(1);
            fName = rs.getString(2);
            eName = lName + ³, ³ + fName;
            }

        isMore = rs.next();

        if ( eName == ³² ) 
            {
            return null;
            } 
            else 
            {
            return eName;
            }
    }

    public boolean setName( String lname ) 
        throws SQLException {

        sqlQry = ³select emp_lname, emp_fname ³;
        sqlQry = sqlQry + ³from employee ³;
        sqlQry = sqlQry + ³where SOUNDEX(emp_lname) = ³;
        sqlQry = sqlQry + ³SOUNDEX(\¹² + lname + ³\¹)²;

        // execute the query
        rs = stmt.executeQuery( sqlQry );

        isMore = rs.next();
        return isMore;
    }

    public boolean more() 
        {
        return isMore;
        }
}