NULL values treatment

aziegler3

I inherited an old website that uses datatables. Old, probably ten years old. I just uploaded the configuration, the uploaded code is ojuxax.
That datatable and editor work against an Oracle database. The table in that database has a few Not NULL columns.
The Editor in the datatable has an editor that sends create queries where some of the fields in the table are not included. Say, out of 15 columns, only 12 are in the query.
When the Editor in the datatables sends CREATE queries to the table. The table has a trigger that in insert fills the values that were not provided by the datatables editor create.
It needs to be done that way because the table has a geographic component that has some other implications that there is no need to discuss here
It works. It can create new rows as right now.

I want to modernize that website and I am implementing everything with the latest versions of datatables.
I have a test website where I can recreate the query 100% identical to the one in the old website.
However, this datatables editor with the latest version gives me back a ORACLE message complaining about the not NULL fields missing in the query

My sense is that there is something in the new datatables Editor that handles NULL, or not listed values in a different manner.
So, I need to find a way for how to handle those not null values.

Any wisdom on this?

allan

I'm afraid nothing immediately springs to mind here. Are you using our PHP libraries? Could you add ->debug(true) just before the ->process(...) call, and then show me the JSON response for when you do an edit please? It will show the SQL that is being generated and executed. That should give me a better idea of what is going on.

Thanks,
Allan

aziegler3

Here are the JSON responses.
create and edited for both the old (that works) and the new (that does not work).

Just one more comment. Multiple editors, with create, delete and edit work on this website work.
The only one that does not work is the create with one of two NOT NULLfields not included in the query.
In the old website, the fields that are not included in the query get filled by a trigger in the database. The trigger kicks in ON INSERT.

aziegler3

old version
create query  -> does not work
{
    "id":"row_34127",
    "error":"",
    "fieldErrors":[],
    "data":[],
    "row":
        {
        "DT_RowId":"row_34127",
        "OBJECTID":"34127",
        "STATE":"ND",
        "OB_DATE":"14-Dec-20",
        "SITE_ID":"5401",
        "SITE_NAME":"oscar dec 14",
        "SITE_LOCATION":"oscar dec 14",
        "POINT_Y":"46",
        "POINT_X":"-100",
        "SIZE_WOODED_AREA":"0",
        "ASH_PCT":null,
        "OVERSTORY_AB_SP1":null,
        "OVERSTORY_AB_SP2":null,
        "OVERSTORY_AB_SP3":null,
        "EAB_DENSITY":null,
        "PLOT_TYPE":"Release",
        "TOPOGRAPHIC_POSITION":null,
        "FLOODING":null,
        "DEGREE_ISOLATION":null,
        "SITE_ID_OF_PAIR_RC":null,
        "COMMENTS":null,
        "STATUS":"Proposed",
        "USERID":"oscar5",
        "CONTROL":"0",
        "ORIGIN":"Program"
        }
}

new version
create query -> does not work
{
"fieldErrors":[],
"error":"Oracle SQL error: ORA-01400: cannot insert NULL into (\"GIS\".\"APHIS_RELEASE_SITES\".\"GLOBALID\")",
"data":[],
"ipOpts":[],
"cancelled":[]}

old version
edit query -> it works
    {
    "id":"row_34127",
    "error":"",
    "fieldErrors":[],
    "data":[],
    "row":
    {
        "DT_RowId":"row_34127",
        "OBJECTID":"34127",
        "STATE":"MI",
        "OB_DATE":"14-Dec-20",
        "SITE_ID":"5401",
        "SITE_NAME":"oscar dec 14 edited",
        "SITE_LOCATION":"oscar dec 14",
        "POINT_Y":"46",
        "POINT_X":"-100",
        "SIZE_WOODED_AREA":"0",
        "ASH_PCT":null,
        "OVERSTORY_AB_SP1":null,
        "OVERSTORY_AB_SP2":null,
        "OVERSTORY_AB_SP3":null,
        "EAB_DENSITY":null,
        "PLOT_TYPE":"Release",
        "TOPOGRAPHIC_POSITION":null,
        "FLOODING":null,
        "DEGREE_ISOLATION":null,
        "SITE_ID_OF_PAIR_RC":null,
        "COMMENTS":null,
        "STATUS":"Proposed",
        "USERID":"oscar5",
        "CONTROL":"0",
        "ORIGIN":"Program"
    }
}

