import java.sql.*;
import java.util.*;

/**
 * Class that shows how to connect from IBM i to SQL Server
 * using the Microsoft JDBC Driver
 */ 
public class ibmiToSqlServer {
	
    final static String DEFAULT               = "*DEFAULT";
    final static String PROPERTY_DATABASENAME = "databaseName=";
    final static String PROPERTY_INSTANCENAME = "instanceName=";
    final static String PROPERTY_PASSWORD     = "password=";
    final static String PROPERTY_PORT         = "port=";
    final static String PROPERTY_USER         = "user=";
    final static String SEMICOLON             = ";";

    public static void main(String[] args) {
		
	String connectionUrl = createConnectionString();
	System.out.println("connectionUrl: " + connectionUrl);
		
	Connection conn = null;  
	Statement  stmt = null;
	ResultSet  rs   = null;
		
	try {
	    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			
	    conn = DriverManager.getConnection(connectionUrl);
			
	    String sql = "SELECT * FROM QIWS.QCUSTCDT ORDER BY BALDUE DESC";
			
            stmt = conn.createStatement();
	    rs   = stmt.executeQuery(sql);
			
	    while (rs.next()) {
		System.out.println(  rs.getString("CUSNUM") + "|"
		                   + rs.getString("LSTNAM") + "|"
			           + rs.getString("INIT"  ) + "|"
			           + rs.getString("BALDUE"));
	    }
        }
		
        catch (SQLException sqle) {
 	    System.out.println("sqle.getMessages: " + sqle.getMessage());
	    System.out.println("sqle.getSQLState: " + sqle.getSQLState());
	    System.out.println("sqle.getCause:    " + sqle.getCause());
	}
        catch (Exception e) {
            System.out.println("Exception message: " + e.getMessage());
	    e.printStackTrace();
	}
    }
	
    /**
     * Prompt for connection string values. Return the connection 
     * string to the caller.
     * @return A connection string for SQL Server.
     */
    private static String createConnectionString() {
	
	Scanner console = new Scanner(System.in);
		
        boolean promptForString = true;

	String connectionString = null;
				
	while (promptForString) {
			
	    connectionString = "jdbc:sqlserver://";
			
	    String sqlServerName     = null;
	    String sqlServerInstance = null;
	    String sqlServerPort     = null;
	    String sqlServerDatabase = null;
	    String sqlServerUID      = null;
	    String sqlServerPWD      = null;
			
	    System.out.println("Enter the following parameters for the SQL Server connection string");
	    System.out.println("-------------------------------------------------------------------");
			
	    System.out.print("  SQL Server name ...............: ");
	    sqlServerName = console.nextLine();
	
  	    System.out.print("  SQL Server instance or *DEFAULT: ");
	    sqlServerInstance = console.nextLine();
			
	    System.out.print("  Port or *DEFAULT ..............: ");
	    sqlServerPort = console.nextLine();
			
	    System.out.print("  Database ......................: ");
	    sqlServerDatabase = console.nextLine();
			
	    System.out.print("  User ID .......................: ");
	    sqlServerUID = console.nextLine();
			
	    System.out.print("  Password ......................: ");
	    sqlServerPWD = console.nextLine();
					
	    connectionString +=                         sqlServerName     + SEMICOLON;
	    connectionString += PROPERTY_DATABASENAME + sqlServerDatabase + SEMICOLON;
	    connectionString += PROPERTY_USER         + sqlServerUID      + SEMICOLON;
	    connectionString += PROPERTY_PASSWORD     + sqlServerPWD      + SEMICOLON;
			
	    if (sqlServerPort.trim().length() == 0) {
	        sqlServerPort = DEFAULT;
	    }
			
	    if (!sqlServerPort.toUpperCase().equals(DEFAULT)) {
		connectionString += PROPERTY_PORT + sqlServerPort + SEMICOLON;
	    }
	
	    if (sqlServerInstance.trim().length() == 0) {
		sqlServerInstance = DEFAULT;
	    }
			
	    if (!sqlServerInstance.toUpperCase().equals(DEFAULT)) {
		connectionString += PROPERTY_INSTANCENAME + sqlServerInstance + SEMICOLON;
	    }
			
	    System.out.println("connectionString: " + connectionString);
		
	    System.out.print("Do you need to make any changes? (y/n) ");
		
	    String makeChanges = console.nextLine();
			
	    if (makeChanges.trim().toUpperCase().equals("N")) {
		promptForString = false;
	    }
        }
		
	console.close();
		
	return connectionString;
    }
}