Solved

Can I use SQL code to enter default in the Software Factory?

  • 23 June 2020
  • 7 replies
  • 152 views
Marcel van Langen
Rank
Userlevel 4
Badge +3

Hello!

A question. I want to fill a column in a table with a default value. I can do this in many ways, but normally in SQL I can do something like this:

ALTER TABLE MijnTabel ADD CONSTRAINT DF_xxx DEFAULT GETDATE() FOR MijnKolom

Something like this. I wanted to do the same thing within the SF, but it seems that is not possible. What I did was go to the table and then fill in 'GetDate()’ as the default for that column. However, then I get a message during deployment that there was a problem converting the varchar to a date. In short, it sees 'GetDate()’ not as a function, but as a string (the single quotes were added too). 

Long story short: is it possible to use GetDate() as a default from within the SF, or should I use another method (f.i. create a default that automatically fills this field with the current date)

BTW: there is a slight difference between these two: if I were to use GetDate() as the default, it would get the date when the record was added. If I create my own default, it would get the date the form was shown to the user. But that is not really a problem here.

icon

Best answer by Jasper 23 June 2020, 16:50

View original
= = = = Marcel van Langen - Landmerc = = = =

7 replies

Jasper
Rank
Userlevel 7
Badge +11

Hi Marcel,

We’re currently working on this feature and it will be available later this year.

 

 

Marcel van Langen
Rank
Userlevel 4
Badge +3

Thanks for the reply. Good to see others like this feature as well.

= = = = Marcel van Langen - Landmerc = = = =
Frank Wijnhout
Rank

Hi Marcel,

You have to realize that database defaults don't work for update statements. It only works for insert statements where the field with the default is not in the column list.

With this knowledge, is it still something you really need?

Marcel van Langen
Rank
Userlevel 4
Badge +3

@Frank Wijnhout Yes most definitely. The default gets set on the insert when there is not a specific value given. So for instance on a column 'date_record_added’ it would serve a purpose. I'm not sure why you'd think I want to use it differently in my example?

= = = = Marcel van Langen - Landmerc = = = =
Frank Wijnhout
Rank

@Marcel van Langen  In community there are examples of people wanting this for filling trace fields. In that case it only works for insert, not for update, which makes it not really useful.

Frank Wijnhout
Rank

I have another question: Are you especially interested in using system functions like getdate() and suser_sname(), or do you need to use user defined functions here too? The implementation of the latter is quite a hassle: functions can't be altered when used in a database default.

Marcel van Langen
Rank
Userlevel 4
Badge +3

Ah, I understand. For us, I see no need for using defaults to be filled with (scalar) UDF's. Looking back, I can't think of a time when we've used that (when filling a default), although I know you can in SQL Server. In terms of performance (when using it as a parameter to query the database) there is also good reason not to use this, so that is probably a good reason not to be to hasty to build this into the TSF. Thinking about it, that's probably a good reason for us to not use UDF's for defaults, even when it would have been possible in TSF.

= = = = Marcel van Langen - Landmerc = = = =

Reply


Terms & ConditionsCookie settings