CODEWITHSUNDEEP
javasqloracle-databaseora-00933
Siti Haslina Mohd Zulkafli
Siti Haslina Mohd Zulkafli

Reputation: 27

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

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) {}
}
}

Upvotes: 1

Views: 4790

Answers (3)

ono2012
ono2012

Reputation: 5257

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.

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

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  
}  
...

Upvotes: 1

Thomas Mueller
Thomas Mueller

Reputation: 50097

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);

Upvotes: 1

Related Questions