• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Insert query with apostrophe

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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):

Use the following:

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
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:

Good Luck,
Avi.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
He does not suffer fools gladly. But this tiny ad does:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic