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

Have you tried printing the sql and running on the SQL prompt. If there is missing quotes, it will be easy to find. — ejb_guy
– ejb_guy, Commented May 23, 2012 at 5:18
Whats the value in currentFile. This might be containing some slash causing problem — ejb_guy
– ejb_guy, Commented May 23, 2012 at 5:20
i am sorry, what do you mean by printing the sql?..I already check the "result" value, it show the number, means the sql is work properly but i do not know why I keep getting this error.ORA-00933: SQL command not properly ended. — Siti Haslina Mohd Zulkafli
– Siti Haslina Mohd Zulkafli, Commented May 23, 2012 at 5:23
If any of the variables from which you build the update query contains a single quote it will cause this error. More serious is the fact that this technique of building a statement directly from user input is vulnerable to "SQL Injection" (Google it) and is a huge security hole that should never appear in production code. — Jim Garrison
– Jim Garrison, Commented May 23, 2012 at 5:25
@SitiHaslinaMohdZulkafli - Hmm. Not just error message but statcktrace. check my answer, if it helps. — Ravinder Reddy
– Ravinder Reddy, Commented May 24, 2012 at 0:50

Thomas Mueller · Accepted Answer · 2012-05-23 10:19:16Z

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

answered May 23, 2012 at 10:19

Thomas Mueller

50.4k16 gold badges123 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siti Haslina Mohd Zulkafli Over a year ago

Thank you..your suggestion is really useful. I am really appreciate it..Thanks..:)

Ravinder Reddy · Accepted Answer · 2012-05-24 00:49:08Z

1

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

answered May 24, 2012 at 0:49

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

3 Comments

Siti Haslina Mohd Zulkafli Over a year ago

Thank you Ravinder, when I try this its working but need a bit modification. Anyway, thanks for this idea. Really appreciate it.Yeyy!!..:)

Ravinder Reddy Over a year ago

@SitiHaslinaMohdZulkafli - Did you trace the error stack? If yes, post it in your query.

Siti Haslina Mohd Zulkafli Over a year ago

Ok, I will update it. I cannnot "upvote" because my reputation only 11. I need to increase my reputation to 15 than I can do that. I am sorry..:)

ono2012 · Accepted Answer · 2013-10-22 15:23:44Z

0

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.

answered Oct 22, 2013 at 15:23

ono2012

5,3072 gold badges35 silver badges43 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related