wood burning stoves 2.0*
The moose likes JDBC and the fly likes Problem with escape chars... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with escape chars..." Watch "Problem with escape chars..." New topic
Author

Problem with escape chars...

raghavender rao
Ranch Hand

Joined: Jul 20, 2001
Posts: 45
hi ther,
How can I insert special characters (for example: " , ', % )
when I execute an INSERT statement?
i am working onto customise my database, the solution should be through jdbc coz
in oracle u cant insert '
and in sql server "
is ther anyway to insert these chars without writing separate code for oracle,sql database etc,...


Rao<BR>Sun Certified Programmer for the Java��� 2 Platform
Dakshinamurthy Ramachandran
Greenhorn

Joined: Mar 09, 2002
Posts: 16
Hi,
Why dont you use PrepareStatement for insertion ... Think it does the escaping of special characters automatically.
Mahesh Mamani
Ranch Hand

Joined: Jun 25, 2001
Posts: 110
Hi,
From a reference source from the web, got the following for Ur problem...Read on...

In JDBC, strings containing SQL commands are just normal strings - the SQL is not parsed or interpreted by the Java compiler. So there is no special mechanism for dealing with special characters; if you need to use a quote (") within a Java string, you must escape it.
The Java programming language supports all the standard C escapes, such as \n for newline, \t for tab, etc. In this case, you would use \" to represent a quote within a string literal:
String stringWithQuote =
"\"No,\" he replied, \"I did not like that salted licorice.\"";

This only takes care of one part of the problem: letting us control the exact string that is passed on to the database. If you want tell the database to interpret characters like a single quote (') literally (and not as string delimiters, for instance), you need to use a different method. JDBC allows you to specify a separate, SQL escape character that causes the character following to be interpreted literally, rather than as a special character.
An example of this is if you want to issue the following SQL command:

SELECT * FROM BIRDS
WHERE SPECIES='Williamson's Sapsucker'
In this case, the apostrophe in "Williamson's" is going to cause a problem for the database because SQL will interpret it as a string delimiter. It is not good enough to use the C-style escape \', because that substitution would be made by the Java compiler before the string is sent to the database.
Different flavors of SQL provide different methods to deal with this situation. JDBC abstracts these methods and provides a solution that works for all databases. With JDBC you could write the SQL as follows:
Statement statement = // obtain reference to a Statement
statement.executeQuery(
"SELECT * FROM BIRDS WHERE SPECIES='Williamson/'s Sapsucker' {escape '/'}");
The clause in curly braces, namely {escape '/'}, is special syntax used to inform JDBC drivers what character the programmer has chosen as an escape character. The forward slash used as the SQL escape has no special meaning to the Java compiler; this escape sequence is interpreted by the JDBC driver and translated into database-specific SQL before the SQL command is issued to the database.
Escape characters are also important when using the SQL LIKE clause. This usage is explicitly addressed in section 11.5 of the JDBC specification:

The characters "%" and "_" have special meaning in SQL LIKE clauses (to match zero or more characters, or exactly one character, respectively). In order to interpret them literally, they can be preceded with a special escape character in strings, e.g. "\". In order to specify the escape character used to quote these characters, include the following syntax on the end of the query:
{escape 'escape-character'}
For example, the query
SELECT NAME FROM IDENTIFIERS WHERE ID LIKE '\_%' {escape '\'}
finds identifier names that begin with an underbar.


Mahesh
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Problem with escape chars...
 
Similar Threads
Conversion into SQL Result Clob from a String
run without errors, but data not inserted in database
efficient way to perform CRUD operation in Oracle TopLink
Mail Server
Size for Prepared Statement setString()