CODEWITHSUNDEEP
javamysqlsqljdbcunicode
cagryInside
cagryInside

Reputation: 800

Creating SQL queries with UNICODE or ASCII character codes

I would like to create a SQL query containing ASCII or UNICODE character codes in it. For example, ASCII character code for single quote (') is 39 and unicode code is U+0027. In Java, I would like to write a query by replacing the single codes with their character codes:

ASCII:

connection.createStatement().executeQuery("select * from users where name =39test39")

Unicode:

connection.createStatement().executeQuery("select * from users where name =U+0027testU+0027")

All of these queries should be equivalent to "select * from users where name ='test'"

When I run the codes above, DBMS (I tried with Mysql and SQLite) does not recognize the ascii and unicode codes as a single quote.

In summary, I know parametrized queries are the ideal. But, here in this case what I wanted to do is, when the sql code is parsed by the DBMS, then the DBMS should recognize the unicode character. For example, if I use \u0027, the JVM would recognize this as a single quote, but I want JVM to not recognize and DMBS to recognize the character encoding.

Is there any way use char codes instead of the character itself?

Upvotes: 0

Views: 1383

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123409

No, you don't want to do that. You should be doing

PreparedStatement ps = conn.prepareStatement("select * from users where name = ?");
ps.setString(1, "test");
ResultSet rs = ps.executeQuery();

Remember that all strings in Java are Unicode strings, so what you are proposing is to start sending string values as byte streams to the JDBC driver, which would be messy and error-prone (if it is even possible).

Upvotes: 4

Anuja Barve
Anuja Barve

Reputation: 320

You query should look like this :

"select * from users where name =" + Character.toString((char)39) + "test" + Character.toString((char)39) + "\""

Upvotes: 0

John Kalupa
John Kalupa

Reputation: 1

When you put the ascii/unicode numbers within double quotes they aren't resolved to characters instead try something like:

"select * from users where name =" + Character.toString(Character.toChar(yourIntHere)) + ...

And then that should build the string you are looking for

Upvotes: 0

Related Questions