Sayantan Chandra Sayantan Chandra - 6 months ago 34
MySQL Question

ORA-01747: invalid user.table.column, table.column, or column specification in java

I am getting this error-
ORA-01747: invalid user.table.column, table.column, or column specification

And this is my block of code-

private void updateAttendance(){
MyQuery mq=new MyQuery();
Connection con=mq.getConnection();
Statement st;
ResultSet rs;
try{
st=con.createStatement();
rs=st.executeQuery("Select STU_ID FROM STUDENT WHERE NAME='"+cmbName.getSelectedItem()+"'");
if(rs.next()){
//System.out.println("getting student name");
int id=rs.getInt("STU_ID");
System.out.println(id);
String sql="UPDATE STUDENT SET CURRENT_DATE='"+lblTime.getText()+"',SUBJECT='"+cmbSub.getSelectedItem()+"',ATTENDANCE=";

if(rdbtnPresent.isSelected())
sql+= "'"+Atdnc[0]+"',";
else
sql+= "'"+Atdnc[1]+"'";

sql+="WHERE STU_ID='"+id+"'";
st.executeUpdate(sql);
//cmbName.removeAllItems();
}

}catch(SQLException ex){
Logger.getLogger(Student.class.getName()).log(Level.SEVERE, null, ex);
}

}


when I am executing this query(UPDATE STUDENT SET CURRENT_DATE='27TH MAY',SUBJECT='CRYPTOGRAPHY',ATTENDANCE='ABSENT' WHERE STU_ID='40';) in oracle 11g it works fine...but the same sql in eclipse returns error.

Answer

Your problem should be here:

if (rdbtnPresent.isSelected())
    sql+= "'"+Atdnc[0]+"',"; // --> this should be "'" as there is no more field
else
    sql+= "'"+Atdnc[1]+"'";
sql+="WHERE STU_ID='"+id+"'"; // --> this should start with a space sql+=" WHERE..

So you have 2 issues:

  1. If rdbtnPresent.isSelected() return true, there is a comma that has nothing to do here
  2. There is no space before your WHERE

NB: Consider using PrepareStatement instead to avoid this kind of issue, to avoid having to escape your values and to avoid SQL injection attacks.

Comments