5.4.1 Set DELETE [Top]
-----------------------------------------------------------------------------------
SELECT customer.id AS cust_id,
       customer.company_name,
       sales_order.id AS order_id,
       sales_order.order_date,
       employee.emp_id,
       STRING ( employee.emp_fname, ' ', employee.emp_lname ) AS emp_name,
       sales_order_items.line_id
  FROM customer
       INNER JOIN sales_order
               ON sales_order.cust_id = customer.id
       INNER JOIN employee
               ON employee.emp_id = sales_order.sales_rep
       INNER JOIN sales_order_items
               ON sales_order_items.id = sales_order.id
WHERE STRING ( employee.emp_fname, ' ', employee.emp_lname )
          IN ( 'Rollin Overbey', 'Philip Chin' )
   AND customer.company_name
          IN ( 'The Power Group', 'Darling Associates' )
   AND sales_order.order_date <= '2000-12-31'
ORDER BY 1, 2, 3, 4, 5, 6, 7;
-----------------------------------------------------------------------------------
DELETE sales_order_items
  FROM customer
       INNER JOIN sales_order
               ON sales_order.cust_id = customer.id
       INNER JOIN employee
               ON employee.emp_id = sales_order.sales_rep
       INNER JOIN sales_order_items
               ON sales_order_items.id = sales_order.id
WHERE STRING ( employee.emp_fname, ' ', employee.emp_lname )
          IN ( 'Rollin Overbey', 'Philip Chin' )
   AND customer.company_name
          IN ( 'The Power Group', 'Darling Associates' )
   AND sales_order.order_date <= '2000-12-31';
-----------------------------------------------------------------------------------
DELETE sales_order
  FROM customer
       INNER JOIN sales_order
               ON sales_order.cust_id = customer.id
       INNER JOIN employee
               ON employee.emp_id = sales_order.sales_rep
WHERE STRING ( employee.emp_fname, ' ', employee.emp_lname )
          IN ( 'Rollin Overbey', 'Philip Chin' )
   AND customer.company_name
          IN ( 'The Power Group', 'Darling Associates' )
   AND sales_order.order_date <= '2000-12-31';
-----------------------------------------------------------------------------------
SELECT DISTINCT sales_order_items.*
  FROM customer
       INNER JOIN sales_order
               ON sales_order.cust_id = customer.id
       INNER JOIN employee
               ON employee.emp_id = sales_order.sales_rep
       INNER JOIN sales_order_items
               ON sales_order_items.id = sales_order.id
WHERE STRING ( employee.emp_fname, ' ', employee.emp_lname )
          IN ( 'Rollin Overbey', 'Philip Chin' )
   AND customer.company_name
          IN ( 'The Power Group', 'Darling Associates' )
   AND sales_order.order_date <= '2000-12-31';
SELECT DISTINCT sales_order.*
  FROM customer
       INNER JOIN sales_order
               ON sales_order.cust_id = customer.id
       INNER JOIN employee
               ON employee.emp_id = sales_order.sales_rep
       INNER JOIN sales_order_items
               ON sales_order_items.id = sales_order.id
WHERE STRING ( employee.emp_fname, ' ', employee.emp_lname )
          IN ( 'Rollin Overbey', 'Philip Chin' )
   AND customer.company_name
          IN ( 'The Power Group', 'Darling Associates' )
   AND sales_order.order_date <= '2000-12-31';
-----------------------------------------------------------------------------------
CREATE VIEW v_old_items AS
SELECT *
  FROM sales_order_items
WHERE ship_date < DATEADD ( YEAR, -3, CURRENT DATE );
DELETE v_old_items
WHERE quantity <= 12;
-----------------------------------------------------------------------------------

5.5 DELETE WHERE CURRENT OF Cursor [Top]
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         UNSIGNED INTEGER NOT NULL PRIMARY KEY,
   non_key_1     INTEGER NOT NULL );
INSERT t1 VALUES ( 1, 1 );
INSERT t1 VALUES ( 2, 2 );
INSERT t1 VALUES ( 3, 3 );
INSERT t1 VALUES ( 4, 4 );
INSERT t1 VALUES ( 5, 5 );
BEGIN
DECLARE @t1_key_1          INTEGER;
DECLARE @t1_non_key_1      INTEGER;
DECLARE @SQLSTATE          VARCHAR ( 5 );
DECLARE cloop1 CURSOR FOR
SELECT t1.key_1,
       t1.non_key_1
  FROM t1
       CROSS JOIN t1 AS x
