CREATE OR REPLACE TABLE t1 (

id int not null, 

nb bigint not null,

msg char(32))

ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=t2

CONNECTION='jdbc:mysql://localhost:3308/test?user=root&useSSL=false';

CREATE OR REPLACE PROCEDURE xup(IN n INT, IN c CHAR(32))

  BEGIN

    UPDATE t1 SET nb = n, msg = c  WHERE id = 2;

  END;

CALL xup(123,'Hello');

UPDATE t2 SET nb =  NAME_CONST('n',123), msg =  NAME_CONST('c',_latin1'Hello' COLLATE 'latin1_swedish_ci') WHERE id = 2

UPDATE t2 SET nb =  123, msg = 'Hello' WHERE id = 2

-- I grabbed these with SHOW TABLE

CREATE TABLE "Vendors" ( "Vendor_ID" varchar(20) NOT NULL, "Name" varchar(50) NOT NULL, "Default_Lead_Time" bigint(20) NOT NULL, "Order_Attention" varchar(50) NOT NULL, "Order_Address1" varchar(50) NOT NULL, "Order_Address2" varchar(50) DEFAULT NULL, "Order_City" varchar(35) NOT NULL, "Order_State" varchar(2) NOT NULL, "Order_Zip" varchar(10) NOT NULL, "Order_Country" varchar(30) DEFAULT NULL, "Order_Phone" varchar(30) NOT NULL, "Order_Email_TO" varchar(60) NOT NULL, "Order_Email_CC" varchar(60) DEFAULT NULL, "Order_Email_BCC" varchar(60) DEFAULT NULL, PRIMARY KEY ("Vendor_ID") ) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE "Vendor_Items" ( "Vendor_Item_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Vendor_ID" varchar(20) NOT NULL, "Vendor_SKU" varchar(40) NOT NULL, "Vendor_Description" varchar(255) DEFAULT NULL, "Unit_Cost" double NOT NULL COMMENT 'CURRENCY', "MOQ" bigint(20) NOT NULL, "Special_Order" tinyint(1) NOT NULL, "Lead_Time" bigint(20) NOT NULL, "Units_In_Package" bigint(20) NOT NULL, "Reorder_Threshold" bigint(20) NOT NULL, "Reorder_Qty" bigint(20) NOT NULL, "Restocks_ProductVariantId" bigint(20) NOT NULL, "Inbound_Qty" bigint(20) NOT NULL, PRIMARY KEY ("Vendor_Item_ID"), UNIQUE KEY "Vendor_Items_ProductVariantId" ("Restocks_ProductVariantId"), KEY "Vendor_ID" ("Vendor_ID"), CONSTRAINT "Vendor_Items_ibfk_1" FOREIGN KEY ("Vendor_ID") REFERENCES "Vendors" ("Vendor_ID") ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Inbound_Qty is INCREMENTED when PO Sent, and DECREMENTED by Receiving of goods\nCREATE UNIQUE INDEX Vendor_Items_ProductVariantId ON Vendor_Items (Restocks_ProductVariantId)\n'

CREATE TABLE "ROG" ( "ROG_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Purchase_Order_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Status" enum('New','Completed') NOT NULL, "Date_Received" date NOT NULL, "Received_By" varchar(60) NOT NULL, "Comments" text DEFAULT NULL COMMENT 'HTMLFIELD', PRIMARY KEY ("ROG_ID"), KEY "Purchase_Order_ID" ("Purchase_Order_ID"), CONSTRAINT "ROG_ibfk_1" FOREIGN KEY ("Purchase_Order_ID") REFERENCES "Purchase_Orders" ("Purchase_Order_ID") ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ROG = Receipt of Goods. Status New, Completed > LOCKS + removes from INBOUND + ADDS to inventory + possibly modifies PO status to Short or Completed. Never let inbound go negative.'

CREATE TABLE "ROG_Lines" ( "ROG_Line_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "ROG_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Purchase_Order_Line_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Vendor_Item_ID" bigint(20) NOT NULL COMMENT 'AUTOKEY', "Qty_Received" bigint(20) NOT NULL, PRIMARY KEY ("ROG_Line_ID"), KEY "ROG_ID" ("ROG_ID"), KEY "Vendor_Item_ID" ("Vendor_Item_ID"), KEY "Purchase_Order_Line_ID" ("Purchase_Order_Line_ID"), CONSTRAINT "ROG_Lines_ibfk_1" FOREIGN KEY ("ROG_ID") REFERENCES "ROG" ("ROG_ID") ON UPDATE CASCADE, CONSTRAINT "ROG_Lines_ibfk_2" FOREIGN KEY ("Vendor_Item_ID") REFERENCES "Vendor_Items" ("Vendor_Item_ID") ON UPDATE CASCADE, CONSTRAINT "ROG_Lines_ibfk_3" FOREIGN KEY ("Purchase_Order_Line_ID") REFERENCES "Purchase_Order_Lines" ("Purchase_Order_Line_ID") ) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- this is the actual command issued

CREATE OR REPLACE TABLE ProductVariants ( `Id` BIGINT , 

`ProductId` BIGINT ,

`InventoryItemId` BIGINT ,

`ImageId` BIGINT ,

`Price` DOUBLE ,

`CompareAtPrice` VARCHAR(255) ,

`Title` VARCHAR(255) ,

`Grams` BIGINT ,

`Barcode` VARCHAR(255) ,

`Weight` BIGINT ,

`WeightUnit` VARCHAR(255) ,

`FulfillmentService` VARCHAR(255) ,

`InventoryManagement` VARCHAR(255) ,

`InventoryPolicy` VARCHAR(255) ,

`InventoryQuantity` BIGINT ,

`OldInventoryQuantity` BIGINT ,

`Option1` VARCHAR(255) ,

`Option2` VARCHAR(255) ,

`Option3` VARCHAR(255) ,

`Position` BIGINT ,

`RequiresShipping` BOOLEAN ,

`Sku` VARCHAR(255) ,

`Taxable` BOOLEAN ,

`CreatedAt` DATETIME ,

`UpdatedAt` DATETIME  )  ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME='ProductVariants' QUOTED=1 BLOCK_SIZE=500  

CONNECTION='jdbc:shopify:AppId=0beb2a6debcc4dc9a50b19723881fdc7;Password=24d822c3e9c4ec42f6e0764243340cf5;ShopUrl=https://kiwi-diamond.myshopify.com;Logfile="/var/log/mysql/shopify.log";Verbosity=4;'  

OPTION_LIST='USER=null,PASSWORD=null,Memory=2' 

CREATE OR REPLACE TRIGGER `a_ROG_Before_Update` BEFORE UPDATE ON `ROG` FOR EACH ROW BEGIN

	DECLARE v_ProductVariantId BIGINT;

	DECLARE v_new_qty BIGINT;

	IF (NEW.Status = 'Completed' AND OLD.Status != 'Completed') THEN

		FOR item IN (SELECT Vendor_Item_ID, Qty_Received FROM ROG_Lines WHERE ROG_ID = NEW.ROG_ID) DO

			UPDATE Vendor_Items 

			SET Inbound_Qty = GREATEST(0, Inbound_Qty - item.Qty_Received)

			WHERE Vendor_Item_ID = item.Vendor_Item_ID;

			SELECT Restocks_ProductVariantId INTO v_ProductVariantId

			FROM Vendor_Items

			WHERE Vendor_Item_ID = item.Vendor_Item_ID;

			-- BEGIN ACCESS of CONNECT table - query with variable: produces right result on first call, null on subsequent

			SET v_new_qty =  (SELECT InventoryQuantity FROM ProductVariants WHERE Id = v_ProductVariantId) + item.Qty_Received;

			-- simple UPDATE CONNECT table with 2 variables - produces error

			UPDATE ProductVariants 

			SET InventoryQuantity = v_new_qty  

			WHERE Id = v_ProductVariantId;

		END FOR;

	END IF;

END

update ROG set Status = 'New' where ROG_ID = 1003

update ROG set Status = 'Completed' where ROG_ID = 1003

-- On 3308 (the remote source, another MariaDB server)

create or replace table animals (n int not null);

insert into animals values(0);

-- On 3306 (the local server)

CREATE OR REPLACE TABLE anum

ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=animals

CONNECTION='jdbc:mysql://localhost:3308/test?user=root';

-- This table cannot be a CONNECT table

create or replace table pets (

id int not null,

name char(32),

race char(32));

create or replace trigger add_one after insert on pets

for each row

begin

update anum set n = n + 1;

end;

insert into pets values(1,'Choupy','chat');

update anum set n = n + 1

update animals set n = n + 1

-- ON REMOTE SYSTEM (Azure SQL server in this case):

create table testme (id bigint, qty bigint, name varchar(50), primary key (id))

insert into testme (id,qty,name) values (1,125,'nut'), (2, 150, 'bolt'), (3, 175,'screw')

-- IN MariaDB:

CREATE TABLE connect_testme ( `id` BIGINT NOT NULL , (TransactionSet.java:370)

`qty` BIGINT ,

`name` VARCHAR(50)  )  ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME='testme' BLOCK_SIZE=500  CONNECTION='jdbc:sqlserver://parasqltest.database.windows.net:1433;database=test6;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

'  OPTION_LIST='USER=testuser1@parasqltest,PASSWORD=Test33410,Memory=2' 

CREATE TABLE `local_testme` (`id` bigint NOT NULL COMMENT '' , `qty` bigint COMMENT '' , `name` varchar(50) COMMENT '' ,  PRIMARY KEY (`id`)  ) COMMENT '' 

CREATE OR REPLACE PROCEDURE `test_read_loop` () LANGUAGE SQL 

BEGIN

	-- insert records from connect_testme in a loop

	FOR rec IN (SELECT id, qty, name FROM connect_testme WHERE id >= 2) DO

		insert into local_testme (id, qty, name) values (rec.id, rec.qty, rec.name);

	END FOR;

END

call test_read_loop()

select * from local_testme

id	qty	name

2	150	bolt

3	175	screw

CORRECT SO FAR - with procedure not trigger AND with id >= 2 not id >= some_var (later test case)

CREATE OR REPLACE PROCEDURE `test_update_loop` (v_id bigint) LANGUAGE SQL 

BEGIN 

	declare new_qty bigint;

	FOR rec IN (SELECT id, qty, name FROM local_testme where id >= v_id) DO

		set new_qty = rec.qty *2;

		update connect_testme

		set qty = new_qty

		where id = rec.id;

	END FOR;

END

call test_update_loop(1)

Got error 122 'ExecuteUpdate: com.microsoft.sqlserver.jdbc.SQLServerException: 'NAME_CONST' is not a recognized built-in function name. n=-1' from CONNECT

CREATE OR REPLACE PROCEDURE `test_read_loop2` (v_qty bigint) LANGUAGE SQL 

BEGIN

	-- insert records from connect_testme in a loop

	FOR rec IN (SELECT id, qty, name FROM connect_testme WHERE qty >= v_qty) DO

		UPDATE local_testme 

		SET qty = qty + rec.qty

		WHERE id = rec.id;

	END FOR;

END

call test_read_loop2(150)

OK

id	qty	name

2	300	bolt

3	350	screw

(looks correct to me so far)

CREATE TABLE `trig_table` (`id` bigint NOT NULL COMMENT '' , `status` enum('NEW','DOIT') NOT NULL COMMENT '' ,  PRIMARY KEY (`id`)  ) COMMENT '' 

insert into trig_table(id, status) values (1, 'NEW')

CREATE OR REPLACE TRIGGER `trig_table_before_update` BEFORE UPDATE ON `trig_table` FOR EACH ROW 

BEGIN

	declare new_qty bigint;

	if (NEW.status = 'DOIT' and OLD.status != 'DOIT') THEN

		FOR rec IN (SELECT id, qty, name FROM local_testme) DO

			set new_qty = (select qty from connect_testme where id = rec.id) + 9 ;

			update local_testme set qty = new_qty where id = rec.id; -- we already know remote update will fail, now test read in trigger

		END FOR;

	END IF;

END

update trig_table set status = 'DOIT' where id = 1

select * from local_testme

id	qty	name

2	159	bolt

3		screw

WRONG RESULT - note that first row had 9 added to it, but second is now NULL

SET connect_xtrace=33;

CREATE OR REPLACE PROCEDURE `test_read_loop_3` () LANGUAGE SQL 

BEGIN

	declare new_qty bigint;

	FOR rec IN (SELECT id, qty, name FROM local_testme where id in (2,3)) DO

		set new_qty = (select qty from connect_testme where id = rec.id);

		update local_testme set qty = new_qty where id = rec.id;

	END FOR;

END

BEGIN

	declare new_qty bigint;

	FOR rec IN (SELECT id, qty, name FROM local_testme) DO

		SET new_qty = rec.qty;

		insert into connect_testme (id, qty, name) values (rec.id, new_qty, rec.name);

	END FOR;

END

SET v_old_qty =  (SELECT InventoryQuantity FROM ProductVariants WHERE Id = v_pv_id);

create or replace trigger addone before insert on pets

for each row

  begin

    update anum set n = n + 1 where genre = new.race;

  end;

create or replace procedure dot(in r char(32)) language sql

  begin

    declare gr char(34);

    set gr = concat('''',r,'''');

    update anum set n = n + 1 where genre = gr;

  end;

create or replace trigger addone before insert on pets

  for each row

    begin

      call dot(new.race);

    end;

insert into pets values(1,'Choupy',1,'chat');

insert into pets values(2,'Biscotte',2,'chat');

insert into pets values(3,'Ficelle',2,'chien');

insert into pets values(4,'Buggy',1,'hamster');

insert into pets values(1,'Choupy',1,'chat'),(2,'Biscotte',2,'chat'),(3,'Ficelle',2,'chien'),(4,'Buggy',1,'hamster');

create or replace procedure do_insert(in sx int) language sql

  begin

    declare nm char(34);

    FOR rec IN (SELECT id, name FROM pets WHERE sex = sx) DO

      set nm = concat('''',rec.name,'''');

      insert into names values(rec.id,nm);

      end for;

  end;

-- On the remote server

create table animals (n int not null, genre char(32)) engine=myisam;

insert into animals values(0,'chat'),(0,'chien'),(0,'hamster');

create table last_inserted (name char(32)) engine=myisam;

insert into last_inserted values('None');

-- On the local server

-- The JDBC tables

CREATE TABLE anum ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=animals

CONNECTION='jdbc:mariadb://localhost:3308/test?user=root&password=whatever';

CREATE TABLE lanm ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME=last_inserted

CONNECTION='jdbc:mariadb://localhost:3308/test?user=root&password=whatever';

-- The table with triggers that cannot be a CONNECT table

create table pets (

id int not null,

name char(32),

sex int(1),

race char(32));

create procedure do_update(in r char(32), in x int) language sql

  begin

    declare gr char(34);

    set gr = concat('''',r,'''');

    update anum set n = n + x where genre = gr;

  end;

create procedure do_it(in m char(32)) language sql

  begin

    declare nm char(34);

    set nm = concat('''',m,'''');

    update lanm set name = nm;

  end;

create trigger ibp before insert on pets

  for each row

    begin

      call do_update(new.race,1);

      call do_it(new.name);

    end;

create trigger dbp before delete on pets

  for each row

    begin

      call do_update(old.race,-1);

    end;

create trigger ubp before update on pets

  for each row

    begin

      if new.race != old.race

        then

          begin

            call do_update(old.race,-1);

            call do_update(new.race,1);

          end;

      end if;

    end;

set connect_xtrace='QUERY,STMT';

-- this was not correctly processed before fixing

insert into pets values(1,'Choupy',1,'chat'),(2,'Biscotte',2,'chat'),(3,'Ficelle',2,'chien'),4,'Buggy',1,'hamster');

delete from pets where name = 'Buggy';

update pets set race = 'chien' where name = 'Biscotte';

-- ON 3308

create table testme (id bigint, qty bigint, name varchar(50), primary key (id));

insert into testme (id,qty,name) values (1,125,'nut'), (2, 150, 'bolt'), (3, 175,'screw');

-- ON 3306

-- Using discovery

CREATE TABLE connect_testme

ENGINE=CONNECT TABLE_TYPE=JDBC TABNAME='testme' BLOCK_SIZE=500

CONNECTION='jdbc:mariadb://localhost:3308/test?user=root&password=whatever';

-- Now a CONNECT table

CREATE TABLE local_testme (

id bigint NOT NULL,

qty bigint,

name varchar(50),

PRIMARY KEY (`id`))

ENGINE=CONNECT TABLE_TYPE=CSV HEADER=1;

-- I added a parameter

CREATE OR REPLACE PROCEDURE test_read_loop (IN v_id bigint) LANGUAGE SQL 

BEGIN

-- insert records from connect_testme in a loop

	FOR rec IN (SELECT id, qty, name FROM connect_testme WHERE id >= v_id) DO

		insert into local_testme (id, qty, name) values (rec.id, rec.qty, rec.name);

	END FOR;

END;

-- To see the local received cmd and the command sent to the remote table

SET connect_xtrace='STMT,QUERY';

call test_read_loop(2);

/*

Query=SELECT id, qty, name FROM testme WHERE id >= 2

Cmd=call test_read_loop(2)

Query=SELECT id, qty, name FROM testme WHERE id >= 2

Cmd=insert into local_testme (id, qty, name) values ( NAME_CONST('rec.id',2),  NAME_CONST('rec.qty',150),  NAME_CONST('rec.name',_latin1'bolt' COLLATE 'latin1_swedish_ci'))

000002182D8A1070 check_mode: cmdtype=5

Cmd=insert into local_testme (id, qty, name) values ( NAME_CONST('rec.id',3),  NAME_CONST('rec.qty',175),  NAME_CONST('rec.name',_latin1'screw' COLLATE 'latin1_swedish_ci'))

*/

select * from local_testme;

/*

+----+-----+-------+

| id | qty | name  |

+----+-----+-------+

|  2 | 150 | bolt  |

|  3 | 175 | screw |

+----+-----+-------+

*/

CREATE OR REPLACE PROCEDURE test_update_loop (v_id bigint) LANGUAGE SQL 

BEGIN 

	declare new_qty bigint;

	FOR rec IN (SELECT id, qty, name FROM local_testme where id >= v_id) DO

		set new_qty = rec.qty *2;

		update connect_testme set qty = new_qty where id = rec.id;

	END FOR;

END;

call test_update_loop(1);

/*

Cmd=call test_update_loop(1)

Cmd=call test_update_loop(1)

Cmd=update connect_testme set qty =  NAME_CONST('new_qty',300) where id =  NAME_CONST('rec.id',2)

Command=update testme set qty =  (300) where id = 2

Cmd=update connect_testme set qty =  NAME_CONST('new_qty',350) where id =  NAME_CONST('rec.id',3)

Command=update testme set qty =  (350) where id = 3

*/

select * from connect_testme;

/*

+----+-----+-------+

| id | qty | name  |

+----+-----+-------+

|  1 | 125 | nut   |

|  2 | 300 | bolt  |

|  3 | 350 | screw |

+----+-----+-------+

*/

-- SYNTAX A: SELECT from CONNECT in LOOP

DECLARE old_qty, new_qty BIGINT;

FOR rec IN (SELECT id, qty, name FROM local_testme where id >= v_id) DO

  -- select needs to run 3 times or more to test

  SET  old_qty  = (SELECT qty FROM connect_testme WHERE id = rec.id);

  SET new_qty = old_qty  + 10;

  UPDATE connect_testme SET qty = new_qty WHERE id = rec.id;

END FOR;

-- SYNTAX B: SELECT INTO SIMPLE_VAR from CONNECT in LOOP

DECLARE old_qty, new_qty BIGINT;

FOR rec IN (SELECT id, qty, name FROM local_testme where id >= v_id) DO

  -- select needs to run 3 times or more to test

  SELECT qty INTO old_qty 

  FROM connect_testme 

  WHERE id = rec.id;

  SET new_qty = old_qty  + 10;

  UPDATE connect_testme SET qty = new_qty WHERE id = rec.id;

END FOR;

-- SYNTAX C: SELECT INTO ROW_TYPE from CONNECT in LOOP using

DECLARE old_qty, new_qty BIGINT;

DECLARE conn_rec ROW TYPE OF connect_testme ;

FOR rec IN (SELECT id, qty, name FROM local_testme where id >= v_id) DO

  -- select needs to run 3 times or more to test

  SELECT * INTO conn_rec 

  FROM connect_testme 

  WHERE id = rec.id;

  SET new_qty = conn_rec.qty + 10;

  UPDATE connect_testme SET qty = new_qty WHERE id = rec.id;

END FOR;

 Table 'ProductVariants' is marked as crashed and should be repaired

	UPDATE ProductVariants

	SET InventoryQuantity = v_new_qty

	WHERE Id = v_ProductVariantId;

1005: Can't create table `db11615`.`purchase_orders` (errno: 150

"Foreign key constraint is incorrectly formed")

1064: You have an error in your SQL syntax; check the manual that

corresponds to your MariaDB server version for the right syntax to use

near '"PurchaseOrder_SendEmailVendor"(po_id BIGINT)

BEGIN

CREATE PROCEDURE ProductVariants_AdjustQty (p_ProductVariantId BIGINT, p_qty_adj BIGINT)

BEGIN

	DECLARE v_old_qty, v_new_qty BIGINT;

	-- ProductVariants is a CONNECT JDBC table

	SELECT InventoryQuantity INTO v_old_qty

	FROM ProductVariants 

	WHERE Id = p_ProductVariantId ;

	SET v_new_qty = v_old_qty + p_qty_adj;

	UPDATE ProductVariants

	SET InventoryQuantity = v_new_qty

	WHERE Id = p_ProductVariantId;

END

call ProductVariants_AdjustQty(21375917195344, 5)

CREATE PROCEDURE testmain

BEGIN

	declare pvid bigint default 21375917195344;

	declare qty bigint default 0;

	WHILE (qty < 4) DO

		SET qty = qty + 1;

		call ProductVariants_AdjustQty(21375917195344, qty);

	END WHILE;

END

CREATE TRIGGER  testme_trigger BEFORE UPDATE ON ROG FOR EACH ROW

BEGIN

	declare pvid bigint default 21375917195344;

	declare qty bigint default 0;

	WHILE (qty < 4) DO

		SET qty = qty + 1;

		call ProductVariants_AdjustQty(21375917195344, qty);

	END WHILE;

END

1296: Got error 174 'Connecting: java.sql.SQLException: No suitable driver found for

jdbc:shopify:Appld=0beb2a6debcc4dc9a50b19723881fdc7;

Password=24d822c3e9c4ec42f60764243340cf5;

ShopUri=https://kiwi-diamond.myshopify.com' from CONNECT

CREATE OR REPLACE procedure a_test (v_id bigint)

BEGIN	

  DECLARE v_old_qty, v_new_qty, v_inventory_qty_received BIGINT;

  DECLARE items CURSOR FOR

    SELECT t2.linked_id, t2.adj_qty

    FROM t2

    WHERE t2.linked_id = v_id;

  SET v_inventory_qty_received = 0;

  FOR item IN items DO		

    SET v_inventory_qty_received = v_inventory_qty_received + item.adj_qty;			

  END FOR;

  UPDATE MS_t1

  SET qty = qty + v_inventory_qty_received

  WHERE id = v_id;		

END;