Database

KNIME Analytics Platform
1

Hi!

I have a MYSQL database with fields formatted as text or mediumtext for large text strings. In a workflow I want to write text from a table column "titel" to those fields but I get the following error message: Column "titel" of type "StringCell" from input does not match type "VARCHAR" in database. I am not sure but I think that I did not have this problem in version 2.6. Are there any suggestions how to solve the problem writing large text strings to the database fields?

Thanks, Jerry

2

Hi Jerry, within the Database Writer configuration, tab SQL types you can specifiy the size of the varchar(int) created in the database, have you done this in 2.6?

3

Yes, I tried it already with various settings for varchar (like 1024, 65000, and so on). None of the settings worked in 2.7 and I did not set it in 2.6.

Thanks

4

Hi Jerry, do you see any error in the KNIME Console, can you please try to isolate the other columns to see if it real depends on this 'title' column? Cheers.

5
I tried your suggestion on a very simple model:
 
Table Creator (with one cell of short text, even below 255 chars) > Column Rename (to Titel) > Database Writer (append to a table with one field of the same name, field type: text)
 
...and got the same error message on the KNIME console.
 
Thanks for your suggestion
Jerry

6

Hi Jerry, what is the error message you see in the KNIME Console view. Cheers, Thomas

7

Column "titel" of type "StringCell" from input does not match type "VARCHAR" in database at position 0. The position count depends on the position of the field in the database table. In this case it is the only field.

Best regards

Jerry

8

Thanks, Jerry. Might this be the problem that the column names do not exactly match, lower vs. upper case?

9

No, It works immediately if you reformat the database field to VARCHAR(255) without changing the name. I tried it also on different computers/installations of KNIME with the same result.

 

Thanks anyway, Jerry

10

I found a work around by appending only those columns which are short text, date or int fields first and then afterwards use the Database Update node to fill in the fields formatted as text or mediumtext. Not really efficient but it works. Therefore, I think this is a bug in the Database Writer node.

Best regards

Jerry

11

Hello,

got the same issue today.

MySQL Text column is shown with the same error message.

Workaround to remove the column at DatabaseWriter and using a separate update with DatabasUpdate for the MySQL Text column.

Certainly not a good workaround since far too slow.

Regards

 

12

Did you consider changing the type of the written column in "SQL Types" tab in the node dialog configuration?

13

Yes, I tried to change to varchar(65000), text, but got the same error message.

The column is defined as MySQL "Text"

14

I looks like the type as entered the dialog configuration and from the database are not compatible. I found a link where they talk about text vs. varchar SQL types, it might help: http://stackoverflow.com/questions/564755/sql-server-text-type-vs-varchar-data-type

15

Hello Gabriel,

thanks for the hint.

I found the following concerning MySQL (the link above refers to MS SQL).

I tried to change the set SQL Types also to Longvarchar, Blob, but nothing really seems work.

(Anyway also if changed for other columns, it seems that the setting in SQL Types is even ignored)

Database Update node can write the colum.

 

16

I tried further - new column as "blob". Same error message:

Execute failed: Column "b1" of type "StringCell" from input does not match type "BLOB" in database at position 16

Then new column as varchar(5000).

This works.

The problem is just that the values of the column will be larger thatn 5000 chars.

Then MySQL need either blob or text in my understanding.

The workaround with Database Update is really quite time consuming.

I appreciate any help

Thanks