I'm new to MySQL and I'm trying to port a SQL stored procedure code from Firebird to MySQL. I tried my best, but is still generating an error on the line with CONCAT statement.
This is the code:
CREATE PROCEDURE BUILD_MATRIX(IN RECURSIVECOLLECTION VarChar(1), collectionID Integer)
BEGIN
declare collectionsort varchar(255);
declare zotero_itemid integer;
declare itemcount integer;
declare countlinks integer;
declare totals varchar(150);
declare acollectionid integer;
declare acollectionsort varchar(255);
declare azotero_itemid integer;
declare amodalityid integer;
declare atagid integer;
declare aname varchar(255);
declare bcollectionid integer;
declare bcollectionsort varchar(255);
declare bzotero_itemid integer;
declare bmodalityid integer;
declare btagid integer;
declare bname varchar(255);
IF (RecursiveCollection = '1') THEN /* Recursive Collections Enabled */
SET collectionSort = CONCAT('[',CAST(collectionID AS varchar(255)) , ']');
WHILE
Select zotero_itemID, count(zotero_itemID) As ItemCount
From factors
WHERE POSITION(collectionSort IN :collectionSort) <> 0 GROUP BY zotero_itemID
INTO :zotero_itemID, :ItemCount
DO
IF (ItemCount > 1) THEN
WHILE
Select a.collectionID as acollectionID, a.collectionSort as acollectionSort, a.zotero_itemID as azotero_itemID, a.modalityID as amodalityID, a.tagID as atagID, a.name as aname,
b.collectionID as bcollectionID, b.collectionSort as bcollectionSort, b.zotero_itemID as bzotero_itemID, b.modalityID as bmodalityID, b.tagID as btagID, b.name as bname
From factors a
JOIN factors b On a.modalityID <= b.modalityID
WHERE (a.modalityID > 0 AND b.modalityID > 0) AND (a.tagID <> b.tagID) AND (POSITION(a.collectionSort IN :collectionSort) <> 0) And (a.zotero_itemID = :zotero_itemID) And (POSITION(b.collectionSort IN :collectionSort) <> 0) And (b.zotero_itemID = :zotero_itemID)
ORDER BY a.modalityID
INTO :ACOLLECTIONID, :ACOLLECTIONSORT, :AZOTERO_ITEMID, :amodalityID, :atagID, :ANAME, :BCOLLECTIONID, :BCOLLECTIONSORT, :BZOTERO_ITEMID, :bmodalityID, :btagID, :BNAME
DO
/* Verify if there are links with the same normative-determinative modalities and factors to avoid duplications */
SELECT count(linkID) as countLinks
FROM matrix
WHERE collectionID = :collectionID And recursiveCollection = :recursiveCollection AND normativeModality = :amodalityID AND normativeFactorID = :atagID And determinativeModality = :bmodalityID AND determinativeFactorID = :btagID
INTO :countLinks;
IF (countLinks > 0) THEN
/* Set synchronized = True only. To avoid duplications */
SELECT totals
FROM get_total_items(:atagid, :btagID, :recursivecollection, :collectionID)
INTO totals;
UPDATE matrix
SET totals = :totals, synchronized = '1'
WHERE collectionID = :collectionID AND recursiveCollection = :recursiveCollection AND normativeModality = :amodalityID AND normativeFactorID = :atagID And determinativeModality = :bmodalityID AND determinativeFactorID = :btagID;
ELSE
/* Add only new links and ignore similar combinations like A-B and B-A if the table Matrix already has one */
SELECT count(linkID) as countLinks
FROM matrix
WHERE collectionID = :collectionID AND recursiveCollection = :recursiveCollection AND normativeModality = :amodalityID AND normativeFactorID = :btagID And determinativeModality = :bmodalityID AND determinativeFactorID = :atagID
INTO :countLinks;
IF (countLinks = 0) THEN
SELECT totals
FROM get_total_items(:atagid, :btagID, :recursivecollection, :collectionID)
INTO totals;
INSERT INTO matrix (collectionID,normativeModality,normativeFactorID,determinativeModality,determinativeFactorID, totals, synchronized, recursiveCollection)
VALUES (:collectionID,:amodalityID,:atagID,:bmodalityID,:btagID, :totals, '1', :recursiveCollection);
END IF;
END IF;
END WHILE;
END IF;
END WHILE;
ELSE IF (RecursiveCollection = '0') THEN /* One Collection Only */
WHILE
Select zotero_itemID, count(zotero_itemID) As ItemCount
From factors
WHERE collectionID = :collectionID GROUP BY zotero_itemID
INTO :zotero_itemID, :ItemCount
DO
IF (ItemCount > 1) THEN
WHILE
Select a.collectionID as acollectionID, a.collectionSort as acollectionSort, a.zotero_itemID as azotero_itemID, a.modalityID as amodalityID, a.tagID as atagID, a.name as aname,
b.collectionID as bcollectionID, b.collectionSort as bcollectionSort, b.zotero_itemID as bzotero_itemID, b.modalityID as bmodalityID, b.tagID as btagID, b.name as bname
From factors a
JOIN factors b On a.modalityID <= b.modalityID
WHERE (a.modalityID > 0 AND b.modalityID > 0) AND (a.tagID <> b.tagID) AND (a.collectionID = :collectionID) And (a.zotero_itemID = :zotero_ItemID) And (b.collectionID = :collectionID) And (b.zotero_itemID = :zotero_ItemID)
ORDER BY a.modalityID
INTO :ACOLLECTIONID, :ACOLLECTIONSORT, :AZOTERO_ITEMID, :amodalityID, :atagID, :ANAME, :BCOLLECTIONID, :BCOLLECTIONSORT, :BZOTERO_ITEMID, :bmodalityID, :btagID, :BNAME
DO
/* Verify if there are links with the same normative-determinative modalities and factors to avoid duplications */
SELECT count(linkID) as countLinks
FROM matrix
WHERE collectionID = :collectionID And recursiveCollection = :recursiveCollection AND normativeModality = :amodalityID AND normativeFactorID = :atagID And determinativeModality = :bmodalityID AND determinativeFactorID = :btagID
INTO :countLinks;
IF (countLinks > 0) THEN
/* Set synchronized = True only. To avoid duplications */
SELECT totals
FROM get_total_items(:atagid, :btagID, :recursivecollection, :collectionID)
INTO totals;
UPDATE matrix
SET totals = :totals, synchronized = '1'
WHERE collectionID = :collectionID AND recursiveCollection = :recursiveCollection AND normativeModality = :amodalityID AND normativeFactorID = :atagID And determinativeModality = :bmodalityID AND determinativeFactorID = :btagID;
ELSE
/* Add only new links and ignore similar combinations like A-B and B-A if the table Matrix already has one */
SELECT count(linkID) as countLinks
FROM matrix
WHERE collectionID = :collectionID AND recursiveCollection = :recursiveCollection AND normativeModality = :amodalityID AND normativeFactorID = :btagID And determinativeModality = :bmodalityID AND determinativeFactorID = :atagID
INTO :countLinks;
IF (countLinks = 0) THEN
SELECT totals
FROM get_total_items(:atagid, :btagID, :recursivecollection, :collectionID)
INTO totals;
INSERT INTO matrix (collectionID,normativeModality,normativeFactorID,determinativeModality,determinativeFactorID, totals, synchronized, recursiveCollection)
VALUES (:collectionID,:amodalityID,:atagID,:bmodalityID,:btagID, :totals, '1', :recursiveCollection);
END IF;
END IF;
END WHILE;
END IF;
END WHILE;
END IF;
/* Delete all rows that where not updated. It means that all rows with synchronized fields set 0 aren't present at Zotero anymore. */
DELETE FROM matrix
WHERE collectionID = :collectionID AND recursiveCollection = :recursiveCollection AND synchronized = '0';
/* Set all rows updated and added to synchronized = 0 */
UPDATE matrix
SET synchronized = '0'
WHERE collectionID = :collectionID AND recursiveCollection = :recursiveCollection AND synchronized = '1';
SUSPEND;
END
When I try to run I receive this message:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('[',CAST(collectionID AS varchar(255)) , ']') AS collectionSort; WHILE ' at line 22
Can you help me, please?
