Page 1 of 2
DateTime Default not working correctly
Posted: Fri 05 May 2017 21:52
by 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;
Re: DateTime Default not working correctly
Posted: Wed 10 May 2017 18:57
by OutOfTouch6947
Does anyone have any ideas on what is causing this?
Re: DateTime Default not working correctly
Posted: Thu 11 May 2017 09:52
by 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
Re: DateTime Default not working correctly
Posted: Thu 11 May 2017 14:38
by 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?
Re: DateTime Default not working correctly
Posted: Thu 11 May 2017 16:57
by 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
Re: DateTime Default not working correctly
Posted: Thu 11 May 2017 17:28
by OutOfTouch6947
Where do I load in code PgSqlMonitor in MVC application?
Re: DateTime Default not working correctly
Posted: Mon 15 May 2017 13:57
by OutOfTouch6947
I can't provide you what you asked, unless you tell me where to load in code PgSqlMonitor in MVC application?
Re: DateTime Default not working correctly
Posted: Wed 17 May 2017 09:07
by 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 };
// ....
}
}
Re: DateTime Default not working correctly
Posted: Thu 18 May 2017 15:28
by 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"
Re: DateTime Default not working correctly
Posted: Thu 18 May 2017 15:33
by 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.
OutOfTouch6947 wrote: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"
Re: DateTime Default not working correctly
Posted: Thu 18 May 2017 17:35
by 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.
OutOfTouch6947 wrote: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.
OutOfTouch6947 wrote: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"
Re: DateTime Default not working correctly
Posted: Thu 18 May 2017 17:43
by 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"
OutOfTouch6947 wrote: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.
OutOfTouch6947 wrote: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.
OutOfTouch6947 wrote: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"
Re: DateTime Default not working correctly
Posted: Thu 18 May 2017 18:20
by 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?
Re: DateTime Default not working correctly
Posted: Fri 19 May 2017 11:55
by Shalex
OutOfTouch6947 wrote: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.
Re: DateTime Default not working correctly
Posted: Fri 19 May 2017 18:45
by OutOfTouch6947
I didn't set the AutoGeneratedValue or Auto-Snc.
Where can I find an detailed explanation of AutoSync Mode?
Shalex wrote:OutOfTouch6947 wrote: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.