WHERE t1.key_1 = 2;
OPEN cloop1;
FETCH cloop1 INTO
   @t1_key_1,
   @t1_non_key_1;
SET @SQLSTATE = SQLSTATE;
WHILE ( @SQLSTATE = '00000' ) LOOP
   DELETE t1
    WHERE CURRENT OF cloop1;
   FETCH cloop1 INTO
      @t1_key_1,
      @t1_non_key_1;
   SET @SQLSTATE = SQLSTATE;
END LOOP;
CLOSE cloop1;
END;
-----------------------------------------------------------------------------------

5.6 TRUNCATE TABLE [Top]
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         UNSIGNED INTEGER NOT NULL PRIMARY KEY,
   non_key_1     INTEGER NOT NULL );
CREATE TABLE t2 (
   key_1         UNSIGNED INTEGER NOT NULL PRIMARY KEY,
   non_key_1     INTEGER NOT NULL );
INSERT t1 VALUES ( 1, 1 );
INSERT t2 VALUES ( 22, 22 );
COMMIT;
-----------------------------------------------------------------------------------
SET EXISTING OPTION PUBLIC.TRUNCATE_WITH_AUTO_COMMIT = 'ON';
UPDATE t2 SET non_key_1 = 999;
TRUNCATE TABLE t1;
ROLLBACK;
-----------------------------------------------------------------------------------
SET EXISTING OPTION PUBLIC.TRUNCATE_WITH_AUTO_COMMIT = 'OFF';
UPDATE t2 SET non_key_1 = 999;
TRUNCATE TABLE t1;
ROLLBACK;
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL PRIMARY KEY,
   inserted_date DATE NOT NULL DEFAULT CURRENT DATE,
   blob          LONG VARCHAR );
-----------------------------------------------------------------------------------
DELETE t1
WHERE inserted_date < DATEADD ( DAY, -7, CURRENT DATE );
-----------------------------------------------------------------------------------
SELECT *
  INTO #t1
  FROM t1
WHERE inserted_date >= DATEADD ( DAY, -7, CURRENT DATE );
TRUNCATE TABLE t1;
INSERT t1
SELECT *
  FROM #t1;
-----------------------------------------------------------------------------------

5.7 Chapter Summary [Top]
-----------------------------------------------------------------------------------

Chapter 6 - FETCHING [Top]
-----------------------------------------------------------------------------------

6.1 Introduction [Top]
-----------------------------------------------------------------------------------

6.2 Cursor FETCH Loop [Top]
-----------------------------------------------------------------------------------
BEGIN
DECLARE @key_1         INTEGER;
DECLARE @non_key_1     VARCHAR ( 100 );
DECLARE @last_updated  TIMESTAMP;
DECLARE @SQLSTATE      VARCHAR ( 5 );
DECLARE @loop_counter  INTEGER;
DECLARE c_fetch NO SCROLL CURSOR FOR
SELECT TOP 1000
       t1.key_1,
       t1.non_key_1,
       t1.last_updated
  FROM t1
WHERE t1.last_updated < DATEADD ( MONTH, -6, CURRENT DATE )
ORDER BY t1.last_updated
FOR UPDATE;
OPEN c_fetch WITH HOLD;
FETCH c_fetch INTO
   @key_1,
   @non_key_1,
   @last_updated;
SET @SQLSTATE = SQLSTATE;
SET @loop_counter = 0;
WHILE @SQLSTATE = '00000' LOOP
   SET @loop_counter = @loop_counter + 1;
   MESSAGE STRING ( 'Deleting ',
                    @loop_counter, ', ',
                    @key_1, ', "',
                    @non_key_1, '", ',
                    @last_updated ) TO CONSOLE;
   DELETE t1 WHERE CURRENT OF c_fetch;
   IF MOD ( @loop_counter, 100 ) = 0 THEN
      COMMIT;
      MESSAGE STRING ( 'COMMIT after ', @loop_counter, ' rows.' ) TO CONSOLE;
   END IF;
   FETCH c_fetch INTO
      @key_1,
      @non_key_1,
      @last_updated;
   SET @SQLSTATE = SQLSTATE;
