Create MySQL table with FLOAT and DOUBLE PRECISION

[jepessen]

I'm creating a MySQL diagram with a table with some column of type FLOAT and double precision, like the one attached to the end of this post.

This table creates the following DDL code when using the Database Builder:

Code: [Select]

CREATE TABLE `gain_frequency`
(
`id` INT NOT NULL,
`resm_id` BINARY(16) NOT NULL,
`antenna_group_id` VARCHAR(50) NOT NULL,
`gain_db` FLOAT(0,0) NOT NULL,
`frequency` BIGINT NOT NULL,
CONSTRAINT `PK_gain_frequency` PRIMARY KEY (`id` ASC, `resm_id` ASC, `antenna_group_id` ASC)
)
When I execute the code in MySQL Workbench I obtain the following error:

Error Code: 1439. Display width out of range for column 'gain_db' (max = 255)

In order to fix this error, I need to remove the "(0,0)" in the FLOAT:

Code: [Select]

CREATE TABLE `gain_frequency`
(
`id` INT NOT NULL,
`resm_id` BINARY(16) NOT NULL,
`antenna_group_id` VARCHAR(50) NOT NULL,
`gain_db` FLOAT NOT NULL,
`frequency` BIGINT NOT NULL,
CONSTRAINT `PK_gain_frequency` PRIMARY KEY (`id` ASC, `resm_id` ASC, `antenna_group_id` ASC)
)
This way the table is create correctly. Is there a way to remove "(0,0)" directly from Enterprise architect? Because I cannot set empty values for Length and Scale fields, and I'd like to do not put any constrains about those values. I want to obtain the second piece of code, considering also that according to MySQL documentation, FLOAT(M,D) and DOBLE(M,D) are deprecated, so it's better do don't use this notation.

Any suggestion?

[ea0818]

You need to edit the script used to create the DDL commands.

I have a similar problem, with EA creating datetime2(0) datatypes incorrectly in SQL Server (MSS).  Here is the documentation that shows how to fix my issue; you will need to do something similar.


Problem:  In the column properties, when user sets the data type to datetime2 and a length of 0, EA is generating a create-table DDL script that gives the datatype of “datetime2”, not “datetime2(0)”.

