Datetime2 precision 0 not honored

Posts   
 
    
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 05-Mar-2018 23:47:27   

Using 5.3 (5.3.4) RTM, adapter with SQL Server

After adding a date field with dbtype datetime2 and precision set to zero (the default), the generated DDL shows datetime2.

Changing the precision to 3 alters the field to datetime2.

But after reverting the precision back to 0 again, the generated script again shows datetime2.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 06-Mar-2018 07:46:49   

Reproduced with v5.3.3 RTM (09-feb.-2018 ). Steps: 1. Create a project (Model first) 2. Create an entity with a PK (int) and another field (datetime) 3. Sync Model->DB 4. Go to Field Mappings and edit the dbtype of the datetime field to be datetime2 with precision 0. 5. Generate Create DDL script. It will emit:

CREATE TABLE [dbo].[Tea] 
(
    [BirthDate] [datetime2](7) NOT NULL, 
    [Id] [int] IDENTITY (1,1) NOT NULL 
)
GO

it should be

CREATE TABLE [dbo].[Tea] 
(
    [BirthDate] [datetime2](0) NOT NULL, 
    [Id] [int] IDENTITY (1,1) NOT NULL 
)
GO

We will look into this...

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Mar-2018 09:56:09   

This is sadly for backwards compatibility: before the change for precision editing, the precision value was 0. To avoid having a breaking change where users have to specify '7' for precision for all their datetime2 fields, we emit 7 as the default value for 0.

We can't do it another way, as we don't know if a '0' for precision in the project file is there because the user has specified that or because it's an older project and it's a leftover from before the change, so we can't correct it when loading the project, and we can't say "just refresh from the DB" as the problem is only occurring in DDL SQL scripts, which are used in model first....

If you want to work around it, you have to use a custom template for this. Create a copy of \Frameworks\DDL SQL\Templates\SqlServerSpecific\FieldCreationInclude.lpt and change line 57. Then create a new templatebindings file in the designer using the template viewer and bind this copy of the template to the ID SD_DDLSQL_FieldCreationInclude, like so: (increased precedence here from 10 to 11 so it overrules the existing one and used dummy filenames)


<?xml version="1.0" encoding="utf-8"?>
<templateBindings name="SD.TemplateBindings.SqlServerSpecific.DDLSQL.Copy" description="SQL Server specific template bindings for DDL SQL generation" 
                        precedenceLevel="11" databaseDriverID="2D18D138-1DD2-467E-86CC-4838250611AE" xmlns="http://sd/llblgen/pro/templateBindingsDefinition.xsd">
    <supportedPlatforms>
        <platform name="RDBMS"/>
    </supportedPlatforms>
    <supportedFrameworks>
        <framework name="DDL SQL" />
    </supportedFrameworks>
    <language name="SQL">
        <templateBinding templateID="SD_DDLSQL_FieldCreationInclude" filename="SqlServerSpecific\FieldCreationIncludeCopy.lpt" templateLanguage="C#" includeOnly="true" />
    </language>
</templateBindings>

And make sure the templatebindings file is reachable by the designer, so in the folder specified in the additionaltemplates folder setting in the project settings. It should then be picked up when you generate DDL SQL the next time.

If you can think of a simpler solution, please let me know simple_smile

Frans Bouma | Lead developer LLBLGen Pro
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 07-Mar-2018 15:08:26   

This works, of course simple_smile

Perhaps you can do something depending on the precision being present in the project file or not. If it is not present it will be read as 7, otherwise as specified.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 08-Mar-2018 14:58:28   

Looking at it again it requires a custom type shortcut (with a default type, set in project settings) or manually added field to make a System.DateTime field create a DB Field with type Datetime2 through model first sync. So it's definitely not something one will do 'by accident'.

So I think it's fair to say (also because database first pulls in the right precision), that changing this won't hurt anyone: it's not as if you by accident are stuck with datetime2 fields with precision 0 and you want them to have precision 7.

It's a breaking change for v5.4 and will be documented as such.

Frans Bouma | Lead developer LLBLGen Pro
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 08-Mar-2018 15:03:23   

Nice! Thanks.