END LOOP;
CLOSE c_fetch;
COMMIT;
MESSAGE STRING ( 'Final COMMIT after ', @loop_counter, ' rows.' ) TO CONSOLE;
END;
-----------------------------------------------------------------------------------
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sqldef.h"
EXEC SQL INCLUDE SQLCA;
int main() {
EXEC SQL BEGIN DECLARE SECTION;
long   key_1;
char   non_key_1 [ 101 ];
char   last_updated [ 24 ];
EXEC SQL END DECLARE SECTION;
char   copy_SQLSTATE [ 6 ];
long   loop_counter;
ldiv_t loop_counter_ldiv;
db_init( &sqlca );
EXEC SQL CONNECT USING 'ENG=test6;DBN=test6;UID=DBA;PWD=SQL';
EXEC SQL DECLARE c_fetch NO SCROLL CURSOR FOR
SELECT TOP 1000
       t1.key_1,
       t1.non_key_1,
       DATEFORMAT ( t1.last_updated, 'yyyy-mm-dd hh:nn:ss.sss' )
  FROM t1
WHERE t1.last_updated < DATEADD ( MONTH, -6, CURRENT DATE )
ORDER BY t1.last_updated
FOR UPDATE;
EXEC SQL OPEN c_fetch WITH HOLD;
EXEC SQL FETCH c_fetch INTO
   :key_1,
   :non_key_1,
   :last_updated;
strcpy ( copy_SQLSTATE, SQLSTATE );
loop_counter = 0;
while ( strcmp ( copy_SQLSTATE, "00000" ) == 0 ) {
   loop_counter = loop_counter + 1;
   printf ( "Deleting %d, %d, '%s', %s\n",
      loop_counter,
      key_1,
      non_key_1,
      last_updated );
   EXEC SQL DELETE t1 WHERE CURRENT OF c_fetch;
   loop_counter_ldiv = ldiv ( loop_counter, 100L );
   if ( loop_counter_ldiv.rem == 0 ) {
      EXEC SQL COMMIT;
      printf ( "COMMIT after %d rows.\n", loop_counter );
   }
   EXEC SQL FETCH c_fetch INTO
      :key_1,
      :non_key_1,
      :last_updated;
   strcpy ( copy_SQLSTATE, SQLSTATE );
} // while...
EXEC SQL CLOSE c_fetch;
EXEC SQL COMMIT;
EXEC SQL DISCONNECT;
db_fini ( &sqlca );
printf ( "Done after %d rows.\n", loop_counter );
return ( 0 );
} // main
-----------------------------------------------------------------------------------

6.2.1 DECLARE CURSOR FOR Select [Top]
-----------------------------------------------------------------------------------
CREATE PROCEDURE p_delete_oldest ( IN @age_in_months INTEGER )
BEGIN
DECLARE @key_1         INTEGER;
DECLARE @non_key_1     VARCHAR ( 100 );
DECLARE @last_updated  TIMESTAMP;
DECLARE @SQLSTATE      VARCHAR ( 5 );
DECLARE @loop_counter  INTEGER;
DECLARE c_fetch NO SCROLL CURSOR FOR
SELECT TOP 1000
       t1.key_1,
       t1.non_key_1,
       t1.last_updated
  FROM t1
WHERE t1.last_updated < DATEADD ( MONTH, -@age_in_months, CURRENT DATE )
ORDER BY t1.last_updated
FOR UPDATE;
OPEN c_fetch WITH HOLD;
FETCH c_fetch INTO
   @key_1,
   @non_key_1,
   @last_updated;
SET @SQLSTATE = SQLSTATE;
SET @loop_counter = 0;
WHILE @SQLSTATE = '00000' LOOP
   SET @loop_counter = @loop_counter + 1;
   DELETE t1 WHERE CURRENT OF c_fetch;
   IF MOD ( @loop_counter, 100 ) = 0 THEN
      COMMIT;
   END IF;
   FETCH c_fetch INTO
      @key_1,
      @non_key_1,
      @last_updated;
   SET @SQLSTATE = SQLSTATE;
END LOOP;
CLOSE c_fetch;
COMMIT;
END; -- p_delete_oldest
CALL p_delete_oldest ( 6 );
-----------------------------------------------------------------------------------
BEGIN
DECLARE @level         INTEGER;
DECLARE @name          VARCHAR ( 20 );
DECLARE @SQLSTATE      VARCHAR ( 5 );
DECLARE @loop_counter  INTEGER;
DECLARE c_fetch NO SCROLL CURSOR FOR
WITH RECURSIVE superior_list
   ( level,
     chosen_employee_id,
     manager_id,
     employee_id,
     name )
