How to support a MySQL 'text' data type in the Editor product.

nproctor

I'm using the Editor -> Generator to create a simple editable data table UI for a .NET / MySQL platform and it all works fine until I try to add a MySQL field of type 'text' (note: I'm trying to match an existing schema instead of creating the table from scratch with the generator sql) https://dev.mysql.com/doc/refman/5.0/en/blob.html

So my question is, what do I need to do to make a MySQL 'text' field work in the Editor? I'm assuming I need to do something in either the model or controller classes.

The error I get is:

DataTables warning: table id=taps - Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

when the ajax call tries to load the data.

If I remove the one field that is of type 'text' then it works like a charm.

Thanks,

allan

Hi,

Thanks for your question - I've just replied by e-mail, and happy to continue the conversation here or there - whichever suits yourself the best!

For anyone else reason, I think we need to know if you have any constraints on that field - either in the .NET code such as a validator, or in the database? I've just tried a text field in my test database and it doesn't appear to have any problems, but might setup probably won't be identical to your own.

Thanks,
Allan

nproctor

I did some more digging and found this answer to be very helpful

https://www.datatables.net/forums/discussion/1227/carriage-return-in-mysql-text-type-field

I did indeed have a few \r\n and \r in my content. Removing those fixed 98% of my issues.

I was able to reduce the final exact issue to when a 'text' field has content greater than 21,000 characters. (exact threshold is somewhere between 21000 and 22000, the lower size worked where the bigger size did not). So I'm wondering if there is a limit somewhere either in the data access library or maybe the JSON library that won't handle over 22000 characters.

I tested by substringing off both the front and the back portions of the string to make sure that there was not just a invalid character hanging around. Both substrings worked, while the entirely size string did not, leaving me to believe it is indeed just a size issue.

nproctor

After a final bit of research, I think I was hitting the field limit for a utf-8 text field

http://help.scibit.com/mascon/masconMySQL_Field_Types.html

Doing an alter

Alter table tablename modify columnname mediumtext;

to make the column a mediumtext type worked and the error is no longer thrown.

Thanks for your help and fast reply Allan.

nproctor

Additonally, I re-added the \n and \r\n back to the content and now that the data type is correct, the line breaks don't need to be removed after all.

allan

Hi,

Thanks for the updates - good to hear that you've got it working now. Frustrating the MySQL does truncates the data rather than throwing an error...

Allan