UCanAccess Java Execution Error

Question

When attempting to get details from an MS Access database using the following code:

//returns the details of a specific employee for use in the update GUI
public static String getEmployeeDetails(int empID) throws SQLException{
    String employee = "";
    Statement stmt = conn.createStatement();
    String query = "SELECT employeetbl.Department, "
            + "employeetbl.Surname, "
            + "employeetbl.FirstName, "
            + "employeetbl.CurrentPosition, "
            + "FORMAT(employeetbl.DateOfBirth, 'yyyy/mm/dd') AS DateOfBirth, "
            + "employeetbl.TotalYearsRelevantExperience, "
            + "employeetbl.HighestQualification, "
            + "employeetbl.EmailAddress, "
            + "employeetbl.PhoneNo, "
            + "FORMAT(employeetbl.DateOfEmployment, 'yyyy/mm/dd') AS DateOfEmployment "
            + "FROM employeetbl WHERE EmployeeID = "+empID+";";
    ResultSet rs = stmt.executeQuery(query);
    while(rs.next()){
        employee = rs.getString("Department")
                +"#"+rs.getString("Surname")
                +"#"+rs.getString("FirstName")
                +"#"+rs.getString("CurrentPosition")
                +"#"+rs.getString("DateOfBirth")
                +"#"+rs.getString("TotalYearsRelevantExperience")
                +"#"+rs.getString("HighestQualification")
                +"#"+rs.getString("EmailAddress")
                +"#"+rs.getString("PhoneNo")
                +"#"+rs.getString("DateOfEmployment");
    }
    return employee;
}

called from this method:

if(cmbTable.getSelectedItem().equals("Employees")){
            String[] tmp = cmbRecord.getSelectedItem().toString().split("-");
            int empID = Integer.parseInt(tmp[0]);
            String employeeDetails = Master.getEmployeeDetails(empID);
            String[] employee = employeeDetails.split("#");
            cmbDepartment.setSelectedItem(employee[0]);
            txtSurname.setText(employee[1]);
            txtFirstName.setText(employee[2]);
            txtCurrentPos.setText(employee[3]);
            txtDOB.setText(employee[4]);
            txtExperience.setText(employee[5]);
            txtQualification.setText(employee[6]);
            txtEmail.setText(employee[7]);
            txtPhone.setText(employee[8]);
            txtEmployment.setText(employee[9]);
        }

I am met with the following error

error: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.0 Java execution: FORMAT

I have no idea what is causing this error since the SQL works perfectly fine when executed in Access and the format is needed otherwise it outputs the records information including the time which is not set or used.

Answer 1

I'd remove the FORMAT from your SQL string and format the date in Java. I do not know what the format of the date is being returned as, but this should allow you to parse it and then input the fields in the Calendar.set() method as shown here:

public static void main(String[] args) {
    final Calendar calendar = Calendar.getInstance();
    calendar.set(2015, Calendar.AUGUST, 21, 9, 27);
    final Date date = calendar.getTime();
    final String formattedDate = new SimpleDateFormat("yyyy/MM/dd").format(date);
    System.out.println("formattedDate = " + formattedDate);
}