Prevent jdbc from padding strings on sql insert and update

Question

We are using the jdbc-odbc bridge to connect to an MS SQL database. When perform inserts or updates, strings are put into the database padded to the length of the database field. Is there any way to turn off this behavior (strings should go into the table without padding)?

For reference, we are able to insert field values that don't contain the padding using the SQL management tools and query analyzer, so I'm pretty sure this is occuring at the jdbc or odbc layer of things.

EDIT: The fields in the database are listed as nvarchar(X), where X = 50, 255, whatever

EDIT 2: The call to do the insert is using a prepared statement, just like:

PreparedStatement stmt = new con.prepareStatement("INSERT INTO....");
stmt.setString(1, "somevalue");

Answer 1

How are you setting the String? Are you doing?:

PreparedStatement stmt = new con.prepareStatement("INSERT INTO....");
stmt.setString(1, "somevalue");

If so, try this:

stmt.setObject(1, "somevalue", Types.VARCHAR);

Again, this is just guessing without seeing how you are inserting.

Answer 2

Are you using CHAR fields in the database or VARCHAR?

CHAR pads the size of the field. VARCHAR does not.

I don't think JDBC would be causing this.

Answer 3

If you are using the bundled Sun JDBC-ODBC Bridge driver, you may want to consider migrating to a proper MS SQL JDBC driver. Sun does not recommend that the bridge driver be used in a production environment.

The JDBC-ODBC Bridge driver is recommended only for experimental use or when no other alternative is available.

Moving to a more targeted driver may fix your problem all together, or at least it will provide a production ready solution when you do fix the bug.

Answer 4

If you can make your insert to work with regular SQL tools ( like ... I don't know Toad for MS SQL Sever or something ) then changing the driver should do.

Use Microsoft SQL Server JDBC type IV driver.

Give this link a try

http://www.microsoft.com/downloads/details.aspx?familyid=F914793A-6FB4-475F-9537-B8FCB776BEFD&displaylang=en

Unfortunately these kinds of download comes with a lot of garbage. There's an install tool and another hundreds of file. Just look for something like:

intalldir\lib\someSingle.jar

Copy to somewhere else and uninstall/delete the rest.

I did this a couple of months ago, unfortunately I don't remeber exactly where it was.

EDIT

Ok, I got it.

Click on the download and at the end of the page click on "I agree and want to download the UNIX version"

This is a regular compressed file ( use win rar or other ) and there look for that sigle jar.

That should work.