jhulbert

jhulbert

  • Name: Jim Hulbert
  • Favorite Languages: Oracle Apex and Flex
  • Website: [not set]
  • Location: Monument, Colorado
  • About Me:
    Have been with Oracle Support for 14 years. Currently learning/experimenting with Flex.

Recent Comments

  • Flex, PHP, JSON, MySQL - Advanced Updating
    04/17/2009 - 09:21

    Thanks. Let me know if there is a better way to do this.
    This seems to be working nicely though.
    Jim

  • Flex, PHP, JSON, MySQL - Advanced Updating
    04/15/2009 - 19:04

    The Fattest - Thanks for not answering this question for me right away. It caused me to stew over it for quite some time and actually learn how the arrays are working more thoroughly.

    I have resolved the mystery of inserting a row or several.

    What I had to do was to insert the blank row into the dataGrid and into the dataToUpdate array after assigning the channel_id a value on the client side and not trying to assign the value on the server side. This would allow the row to be inserted and then updated from the same sendPHPData call. This works because the php code will walk through the json array and execute the sql statements in the correct order. So, first the row will be inserted with CHANNEL_ID = the next channel_id, NAME='Enter Name', and DESCRIPTION='Enter Desc'. Then it will get to the update statement (if the user changed the name and description to something meaningful) and perform the update.

    Hopefully this explains how it is working.

    Here is the mxml dataChanged function with the CollectionEventKind.ADD code block added:

    private function dataChanged(event:CollectionEvent):void
    {
      var item:Object;
      var updatedObj:Object;
      var i:int;
      var x:int;
      if(event.kind == CollectionEventKind.UPDATE)
      {
        for(i = 0; i < event.items.length; i++)
        {
         item = event.items[i];
         updatedObj = new Object;
         updatedObj.CHANNEL_ID = item.source.CHANNEL_ID;
         updatedObj.property = item.property;
         updatedObj.value = item.source[item.property];
         dataToUpdate.push(updatedObj);
        }
     }
     else if(event.kind == CollectionEventKind.REMOVE)
     {
       for(i = 0; i < event.items.length; i++)
       {
         item = event.items[i];
         updatedObj = new Object;
         updatedObj.DELETED = item.CHANNEL_ID
         dataToUpdate.push(updatedObj);
       }
     }
     else if(event.kind == CollectionEventKind.ADD)
     {
       for(i = 0; i < event.items.length; i++)
       {
        item = event.items[i];

      // Setting x to the last number that was in the grid
      // before the new row was added.  The dataAC array is
      // 0 based where my length is 1 based, so I have to
      // subtract 2 to get to the correct row.  This does
      // assume that the records are sorted by channel_id
      // ascending.
        x = channelsAC.getItemAt(dataAC.length-2).CHANNEL_ID;
        x += 1;

      // Setting the new channel_id in the DG via the
      // dataProvider array dataAC
        dataAC.getItemAt(dataAC.length-1).CHANNEL_ID = x
        updatedObj = new Object;
        updatedObj.INSERTED = "1";
        updatedObj.CHANNEL_ID = x;
        updatedObj.NAME = event.items[i].NAME;
        updatedObj.DESCRIPTION = event.items[i].DESCRIPTION;
        dataToUpdate.push(updatedObj);
       }
     }
    }
    ...

    and the updated PHP code snippet is now as follows:

    elseif (isset($_GET['updateChannels'])) {
      if ($db_conn=OCILogon("schema_name", "password",
          "tnsnames_entry.world"))
      {
        $jsonString = urldecode($_GET['jsonSendData']);
        $jsonString = str_replace("\\", "", $jsonString);
        $data = json_decode($jsonString, true);
        foreach ($data as $channelEntry)
        {
         if(isset($channelEntry['INSERTED']))
         {
           //Handle inserted entry
           $query = sprintf(
           'INSERT INTO NEWS_CHANNELS
            values (to_number(\'%s\'),\'%s\',\'%s\')'    ,
           $channelEntry['CHANNEL_ID'],
           $channelEntry['NAME'],
           $channelEntry['DESCRIPTION']);
         }
         elseif(!isset($channelEntry['DELETED']))
         {
           //Handle updated entry
           $query = sprintf(
           'UPDATE NEWS_CHANNELS
           SET %s = \'%s\' WHERE CHANNEL_ID = %d'    ,
           $channelEntry['property'],
           $channelEntry['value'],
           $channelEntry['CHANNEL_ID']);
         }
         elseif(isset($channelEntry['DELETED']))
         {
           // Handle deleted entry
           $query = sprintf(
           'DELETE FROM NEWS_CHANNELS
           WHERE CHANNEL_ID = to_number(\'%s\')'    ,
           $channelEntry['DELETED']);
         }
         $parsed = ociparse($db_conn, $query);
         $result = oci_execute($parsed,OCI_COMMIT_ON_SUCCESS);
    ...

    This now works great. Hope this has helped someone else that was running into this same problem.

  • Flex, PHP, JSON, MySQL - Advanced Updating
    04/15/2009 - 18:54

    You are welcome.

    The only thing I am missing now is how to pass an inserted row back to my PHP file and have it inserted into the DB.
    For some reason it doesn't appear that (event.kind == CollectionEventKind.ADD) is working.

    I have updated the mxml dataChanged function to be as follows:

    private function dataChanged(event:CollectionEvent):void
    {
      var item:Object;
      var updatedObj:Object;
      var i:int;
      if(event.kind == CollectionEventKind.ADD)
      {
        for(i = 0; i < event.items.length; i++)
       {
         updatedObj = new Object;
         updatedObj.INSERTED = "1";
         updatedObj.NAME = item.source.NAME;
         updatedObj.DESCRIPTION = item.source.DESCRIPTION;
         dataToUpdate.push(updatedObj);
       }
     }
     else if(event.kind == CollectionEventKind.UPDATE)
     {
       for(i = 0; i < event.items.length; i++)
       {
         item = event.items[i];
         updatedObj = new Object;
         updatedObj.CHANNEL_ID = item.source.CHANNEL_ID;
         updatedObj.property = item.property;
         updatedObj.value = item.source[item.property];
         dataToUpdate.push(updatedObj);
        }
      }
      else if(event.kind == CollectionEventKind.REMOVE)
      {
        for(i = 0; i < event.items.length; i++)
        {
          item = event.items[i];
          updatedObj = new Object;
          updatedObj.DELETED = item.CHANNEL_ID
          dataToUpdate.push(updatedObj);
        }
      }
    }
    ...

    and the PHP code snippet to:

    elseif (isset($_GET['updateChannels'])) {
      if ($db_conn=OCILogon("schema_name", "password",
          "tnsnames_entry.world")) {
        $jsonString = urldecode($_GET['jsonSendData']);
        $jsonString = str_replace("\\", "", $jsonString);
        $data = json_decode($jsonString, true);
        foreach ($data as $channelEntry) {
          if(isset($channelEntry['INSERTED'])) {
            //Handle inserted entry
            $query = sprintf(
            'INSERT INTO NEWS_CHANNELS
             values ((select max(CHANNEL_ID)+1
                      from NEWS_CHANNELS),\'%s\',\'%s\')'    ,
            $channelEntry['NAME'],
            $channelEntry['DESCRIPTION']);
          }
          elseif(!isset($channelEntry['DELETED'])) {
             //Handle updated entry
             $query = sprintf(
            'UPDATE NEWS_CHANNELS
             SET %s = \'%s\' WHERE CHANNEL_ID = %d'    ,
             $channelEntry['property'],
             $channelEntry['value'],
             $channelEntry['CHANNEL_ID']);
          }
          elseif(isset($channelEntry['DELETED'])) {
            // Handle deleted entry
            $query = sprintf(
            'DELETE FROM NEWS_CHANNELS
             WHERE CHANNEL_ID = to_number(\'%s\')'    ,
            $channelEntry['DELETED']);
          }
          $parsed = ociparse($db_conn, $query);
          $result = oci_execute($parsed,OCI_COMMIT_ON_SUCCESS);
    ...

    However, it fails and the "INSERTED" is never updated so it falls in to the first else if section.
    So, it appears that this isn't working at all.

    Can you provide any guidance here?
    I am so close to having this all working, but I have to get the insert.

    Thanks in advance!
    Jim

  • Flex, PHP, JSON, MySQL - Advanced Updating
    04/15/2009 - 16:59

    It appears that the main problem I am having with the row insert is that the array sent to the PHP file contains a record for each item. How can I make it contain a row with the values for each column in one row? Do I have to send it over on a per item basis or can it be sent as an array of full rows? If it can be sent as full rows, please tell me how.

    I have tried to not loop though the items as in the update, and just set:

    updatedObj.NAME = item.source.NAME
    updatedObj.DESCRIPTION = item.source.DESCRIPTION;

    This didn't work either.
    Please help me when you get a chance.

    Thanks - Jim

  • Flex, PHP, JSON, MySQL - Advanced Updating
    04/13/2009 - 16:52

    You can also check out the other OCI8 Functions here:
    PHP: OCI8 Functions - Manual

  • Flex, PHP, JSON, MySQL - Advanced Updating
    04/13/2009 - 16:41

    I have just completed a very similar example using an Oracle DB instead of MYSQL DB.
    The example I created was very simple using a table with 3 columns: channel_id, name and description.
    The code is almost identical, but I changed the names of a few items to meet my needs. (ie. updateTutorials changed to updateChannels). Pretty much everything else is the same. However, Oracle does capitalize the column names that it returns, so keep that in mind.
    The PHP file is the only thing that needs to change for Oracle vs MYSQL, because you have to make OCI_% calls in place of the MYSQL_% calls.

    Here is the PHP code for an Oracle DB. I hope it is helpful:

    <?php
    if (isset($_GET['getChannels'])) {
      if ($db_conn=OCILogon("schema_name", "password", "tnsnames_entry.world")) {
        $cmdstr = "select * from news_channels";
        $parsed = ociparse($db_conn, $cmdstr);
        ociexecute($parsed);
        $returnArray = array();
        while ($row = oci_fetch_assoc($parsed)) {
          array_push($returnArray, $row);
         }
        OCILogoff($db_conn);
        echo json_encode($returnArray);
      }
    }
    elseif (isset($_GET['updateChannels'])) {
      if ($db_conn=OCILogon("schema_name", "password", "tnsnames_entry.world")) {
        $jsonString = urldecode($_GET['jsonSendData']);
        $jsonString = str_replace("\\", "", $jsonString);
        $data = json_decode($jsonString, true);

        foreach ($data as $channelEntry) {
          if(!isset($channelEntry['deleted'])) {
             //Handle updated entry
             $query = sprintf(
            'UPDATE NEWS_CHANNELS
             SET %s = \'%s\' WHERE CHANNEL_ID = %d'    ,
             $channelEntry['property'],
             $channelEntry['value'],
             $channelEntry['CHANNEL_ID']);
          }
          else {
            // Handle deleted entry
            $query = sprintf(
            'DELETE FROM NEWS_CHANNELS
             WHERE CHANNEL_ID = to_number(\'%s\')'    ,
            $channelEntry['deleted']);
          }
          $parsed = ociparse($db_conn, $query);
          $result = oci_execute($parsed);
          if (!$result) {
            $err = OCIError();
            OCILogoff($db_conn);
            echo $err[text];
            return;
          }
          else {
            echo "Database Updated";
          }
        }
        OCILogoff($db_conn);
      }
    }
    else {
       $err = OCIError();
       echo "Oracle Connect Error " . $err[text];
       OCILogoff($db_conn);
    }
    ?>

Sponsors