edit with new version --> it works 
{"data":
    [
        {
            "DT_RowId":"row_33833",
            "OBJECTID":"33833",
            "STATE":"MI",
            "OB_DATE":null,
            "SITE_ID":"5369",
            "SITE_NAME":"oscar1 edited",
            "SITE_LOCATION":"la casa",
            "PLOT_TYPE":"Release",
            "ORIGIN":"Program",
            "STATUS":"Proposed",
            "USERID":"oscar5",
            "POINT_Y":"-100",
            "POINT_X":"46",
            "SIZE_WOODED_AREA":"0",
            "ASH_PCT":null,
            "OVERSTORY_AB_SP1":null,
            "OVERSTORY_AB_SP2":null,
            "OVERSTORY_AB_SP3":null,
            "EAB_DENSITY":null,
            "TOPOGRAPHIC_POSITION":null,
            "FLOODING":null,
            "DEGREE_ISOLATION":null,
            "SITE_ID_OF_PAIR_RC":null,
            "GLOBALID":"{47B3435B-5EBB-4F5E-B515-E96FFFBDA18A}",
            "COMMENTS":null
        }
    ]
}

aziegler3

What is really interesting is that the old script does not contain the variable GLOBALID, neither the variable OBJECTID. Noneless, they get created on insert.
The new script does not creates them on insert and just says " i need objected and globalid

allan

To confirm my understanding, both GLOBALID and OBJECTID should be filled in by triggers. Is that right?

If so, in your PHP code (assuming you are using our Editor libraries) add ->set(false) to the Field for each of those two columns. That will stop Editor attempting to write to them at all and let the triggers do what they need to do.

Allan

aziegler3

Thanks Allan. I added the ->set(false).
I can exactly see in the JSON response how it works and how it ignores the GLOBALID field and how it does not when ->set(false) is not there.
However, the Editor still does not work. and still complains about the field being NULL.
I know that common sense would say that something is wrong with my trigger, and I would accept that answer, if the old website, with the old Editor would not work, but it does.
I see how this is a weird question.

allan

So to confirm - when you insert a row (with the ->set(false)) it will tell you the field cannot be null. But the trigger should be filling it in?

Is the trigger running at the right time - i.e. BEFORE INSERT (or whatever the Oracle equivalent is)? I'm wondering if we've tightened up the SQL we generate and it used to insert an empty value or something, but no longer.

Can you show me the INSERT that is being generated and also your SQL trigger code?

Thanks,
Allan

aziegler3

How can I show yo u the insert?
Is this what you are talking about?

this is the one that does not work in a recent Editor version

 {
     "action":"create",
     "data":
     {"0":
        {
            "STATUS":"Proposed",
            "STATE":"AB",
            "OB_DATE":"16-Jan-2021",
            "SITE_NAME":"oscar1",
            "SITE_LOCATION":"la casa",
            "POINT_Y":"45",
            "POINT_X":"-100",
            "PLOT_TYPE":"Release",
            "ORIGIN":"a",
            "COMMENTS":"nnnnnnn",
            "OVERSTORY_AB_SP1":"a",
            "EAB_DENSITY":"1",
            "ASH_PCT":"1",
            "SIZE_WOODED_AREA":"0",
            "USERID":"oscar"
        }
     }
}

this is the one that does work, in a very old version of Editor

{
    "action":"create","table":"","id":"",
    "data":
        {
        "STATUS":"Proposed",
        "STATE":"AB",
        "OB_DATE":"14-Jan-21",
        "SITE_NAME":"oscar 1 15",
        "SITE_LOCATION":"mi casa",
        "POINT_Y":"45",
        "POINT_X":"-100",
        "PLOT_TYPE":"Release",
        "ORIGIN":"Program",
        "COMMENTS":"",
        "OVERSTORY_AB_SP1":"",
        "EAB_DENSITY":"",
        "ASH_PCT":"",
        "SIZE_WOODED_AREA":0,
        "USERID":""
        }
}