In Microsoft SQL Server, the datatypes datetime2(0) and datetime2 are not the same.  (The number in “datetime2(#)” specifies precision, which is 0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.)
The following script gives the results shown below.
declare @d1 datetime2(7)
       ,@d2 datetime2(0)
       ,@d3 datetime2

select @d1 = GETDATE()
      ,@d2 = GETDATE()
      ,@d3 = GETDATE()

select @d1 as 'datetime2(7)'
      ,@d2 as 'datetime2(0)'
      ,@d3 as 'datetime2'

datetime2(7)                  datetime2(0)               datetime2
---------------------------   ------------------------   ---------------------------
2018-08-08 14:49:21.1300000   2018-08-08 14:49:21        2018-08-08 14:49:21.1300000

It is therefore necessary to generate DDL statements for SQL Server that specify “datetime2(0)” when the user specifies a length of 0.  Enterprise Architect is generating “datetime2” however.
All generated DDL scripts would therefore need to be corrected before use.


Solution:  It is necessary to open the “DDL Template Editor” tab, to change the scripts used to generate DDL statements.

EA14:  Use Code \ Schema \ DDL \ Edit the DB Schema (DDL) Templates.

EA15:  In the Find Command bar above the ribbon, type  “DDL”, and chose Template; this opens the “DDL Template Editor” tab.

Select the “DDL Data Type” value from the template drop down if available.
Select the required database type from the Language drop down if available.
Sort results by clicking the Name column header.
Select the “DDL Column Definition” template.
Modify the code starting about line 10, as shown below.  Since the templates appear to be specific to the Language (e.g. SQL Server 2012), this should not affect models for other databases.  It will be necessary to make this correction in every EA project file.

%if $Size=="1"%
$LENGTH = %columnProperty:"LENGTH"%
%if $LENGTH != "" and $LENGTH != "0"%
(
$LENGTH
)
$remark = "2018-08-15 Modification by Dale Fox"
$remark = "This elseIf block fixes the generation for datetime2(0)"
$remark = "data types for SQL Server"
%elseIf $LENGTH != "" and $LENGTH == "0" and $Type=="datetime2"%
(
$LENGTH
)
%endIf%


Click Save.

[jepessen]

Hi. Thanks for the help.

I've tried to adapt your code for my purposes but with no results (I've added the Changes by Daniele block). You can find here the DDL template that I've edited for DDL Column Definition of MySQL. It does not work, I obtain always FLOAT(0,0) and DOUBLE(0,0) in my DDL. What I'm doing wrong?

Code: [Select]

%PI=" "%

%DDLName("EA", "COLUMN", "", "INCLUDE_SURROUND")%

$remark = "----------------- Datatype -------------------"
%DDLDataType%

$remark = "----------------- Setup Properties -------------------"
$Type = %columnProperty:"TYPE"%
$Default = %columnProperty:"DEFAULT"%


$remark = "------- CHANGES BY DANIELE START ---------"
$remark = "Trying to create defailt DOUBLE and FLOAT"
%if $Size=="1"%
$LENGTH = %columnProperty:"LENGTH"%
%if $LENGTH != "" and $LENGTH != "0"%
(
$LENGTH
)
%elseIf $LENGTH != "" and $LENGTH == "0" and ($Type=="FLOAT" or $Type=="DOUBLE")%
(
$LENGTH
)
%endIf%


$remark = "------------- CHANGES BY DANIELE END -------------"

$Find = ""
%if $Type != "ENUM" and $Type != "SET"%
%if $Default != ""%
$Find = %FIND($Default, "DEFAULT")%
%if $Find == "-1"%
$Default = "DEFAULT " + $Default
%endIf%
%endIf%
%endIf%

$Zerofill = ""
$Unsigned = ""
%if columnBoolProperty:"ZEROFILL" == "T"%
$Zerofill = "ZEROFILL"
%endIf%
%if columnBoolProperty:"UNSIGNED" == "T"%
$Unsigned = "UNSIGNED"
%endIf%

$remark = "----------------- Is NULL -------------------"
$Nullable = ""
%if columnBoolProperty:"NULLABLE" != "T"%
$Nullable = "NOT NULL"
%else%
%if ddlOptionGenerateNullable == "T"%
$Nullable = "NULL"
%endIf%
%endIf%

$remark = "----------------- Autonum -------------------"
$AutoNum = ""
%if columnBoolProperty:"AutoNum" == "T"%
$AutoNum = "AUTO_INCREMENT"
%endIf%


$remark = "----------------- Add keywords in correct positions -------------------"
%if $Type == "ENUM" or $Type == "SET"%
%if $Default != ""%
$Default
%endIf%
%if $Nullable != ""%
$Nullable
%endIf%
%else%
%if $Zerofill != ""%
$Zerofill
%endIf%
%if $Unsigned != ""%
$Unsigned
%endIf%
%if $Nullable != ""%
$Nullable
%endIf%
%if $AutoNum != ""%
$AutoNum
%endIf%
%if $Default != ""%
$Default
%endIf%
%endIf%


$remark = "----------------- Comments -------------------"
%DDLColumnComment%

%PI("I", "")%
,


[ea0818]

I think your intention is to have EA generate only FLOAT, rather than FLOAT(0).

In your code, you have the same code for both float and other types.

%if $LENGTH != "" and $LENGTH != "0"%
(
$LENGTH
)
%elseIf $LENGTH != "" and $LENGTH == "0" and ($Type=="FLOAT" or $Type=="DOUBLE")%
(
$LENGTH
)
%endIf%


Try using the following instead, see what happens.

%if $LENGTH != "" and $LENGTH != "0"%
(
$LENGTH
)
%elseIf $LENGTH != "" and $LENGTH == "0" and ($Type=="FLOAT" or $Type=="DOUBLE")%
$remark = "Do not add anything to the data type; having (0) is a problem"
%endIf%


No promises that the syntax is correct.
Also, are you sure you are editing the correct place?  My guess is that the code
(
$LENGTH
)
would give text like "(0)", but you are reporting that you are receiving "(0,0)".  I suggest deliberately adding some text like "my change here" so that you can then look at the generated DDL script and confirm that you are altering the correct spot in the script.

[jepessen]

Ok I'll check it thanks.

[Modesto Vega]

I have not read the full thread but why are length and scale 0 for the float in the screenshot below?

Sparx is taking the values from those 2 properties hence it is doing a FLOAT(0,0).

[jepessen]

Yes. I' want to find a way to define a FLOAT type in the DDL, not a FLOAT(x, y), since FLOAT(x, y) and DOUBLE(X, y) are deprecated.

[Geert Bellekens]

Yes. I' want to find a way to define a FLOAT type in the DDL, not a FLOAT(x, y), since FLOAT(x, y) and DOUBLE(X, y) are deprecated.

In that case you should edit the datatype FLOAT itself to not have size and precision.
I suspect that will solve the DDL problem as well.

Geert

[jepessen]

In that case you should edit the datatype FLOAT itself to not have size and precision.
I suspect that will solve the DDL problem as well.

Thanks. Where can I update it? I don't find any place in the DDL editor.

[Geert Bellekens]

It's not in the DDL editor, its in Configure | Reference Data | Settings | Database Datatypes

Geert

[jepessen]

Thanks. I've tried to go there, I can select the type (i.e. DOUBLE PRECISION) but then I'm not able to modify the value.
What should I do? I need to create new types?

[Geert Bellekens]

You might need to create your own version of the MySQL database product, I'm not sure.
In that case you'll have to set this type on your tables as well.

Geert

[jepessen]

Ok thanks. I need to check if this is affordable or if I can use something like sed for fixing the sql file after creation. Maybe it's simpler.