IDENTITY with SQL SERVER

Currently in Uniface, I have found a way to use fields with an IDENTITY declaration in SQL Server. However, it is not functioning correctly due to the loss of data modeling. I am unable to set an ID as the primary key, so I have to maneuver in order to make both a query and a store work within the same service.

The way I am using an IDENTITY is as I mentioned before, by setting another field as the primary key and leaving the fieldlist of the entity in the service empty or without the field that has the IDENTITY, in this case, the ID field. The issue with this, aside from the data modeling problem, is that when I perform a RETRIEVE, the ID field is not returned if I use a putlistitems, for example. I have to manually call it to include it in my list.

Do you have any ideas or suggestions to address this situation?

Hello Miguel

For the datatype IDENTITY from MSS I have this information:
Field interface Numeric I4. 

Use
enable IDENTITY_INSERT for the involved table 
so Uniface can provide key

For example for table testident
sql "SET IDENTITY_INSERT testident ON","DEF" 
edit 
to be able to also insert new records into table identtest with Uniface.

Table used to test: 
create table [testident] 
( 
[identitykey] int IDENTITY(1,1), 
[fname] varchar(20) null, 
[minit] char(1) null, 
[lname] varchar(30) null, 
constraint [testident_pk] primary key 
([identitykey]) 
)

SET IDENTITY_INSERT identtest OFF
INSERT testident
   (fname, minit, lname)
VALUES
   ('Karin', 'F', 'Josephs')

SET IDENTITY_INSERT testident OFF
INSERT identtest
   (fname, minit, lname)
VALUES
   ('Pirkko', 'O', 'Koskitalo')

SET IDENTITY_INSERT testident ON

INSERT testident
   (id_num , fname, minit, lname)
VALUES
   (3,'Pirkko', 'O', 'Koskitalo')

But that removes the utility of the identity field in MSS, in that you have to work out what the next number is, and manually populate it. If that is the case, and Uniface is the language mostly populating the database, you might as well do without identity. 

I understand that the Oracle driver for Uniface is equipped to handle identity fields, would it not be possible to do the same to the MSS driver? 

Regards, 

Iain

Uniface does not know the concept off identity: A  special solution is always need for Uniface.

The identity functionality can still be used but is disabled when using Uniface when following my suggestion.

The Oracle connector does not have anything for identity fields:

At least I haven't seen it.

Do you have an example ?

We in Uniface support think there might be constructions where the primary key field in Uniface is non database.

I remembered it in passing from some patch notification. 

Checking the documentation, it might be that I saw the Sybase driver setting for identity_insert, which seems to (half way) support identity fields in the database. (In that you can have the field declared, and it will (I presume) load the field on retrieve, but you have to look up the field value after a write.). 

I don't use Sybase, so I don't know how annoying this is to use, but it's at least less annoying than not being able to reference identity fields at all in the model. 

Regards, 

Iain

Currently in Uniface, I have found a way to use fields with an IDENTITY declaration in SQL Server. However, it is not functioning correctly due to the loss of data modeling. I am unable to set an ID as the primary key, so I have to maneuver in order to make both a query and a store work within the same service.

The way I am using an IDENTITY is as I mentioned before, by setting another field as the primary key and leaving the fieldlist of the entity in the service empty or without the field that has the IDENTITY, in this case, the ID field. The issue with this, aside from the data modeling problem, is that when I perform a RETRIEVE, the ID field is not returned if I use a putlistitems, for example. I have to manually call it to include it in my list.

Do you have any ideas or suggestions to address this situation?

Hi Miguel,

If dbms portability is not an issue;

We're using Oracle - and for all of our transaction based tables where we need a unique id - we've created
sequences with the appropriate table name (think 'SEQ_CUSTOMER').
We have set the customer_number syntax definition in Uniface to LEN(0-0) in the model.
In the WRITE trigger of CUSTOMER;

if ($storetype = 1 & $keyfields("%%$entname", 1) = "")
  sql "select seq_%%$entname.nextval from DUAL","$ORA"
  $keyfields("%%$entname",1) = $result
endif

SQL-Server should be able to do something similar I believe.

Regards,

Knut