aziegler3

This is the trigger

TRIGGER GIS.PROD2_APHIS_REL2_SITE_TRIG
  FOR INSERT ON GIS.APHIS_RELEASE_SITES
COMPOUND TRIGGER

  TYPE t_change_rec IS RECORD (
    objectid            aphis_release_sites.objectid%TYPE,
    state               aphis_release_sites.state%TYPE,
    site_id             aphis_release_sites.site_id%TYPE,
    origin              aphis_release_sites.origin%TYPE,
    plot_type           aphis_release_sites.plot_type%TYPE
  );


  TYPE t_change_tab IS TABLE OF t_change_rec;
  g_change_tab  t_change_tab := t_change_tab();

    x NUMBER;
    y NUMBER;
    grid_size NUMBER  := 50.0;
    arcsec_in_meters NUMBER  := 30.9;
    max_distance_PR  NUMBER := 500000.0;  -- max distance between pre-release and release site
    max_distance_RC  NUMBER := 500000.0;  -- max distance between release and control site

    llong NUMBER;
    llat  NUMBER;
    llrc  NUMBER;

    regid_grids NUMBER;

    siteid          aphis_release_sites.site_id%TYPE;
    psitename       aphis_release_sites.site_name%TYPE;
    puserid         aphis_release_sites.userid%TYPE;
    porigin         aphis_release_sites.origin%TYPE;
    plocation       aphis_release_sites.site_location%TYPE;
    pairsiteid      aphis_release_sites.site_id_of_pair_rc%TYPE;
    pstate          aphis_release_sites.state%TYPE;
    pstatus         aphis_release_sites.status%TYPE;

    control_first   NUMBER;

  AFTER EACH ROW IS
  BEGIN
    g_change_tab.extend;
    g_change_tab(g_change_tab.last).objectid     :=   :new.objectid;
    g_change_tab(g_change_tab.last).state        :=   :new.state;
    g_change_tab(g_change_tab.last).site_id      :=   :new.site_id;
    g_change_tab(g_change_tab.last).origin       :=   :new.origin;
    g_change_tab(g_change_tab.last).plot_type    :=   :new.plot_type;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN

    SELECT REGISTRATION_ID INTO regid_grids FROM sde.TABLE_REGISTRY where TABLE_NAME='APHIS_GRIDS_50M_ST';

    FOR k IN g_change_tab.first .. g_change_tab.last LOOP

            select a.shape.minx, a.shape.miny, site_name, userid into x,y, psitename, puserid
            from aphis_release_sites a
            where a.objectid = g_change_tab(k).objectid;

        -- compute site_id if coming from Getac (empty value): assign site_id and default values of closest prerelease site.

        IF g_change_tab(k).site_id IS NULL OR g_change_tab(k).site_id < 0 THEN

            select APHIS_SEQUENCE.nextval into siteid from dual;
            select a.STATE_ABBR into pstate from EAB_STATE_BNDS a where sde.st_envintersects(sde.st_geometry('POINT(' || x || ' ' || y || ')',4326), sde.st_envelope(a.shape)) = 1 and sde.st_within( sde.st_geometry('POINT(' || x || ' ' || y || ')',4326), a.shape) = 1;

            IF g_change_tab(k).plot_type = 'Release' THEN

                update aphis_release_sites
                    set
                        site_id = siteid,
                        state = pstate,
                        point_x = x,
                        point_y = y,
                        status = 'Proposed',
                        control = 0
                    where objectid = g_change_tab(k).objectid;

                -- in case the control site got processed first.
                update aphis_release_sites
                set site_id_of_pair_rc = ( select site_id
                                            from aphis_release_sites a
                                            where a.plot_type = 'Control' AND a.site_id_of_pair_rc = siteid )
                where objectid = g_change_tab(k).objectid AND
                exists ( select site_id
                        from aphis_release_sites a
                        where a.plot_type = 'Control' AND a.site_id_of_pair_rc = siteid );

                -- REPORT TO APHIS
                --EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''35.8.202.88''';
                UTL_MAIL.send(sender     => 'webmaster@asets.msu.edu',
                    recipients => 'EAB.Biocontrol.Program@aphis.usda.gov',
                    cc => 'alerts@asets.msu.edu',
                    subject    => 'New Site',
                    message    => 'A new site has been submitted for approval.' || CHR(13) || CHR(10) || CHR(13) || CHR(10) || 'The assigned site name is "' || psitename || '" and the site identifier is: ' || siteid  || CHR(13) || CHR(10) || CHR(13) || CHR(10) ||'You can access the corresponding data by logging onto: '  || CHR(13) || CHR(10) || 'http://www.mapbiocontrol.org  '|| CHR(13) || CHR(10) || CHR(13) || CHR(10) || 'On the "Release" page search by SITE ID, SITE NAME or simply enter "Proposed" in the search box.' || CHR(13) || CHR(10) || CHR(13) || CHR(10) || 'MapBioControl WebMaster');

            ELSIF  g_change_tab(k).plot_type = 'Control' THEN   -- handle a control site entered via the Getac

                llrc  := (max_distance_RC / arcsec_in_meters) / 3600 ;

                select site_id into pairsiteid
                from (  SELECT site_id, sde.st_distance(a.shape,sde.st_geometry('POINT(' || x || ' ' || y || ')',4326)) AS DISTANCE
                        FROM aphis_release_sites a
                        WHERE sde.st_intersects(a.shape,sde.st_buffer(sde.st_geometry('POINT(' || x || ' ' || y || ')',4326),llrc)) = 1
                        AND a.plot_type = 'Release'
                        order by DISTANCE )
                WHERE rownum = 1 ;


                update aphis_release_sites
                set
                    site_id = siteid,
                    site_id_of_pair_rc = pairsiteid,
                    state = pstate,
                    point_x = x,
                    point_y = y,
                    control = 0,
                    status = 'Approved'
                where objectid = g_change_tab(k).objectid;

            -- the exists statement checks only that records are returned, without calling the distance function for speed

                update aphis_release_sites
                set site_id_of_pair_rc = siteid
                where site_id = pairsiteid;

            END IF;
        END IF;

        llat  := (grid_size / arcsec_in_meters) / 3600 ;
        llong := llat / COS(y * 3.141592 / 180) ;

        -- draw grid of 8 x 8 cells of 50 m each
        -- coming from website, create the grid here, otherwise it is coming from Getac already created; updated with its own trigger
        FOR i IN 1..8 LOOP
            FOR j IN 1..8 LOOP
                DBMS_OUTPUT.PUT_LINE ('inside loop ' || i  || ' ' || j);
                INSERT INTO aphis_grids_50m_ST (objectid,
                                               state_abbr,
                                               id_1,
                                               control,
                                               origin,
                                               site_id,
                                               globalid,
                                               shape)
                    VALUES (sde.version_user_ddl.next_row_id ('GIS', regid_grids),
                                                g_change_tab(k).state,
                                                CHR(ASCII('A')+8-j) || to_char(i),
                                                0,
                                                g_change_tab(k).origin,
                                                siteid,
                                                sde.version_user_ddl.retrieve_guid,
                                                sde.st_geometry('POLYGON ((' || to_char(x + (i-5)*llong) || ' ' || to_char(y + (j-5)*llat) || ', '
                                         || to_char(x + (i-5)*llong) || ' ' || to_char(y + (j-4)*llat) || ', '
                                         || to_char(x + (i-4)*llong) || ' ' || to_char(y + (j-4)*llat) || ', '
                                         || to_char(x + (i-4)*llong) || ' ' || to_char(y + (j-5)*llat) || ', '
                                         || to_char(x + (i-5)*llong) || ' ' || to_char(y + (j-5)*llat) || '))' ,4326)
                                                );
               END LOOP;
        END LOOP;

    END LOOP;

    g_change_tab.delete;
  END AFTER STATEMENT;

END prod2_aphis_rel2_site_trig;