Default Value for Columns are not working

Certified Lead Developer

Hi All,

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

Parents
  • 0
    Certified Senior Developer

    Hi melwyan,

    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

    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,

    Thanks,Barat

  • 0
    Certified Lead Developer
    in reply to baratc

    Hi

    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

  • 0
    Certified Senior Developer
    in reply to melwynj5175

    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.

Reply
  • 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.

Children
No Data