How handle calculated fields in sql server

Hi Pedro,

I've never tried directly with SQLserver but I suggest you to try to define them as PERSISTENT then define it normally into Uniface application model.

Regards,
Gianni

Hi, I really don't understand how to define a PERSISTENT field. I'm going to explain the situation in more detail. In a database that uses administrative software (not developed in Uniface) there is a table to which records must be added. It contains fields defined as "computed column". Those fields that don't have "Data Type" are the ones that fail. I hope this explanation is enough.

Thanks

Hi Pedro,

Ah, OK!

If they are defined as calculated fields NOT persistent it is NOT mandatory for you to define them in your Uniface application model: simply strip them out!
You will be anyhow able to access that table from Uniface and to add new occurrences to that table but remember you MUST respect in Uniface ALL business rules related to that table implemented in that external package of yours.
If you will respect ALL business rules needed those calculated fields will be anyhow managed correctly for your external package.

Regards,
Gianni

Hi Gianni

"simply strip them out!"
But what, if one needs to read this columns?

My idea is to define two tables in UnifAce, one with the field in question (for read) and one without (write)
a) Define a VIEW witout the field for updates
b) Use [ENTITIES] to point to the same table on databse
c) Define a second modell

Ingo

Hi Ingo,

you are involved too... 🙂

IMHO as of today this one is still a grey area in the SQL/xQL definition/access by Uniface.

Still today Uniface is simply putting all fields listed in the application model as being part of DB in a select request; if your field is NonDatabase it is not listed at all. Those fields calculated from DB, when they are NOT defined as PERSISTENT are sitting in the middle:
- They are physically NOT part of the table
- They are logically part of the table
You would like to receive them in your buffer in a read but you don't want to send them into the buffer for a write. To DIRECTLY obtain this functionality a modification to the driver level is needed.

Yes, you could workaround it as you have described but, as a personal opinion, I do NOT like it because you are using a view as writable entity. I am NOT sure this is preserving (R)DBMS portability between my 3 basic reference (R)DBMS: Oracle, SQLServer, PostegreSQL.
If you do not need (R)DBMS portability, go for it!

It can also be implemented in Uniface as a NONdatabase field, filling it in the read trigger at model level? Something like:
    read
    if ($status >= 0)
        yourCalculatedField = BasicFieldA * BasicFieldB
   endif
Yes, as a side effect, this behaviour would duplicate the calculation method in DBMS and in Uniface!

As I've told it already this is as of today (IMHO) a grey area...

Regards,
Gianni

Hi, after trying what you told me, another error appears:

“I/O function: W, mode: 0, on file/table: SCCOMPRO length: 625

42000 [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.”

Any ideas?.

Thank you

Hi Pedro,

CONCAT_NULL_YIELDS_NULL and ANSI_WARNINGS are both configuration params at database level; your database was created probably with a default configuration and if this configuration is not enabling you to do certain things you could try to change it, applying maximum attention to avoid (if possible) all side effects.

Let's see first what is their meaning:
CONCAT_NULL_YIELDS_NULL: if you think of null as meaning "unknown", when you concatenate an unknown value with a known value, the result should be still an unknown value. This could be completely right or not depending from your needs. Setting it the other way around, while concatenating a string containing a known value with a null value you will get the know value.
ANSI_WARNINGS: when ON warning will be raised when the SQL statements has problem while execution. such as, null value in aggregate function returns warning message. This is probably connected to the previous one: when the previous one find the case this second one force the DBMS engine to show you the warning you have received back.

Probably the first parameter could be changed without forecasting side effects and consequentely the second one should never bother you again in a case like this.
Before going to change it you should ask to yourself: is my Uniface application the only one working on this database I would like to reconfigure?
If YES, ok you can proceed...time to study and learn how to change a database parameter in SQLserver...
If NO, ahi ahi ahi...things are a little more complex to be solved...and cannot be afforded here in this discussion.

I hope I've put you on the (hope right) path to solve your issue...

Regards,
Gianni