ora-00933:SQL command not properly ended for update sql statement

Question

I am getting the sql command not properly ended when hiting this line below.

stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);    
String updateQ = "update ANI_999 set First_Name = '"+d.getName()+"', HouseNo = '"+d.getAddr1()+"', Indicator_Sourcefile_iCARE3 = Indicator_Sourcefile_iCARE2, Indicator_Sourcefile_iCARE2 = Indicator_Sourcefile_iCARE1, Indicator_Sourcefile_iCARE1='"+currentFile+"' where CALLER_ID = '"+msisdn+"' ";

int result = stmt.executeUpdate(updateQ);
conn.commit();
conn.close();`

I keep getting ORA-00933: SQL command not properly ended.

This is what updateQ statement looks like:

update ANI_999 set First_Name = 'ZAHARAH BINTI ABDUL RAHMAN', HouseNo = 'No. JKR6357,', Indicator_Sourcefile_iCARE3 = Indicator_Sourcefile_iCARE2, Indicator_Sourcefile_iCARE2 = Indicator_Sourcefile_iCARE1, Indicator_Sourcefile_iCARE1='ICAREP_ANI_SVCPROF_20120402_002.DAT' where CALLER_ID = '058011726' 

here is the full function:- Kindly please refer this symbol "<<"

public void updateRecord(icData d, String msisdn) {
   Connection conn = null;
   Statement stmt = null;
   int recCtr = 0;

try {
   conn = ds.getConnection();

       stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); 
       String updateQ = "update ANI_999 set First_Name = '"+d.getName()+"', HouseNo = '"+d.getAddr1()+"', Indicator_Sourcefile_iCARE3 = Indicator_Sourcefile_iCARE2, Indicator_Sourcefile_iCARE2 = Indicator_Sourcefile_iCARE1, Indicator_Sourcefile_iCARE1='"+currentFile+"' where CALLER_ID = '"+msisdn+"' ";


   int result = stmt.executeUpdate(updateQ);
   conn.commit();
   conn.close();
}
catch(SQLException ex) {

    logger.error("iCARE:Error : " + ex.getMessage()); <<this line show me that error>>

}
finally {
    try {if (stmt != null) stmt.close();} catch (SQLException e) {}
        try {if (conn != null) conn.close();} catch (SQLException e) {}
}
}

Answer 1

You should use a PreparedStatement:

String updateQ = "update ANI_999 set First_Name = ?, HouseNo = ?, " +
       "Indicator_Sourcefile_iCARE3 = Indicator_Sourcefile_iCARE2, " +
       "Indicator_Sourcefile_iCARE2 = Indicator_Sourcefile_iCARE1, " +
       "Indicator_Sourcefile_iCARE1=? where CALLER_ID = ? ";
PreparedStatement prep =  conn.prepareStatement(updateQ, 
    ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); 
prep.setString(1, ...);
prep.setString(2, ...);
prep.setString(3, ...);
int result = prep.executeUpdate(updateQ);

Answer 2

ERROR: ORA-00933: SQL command not properly ended.
CAUSE: You tried to execute an SQL statement with an inappropriate clause.

Instead of just catching the error message, you should have caught the stacktrace in the catch block. That gives you line number of your statement execution that has root cause.

Change

logger.error("iCARE:Error : " + ex.getMessage()); // <<this line show me that error>>

To

ex.printStackTrace(); // <<this line show me that error>>

Alternatively you can try the following code change and see if it works for you.

There is a chance that your input to update statement has some un-escaped characters and hence causing an error. Change your Statement object to PreparedStatement and see if it is resolved.

try {  
  ...
  String updateQ = "update ANI_999"  
    + " set First_Name = ?, HouseNo = ?,"  
    + " Indicator_Sourcefile_iCARE3 = Indicator_Sourcefile_iCARE2,"  
    + " Indicator_Sourcefile_iCARE2 = Indicator_Sourcefile_iCARE1,"  
    + " Indicator_Sourcefile_iCARE1=?"   
    + " where CALLER_ID = ?";  

  PreparedStatement pstmt = conn
   .createStatement( updateQ, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY );  
  pstmt.setString( 1, d.getName() );  
  pstmt.setString( 2, d.getAddr1() );  
  pstmt.setString( 3, currentFile );  
  pstmt.setString( 4, msisdn );  

  // print what the query actually holds. Not sure if all drivers support this.
  System.out.println( "DEBUG: query: " + pstmt.toString() );

  int result = pstmt.executeUpdate( updateQ );  
  System.out.println( "DEBUG: Update Result: " + result );
  ...  
} catch ( Exception ex ) {  
  // logger.error( ...  
  ex.printStackTrace(); // keep this until debugged  
}  
...

Answer 3

You can get a ORA-00933, if you are inserting your variable strings into a command string e.g.

string inputName = "Rose";
string sqlCmd = "SELECT * FROM mytable WHERE brand_name = '" + inputName +"'";

the above works fine - but if:

string inputName = "Rose's";

The resulting SQL is SELECT * FROM mytable WHERE brand_name = 'Rose's' which throws ORA-00933, so remember to escape your single quotes!

If you are using a LIKE clause then you might have to start thinking about escaping %'s. One of the reasons people suggest using prepared statements is so you don't have to worry about escaping this things.