Description


Data Type Numeric with packing code A, actually mapped only to Sybase identity storage format, should be extended and normalized to all DBMS drivers. Identities should be by default mapped as part of same transaction running in the Uniface component. Nice variations could be: - Identity with or without holes in the sequence. - Identity with holes in the sequence could be overwritten or not by a user input to close a gap in the sequence.

Use Case


How to deal with it on major Uniface supported DBMSes: - Oracle: Create mySequence object + insert into table_name (ID) VALUES (mySequence.nextval) - mySQL: AUTO_INCREMENT keyword - SQL Server: IDENTITY(x,y) keyword - Sybase: IDENTITY(x,y) keyword - DB2: create table table_name (ID integer not null GENERATED \\\'ALWAYS\\\'|\\\'BY DEFAULT\\\' AS IDENTITY (START WITH 1 INCREMENT BY 1) or create mySequence object + insert into table_name (ID) VALUES (mySequence.nextval) - IBM Solid: Create mySequence object + insert into table_name (ID) VALUES (mySequence.nextval) - Embedded DB: Fields of type CEVT_AUTO_I4 and CEVT_AUTO_I8 can not be assigned a value—when a new record is created, EDB automatically assigns these fields a number, starting at 1 (they are unsigned). - SQLite3: SQLite keeps track of the largest ROWID that a table has ever held using an internal table named \\\'sqlite_sequence\\\'. The sqlite_sequence table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. Minor Uniface supported DBMSes: - Sequential: Uniface counter with standard name? - Text: Uniface counter with standard name? - Informix: serial - RdB: Sequence and sequence generator - RMS: Uniface counter with standard name? - LDAP: N/A Other DBMSes: - Microsoft Jet SQL: Counter - Access: AUTOINCREMENT keyword - PostgreSQL: serial, 4 bytes autoincrementing integer 1 to 2147483647 - FireBird: generator = sequence: if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_T1_ID, 1);

Importance


Low | Medium

Type


Database Drivers

Operating System


Not Applicable

Status


Open

6 thoughts on “Packing code A for all DBMS drivers”

  1. This is probably a pre-requisite of my wishlist request (2261) “Support for technical keys.”
    I understand that the A packing code still doesn’t allow for the incremented field to be the primary key of the table as far as uniface is concerned…

  2. Iain,

    I was used to trap 0129 in the on error trigger of the field being the PK.
    letting the DBMS filling automatically that field later on insert.
    Have you tried this already?

  3. My sentence is not strictly related to SYB but generally to tech key usage.
    Two ways are already currently in place:
    1) Using SYB and A packing code AFAIK means to fill the identity field with a specific string like @@identity
    2) Simply using Uniface it is possibile to trap 0129 and 2206 at field level, filling with a counter mechanism the PK field into the write trigger
    I am simply hoping to have for this mechanism a larger set of choices.

  4. Importance Low/Medium? In 2015 – are you actually serious?

    In these days of modern ORMs that expect identity columns by default and given that most current database design tends to favour technical keys over natural keys it’s unacceptable that Uniface only supports this behaviour for Sybase, ignoring databases with a much larger market share.

    When web developers or C# developers using modern development approaches regularly suggest teh sensible use of identity column primary keys in database tables we share access to, I’m getting tired of the constant embarrassment of telling them they instead have to use a crippled approach to primary keys “because of Uniface”. It doesn’t make us look profesional and it doesn’t reflect well Uniface.

    Even Uniface’s own 2014 reference implementation of Pet Plaza (http://www.uniface.com/wp-content/uploads/2014/10/wp_petstore_01.pdf) has to use an ugly stored procedure based hack to accomplish this.

    In an organisation of any reasonable size, Uniface will not be the only technology with access to databases. It can be expected to share the same data tables with SQL, Perl, .NET, Java, Ruby, Python or many other technologies. Many of these will likely use ORMs as part of their design which would rightly expect identity fields to be available for use.

    Not supporting identity columns in anything but Sybase means that all non-Uniface platforms then also need to use the same nasty hacks to perform a simple table insert with a numeric primary key.

    Your own website describes Uniface as an “Advanced Development Technology”. At the very least this means Uniface should be supporting one of the most fundamental aspects of the major RDBMSs.

    1. In the absence of a comment editing facility here, please chalk the obvious spelling errors in my prior comment up to my present disbelief and anger at the implications of this issue.

      I’m now having to implement a dynamic SQL stored procedure that will let me perform individual inserts on any specified table (populating any non-null columns with dummy data) and return the new identity values for Uniface to retrieve the record for editing only after the insert has occurred.

      This is again a horrible “solution” to the limitations of the MSS driver but at least it would allow true identity columns to be used in the database and keep the ugliness of the hack inside of Uniface, allowing our other environments to perform database inserts properly.

Leave a Reply