DB2 Select and insert based on bound parameters in php script

Question

I've been trying to slowly migrate a script that selects data, and inserts based on bound parameters into another table from mysql to db2.

I have most of it migrated, but this main portion is still failing to insert. My select is working, returning exactly what I expect. However, something is going wrong in the bit where I create the array or parameter values. I"m simply trying to iterate through the selected value rows and insert the values into a matching table.

I'm I using odbc_fetch_array incorrectly, or does it look like something's wrong with my bound parameters?

//Main query to select data
$data = "
  SELECT  
      u.extension
      , sum(duration) as total_talk_time_seconds
      , round(sum(duration) / 60,2) as total_talk_time_minutes
      , sum(case when legtype1 = 1 then 1 else 0 end) as total_outbound

  from SESSION a
    join call_summary b
      on a.notablecallid = b.notablecallid
    inner join system_USERS u
      on u.EXTENSION = CALLINGPARTYNO or u.EXTENSION = FINALLYCALLEDPARTYNO
  group by extension,u.user_id" or die(db2_conn_error($DB2Conn));


$stmt = "
  INSERT into daily_call_totals
    (extension,
    total_talk_time_seconds,
    total_talk_time_minutes,
    total_outbound)
  VALUES (?, ?, ?, ?)" or die(db2_conn_error($DB2Conn));

//create array for binding
$content = [];

$mainResult = odbc_exec($DB2Conn, $data);
while ($d = odbc_fetch_array($mainResult)) {

  $prepInsert = odbc_prepare($DB2Conn, $stmt);

  //for each row, bind param. This is to ensure we get the correct number of records whether they're being inserted or updated for duplicates
  $values = [
      $d['extension'],
      $d['total_talk_time_seconds'],
      $d['total_talk_time_minutes'],
      $d['total_outbound']];

  // Store the current row
  $content[] = $d;

  if($prepInsert){
      $result = odbc_execute($prepInsert,$values);
      if($result){
        print "successfully added record";
      }
    }
}

...this is from DB2 to DB2? DB2 (like every RDBMS I'm aware of) allows you to INSERT <data> FROM SELECT, which is faster and would involve no bound parameters. If you need to know which rows were inserted, you want the FINAL TABLE feature. If you need to perform some filtering first, though, obviously this won't work. Otherwise, we need to have a better idea of what the actual problem is. Are you getting an error? No results? Are you even getting rows to add? — Clockwork-Muse
– Clockwork-Muse, Commented Nov 5, 2018 at 19:24
So, I'm getting the rows, but when it gets to the part to insert the script just ends with no error thrown but when I select from the table it's empty showing no results were inserted — Geoff_S
– Geoff_S, Commented Nov 5, 2018 at 19:29
@Clockwork-Muse I think i resolved it by doing INSERT INTO...SELECT actually but my only hang up is I have a duplicate key constraint on two columns because this job runs throughout the day but should only create one record per day for each user. Maybe I can update it to use Merge — Geoff_S
– Geoff_S, Commented Nov 5, 2018 at 20:13
What are the two columns with dup key constraint? Are they in the append query? — Parfait
– Parfait, Commented Nov 5, 2018 at 22:29

Parfait · Accepted Answer · 2018-11-06 16:42:45Z

As mentioned consider an INSERT...SELECT and avoid the looped INSERT...VALUES. In fact, even consider the ANSI version (i.e., compliant in all SQL-supported DBMS's) of avoiding duplicates using the NOT EXISTS clause. But this will only run one action and not conditionally two actions.

Below can be run anytime and only unique pairings of date_of_report and extension will be appended, ignoring matches. Be sure to replace date_of_report and my_report_date_column for actual columns as you never explicitly do so in aggregate query.

INSERT into daily_call_totals
        (date_of_report,                                  -- UPDATE COLUMN
         extension,
         total_talk_time_seconds,
         total_talk_time_minutes,
         total_outbound)
SELECT
      my_report_date_column,                              -- UPDATE COLUMN
      u.extension
      , sum(duration) as total_talk_time_seconds
      , round(sum(duration) / 60,2) as total_talk_time_minutes
      , sum(case when legtype1 = 1 then 1 else 0 end) as total_outbound

FROM SESSION a
JOIN call_summary b
  ON a.notablecallid = b.notablecallid
JOIN system_USERS u
  ON u.EXTENSION = CALLINGPARTYNO or u.EXTENSION = FINALLYCALLEDPARTYNO
WHERE NOT EXISTS
   (SELECT sub.date_of_report, sub.extension
    FROM daily_call_totals sub
    WHERE sub.date_of_report = my_report_date_column     -- UPDATE COLUMN
      AND sub.extension = u.extension)
GROUP BY my_report_date_column, extension, u.user_id     -- UPDATE COLUMN

Now if you want to run two actions conditionally: 1) update existing values OR 2) insert new values, then use DB2's MERGE (available in some but not all RDBMS's):

MERGE INTO daily_call_totals AS d
USING
   (SELECT
          my_report_date_column,                              -- UPDATE COLUMN
          u.extension
          , sum(duration) as total_talk_time_seconds
          , round(sum(duration) / 60,2) as total_talk_time_minutes
          , sum(case when legtype1 = 1 then 1 else 0 end) as total_outbound

    FROM SESSION a
    JOIN call_summary b
      ON a.notablecallid = b.notablecallid
    JOIN system_USERS u
      ON u.EXTENSION = CALLINGPARTYNO or u.EXTENSION = FINALLYCALLEDPARTYNO
    GROUP BY my_report_date_column, extension, u.user_id     -- UPDATE COLUMN
   ) AS q
ON (d.date_of_report = q.my_report_date_column               -- UPDATE COLUMN
    AND d.extension = q.extension)
WHEN MATCHED THEN
     UPDATE SET d.total_talk_time_seconds = q.total_talk_time_seconds,
                d.total_talk_time_minutes = q.total_talk_time_minutes,
                d.total_outbound = q.total_outbound            
WHEN NOT MATCHED THEN
     INSERT (date_of_report,                                 -- UPDATE COLUMN
             extension,
             total_talk_time_seconds,
             total_talk_time_minutes,
             total_outbound)
     VALUES (q.my_report_date_column,                        -- UPDATE COLUMN
             q.extension
             q.total_talk_time_seconds
             q.total_talk_time_minutes
             q.total_outbound);

This is a perfect usage, as far as the merge. Trying this now

Collectives™ on Stack Overflow

DB2 Select and insert based on bound parameters in php script

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related