MySQL: Defining Length of Number/Date Fields

Posts   
 
    
ab-tools
User
Posts: 15
Joined: 18-Dec-2020
# Posted on: 24-Dec-2020 12:13:08   

Hello,

the parameter "Max. length" in the Table Field Editor seems currently only working for string type of fields.

How can the (display) length of number (especially useful for boolean values with type "TINYINT(1)") and date/time (where you only want to see the date, so "DATETIME(6)") fields be defined?

Thanks for all your support Andreas

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 24-Dec-2020 19:13:22   

Max.Length is only for String fields. It works as a field value input validation.

I believe you are talking about, displaying data, this can either be done in the presentation layer, by formatting. As for the TinyINT (1) boolean values, for this you can use a TypeConvrter. There is one that ships by default, that convert from numeric value to boolean.

ab-tools
User
Posts: 15
Joined: 18-Dec-2020
# Posted on: 24-Dec-2020 22:11:05   

Hello Walaa,

yes, the default TypeConverter is exactly what I'm using for boolean value, but as you can see on the attached screenshot despite me even explicitly defining a length value of "1" for the "TinyInt" MySQL column in the Table Field Editor this "Max. length" is not set (and I also can't set it manually).

As a result the MySQL database TinyInt column gets defined with its maximum (display) length of "4" instead of the intended "1" for only boolean value.

And yes, I do know that internally the storage space needed for the column value stays always the same (it's still from type TinyInt after all), but it's good practice to define the length based on the actual need. This is even more true for DateTime field/column types where you only need the date part (and therefore restrict it to a length of "6").

Best regards and thanks again for your quick support on Christmas :-) Andreas

Attachments
Filename File size Added on Approval
BooleanTypeConverter.png 21,345 24-Dec-2020 22:11.12 Approved
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 25-Dec-2020 09:34:46   

You can't. This is by design, as a TinyInt is materialized as an Int16. If you define a typeshortcut (see project settings) for System.Byte and for that type shortcut a default database type of Unsigned TinyInt, you'll get a byte

Frans Bouma | Lead developer LLBLGen Pro
ab-tools
User
Posts: 15
Joined: 18-Dec-2020
# Posted on: 25-Dec-2020 10:02:37   

Thanks, Frans, for the quick reply as always!

Is that also possible to do the same with such a type shortcut for DateTime fields/columns which I would only like to have the date part (and thus in MySQL defined as "DATETIME(6)"?

Best regards Andreas

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 26-Dec-2020 09:49:58   

yes

Frans Bouma | Lead developer LLBLGen Pro