Description:
Using the sample in the documentation to open and repeat till done on a cursor will work for simple queries but not for advanced queries.
Attempting to open a query for the following
DECLARE invcur CURSOR FOR SELECT i.invoice_id, i.Amount AS Total, SUM(ip.CPL_applied_amt) AS ApplAmt, i.InvoiceType_id FROM invoice i LEFT JOIN invoicepayment ip on i.invoice_id = ip.invoice_id WHERE i.Customer_id = inCustID GROUP BY i.invoice_id HAVING sum(IF(ip.CPL_applied_amt IS NULL, 0,ip.CPL_applied_amt)) < Total;
Tables are developed using INNODB
Working from the command line
How to repeat:
Table strucure
--
-- Table structure for table `customerpaymentlog`
--
DROP TABLE IF EXISTS customerpaymentlog;
CREATE TABLE customerpaymentlog (
CPL_id bigint(20) unsigned NOT NULL auto_increment,
Customer_id bigint(20) unsigned NOT NULL default '0',
Payment float(10,2) NOT NULL default '0.00',
Commission float(10,2) default NULL,
Transaction_dt date NOT NULL default '0000-00-00',
Dealer_id bigint(20) unsigned NOT NULL default '0',
CPL_id_void bigint(20) unsigned default NULL,
Reconcile enum('Y','N') default 'N',
PRIMARY KEY (CPL_id),
INDEX IDX_CUSTPAYLOG_CUSTOMER (Customer_id),
INDEX IDX_CUSTPAYLOG_DEALER (Dealer_id),
CONSTRAINT FK_CUSTPAYLOG_CUST_ID FOREIGN KEY (Customer_id) REFERENCES Customer(Customer_id),
CONSTRAINT FK_CUSTPAYLOG_DEALER_ID FOREIGN KEY (Dealer_id) REFERENCES Dealer(Dealer_id)
) ENGINE=INNODB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `customerpaymentlog`
--
/*!40000 ALTER TABLE customerpaymentlog DISABLE KEYS */;
LOCK TABLES customerpaymentlog WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE customerpaymentlog ENABLE KEYS */;
--
-- Table structure for table `invoicetype`
--
DROP TABLE IF EXISTS invoicetype;
CREATE TABLE invoicetype (
InvoiceType_id bigint(20) unsigned NOT NULL auto_increment,
Descr varchar(30) default NULL,
PRIMARY KEY (Invoicetype_id)
) ENGINE=INNODB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `invoicetype`
--
/*!40000 ALTER TABLE invoicetype DISABLE KEYS */;
LOCK TABLES invoicetype WRITE;
INSERT INTO invoicetype VALUES (1,'Automatic'),(2,'Change Feature'),(3,'First Payment'),(4,'Reconnection');
UNLOCK TABLES;
/*!40000 ALTER TABLE invoicetype ENABLE KEYS */;
-- Table structure for table `invoice`
--
DROP TABLE IF EXISTS invoice;
CREATE TABLE invoice (
Invoice_id bigint(20) unsigned NOT NULL auto_increment,
Customer_id bigint(20) unsigned NOT NULL default '0',
Tax float(10,2) NOT NULL default '0.00',
Amount float(10,2) default '0.00',
Invoice_dt date NOT NULL default '0000-00-00',
Creation_dt timestamp NOT NULL,
InvoiceType_id bigint(20) unsigned NOT NULL default '0',
PRIMARY KEY (Invoice_id),
INDEX IDX_INVOICE_CUST (Customer_id),
INDEX IDX_INVOICE_INVTYPE (InvoiceType_id),
CONSTRAINT FK_INVOICE_CUST_ID FOREIGN KEY (Customer_id) REFERENCES Customer(Customer_id),
CONSTRAINT FK_INVOICE_INVTYPE_ID FOREIGN KEY (InvoiceType_id) REFERENCES InvoiceType(InvoiceType_id)
) ENGINE=INNODB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `invoice`
--
/*!40000 ALTER TABLE invoice DISABLE KEYS */;
LOCK TABLES invoice WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE invoice ENABLE KEYS */;
--
-- Table structure for table `invoicepayment`
--
DROP TABLE IF EXISTS invoicepayment;
CREATE TABLE invoicepayment (
Invoice_id bigint(20) unsigned NOT NULL default '0',
CPL_id bigint(20) unsigned NOT NULL default '0',
CPL_applied_amt float(10,2) NOT NULL default '0.00',
INDEX IDX_IPAY_INVOICE (Invoice_id),
INDEX IDX_IPAY_CPL (CPL_id),
CONSTRAINT FK_IPAY_INVOICE_ID FOREIGN KEY (Invoice_id) REFERENCES Invoice(Invoice_id),
CONSTRAINT FK_IPAY_CPL_ID FOREIGN KEY (CPL_ID) REFERENCES customerpaymentlog(CPL_id)
) ENGINE=INNODB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `invoicepayment`
--
/*!40000 ALTER TABLE invoicepayment DISABLE KEYS */;
LOCK TABLES invoicepayment WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE invoicepayment ENABLE KEYS */;
input the following stored procedure
Create procedure sp_CustomerPayment (in inCPLid BIGINT unsigned, in inCustID BIGINT unsigned, in inPayment Float(10,2), in inTransDt Date, in inDealID BIGINT Unsigned, in inReconciled varchar(1) , in inAction integer, OUT ErrID INTEGER)
BEGIN
DECLARE PotOMoney FLOAT(10,2);
DECLARE InvPayExt FLOAT(10,2);
DECLARE RecExt VARCHAR(1);
DECLARE CommisInvID BIGINT Unsigned;
DECLARE invid, invtypid BIGINT Unsigned;
DECLARE tot,aplamt,CurAplAmt,calcComm FLOAT(10,2);
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-- DECLARE invcur CURSOR FOR SELECT i.invoice_id, i.Amount AS Total, SUM(ip.CPL_applied_amt) AS ApplAmt, i.InvoiceType_id FROM invoice i LEFT JOIN invoicepayment ip on i.invoice_id = ip.invoice_id WHERE i.Customer_id = inCustID GROUP BY i.invoice_id HAVING sum(IF(ip.CPL_applied_amt IS NULL, 0,ip.CPL_applied_amt)) < Total;
DECLARE invcur CURSOR FOR SELECT i.invoice_id, i.Amount AS Total, SUM(ip.CPL_applied_amt) AS ApplAmt, i.InvoiceType_id FROM invoice i LEFT JOIN invoicepayment ip on i.invoice_id = ip.invoice_id GROUP BY i.invoice_id;
DECLARE ERROR_REC_PAY_DEL INTEGER DEFAULT -5; -- look up in ERROR table
DECLARE ERROR_REC_PAY_UPD INTEGER DEFAULT -4; -- look up in ERROR table
SET ErrID = 0;
-- CPL_id_void --> to reference the transaction with the corrected dealer / customer
CASE inAction
WHEN 1 THEN -- INSERT
INSERT INTO CustomerPaymentLog (Customer_id, Payment, Transaction_dt, Dealer_id) VALUES (inCustID,inPayment,inTransDt,inDealID);
WHEN 2 THEN -- UPDATE
SET PotOMoney = inPayment;
SELECT Payment, Reconcile into InvPayExt, RecExt FROM CustomerPaymentLog WHERE CPL_id = inCPLid;
-- calculate the commission
-- determine if the payment entered pays at least 50% a base package; if not then no commission on this payment.
-- get all invoices where the Amount of the invoice is greater than the summation of the applied amount from invoicepayment for that invoice
-- apply this payment to the earliest unpaid invoice. Any excess will be applied to the next in cronological order till no money is left in this payment
IF (RecExt = 'Y') THEN
SET ErrID = ERROR_REC_PAY;
END IF;
-- IF (inReconciled = 'Y' AND RecExt = 'N') THEN
open invcur;
REPEAT
FETCH invcur into invid;
-- IF NOT done THEN
-- INSERT INTO mytest(Invoice_id, Total, AppliedAmt, InvoiceTypeID) VALUES (invid,tot, aplamt, invtypid);
SET ErrID = -10;
-- END IF;
UNTIL done END REPEAT;
close invcur;
-- END IF;
WHEN 3 THEN -- DELETE
SELECT Payment, Reconcile into InvPayExt, RecExt FROM CustomerPaymentLog WHERE CPL_id = inCPLid;
if(inReconciled = 'N' AND RecExt = 'N') THEN
DELETE FROM CustomerPaymentLog WHERE CPL_id = inCPLid;
ELSE
SET ErrID = ERROR_REC_PAY_DEL;
END IF;
END CASE;
END;$
Add data to the invoice customerpaymentlog and invoicepayment table
Run the query at the command line
CURSOR FOR SELECT i.invoice_id, i.Amount AS Total, SUM(ip.CPL_applied_amt) AS ApplAmt, i.InvoiceType_id FROM invoice i LEFT JOIN invoicepayment ip on i.invoice_id = ip.invoice_id WHERE i.Customer_id = <<inCustID>> GROUP BY i.invoice_id HAVING sum(IF(ip.CPL_applied_amt IS NULL, 0,ip.CPL_applied_amt)) < Total;
rows return fine.
Clear the tables just set.
call the procedure and pass in data making the action 1 to start
call the procedure again pass in data making the action 2
The server will crash.
Restart the server
Change the cursor sql to something simple like
select i.invoice_id from invoice i;
drop the procedure and reup
run the procedure again using the second call defined above with action 2
procedure will run just fine.
Suggested fix:
cursor can't seem to open on complex query. Have no idea why! I would imagine that the same engine to parse a sql statement on command line would be used for a cursor as well.
Description: Using the sample in the documentation to open and repeat till done on a cursor will work for simple queries but not for advanced queries. Attempting to open a query for the following DECLARE invcur CURSOR FOR SELECT i.invoice_id, i.Amount AS Total, SUM(ip.CPL_applied_amt) AS ApplAmt, i.InvoiceType_id FROM invoice i LEFT JOIN invoicepayment ip on i.invoice_id = ip.invoice_id WHERE i.Customer_id = inCustID GROUP BY i.invoice_id HAVING sum(IF(ip.CPL_applied_amt IS NULL, 0,ip.CPL_applied_amt)) < Total; Tables are developed using INNODB Working from the command line How to repeat: Table strucure -- -- Table structure for table `customerpaymentlog` -- DROP TABLE IF EXISTS customerpaymentlog; CREATE TABLE customerpaymentlog ( CPL_id bigint(20) unsigned NOT NULL auto_increment, Customer_id bigint(20) unsigned NOT NULL default '0', Payment float(10,2) NOT NULL default '0.00', Commission float(10,2) default NULL, Transaction_dt date NOT NULL default '0000-00-00', Dealer_id bigint(20) unsigned NOT NULL default '0', CPL_id_void bigint(20) unsigned default NULL, Reconcile enum('Y','N') default 'N', PRIMARY KEY (CPL_id), INDEX IDX_CUSTPAYLOG_CUSTOMER (Customer_id), INDEX IDX_CUSTPAYLOG_DEALER (Dealer_id), CONSTRAINT FK_CUSTPAYLOG_CUST_ID FOREIGN KEY (Customer_id) REFERENCES Customer(Customer_id), CONSTRAINT FK_CUSTPAYLOG_DEALER_ID FOREIGN KEY (Dealer_id) REFERENCES Dealer(Dealer_id) ) ENGINE=INNODB DEFAULT CHARSET=latin1; -- -- Dumping data for table `customerpaymentlog` -- /*!40000 ALTER TABLE customerpaymentlog DISABLE KEYS */; LOCK TABLES customerpaymentlog WRITE; UNLOCK TABLES; /*!40000 ALTER TABLE customerpaymentlog ENABLE KEYS */; -- -- Table structure for table `invoicetype` -- DROP TABLE IF EXISTS invoicetype; CREATE TABLE invoicetype ( InvoiceType_id bigint(20) unsigned NOT NULL auto_increment, Descr varchar(30) default NULL, PRIMARY KEY (Invoicetype_id) ) ENGINE=INNODB DEFAULT CHARSET=latin1; -- -- Dumping data for table `invoicetype` -- /*!40000 ALTER TABLE invoicetype DISABLE KEYS */; LOCK TABLES invoicetype WRITE; INSERT INTO invoicetype VALUES (1,'Automatic'),(2,'Change Feature'),(3,'First Payment'),(4,'Reconnection'); UNLOCK TABLES; /*!40000 ALTER TABLE invoicetype ENABLE KEYS */; -- Table structure for table `invoice` -- DROP TABLE IF EXISTS invoice; CREATE TABLE invoice ( Invoice_id bigint(20) unsigned NOT NULL auto_increment, Customer_id bigint(20) unsigned NOT NULL default '0', Tax float(10,2) NOT NULL default '0.00', Amount float(10,2) default '0.00', Invoice_dt date NOT NULL default '0000-00-00', Creation_dt timestamp NOT NULL, InvoiceType_id bigint(20) unsigned NOT NULL default '0', PRIMARY KEY (Invoice_id), INDEX IDX_INVOICE_CUST (Customer_id), INDEX IDX_INVOICE_INVTYPE (InvoiceType_id), CONSTRAINT FK_INVOICE_CUST_ID FOREIGN KEY (Customer_id) REFERENCES Customer(Customer_id), CONSTRAINT FK_INVOICE_INVTYPE_ID FOREIGN KEY (InvoiceType_id) REFERENCES InvoiceType(InvoiceType_id) ) ENGINE=INNODB DEFAULT CHARSET=latin1; -- -- Dumping data for table `invoice` -- /*!40000 ALTER TABLE invoice DISABLE KEYS */; LOCK TABLES invoice WRITE; UNLOCK TABLES; /*!40000 ALTER TABLE invoice ENABLE KEYS */; -- -- Table structure for table `invoicepayment` -- DROP TABLE IF EXISTS invoicepayment; CREATE TABLE invoicepayment ( Invoice_id bigint(20) unsigned NOT NULL default '0', CPL_id bigint(20) unsigned NOT NULL default '0', CPL_applied_amt float(10,2) NOT NULL default '0.00', INDEX IDX_IPAY_INVOICE (Invoice_id), INDEX IDX_IPAY_CPL (CPL_id), CONSTRAINT FK_IPAY_INVOICE_ID FOREIGN KEY (Invoice_id) REFERENCES Invoice(Invoice_id), CONSTRAINT FK_IPAY_CPL_ID FOREIGN KEY (CPL_ID) REFERENCES customerpaymentlog(CPL_id) ) ENGINE=INNODB DEFAULT CHARSET=latin1; -- -- Dumping data for table `invoicepayment` -- /*!40000 ALTER TABLE invoicepayment DISABLE KEYS */; LOCK TABLES invoicepayment WRITE; UNLOCK TABLES; /*!40000 ALTER TABLE invoicepayment ENABLE KEYS */; input the following stored procedure Create procedure sp_CustomerPayment (in inCPLid BIGINT unsigned, in inCustID BIGINT unsigned, in inPayment Float(10,2), in inTransDt Date, in inDealID BIGINT Unsigned, in inReconciled varchar(1) , in inAction integer, OUT ErrID INTEGER) BEGIN DECLARE PotOMoney FLOAT(10,2); DECLARE InvPayExt FLOAT(10,2); DECLARE RecExt VARCHAR(1); DECLARE CommisInvID BIGINT Unsigned; DECLARE invid, invtypid BIGINT Unsigned; DECLARE tot,aplamt,CurAplAmt,calcComm FLOAT(10,2); DECLARE done INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- DECLARE invcur CURSOR FOR SELECT i.invoice_id, i.Amount AS Total, SUM(ip.CPL_applied_amt) AS ApplAmt, i.InvoiceType_id FROM invoice i LEFT JOIN invoicepayment ip on i.invoice_id = ip.invoice_id WHERE i.Customer_id = inCustID GROUP BY i.invoice_id HAVING sum(IF(ip.CPL_applied_amt IS NULL, 0,ip.CPL_applied_amt)) < Total; DECLARE invcur CURSOR FOR SELECT i.invoice_id, i.Amount AS Total, SUM(ip.CPL_applied_amt) AS ApplAmt, i.InvoiceType_id FROM invoice i LEFT JOIN invoicepayment ip on i.invoice_id = ip.invoice_id GROUP BY i.invoice_id; DECLARE ERROR_REC_PAY_DEL INTEGER DEFAULT -5; -- look up in ERROR table DECLARE ERROR_REC_PAY_UPD INTEGER DEFAULT -4; -- look up in ERROR table SET ErrID = 0; -- CPL_id_void --> to reference the transaction with the corrected dealer / customer CASE inAction WHEN 1 THEN -- INSERT INSERT INTO CustomerPaymentLog (Customer_id, Payment, Transaction_dt, Dealer_id) VALUES (inCustID,inPayment,inTransDt,inDealID); WHEN 2 THEN -- UPDATE SET PotOMoney = inPayment; SELECT Payment, Reconcile into InvPayExt, RecExt FROM CustomerPaymentLog WHERE CPL_id = inCPLid; -- calculate the commission -- determine if the payment entered pays at least 50% a base package; if not then no commission on this payment. -- get all invoices where the Amount of the invoice is greater than the summation of the applied amount from invoicepayment for that invoice -- apply this payment to the earliest unpaid invoice. Any excess will be applied to the next in cronological order till no money is left in this payment IF (RecExt = 'Y') THEN SET ErrID = ERROR_REC_PAY; END IF; -- IF (inReconciled = 'Y' AND RecExt = 'N') THEN open invcur; REPEAT FETCH invcur into invid; -- IF NOT done THEN -- INSERT INTO mytest(Invoice_id, Total, AppliedAmt, InvoiceTypeID) VALUES (invid,tot, aplamt, invtypid); SET ErrID = -10; -- END IF; UNTIL done END REPEAT; close invcur; -- END IF; WHEN 3 THEN -- DELETE SELECT Payment, Reconcile into InvPayExt, RecExt FROM CustomerPaymentLog WHERE CPL_id = inCPLid; if(inReconciled = 'N' AND RecExt = 'N') THEN DELETE FROM CustomerPaymentLog WHERE CPL_id = inCPLid; ELSE SET ErrID = ERROR_REC_PAY_DEL; END IF; END CASE; END;$ Add data to the invoice customerpaymentlog and invoicepayment table Run the query at the command line CURSOR FOR SELECT i.invoice_id, i.Amount AS Total, SUM(ip.CPL_applied_amt) AS ApplAmt, i.InvoiceType_id FROM invoice i LEFT JOIN invoicepayment ip on i.invoice_id = ip.invoice_id WHERE i.Customer_id = <<inCustID>> GROUP BY i.invoice_id HAVING sum(IF(ip.CPL_applied_amt IS NULL, 0,ip.CPL_applied_amt)) < Total; rows return fine. Clear the tables just set. call the procedure and pass in data making the action 1 to start call the procedure again pass in data making the action 2 The server will crash. Restart the server Change the cursor sql to something simple like select i.invoice_id from invoice i; drop the procedure and reup run the procedure again using the second call defined above with action 2 procedure will run just fine. Suggested fix: cursor can't seem to open on complex query. Have no idea why! I would imagine that the same engine to parse a sql statement on command line would be used for a cursor as well.