String or binary data would be truncated

Ten Stars

String or binary data would be truncated

Something to keep in mind... and love to hear your thoughts on this one.

 

There're two types in MSSQL to store your byte[] array, 

- BINARY, fixed length

- VARBINARY, flexible with a max of 8000. (if you want bigger -> BLOB)

MSSQL Documentation

 

As I want to store my generated SHA1 key in a Binary format   

- The bytes array size, hashSHA1.length = 20.

- Define column size HUB_KEY with a length/size of 20, type BINARY.

 

But, when inserting the records, an error occured:

java.sql.BatchUpdateException: String or binary data would be truncated.

I took a closer look at the create table statement in the java-code, see attached picture

There's no size added to the BINARY statement. I think this is a bug...

 

Changed to VARBINARY and (20) is added to the CREATE statement.

Solved.

Thirteen Stars

Re: String or binary data would be truncated

look like a bug, what the version of Talend?

-----------
Thirteen Stars

Re: String or binary data would be truncated

-----

-----------
Ten Stars

Re: String or binary data would be truncated

Version:

TOS_BD-20180116_1512-V6.5.1

Thirteen Stars

Re: String or binary data would be truncated

it really not important - for any from 6.4 - 7.01 it create binary without length not depending from schema (not only for MS SQL)

if You on subscription version - open issue (still would be very long)

in other case - just not use create if not exists and create tables in advance

-----------
Ten Stars

Re: String or binary data would be truncated

The problem was that I got an error, that I need to specify length.
I first had no length specified.Which resulted in this... trying to on the Binary field.
Thirteen Stars

Re: String or binary data would be truncated

You can create table manually with length 

then must work

-----------
Ten Stars

Re: String or binary data would be truncated

Yes, I know (I did, but for other reasons).
Its just convenient of not having to maintain scripts.
Varbinary(20) works fine, And while reading some technical docs on performance and how search work, there's technically not much difference beside the variable length.

Thanks for sharing your thoughts!