Java Answers Forum - SQL Question

Articles |
News |
Weblogs |
Books |
Forums

Artima Forums | Articles | Weblogs | Java Answers | News

Sponsored Link •

Java Answers Forum
SQL Question

8 replies on 1 page. Most recent reply: Aug 6, 2003 11:31 AM by Matt Gerrans

Welcome Guest
Sign In

Back to Topic List

Reply to this Topic

Search Forum

Threaded View


Previous Topic		Next Topic

Flat View: This topic has 8 replies on 1 page

DMorgan

Posts: 3
Nickname: mogain
Registered: Aug, 2003

SQL Question

Posted: Aug 5, 2003 7:30 AM

I am trying to write a sql insert statement into a mysql database, it works fine most times, however I get an error when I use a string containing an " ' ". For example if I tried to insert the text O'Neil I get a sql statement error.

Boris Pruessmann

Posts: 2
Nickname: docbobo
Registered: Jul, 2003

Re: SQL Question

Posted: Aug 5, 2003 7:52 AM

Because you're creating the complete SQL statement on your own, you need to escape the quotes yourself. I think doubling the quote will work for most databases.

DMorgan

Posts: 3
Nickname: mogain
Registered: Aug, 2003

Re: SQL Question

Posted: Aug 5, 2003 8:20 AM

Can you supply an example

Slager .

Posts: 16
Nickname: slager
Registered: May, 2003

Re: SQL Question

Posted: Aug 5, 2003 8:24 AM

Although you can escape the parameters yourself like Boris says, for example O'Neill would become O''Neil, you better take a look at java.sql.PreparedStatement and let the driver handle the parameters for you. You can then just call statement.setString(1, "O'Neill");

DMorgan

Posts: 3
Nickname: mogain
Registered: Aug, 2003

Re: SQL Question

Posted: Aug 5, 2003 8:36 AM

Slager can you post a code example?

Charles Bell

Posts: 519
Nickname: charles
Registered: Feb, 2002

Re: SQL Question

Posted: Aug 5, 2003 10:15 AM

Suggest escaping the single quote with a slant
\'

Usually tha character ' is used to delimit character strings
such as 'this is an example'

but when you want to include an apostrophe in it may have to be written

'It\'s good to be 50.'

Frank Sommers

Posts: 2642
Nickname: fsommers
Registered: Jan, 2002

Re: SQL Question

Posted: Aug 5, 2003 4:19 PM

You'd want the database driver to worry about this. If you use java.sql.PreparedStatement class, such character-escapes should work fine. E.g.,:

Connection c = [Obtain your connection here]
PreparedStatement st = c.prepareStatement("insert into a values(?)");
st.setString(1, "O'Neil");
st.executeUpdate();
....

Vincent O'Sullivan

Posts: 724
Nickname: vincent
Registered: Nov, 2002

Re: SQL Question

Posted: Aug 6, 2003 1:18 AM

Well done for putting some effort into solving this problem. The number of data entry screens that still can't accept my surname still astonishes me.

My work email address also is of the form vincent.j.o'sullivan@... 99% of the time it works fine. I only know of one email recipient who has a problem with it. Emails to him vanish. Apparently, he's raised the problem with Lotus (Notes) and they say it can't be fixed because of "the way the Internet works" which is strange because guess what software we use for emails at my place of work, where it all works fine

The worst offenders are electronic address books seem to be particularly reluctant to accept it as a valid email address. On the other hand; it receives no junk mail - perhaps for the same reason.

Vince O'Sullivan (not Sullivan, Osullivan, OSullivan, O' 'Sullivan, O"Sullivan, O/Sullivan, O`Sullivan... all of which turn up in the postal mail from time to time).

Matt Gerrans

Posts: 1153
Nickname: matt
Registered: Feb, 2002

Re: SQL Question

Posted: Aug 6, 2003 11:31 AM

> On the other hand; it receives no junk mail -
> perhaps for the same reason.

Wow, I think I'll change my name to Matt O'Gerrans!

(Actually, I just got SpamBayes (http://sourceforge.net/projects/spambayes/) set up and it works like a charm, so it looks I won't need the name change)

It is sort of amazing to me, that even today, programs have all kinds of problems with simple things like special characters (as in this case), extended characters (letters with diacritics) and unicode. It still takes a ton of attention to detail and testing to make sure a program works well with all of these. Modern languages have improved things a lot with built-in unicode strings, but we're not there yet (and working with unicode or the too-many variants of it in C/C++ is ridiculously messy).

Flat View: This topic has 8 replies on 1 page

Previous Topic

Next Topic