2

I am trying to insert values into Oracle 10g database table. I want to insert value for column FirstName and value is having two single quotes like this: O'Rielly's Edition.

I am using the prepared statement of JDBC to pass the value for parameter FirstName. It is working fine with one single quote(O'Rielly).

But when I use two single quotes it's giving SQL error as ORA-00907: missing right parenthesis. Any suggestions on how to handle two single quotes in PreparedStatement? My query is as below: 

StringBuffer strUpdateUserQuery = new StringBuffer("UPDATE USERS set FNAME = ? , LNAME = ?, USER_TIMEZONE = ?, CREATED_DATE = CURRENT_DATE, ACTIVE_STATUS = ?, APPROVAL_STATUS = ?,USER_GROUP_ID = ? where USER_ID = ?");

pstmt = conn.prepareStatement(strUpdateUserQuery.toString());
pstmt.setString(1, userVO.getFirstName());
pstmt.setString(2, userVO.getLastName());
pstmt.setString(3, userVO.getStrTimeZone());
pstmt.setString(4, userVO.getStatus());
pstmt.setString(5, userVO.getStrAppStatus());
pstmt.setInt(6, Integer.parseInt(userVO.getUserGroupId()));
pstmt.setString(7, userVO.getUserId());

int iCount = pstmt.executeUpdate(strUpdateUserQuery.toString());

And the error I am getting is as below:

ORA-00907: missing right parenthesis\n

With above code, I am getting the issue.

gprathour
  • 14,813
  • 5
  • 66
  • 90
Babanna Duggani
  • 737
  • 2
  • 12
  • 34
  • Show an example of what you are trying to insert using preparedStatement. – Jacob Jun 19 '17 at 10:48
  • 3
    You don't need to escape single quotes in bind values. Please edit your question to show the actual prepared statement you are using. (And the value, I suppose, exactly as it's bound). What is the full error you get back? – Alex Poole Jun 19 '17 at 10:49
  • 3
    One of the important advantages of a PreparedStatement is the fact that you don't need to worry about escaping. It sounds as if you are not using the PreparedStatement correctly. **[edit]** your question and add the code you are using. Do **not** post code in comments. –  Jun 19 '17 at 10:54
  • And what is the string you are passing to FirstName? – Jacob Jun 19 '17 at 11:12
  • I am trying to insert O''keffe as FName value. – Babanna Duggani Jun 19 '17 at 11:15
  • @BabannaDuggani What exactly is `CREATED_DATE = CURRENT_DATE`, you mean `SYSDATE`? – Jacob Jun 19 '17 at 11:18
  • Yes, it's similar to SYSDATE but takes current date value from the User's session. – Babanna Duggani Jun 19 '17 at 11:19
  • 1
    @user75ponic - https://stackoverflow.com/a/17925834/266304 *8-) – Alex Poole Jun 19 '17 at 11:20
  • @BabannaDuggani Try printing the output of each of the parameter, perhaps the any one would have a parenthesis which is causing an `ORA-00907: missing right parenthesis` – Jacob Jun 19 '17 at 11:25
  • What you've shown should get ORA-01008: not all variables bound; don't pass the query string in again (as gprathour said). You'll get the same error(s) with one or two single quotes though. – Alex Poole Jun 19 '17 at 11:27
  • @BabannaDuggani Any why would you need `StringBuffer`? Rather a `String` is enough for this in my opinion. – Jacob Jun 19 '17 at 11:29
  • 1
    I don't see a reason for this error. Are you really sure it is coming from here? – Henry Jun 19 '17 at 11:33

1 Answers1

2

Try changing

int iCount = pstmt.executeUpdate(strUpdateUserQuery.toString());

to

int iCount = pstmt.executeUpdate();
gprathour
  • 14,813
  • 5
  • 66
  • 90