AS ( SELECT CAST ( 1 AS INTEGER ) AS level,
            employee.employee_id    AS chosen_employee_id,
            employee.manager_id     AS manager_id,
            employee.employee_id    AS employee_id,
            employee.name           AS name
       FROM employee
     UNION ALL
     SELECT superior_list.level + 1,
            superior_list.chosen_employee_id,
            employee.manager_id,
            employee.employee_id,
            employee.name
       FROM superior_list
            INNER JOIN employee
                    ON employee.employee_id = superior_list.manager_id
      WHERE superior_list.level <= 99
        AND superior_list.manager_id <> superior_list.employee_id )
SELECT superior_list.level,
       superior_list.name
  FROM superior_list
WHERE superior_list.chosen_employee_id = 13
ORDER BY superior_list.level DESC
FOR READ ONLY;
OPEN c_fetch WITH HOLD;
FETCH c_fetch INTO
   @level,
   @name;
SET @SQLSTATE = SQLSTATE;
SET @loop_counter = 0;
WHILE @SQLSTATE = '00000' LOOP
   SET @loop_counter = @loop_counter + 1;
   MESSAGE STRING ( @level, ' ', @name ) TO CONSOLE;
   FETCH c_fetch INTO
      @level,
      @name;
   SET @SQLSTATE = SQLSTATE;
END LOOP;
CLOSE c_fetch;
END;
-----------------------------------------------------------------------------------

6.2.2 DECLARE CURSOR USING Select [Top]
-----------------------------------------------------------------------------------
BEGIN -- outer block
DECLARE @select LONG VARCHAR;
SET @select = '
SELECT TOP 1000
       t1.key_1,
       t1.non_key_1,
       t1.last_updated
  FROM t1
WHERE t1.last_updated < DATEADD ( MONTH, -6, CURRENT DATE )
ORDER BY t1.last_updated
FOR UPDATE';
BEGIN -- inner block
DECLARE @key_1         INTEGER;
DECLARE @non_key_1     VARCHAR ( 100 );
DECLARE @last_updated  TIMESTAMP;
DECLARE @SQLSTATE      VARCHAR ( 5 );
DECLARE @loop_counter  INTEGER;
DECLARE c_fetch NO SCROLL CURSOR USING @select;
OPEN c_fetch WITH HOLD;
FETCH c_fetch INTO
   @key_1,
   @non_key_1,
   @last_updated;
SET @SQLSTATE = SQLSTATE;
SET @loop_counter = 0;
WHILE @SQLSTATE = '00000' LOOP
   SET @loop_counter = @loop_counter + 1;
   DELETE t1 WHERE CURRENT OF c_fetch;
   IF MOD ( @loop_counter, 100 ) = 0 THEN
      COMMIT;
      MESSAGE STRING ( 'COMMIT after ', @loop_counter, ' rows.' ) TO CONSOLE;
   END IF;
   FETCH c_fetch INTO
      @key_1,
      @non_key_1,
      @last_updated;
   SET @SQLSTATE = SQLSTATE;
END LOOP;
CLOSE c_fetch;
COMMIT;
END; -- inner block
END; -- outer block
-----------------------------------------------------------------------------------

6.2.3 DECLARE CURSOR FOR CALL [Top]
-----------------------------------------------------------------------------------
CREATE PROCEDURE p_oldest ( IN @age_in_months INTEGER )
BEGIN
SELECT TOP 1000
       t1.key_1,
       t1.non_key_1,
       t1.last_updated
  FROM t1
WHERE t1.last_updated < DATEADD ( MONTH, -@age_in_months, CURRENT DATE )
ORDER BY t1.last_updated;
END;
BEGIN
DECLARE @key_1         INTEGER;
DECLARE @non_key_1     VARCHAR ( 100 );
DECLARE @last_updated  TIMESTAMP;
DECLARE @SQLSTATE      VARCHAR ( 5 );
DECLARE @loop_counter  INTEGER;
DECLARE c_fetch NO SCROLL CURSOR FOR CALL p_oldest ( 6 );
OPEN c_fetch WITH HOLD;
FETCH c_fetch INTO
   @key_1,
   @non_key_1,
   @last_updated;
SET @SQLSTATE = SQLSTATE;
SET @loop_counter = 0;
WHILE @SQLSTATE = '00000' LOOP
   SET @loop_counter = @loop_counter + 1;
   DELETE t1
    WHERE t1.key_1 = @key_1;
   IF MOD ( @loop_counter, 100 ) = 0 THEN
      COMMIT;
   END IF;
   FETCH c_fetch INTO
      @key_1,
      @non_key_1,
      @last_updated;
   SET @SQLSTATE = SQLSTATE;
