String or binary data would be truncated

[Jesperrekuh]

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.

[vapukov]

look like a bug, what the version of Talend?

[vapukov]

-----

[Jesperrekuh]

Version:

TOS_BD-20180116_1512-V6.5.1

[vapukov]

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

[Jesperrekuh]

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.

[vapukov]

You can create table manually with length 

then must work

[Jesperrekuh]

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!