Insert query with apostrophe

[Sneha Neil]

Hi,

I want to insert some values into a table. These values might contain apostrophe which will throw an error while inserting.

I cannot do the following:
insert into xx values('how''s u?')
where the value is hardcoded and so the apostrophe can be escaped.

I have to do something like
insert into xx values(someval)
where someval might contain an apostrophe in any position.

Can someone please help me in finding a solution?
Thanks!

[Mark Spritzler]

In your code you will need to create a method that takes a string, checks if it has an apostrophe, then get the index and place an escape character in front of it so that you won't get the error.

This is not just a Java issue, I had to do the same type of thing in a VB 6.0 application.

Mark

[Julian Kennedy]

Hi Sneha Neil,

Are you using Java/JDBC? If you are then just use a PreparedStatement rather than a plain old Statement and all your worries go away!

Maybe I should give an example. Instead of the following (which will break):

String someValue = "Oh no, it's an apostrophe!";
Statement stmt = con.[B]create[/B]Statement("insert into xx values ('" + someValue + "')");
int rowsAffected = stmt.executeUpdate();

Use the following:

String someValue = "Oh no, it's an apostrophe!";
[B]PreparedStatement[/B] pstmt = con.[B]prepare[/B]Statement("insert into xx values [B](?)[/B]");
[B]pstmt.setString(1, someValue);[/B]
int rowsAffected = pstmt.executeUpdate();

The JDBC driver knows that you're supplying a String parameter and takes care of any escaping required for you.

If you're not using Java, what technology are you using with Oracle?

Jules

[Sneha Neil]

Hi,
Sorry for the late response.The different time zones are to blame!

I'm not using Java technology here. I'm using a workflow tool called PEGA which calls Oracle. Since database interactions are not very efficient through PEGA,we directly called the insert query without handling it through any method as suggested by Mark.

Since we want the inserts to be done as a background process without any user interaction, we are facing this problem.We will now have to handle it through scripting in the front end. This defeats our purpose but there doesn't seem to be any workaround.

Thanks a lot for all your help!

[Avi Abrami]

Sneha,
In SQL, string literals are delimited by apostrophes (otherwise known as single quotes). In order to use an apostrophe as part of your string literal, you simply write it twice, for example:

insert into TABLE (STRING_COLUMN) values ('How''s that?')

Good Luck,
Avi.

[Jeanne Boyarsky]

Avi's example jogged my memory a bit. There is a problem in Oracle (or at least in sql plus) where the '' is only recognized if it is the last character in a string. I had to concatenate the string up to and including the quote with a string containing the rest.