MySQL Bugs: #15213: Unable to insert "whitespace-only" values into a column through Connector/J

Bug #15213	Unable to insert "whitespace-only" values into a column through Connector/J
Submitted:	24 Nov 2005 3:26	Modified:	29 Nov 2005 9:33
Reporter:	Edwin Lee	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	mysql-connector-java-3.1.11	OS:	Windows (MS Windows XP Professional)
Assigned to:	Vasily Kishkin	CPU Architecture:	Any

Description:
Attempts to insert "whitespace-only" values (" ") into table columns using Connector/J fails.

After insertion, value retrieved is only the empty string ("").

Inspection of the value using MySQL Query Browser version 1.1.13 also shows only the empty string ("").

However, insertion of a whitespace character using the same MySQL Query Browser works fine. 

Database Version: 4.1.13-nt
Driver Version: mysql-connector-java-3.1.11

How to repeat:
Create the test table:

CREATE TABLE testtable
(
	TestColumn	VARCHAR(255),
	PRIMARY KEY (TestColumn)
);

Compile and run the following:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestDatabase
{
	public static void main(String[] args) throws Exception
	{
		Class.forName("com.mysql.jdbc.Driver");
		Connection c = DriverManager.getConnection(
				"jdbc:mysql://127.0.0.1:3306/test",
				"user",
				"password");
		
		System.out.println(c.getMetaData().getDatabaseProductName());
		System.out.println(c.getMetaData().getDatabaseProductVersion());
		System.out.println(c.getMetaData().getDriverName());
		System.out.println(c.getMetaData().getDriverVersion());
		
		String s = "DELETE FROM TestTable";
		Statement st = c.createStatement();
		st.execute(s);
		st.close();
		
		String value = " ";
		System.out.println("In: #" + value + "#");
		
		s = "INSERT INTO TestTable (TestColumn) VALUES (?)";
		PreparedStatement ps = c.prepareStatement(s);
		ps.setString(1, value);
		ps.execute();
		ps.close();
		
		s = "SELECT * FROM TestTable";
		st = c.createStatement();
		ResultSet rs = st.executeQuery(s);
		rs.next();
		
		System.out.println("Out: #" + rs.getString(1) + "#");
		
		rs.close();
		st.close();
		c.close();
	}
}

The following output is observed:

MySQL
4.1.13-nt
MySQL-AB JDBC Driver
mysql-connector-java-3.1.11 ( $Date: 2005-09-21 18:20:03 +0000 (Wed, 21 Sep 2005) $, $Revision: 4287 $ )
In: # #
Out: ##

Also tried using a Statement instead of a PreparedStatement to insert the row:
"INSERT INTO TestTable (TestColumn) VALUES (' ')"
and the same result was observed.

According to mysql documentation for 4.1 : VARCHAR values are not padded when they are stored. Trailing spaces in MySQL version up to and including 4.1 are removed from values when stored in a VARCHAR column; this also means that the spaces are absent from retrieved values.
I would like to know how you inserted whitespace in Query Browser ?

This is what i did to insert the whitespace character using MySQL Query Browser:

1. Double-double-click on "testtable" in the Schemata view to bring up the table contents in the Resultset view.
 
2. Click on "Edit" to enable edit.

3. Double-click on the first row of the TestColumn column of the testtable table in the Resultset view to bring up the caret to start editing the cell.

4. Press "spacebar" and "enter" to insert a whitespace character.

5. Click on "Apply Changes" to commit the change.

6. Click on "Edit" to disable editing.

Now,

1. Right-click on the cell that was previously edited, and click on "Copy Row Values" from the popup menu to copy the row value onto clipboard

2. Open notepad and press "Ctrl-v" to "paste" the value from clipboard to notepad.

3. The whitespace character can be seen there.

In any case, what i am more interested in, is, how can i programmatically insert a whitespace-character into a MySQL table from a Java application?

I was not able to insert whitespace in MySQL Query Browser. Only empty string. As fa as I know you are not avaible to insert single whitespace into a table.