CODEWITHSUNDEEP
javasql-server
jmrodrigg
jmrodrigg

Reputation: 600

SQLServerExeption trying to insert NULL value in a column

I'm having trouble to insert a NULL value in a float-type column that allows null values. I'm getting this exception:

SQLServerException: Operand type clash: varbinary is incompatible with float
    at (...)

I'm using PreparedStatement to query the database. The piece of code I use to set this specific value is:

Float floatValue;

(...)

if((this.floatValue != null)&&(this.floatValue != 0)) ps.setFloat(col_pos,this.floatValue);
else ps.setNull(col_pos,java.sql.Types.NULL);

(...) int result = ps.executeUpdate(); //Here is when i get the error, when it tries to insert/update the table).

What am I doing wrong? This piece of code works (in other parts of my code) for nvarchar or int columns, it's the first time I get this error.

Thank you in advance.

Upvotes: 2

Views: 6940

Answers (2)

jmrodrigg
jmrodrigg

Reputation: 600

Thanks to gbn's answer, I realized what is really happening when I use ps.setNull(col_name, java.sql.Types.NULL) in my code. I was wrong, I needed to specify the type of the column I was inserting the NULL-value to. So the solution was:

ps.setNull(col_name, java.sql.Types.FLOAT);

When I needed to set a null-value in a column I first used ps.setInt(col_name,null), but it returns an exception as the setter methods use standard basic types (int,float,...) rather than objects. I searched for a solution and I (wrongly) learned that I needed to use ps.setNull(col_name, java.sql.Types.NULL). The

Why java.sql.Types.NULL? I didn't know, I thought it was the way to add a null in a column.

Then gbn's said:

varbinary will cast implicitly to nvarchar and int but not float (...) I suspect you are not storing a true database null in the nvarchar and int columns

So I considered that possibility that, internally, my connection was creating a varbinary NULL and the exception appeared when trying to convert it to a Float type, not supported according to the graph referred by gbn. Then I tried to specify a java.sql.Types.FLOAT type and it worked.

I hope this explanation would help other users with similar issues. Thank you gbn for providing me the hints.

Upvotes: 3

gbn
gbn

Reputation: 432301

varbinary will cast implicitly to nvarchar and int but not float.

See the graph here: http://msdn.microsoft.com/en-gb/library/ms187928.aspx

I suspect you are not storing a true database null in the nvarchar and int columns

Upvotes: 4

Related Questions