[odb-users] SQL functions as values for certain columns on INSERT or UPDATE

[Sten Kultakangas]

Hello

I want to set a column's value to a value returned by
DATEADD(ss,-something,GETDATE()) on INSERT. Can this be achieved using
standard ways without using execute() etc ?

A similar functionality for SELECT can be achieved using views. For example:

/*
SELECT [KampanjaID] ,[Tila], MIN([CreateDateTime]), count(*)
FROM [OutboundManager].[dbo].[KampanjaDetails]
where Tila IN('S','1','2') GROUP BY KampanjaID,Tila
*/
PRAGMA_DB(view object(KampanjaDetails) query("Tila IN('S','1','2')
GROUP BY KampanjaID,Tila"))
struct KampanjaDetails_QueueSummaryView {
PRAGMA_DB(column(KampanjaDetails::KampanjaID))
int KampanjaID;

PRAGMA_DB(column(KampanjaDetails::Tila))
string Tila;

PRAGMA_DB(column("MIN(" + KampanjaDetails::CreateDateTime + ")"))
ptime oldest;

PRAGMA_DB(column("COUNT(*)"))
int total;
};


As far as i understand, views cannot be abused for INSERT/UPDATE operations.
I tried the following:

PRAGMA_DB(object schema("ICCAnalyzer.dbo"))
struct CallDetailRecord {
PRAGMA_DB(id auto type("bigint"))
//...
PRAGMA_DB(type("datetime") options("DEFAULT GETDATE()"))
ptime CollectedDateTime;
};

however, the newly inserted rows are NULL.

I know i can workaround this by altering the table definition:

ALTER TABLE [ICCAnalyzer].[dbo].[CallDetailRecord] ADD  CONSTRAINT
[DF_CallDetailRecord_CollectedDateTime] DEFAULT (getdate()) FOR
[CollectedDateTime]

and then simply exclude CollectedDateTime from the object's definition
in the header file.

Is it possible to use similar functionality which is available for
views? For example something like this:

PRAGMA_DB(column_value("GETDATE()"))
ptime CollectedDateTime;
PRAGMA_DB(column_value("DATEADD(ss,-" +
CallDetailRecord::DurationSeconds + ",GETDATE())"))
ptime CallStartedDateTime;


Best regards,
Sten Kultakangas