In MySQL/MariaDB, we have an option to default the column value while creating a table(For Example createdBy: System; createdOn: CURRENT_TIMESTAMP). In Appian, if we set those values to NULL and pass them to DB, those default values get initialized and stored in the appropriate table. This works fine in MySQL and MariaDB
However, Whilst tries the same in SQL Server, I don't see that the default values get triggered and stored into the fields. Should I add anything else for SQL Server DB? Did anyone face this issue before?
Note: I don't want to set the values in Appian and store them in DB. Instead, I need to handle it DB end.
Thanks in advance.
Discussion posts and replies are publicly visible
I have not tried this approach on myself. Just a quick thought
Make sure user account you are trying has ALTER permission.
Please check the following items.
1) Please review the DDL for the create table which is having the default value for that columns
Ex:ALTER TABLE table
ADD datetime default CURRENT_TIMESTAMP
ALTER TABLE table
2)Verify by inserting the values into the table using from SQL server(backend) and see whether the default is working
3)if steps 1 and 2 are ok. then download and Verify the XSD.
(If required please create a new cdt after altering the table with default and verify the datastore again)
Hope by using any of the above 3 steps then you can find out the cause and add the fix.
Please let me know in case of any challneges,
While I try to insert the value using the INSERT command via SSMS, I could see the default values get stored in the appropriate columns. However, after creating the CDT from the table and inserting the value from Appian, it's saving as NULL for those fields it must be saved with default values.
As I mentioned above, it works well in MySQL and MariaDB. Only in SQL server, I have been facing this issue
could you please take a look into the XSD/DDL. if possible, pls share DDL and XSD here.
In my situations with auto-generated DB values, I typically exclude those fields from the main CDT which provides the write from the processes - and include them back in any CDT views used for display, reporting, etc.
However, you may have success by using @GeneratedValue in your XSD definition if you need to include those columns in the main CDT.
Also to note, don't forget that with datetime values, Appian automatically performs GMT conversion for data storage, so if you use SQL's GETDATE() for a default on the DB level, this value will be shifted when displayed back through Appian. In this situation, we use a custom function within the DB to perform the conversion within a view for display. A conversion function is also used when we utilize DATEPART() for reporting aggregations on day, month, year, etc.
© 2021 Appian. All rights reserved.