topic Re: String or binary data would be truncated in Talend Studio

String or binary data would be truncated

Jesperrekuh — Sun, 10 Jun 2018 12:23:54 GMT

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.

Re: String or binary data would be truncated

vapukov — Wed, 13 Jun 2018 22:49:43 GMT

look like a bug, what the version of Talend?

Re: String or binary data would be truncated

vapukov — Wed, 13 Jun 2018 22:52:07 GMT

-----

Re: String or binary data would be truncated

Jesperrekuh — Wed, 13 Jun 2018 23:35:33 GMT

Version:

TOS_BD-20180116_1512-V6.5.1

Re: String or binary data would be truncated

vapukov — Thu, 14 Jun 2018 00:04:03 GMT

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

Re: String or binary data would be truncated

Jesperrekuh — Thu, 14 Jun 2018 00:55:58 GMT

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.

Re: String or binary data would be truncated

vapukov — Thu, 14 Jun 2018 01:15:45 GMT

You can create table manually with length

then must work

Re: String or binary data would be truncated

Jesperrekuh — Thu, 14 Jun 2018 12:23:42 GMT

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!