END LOOP;
CLOSE c_fetch;
COMMIT;
END;
-----------------------------------------------------------------------------------

6.2.4 OPEN and CLOSE Cursor [Top]
-----------------------------------------------------------------------------------
OPEN c_fetch WITH HOLD;
IF SQLSTATE <> '00000' THEN
   RAISERROR 20000 STRING ( 'Cursor OPEN SQLSTATE = ', SQLSTATE )
END IF;
-----------------------------------------------------------------------------------

6.2.5 FETCH Cursor [Top]
-----------------------------------------------------------------------------------

6.3 Cursor FOR Loop [Top]
-----------------------------------------------------------------------------------
BEGIN
DECLARE @loop_counter  INTEGER;
SET @loop_counter = 0;
FOR f_fetch
AS c_fetch NO SCROLL CURSOR FOR
SELECT TOP 1000
       t1.key_1        AS @key_1,
       t1.non_key_1    AS @non_key_1,
       t1.last_updated AS @last_updated
  FROM t1
WHERE t1.last_updated < DATEADD ( MONTH, -6, CURRENT DATE )
ORDER BY t1.last_updated
FOR UPDATE
DO
   SET @loop_counter = @loop_counter + 1;
   MESSAGE STRING ( 'Deleting ',
                    @loop_counter, ', ',
                    @key_1, ', "',
                    @non_key_1, '", ',
                    @last_updated ) TO CONSOLE;
   DELETE t1 WHERE CURRENT OF c_fetch;
   IF MOD ( @loop_counter, 100 ) = 0 THEN
      COMMIT;
   END IF;
END FOR;
COMMIT;
END;
-----------------------------------------------------------------------------------
CREATE PROCEDURE p_oldest ( IN @age_in_months INTEGER )
BEGIN
SELECT TOP 1000
       t1.key_1        AS @key_1,
       t1.non_key_1    AS @non_key_1,
       t1.last_updated AS @last_updated
  FROM t1
WHERE t1.last_updated < DATEADD ( MONTH, -@age_in_months, CURRENT DATE )
ORDER BY t1.last_updated;
END;
BEGIN
DECLARE @loop_counter  INTEGER;
SET @loop_counter = 0;
FOR f_fetch
AS c_fetch NO SCROLL CURSOR FOR CALL p_oldest ( 6 )
DO
   SET @loop_counter = @loop_counter + 1;
   MESSAGE STRING ( 'Deleting ',
                    @loop_counter, ', ',
                    @key_1, ', "',
                    @non_key_1, '", ',
                    @last_updated ) TO CONSOLE;
   DELETE t1
    WHERE t1.key_1 = @key_1;
   IF MOD ( @loop_counter, 100 ) = 0 THEN
      COMMIT;
   END IF;
END FOR;
COMMIT;
END;
-----------------------------------------------------------------------------------

6.4 Chapter Summary [Top]
-----------------------------------------------------------------------------------

CHAPTER 7 - SYNCHRONIZING [Top]
-----------------------------------------------------------------------------------

7.1 Introduction [Top]
-----------------------------------------------------------------------------------

7.2 How MobiLink Works [Top]
-----------------------------------------------------------------------------------

7.3 The MobiLink ASA Client [Top]
-----------------------------------------------------------------------------------

7.4 MobiLink Client Setup [Top]
-----------------------------------------------------------------------------------

7.4.1 CREATE PUBLICATION [Top]
-----------------------------------------------------------------------------------
CREATE PUBLICATION p1 (
   TABLE t1 ( key_1,
              key_2,
              non_key_1,
              non_key_2 ),
   TABLE t2 ( key_1,
              non_key_1 ) );
-----------------------------------------------------------------------------------

7.4.2 CREATE SYNCHRONIZATION USER [Top]
-----------------------------------------------------------------------------------
CREATE SYNCHRONIZATION USER "1"
   TYPE tcpip
   ADDRESS 'host=mobilink.risingroad.com'
   OPTION ConflictRetries='2';
-----------------------------------------------------------------------------------

7.4.3 CREATE SYNCHRONIZATION SUBSCRIPTION [Top]
-----------------------------------------------------------------------------------
CREATE SYNCHRONIZATION SUBSCRIPTION TO p1 FOR "1"
   TYPE tcpip
   ADDRESS 'port=2439'
   OPTION LockTables='OFF',
          FireTriggers='ON',
          SendTriggers='ON',
          ScriptVersion='1',
          SendDownloadACK='ON';
-----------------------------------------------------------------------------------