"String or binary data would be truncated" error with DB Writer node

Hi,

I’m trying to use DB writer to write table into sql server database. Some of my columns are in string with Chinese character, DB writing by mapping string to varchar is ok, but the Chinese charater is displayed like ??? in varchar format. I tried to map string to nvarchar, the DB writer reported “String or binary data would be truncated” error.

In the past when I was using the legacy database writer node to write string containing Chinese character in nvarchar format, everything is good.

So the question is:

  1. Is there a way to set the length of the nvarchar format? I searched the error code, looks like it is caused by inserting data into a string column while the data size is more than the column size.
  2. Probably above is not the real cause? Any idea on how to solve this?

Appreciate your kind suggestion
thanks

Xiaoan

Hi,
I think you have to specify the length of your nvarchars by specifying the type like this: nvarchar(max), where max is the maximum length of your strings. If max is not given, it defaults to 1. Please have a look at this website for more info:


Kind regards
Alexander

Hi there @XXA,

there is difference between old and new database framework regarding character length
Think these topics might help you:

Br,
Ivan

1 Like

Hi Both,

Thanks for the suggestion. Indeed DB table creator can be used to modify size of nvarchars.

However the issue is still there and I tested with some simple string and discovered more of this issue:

  • with a single Chinese character, DB writing is ok even with the default nvarchar size, only need to change the mapping to “String to NVARCHAR”
  • if the cell contains two Chinese characters, DB Insert or DB writer failed, even when I set size of the nvarchar to (max)

Best,
Xiaoan

Hi there @XXA,

seems to me even with max argument length is still one. Have you tried some number instead of max?

Br,
Ivan

Yes, Max is just a placeholder! There must be an actual number.

1 Like

I tried 255 or 1024 or 9999, still the same

Hello,
I was able to write Chinese characters into our SQL db. However I had to change the following default settings to make it work:

When creating the table using the DB Table Creator I changed the column type to nvarchar(255)

In the DB Insert node I changed the default type mapping for String types to NVARCHAR via the Output Type Mapping tab

If you mostly want to write NVARCHAR you can also change the default output type mapping in the Microsoft SQL Server Connector node. All nodes that are connected to the connector node inherit its type mapping settings.

For more information about the type mapping have a look at the database documentation.

I also used the official Microsoft JDBC driver which is available in KNIME as a separate plugin. For an installation instruction see here.
Bye
Tobias

5 Likes

I tested the same setting as yours, but still failed. Tested in both 4.1.1 and 4.0.2 version.

I will just skip using the new DB node to write Chinese strings, at least the legacy database writer works well…

thanks for your help and support

regards,
Xiaoan

Hello Xiaoan,
could you please help to understand what the problem might be. Attached you can find the workflow I used for testing. I created one table with database type varchar(max) which didn’t work since it returned as expected ??? for the Chinese characters.
However when I created a table with nvarchar(max) I was able to write and read my example text. The workflow also contains a DB Query Reader that executes a statement which gets detailed information about the table structure from the database.
Can you please check with your database if the example workflow runs as expected or adapt your workflow accordingly and also double check using the DB Query Reader node that the column in your database table has the right type as shown below.

Thanks
Tobias

P.s. You can use max in the varchar/nvarchar definition whereas max indicates that the maximum storage size is 2^30-1 characters (2 GB) should be used. For more details see here.

SQLServerChineseCharacter.knwf (30.7 KB)

3 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.