4 Replies Latest reply on Feb 7, 2008 6:43 AM by (jerome_moussalli)

    How to use Php a function in an insert transaction ?

      Hi,

      I want to insert a computed data (string) based on a date (dd-mm-aaaa hh:mm:ss).

      The result must be AAAAMM Year+month without spaces.

      So i tried this :

      $ins_agenda->addColumn("aaaa_mm", "string", "POST", "date({date_debut_effective},'Ym')");

      But it failed.

      Is it possible to use Php fonction with an appropriated syntax ?

      Thanks for your help. JM.
        • 1. Re: How to use Php a function in an insert transaction ?
          Günter Schenk Level 4
          Hi Jerome,

          I´d rather do that with a Custom BEFORE Trigger, like this:

          function Trigger_Custom(&$tNG) {
          $date_field = $tNG->getColumnValue("aaaa_mm");
          $tNG->setColumnValue("aaaa_mm", date($date_field,'Ym') );
          }

          I *think* this should work -- does it ?

          Cheers,
          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: How to use Php a function in an insert transaction ?
            Level 1
            Thanks Günther,

            I modified your example a bit because aaaa_mm is a varchar(6)
            and $date_debut_effective a form field with date format :

            function Trigger_Custom_aaaamm(&$tNG) {
            //$date_field = $tNG->getColumnValue("aaaa_mm");
            $tNG->setColumnValue("aaaa_mm", date($date_debut_effective,'Ym') );
            }

            And then, in my insert transaction :

            // Make an insert transaction instance
            $ins_agenda_previsionnel = new tNG_insert($conn_cnxWarh);
            $tNGs->addTransaction($ins_agenda_previsionnel);
            // Register triggers
            $ins_agenda_previsionnel->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "");
            $ins_agenda_previsionnel->registerTrigger("BEFORE", "Trigger_Custom_aaaamm", 98);
            $ins_agenda_previsionnel->registerTrigger("AFTER", "Trigger_LinkTransactions3", 98);
            $ins_agenda_previsionnel->registerTrigger("ERROR", "Trigger_LinkTransactions3", 98); //liste IRP
            // Add columns
            $ins_agenda_previsionnel->setTable("agenda_previsionnel");
            $ins_agenda_previsionnel->addColumn("id_agent", "NUMERIC_TYPE", "VALUE", "{rsfiche_agent.id_agent}");

            $ins_agenda_previsionnel->addColumn("aaaa_mm", "STRING_TYPE", "CURRVAL", "aaaa_mm");

            $ins_agenda_previsionnel->addColumn("titre", "STRING_TYPE", "VALUE", "ENTREE AGENT");
            $ins_agenda_previsionnel->addColumn("evenement", "STRING_TYPE", "VALUE", "Entrée Agent {Vue_Visu.prenom} {Vue_Visu.nom_complet} ");
            $ins_agenda_previsionnel->addColumn("deb_eve", "DATE_TYPE", "POST", "date_debut_effective");
            $ins_agenda_previsionnel->addColumn("fin_eve", "DATE_TYPE", "VALUE", "{Vue_Visu.date_fin_contrat}");
            $ins_agenda_previsionnel->addColumn("frequence", "STRING_TYPE", "VALUE", "1");
            $ins_agenda_previsionnel->setPrimaryKey("id_agenda", "NUMERIC_TYPE");

            I got no error but the aaaa_mm field in the resulting row is empty.

            Any idea ?
            • 3. Re: How to use Php a function in an insert transaction ?
              Günter Schenk Level 4
              Jerome,

              I see that the field "date_debut_effective" is part of the insert transaction, so the Custom Trigger should assumingly reference the transaction value this way:

              $date_field = $tNG->getColumnValue("date_debut_effective");
              $tNG->setColumnValue("aaaa_mm", date($date_field,'Ym') );

              works better ?

              Cheers,
              Günter Schenk
              Adobe Community Expert, Dreamweaver
              • 4. Re: How to use Php a function in an insert transaction ?
                Level 1
                I get this :

                tNG_fields.getColumnValue:
                Column date_debut_effective is not part of the current transaction

                In fact, date_debut_effective is the form field name, deb_eve is the database field name (transaction name).

                aaaa_mm is another database field in the same table, same transaction.

                aaaa_mm should only be computed with deb_eve data.

                When I use deb_eve as transaction field name in the trigger, the targeted database field (aaaa_mm) remain empty after the insert.

                The logic should like this :

                Form field (format datetime)
                -> database X field deb_eve (same format datetime)
                -> computed database X field aaaa_mm (format aaaamm or Ym)

                I use this : $ins_agenda_previsionnel->addColumn("aaaa_mm", "STRING_TYPE", "CURRVAL", ""); to fill aaaa_mm field.

                My code :

                before trigger

                // date de début effective vers date aaaamm
                function Trigger_Custom_aaaamm(&$tNG) {
                $date_field = $tNG->getColumnValue(deb_eve);
                $tNG->setColumnValue("aaaa_mm", date($date_field,'Ym') );
                }

                insert transaction

                //*******************************************************

                // Make an insert transaction instance
                $ins_agenda_previsionnel = new tNG_insert($conn_cnxWarh);
                $tNGs->addTransaction($ins_agenda_previsionnel);
                // Register triggers
                $ins_agenda_previsionnel->registerTrigger("STARTER", "Trigger_Default_Starter", 1, "POST", "");
                $ins_agenda_previsionnel->registerTrigger("BEFORE", "Trigger_Custom_aaaamm", 98);
                $ins_agenda_previsionnel->registerTrigger("AFTER", "Trigger_LinkTransactions3", 98);
                $ins_agenda_previsionnel->registerTrigger("ERROR", "Trigger_LinkTransactions3", 98); //liste IRP
                // Add columns
                $ins_agenda_previsionnel->setTable("agenda_previsionnel");
                $ins_agenda_previsionnel->addColumn("id_agent", "NUMERIC_TYPE", "VALUE", "{rsfiche_agent.id_agent}");
                $ins_agenda_previsionnel->addColumn("aaaa_mm", "STRING_TYPE", "CURRVAL", "");
                $ins_agenda_previsionnel->addColumn("titre", "STRING_TYPE", "VALUE", "ENTREE AGENT");
                $ins_agenda_previsionnel->addColumn("evenement", "STRING_TYPE", "VALUE", "Entrée Agent {Vue_Visu.prenom} {Vue_Visu.nom_complet} ");
                $ins_agenda_previsionnel->addColumn("deb_eve", "DATE_TYPE", "POST", "date_debut_effective");
                $ins_agenda_previsionnel->addColumn("fin_eve", "DATE_TYPE", "VALUE", "{Vue_Visu.date_fin_contrat}");
                $ins_agenda_previsionnel->addColumn("frequence", "STRING_TYPE", "VALUE", "1");
                $ins_agenda_previsionnel->setPrimaryKey("id_agenda", "NUMERIC_TYPE");

                //*******************************************************