DateTime Default not working correctly

[OutOfTouch6947]

No matter what I try as the default now(), localtimestamp, etc... I always see, 0001-01-01 00:00:00 as the value when the record is inserted and I am not providing the value. I am not sure if it is the ED or me and my lack of postgresql knowledge but this is a pretty easy and basic thing to do in the other database I came from.
Here is an example of one of the ways I have tried to default the value.

Code: Select all

ALTER TABLE "SalesOrderHeader" ALTER COLUMN "CreatedDate"  SET DEFAULT localtimestamp;

[OutOfTouch6947]

Does anyone have any ideas on what is causing this?

[Shalex]

Did you set AutoGeneratedValue=True and Auto-Sync=OnInsert for your CreatedDate property in the *.lqml model?

If this doesn't help, please give us the following information:
1) enable the dbMonitor tool and specify the exact SQL statement sent to PostgreSQL Server when the record is inserted
2) send us a small test project with the corresponding DDL/DML script for reproducing the issue in our environment

[OutOfTouch6947]

I took CreatedDate out of the model and I get the same result. So is it even possible to still be the model that is causing the issue?

[Shalex]

Please give us the following information:
1) enable the dbMonitor tool and specify the exact SQL statement sent to PostgreSQL Server when the record is inserted
2) send us a small test project with the corresponding DDL/DML script for reproducing the issue in our environment

[OutOfTouch6947]

Where do I load in code PgSqlMonitor in MVC application?

[OutOfTouch6947]

I can't provide you what you asked, unless you tell me where to load in code PgSqlMonitor in MVC application?

[Shalex]

Please open your \WebApplication\Global.asax and create the PgSqlMonitor instance:

Code: Select all

    public class MvcApplication : System.Web.HttpApplication
    {
        PgSqlMonitor monitor;

        protected void Application_Start()
        {
            monitor = new PgSqlMonitor() { IsActive = true };

            // ....
        }
    }

[OutOfTouch6947]

You can see that I removed the CreatedDate from the model for this table as a test to see, if it would default correctly, and it still does not, but it also is not in this insert statement.

Code: Select all

INSERT INTO public."SalesOrderHeader" ("CustomerId", "OrderNumber", "SalesRepId", "SalesRepName", "SourceCode", "Date", "SubTotal", "Discount", "SalesTaxRate", "SalesTaxAmount", "ShippingTotal", "OrderTotal", "PaymentMethodTypeId", "PromoCode", "CreatedBy", "ModifiedBy", "Exported", "Notes") VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18) RETURNING "SalesOrderHeaderId"

[OutOfTouch6947]

Correction I now see the correct value being inserted into the table for the CreatedDate, I am going to add it back to the model and run dbmonitor again and see if it works correctly.

You can see that I removed the CreatedDate from the model for this table as a test to see, if it would default correctly, and it still does not, but it also is not in this insert statement.

Code: Select all

INSERT INTO public."SalesOrderHeader" ("CustomerId", "OrderNumber", "SalesRepId", "SalesRepName", "SourceCode", "Date", "SubTotal", "Discount", "SalesTaxRate", "SalesTaxAmount", "ShippingTotal", "OrderTotal", "PaymentMethodTypeId", "PromoCode", "CreatedBy", "ModifiedBy", "Exported", "Notes") VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18) RETURNING "SalesOrderHeaderId"

[OutOfTouch6947]

I just added it back to the model and again I don't see the correct datetime being inserted, I am going to run DBMONITOR again and post the insert statement.

Correction I now see the correct value being inserted into the table for the CreatedDate, I am going to add it back to the model and run dbmonitor again and see if it works correctly.

You can see that I removed the CreatedDate from the model for this table as a test to see, if it would default correctly, and it still does not, but it also is not in this insert statement.

Code: Select all

INSERT INTO public."SalesOrderHeader" ("CustomerId", "OrderNumber", "SalesRepId", "SalesRepName", "SourceCode", "Date", "SubTotal", "Discount", "SalesTaxRate", "SalesTaxAmount", "ShippingTotal", "OrderTotal", "PaymentMethodTypeId", "PromoCode", "CreatedBy", "ModifiedBy", "Exported", "Notes") VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18) RETURNING "SalesOrderHeaderId"

[OutOfTouch6947]

Here is the insert statement with the CreatedDate and ModifiedDate added back into the models and the values are not defaulting correctly in the backend, so the model must be passin in some junk value.

Code: Select all

INSERT INTO public."SalesOrderHeader" ("CustomerId", "OrderNumber", "SalesRepId", "SalesRepName", "SourceCode", "Date", "SubTotal", "Discount", "SalesTaxRate", "SalesTaxAmount", "ShippingTotal", "OrderTotal", "PaymentMethodTypeId", "PromoCode", "CreatedDate", "CreatedBy", "ModifiedDate", "ModifiedBy", "Exported", "Notes") VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18, :p19, :p20) RETURNING "SalesOrderHeaderId"

I just added it back to the model and again I don't see the correct datetime being inserted, I am going to run DBMONITOR again and post the insert statement.

Correction I now see the correct value being inserted into the table for the CreatedDate, I am going to add it back to the model and run dbmonitor again and see if it works correctly.

You can see that I removed the CreatedDate from the model for this table as a test to see, if it would default correctly, and it still does not, but it also is not in this insert statement.

Code: Select all

INSERT INTO public."SalesOrderHeader" ("CustomerId", "OrderNumber", "SalesRepId", "SalesRepName", "SourceCode", "Date", "SubTotal", "Discount", "SalesTaxRate", "SalesTaxAmount", "ShippingTotal", "OrderTotal", "PaymentMethodTypeId", "PromoCode", "CreatedBy", "ModifiedBy", "Exported", "Notes") VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18) RETURNING "SalesOrderHeaderId"

[OutOfTouch6947]

Ok so I determined as soon as I remove it from the model it uses the default constraint correctly. When it is in the model I don't set any value for it not even null. Is that what is causing the problem?

[Shalex]

Here is the insert statement with the CreatedDate and ModifiedDate added back into the models and the values are not defaulting correctly in the backend, so the model must be passin in some junk value. [...]

1. Did you set the following attributes for your CreatedDate and ModifiedDate properties in *.lqml?
CreatedDate: AutoGeneratedValue=True, Auto-Sync=OnInsert
ModifiedDate: AutoGeneratedValue=True, Auto-Sync=OnUpdate

2. Specify (via dbMonitor) the values of parameters used in the INSERT statement for "CreatedDate" and "ModifiedDate" columns.

3. Send us a small test project with the corresponding DDL/DML script for reproducing the issue in our environment.

[OutOfTouch6947]

I didn't set the AutoGeneratedValue or Auto-Snc.
Where can I find an detailed explanation of AutoSync Mode?

Here is the insert statement with the CreatedDate and ModifiedDate added back into the models and the values are not defaulting correctly in the backend, so the model must be passin in some junk value. [...]

1. Did you set the following attributes for your CreatedDate and ModifiedDate properties in *.lqml?
CreatedDate: AutoGeneratedValue=True, Auto-Sync=OnInsert
ModifiedDate: AutoGeneratedValue=True, Auto-Sync=OnUpdate

2. Specify (via dbMonitor) the values of parameters used in the INSERT statement for "CreatedDate" and "ModifiedDate" columns.

3. Send us a small test project with the corresponding DDL/DML script for reproducing the issue in our environment.