Announcement

Collapse
No announcement yet.

GUID primary key on INSERT

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • GUID primary key on INSERT

    I always use GUIDs as primary keys on my tables. It's amazing how many problems they solve. In SC8 I have a table that I want my "app" to generate it's own GUID for my OBJ_GUID primary key field, just before doing an INSERT. How can I accomplish this? I've set the field to DB Insert Value "Autoincrement - Manual". I'm assuming I'm supposed to do something in the "Events - onBeforeInsert", but I can't find a sc_macro to generate a GUID. Please tell me what to do and give me a code snippet. Those help a lot. Thanks in advance. (That word "increment" scares me...as if it's expecting me to generate an integer).....(I would rather start smoking cigarettes and ruin my health before building another table with an Integer primary key)

  • #2
    2 questions...

    1.- does your Database selfgenerates your guid's?
    2.- If not how do you generate them?.


    My questions are due to the following:

    1.- the main problem with GUID's is that they are not secuential... which means you have to generate them everysingle time. therefor commonly you would create some sort of function or procedure to create a guid depending on your db engine...

    2.- php has its own GUID generating funciton call com_create_guid().. for example

    PHP Code:

    echo uuid_create(); // this need the PECL uuid php extension to be enabled. 
    so you would have to assing the id value in the onbeforeinsert event.

    check this KB entry

    http://php.net/manual/en/function.com-create-guid.php

    Hope this helps.

    Regards
    Last edited by kafecadm; 03-14-2016, 10:45 PM.

    Comment


    • #3
      Hi Kafecadm,

      In answer to your questions:

      1) Does your Database GUIDs?

      Every table has an OBJ_GUID field. I call it that because in Delphi, I will often create objects (in memory) constructed from entries in a DB table. The objects need an ID which the GUID is perfect for, because I can be guaranteed that the object ID will be unique in both the table and the session memory of the running Windows .exe. Using an integer might cause a problem where the integer is unique in the DB but not in memory, or vis versa.

      Every table has a trigger on it, I always call it trigger 1, which tests to see if the NEW.OBJ_GUID is a blank string or NULL, in which case the trigger generates one for itself, but if a OBJ_GUID is coming in from an application (of which there are many, from different OS platforms, different languages, different dev tools)... the trigger will allow the incomming GUID to be used as the primary key.

      Here is my trigger:

      SET TERM ^ ;
      CREATE TRIGGER GOC_USR_DDYS_1 FOR GOC_USR_DDYS ACTIVE
      BEFORE INSERT POSITION 1
      AS
      BEGIN
      IF ((NEW.OBJ_GUID IS NULL) OR (NEW.OBJ_GUID = '')) THEN NEW.OBJ_GUID = UUID_TO_CHAR(GEN_UUID());
      END^
      SET TERM ; ^


      2.- If not how do you generate them?.

      See above.

      I will now read the link you have provided. Thanks for that.

      Comment


      • #4
        - How do I enable the PECL uuid php extension in SC8 ?

        Here is my best guess about what to put in the onBeforeInsert...is this correct?

        {OBJ_GUID} = uuid_create();

        But this won't work if I don't have the PECL uuid php extension installed in SC8, which I haven't done yet because I don't know now.

        Comment


        • #5
          WAIT! Hey... I don't need the extension really... all I have to do is call a stored procedure in the DB to get one. Yes this causes another DB hit, but maybe it's simpler than installing a PHP extension...that must also be installed on the production environment? Hmmmmm...trade offs.

          I have found the ext folder for SC. This looks like all the extension DLLs.

          C:\Program Files (x86)\NetMake\v81\components\php\ext

          I have found php.ini in the folder:

          C:\Program Files (x86)\NetMake\v81\components\php

          I examine it for extensions... looks a lot like Apache.config

          Found the "extension" lines, starting at line 878... says I need to download the PECL extension.... from where? What version?

          Hunting for which version of PECL I'm supposed to DL and install.
          Last edited by Boxer55; 03-14-2016, 11:47 PM. Reason: Thought process ongoing....

          Comment


          • #6
            Ok, I found that the php.exe is clearly marked as 5.6.8.0 in the Windows File Explorer File Version column. So that question is answered. When reading about extensions, I got scared because it looks like I'd have to depend on a specific DLL of a specific version, of a specific threading model, and it may not be supported for future versions etc. etc. Maybe for 100% reliability sake I'll just depend on my one stored procedure in my DB to hand me a new GUID.

            Comment


            • #7
              Hello:

              Well actually if what you say is true then you need to do nothing... check your logic:

              if you insert a new record into your table lets say it goes like

              PHP Code:
              INSERT INTO myTable (GUIDfield1,field2VALUES (NULL'Value 1''Value 2'
              then your trigger will detect the insert has no guid and then will create one for the record automatically. Solving your problem.

              Regards

              Comment


              • #8
                Hi, kafecadm, Yes, it is true that the record will be assigned a new GUID, however, it is my understanding that my ScriptCase app "wants to" or "needs to" "know" what the unique identifier is of the newly added record so it can open it and allow further edits. This, I assumed would be the reason I needed to set the ScriptCase field value of the OBJ_GUID just before the INSERT. If I just allow an insert to happen, and don't know the GUID that the new record was assigned, then there is a strong possibility that if more than one person (out of 4,000 users) happened to INSERT a new record of their own, there is a strong possibility that *my* new record would be displayed to some one else, and *I* would get some one else's record displayed to *me*.

                Comment


                • #9
                  Originally posted by Boxer55 View Post
                  Hi, kafecadm, Yes, it is true that the record will be assigned a new GUID, however, it is my understanding that my ScriptCase app "wants to" or "needs to" "know" what the unique identifier is of the newly added record so it can open it and allow further edits. This, I assumed would be the reason I needed to set the ScriptCase field value of the OBJ_GUID just before the INSERT. If I just allow an insert to happen, and don't know the GUID that the new record was assigned, then there is a strong possibility that if more than one person (out of 4,000 users) happened to INSERT a new record of their own, there is a strong possibility that *my* new record would be displayed to some one else, and *I* would get some one else's record displayed to *me*.

                  I'm not sure Scriptcase really needs to "Know" the just inserted record, and what you say would be true if you configure your application to go back to the just inserted record... is that the functionality you wanna use?.

                  Regards

                  Comment


                  • #10
                    I have not yet added a new record using a ScriptCase grid. I'm just adding some dummy records in the tables (using a different tool) so I have something to look at while configuring SC. I think I'll understand the process more when I start adding records. Based on what you say, the "Add New" process must be such that the newly added record window closes once I click "Add" or "Save" .. that way, knowing what the newly added unique identifier is, would not be necessary to "re-find" the record just added to the DB.

                    Comment

                    Working...
                    X