Sql Server Identities

Author: roger.wallin@abilita.fi (rogerw)

Hi,

In Uniface we have to access tables from a database using Sql-Server  Identity (usually int) and uniqueidentifier GUID (16-byte binary), ie. datatypes/values generated by the database.

I would need some quick advice about what you can do with Uniface in this situation. Is it possible to

read Identity?
read  GUID?
write Identity?
write GUID?

Regards RogerW.

14 Comments

  1. You can't write identity, that defeats the nature of it.
    You could possibly create an updateable view, or an instead of trigger on insert and update to leave the identity field out of the update.

    The problem is that the main reason for an identity field is usually the primary key. Which uniface will complain if you don't populate prior to the write command. But you don't know what the resultant value is going to be until afterwards.

    This is why I have a wish on file for support of the identity function in Prof.IT.


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  2. Hi,

    Actually GUIDs could be easier to write as someone told me that you can generate a GUID and do a normal Insert. The problem may then be the database type of the GUID and the Uniface model.
    I have to start testing, but any advice appreciated.

    Regards RogerW.


    Author: rogerw (roger.wallin@abilita.fi)
  3. Hi RogerW.,

    you have to check your DB reference to see what the real GUID format is (a quoted string or some hex-numbers).

    I used to call a TK generator in the WRITE trigger and this can be implemented to return a UUID

    (the dITO 3TB "3gl toolbox") provides this feature, as one option to get this even for older uniface versions.

    Success, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  4. Thanks Uli,

    sounds promising. I'll test it today or tomorrow.

    Regards RogerW.

    PS. Didn't the young German team stand the preassure? :-)

     


    Author: rogerw (roger.wallin@abilita.fi)
  5. Hi,

    I got it working by LEN(0-0) and putting this in the write-trigger:

    sql

    "select cast(newid() as binary)","def"
    mguid.myguidtest=$result
    write

    I also got it working all database generated, by removing the mguid-parameter of the Uniface generated "_INS" procedure.
    With "procedures off" it would probably not be possible.

    (mguid = type R16).

    Regards RogerW.

     


    Author: rogerw (roger.wallin@abilita.fi)
  6. Hi all,

    you can ommit a PK if you set the field to LEN(0-0).

    Then even an empty field is accepted (usually, you enter a value in the WRITE trigger, but if the database

    has some internal mechanism to do so (like identity or seq) i think you can leave it empty.

     

    Success, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  7. Hi,

    Just a clearification about the stored procedure, ie don't remove the in-parameter completely, just inside the procedure:

    Default procedure:
    create procedure [myguidtest_INS]
    @insert1
    binary(16),
    @insert2 char(10)
    as insert into [myguidtest] ([mguid], [othvalue]) values (@insert1, @insert2)Edited procedure:
    create procedure [myguidtest_INS]
    @insert1
    binary(16),
    @insert2 char(10)
    as insert into [myguidtest] ([othvalue])
    values (@insert2)


    And, I haven't yet got the Master-Detail (1:n) working in the full database generate scenario. There are some problems with the foreign-key of the child. I don't know if there are some problems with the mix of the types uniqueidentifier (used for the master table) and binary(16) (used for all stored procs and child) or something else ??

    Any advice appreciated :-).

    Regards RogerW.  


    PS. Reading and writing one table works very well.

     


    Author: rogerw (roger.wallin@abilita.fi)
  8. If you used the frame-in-frame, the following should happen:

     

    WRITE of the ONE is done at first.
    Data are re-read from the database, so you should have a value for the generated ones.

    WRITE of the MANY is done afterwards, because you have painted the FK only as part of the ONE entity,

    there should not be a problem at all. (or you can refer to the PK.ONE in the wtrite of the MANY.occurences.

    Success, Uli

     


    Author: ulrich-merkel (ulrichmerkel@web.de)
  9. Is the data re-read from the database? If so, how is it fetched when Uniface doesn't have the primary key to do it from....


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  10. Hi,

    interesting question :-).
    And you are probably right. It doesn't even work with one table, if the record isn't retrieved before an update.

    Regards RogerW.


    Author: rogerw (roger.wallin@abilita.fi)
  11. Hi Iain,

    it's a fetch from current.

    As soon as the record is written, the values are shown on my application (if I remember it well).

    Success, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)
  12. Hi,

    If I try to update other fields of an occurence just inserted (stored), I get "occurence does not exist".
    Having re-retrieved before updating I'm able to update it. And probably adding child-records.
    To me it looks that the client isn't aware of the GUID=PK value just after the record has been inserted (stored).

    Regards RogerW.


    Author: rogerw (roger.wallin@abilita.fi)
  13. Hi,

    Uli or someone else,  are you sure that a  "current fetch" should handle the server-side generated GUID/Identity in a parent-child situation?
    Is there a difference between different database-drivers of Uniface?

    (I do get the client-side generation working, but this doesn't support a sequential insert, which could be bad in some non-composite key scenarios.......).

    Regards RogerW.


    Author: rogerw (roger.wallin@abilita.fi)
  14. Hi RogerW.

    I have to check the DB documentation, but if I remember it well,

    the create of a database-record using SEQUENTIAL etc. has to return the generated value
    so you can add it to the values displayed.

    No practcal experience in the last days, sorry.

     

    SUccess, Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)