DB2 not saving stylized apostrophe (’)

Question

I am trying to insert a stylized apostrophe (’) into a table in DB2 LUW 9.7. Every method I have tried has resulted in it being a square () when selected back.

The methods I have tried to insert it:

• Modifying and committing a row in Control Center (GUI)
• Updating a row via SQL command in Command Editor
• Loading from a file (both UTF-8 encoded and ANSI encoded) through IBM Data Studio 2.2

Is there a way to get this character into the database? If not, is there a list of characters that simply cannot be used?

Answer 1

What is the encoding of your database/tablespace? I suspect it needs to be set to UTF-8. You may need to create a new tablespace or recreate the database with the encoding explicitly set to UTF-8.

To determine the encoding, run the following command and look for the "Code set" information:

db2 get db cfg for <database name>

Answer 2

from Inserting an Apostrophe in DB2 table

 insert into userid.empy values(1234,'RAM'''); 

You have put 2 Single Quotations, for a single quote to appear in the table.

see also: http://www.ibmmainframes.com/viewtopic.php?t=36901&highlight=apostrophe and http://www.ibmmainframes.com/viewtopic.php?t=29213&highlight=apostrophe

Answer 3

if you are using the API, a good way is to prepare the statement and bind the quote-containing text to a parameter.

Remind that allowing someone to type in a quote and insert that unchanged (like sprintf(sqlstr, "INSERT INTO mytable VALUES ('%s')", inputstr);) may lead to an SQL-Injection.

my input:

test');drop database;insert into mytable values ('test

may lead to an empty database