MySQL Forums - Cursors

Deleting Rows in a table. (2 replies)

Faheem Ahmad — Fri, 26 Apr 2013 17:08:28 +0000

I have a table like that
MemberId(F.K) ! Event ! Event Date
1 'Test Event1' "2012-03-20 05:39:51"
1 'Test Event1' "2012-03-21 05:39:51"
1 'Test Event1' "2012-03-22 05:39:51"
3 'Test Event3' "2012-03-23 05:39:51"
2 'Test Event2' "2012-03-24 05:39:51"
2 'Test Event2' "2012-03-19 05:39:51"
1 'Test Event1' "2012-03-23 05:49:51"
3 'Test Event3' "2012-03-23 05:49:51"
4 'Test Event4' "2012-03-27 05:39:51"
3 'Test Event3' "2012-03-21 05:39:51"
. . .
. .
and what i require is to keep only two latest events for each members and to delete the rest ones.
i.e.
1 'Test Event1' "2012-03-23 05:49:51"
1 'Test Event1' "2012-03-22 05:39:51"
2 'Test Event2' "2012-03-24 05:39:51"
2 'Test Event2' "2012-03-19 05:39:51"
3 'Test Event3' "2012-03-23 05:39:51"
3 'Test Event3' "2012-03-23 05:49:51"
4 'Test Event4' "2012-03-27 05:39:51"

I am able to do that by using Cursor but i want to avoid using it and to write a pure SQL Query for that ,
any help will be appreciated.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that (no replies)

ayman hodesh — Mon, 25 Feb 2013 08:23:00 +0000

create table department
(
departmentID int not null auto_increment primary key ,
name varchar(30)
)
type=InnoDB;

Getting warning code 1329 Warning Code : 1329 No data - zero rows fetched, selected, or processed (no replies)

santosh kola — Wed, 19 Dec 2012 17:01:53 +0000

when i try to execute below code in mysql 5.0 iam getting warning code 1329
No data - zero rows fetched, selected, or processed. please suggest be how to handle such type of warnings..
/*
Highlight and execute the following statement to drop the procedure
before executing the create statement.

DROP PROCEDURE ProcedureTest.test;

*/

CREATE DEFINER=`AppAdmin`@`%` PROCEDURE `test`(IN newloginId VARCHAR(40),OUT msg VARCHAR(75), out total int(3))
BEGIN

DECLARE d INT DEFAULT 0;
DECLARE isExistedAccId INT DEFAULT 0;
DECLARE a INT;

DECLARE p_AccId INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE accCount int(10);
DECLARE cur CURSOR FOR SELECT AccId FROM Table2 order by AccId;

SELECT count(AccId) into accCount FROM Table2 ORDER BY AccId;
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET isExistedAccId = 0;

OPEN cur;

WHILE(accCount > 0)
DO

FETCH cur INTO p_AccId;

SET isExistedAccId = 0;

-- DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;
-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET isExistedAccId = 0;

SELECT AccountId into isExistedAccId FROM Table1 where AccountId = p_AccId and TeamMemberId = newloginId limit 1;

IF isExistedAccId = 0 THEN
INSERT INTO Table1(AccountId, TeamMemberId, SalesFlag, PrimaryFlag) VALUES(p_AccId,newloginId,1,0);
SET isExistedAccId = 0;
SET i = i + 1;
END IF;
SET accCount = accCount - 1;
END WHILE;
CLOSE cur;

SET msg = 'rows Inserted';
set total = i;
END

Cursor created for view still returns the same data (no replies)

x y — Wed, 21 Nov 2012 07:57:36 +0000

I have data in several tables, that is created every month. I need check all records for inputed user_id.
Here is example:

DROP TABLE IF EXISTS `month`;
CREATE TABLE IF NOT EXISTS `month` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;

DROP TABLE IF EXISTS `month_01`;
CREATE TABLE IF NOT EXISTS `month_01` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;

DROP TABLE IF EXISTS `month_02`;
CREATE TABLE IF NOT EXISTS `month_02` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;

INSERT INTO `month_01` (`user_id`, `value`) VALUES(1,1);
INSERT INTO `month_01` (`user_id`, `value`) VALUES(2,8);

INSERT INTO `month_02` (`user_id`, `value`) VALUES(1,21);
INSERT INTO `month_02` (`user_id`, `value`) VALUES(2,28);

INSERT INTO `month` (`user_id`, `value`) VALUES(1,31);
INSERT INTO `month` (`user_id`, `value`) VALUES(2,38);

DELIMITER $$
DROP PROCEDURE IF EXISTS `create_view`$$
CREATE DEFINER='root'@'localhost'
PROCEDURE `create_view`(
p_user_id INT
)
COMMENT 'create view'
BEGIN
DECLARE l_sql_command VARCHAR(50000);
DECLARE l_table_name VARCHAR(64);

DECLARE done INT DEFAULT FALSE;
DECLARE cur_cv CURSOR FOR
SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=SCHEMA() AND (`TABLE_NAME` LIKE 'month_%') ORDER BY 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

SET l_sql_command = 'CREATE VIEW `v_temp_data` AS ';

OPEN cur_cv;
read_loop: LOOP
FETCH cur_cv INTO l_table_name;
IF done THEN
LEAVE read_loop;
END IF;

IF l_table_name LIKE 'month_%' THEN
SET l_sql_command = CONCAT(l_sql_command
, " SELECT `id`, `user_id`, `value` FROM ",l_table_name ," WHERE `user_id` = ",p_user_id
, " UNION");
END IF;

END LOOP;
CLOSE cur_cv;

SET l_sql_command = CONCAT(l_sql_command
, " SELECT `id`, `user_id`, `value` FROM `month`"," WHERE `user_id` = ",p_user_id
);
SET done = FALSE;

SET @query = l_sql_command;
PREPARE stmt from @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$

DELIMITER $$
DROP PROCEDURE IF EXISTS `process_data`$$
CREATE DEFINER='root'@'localhost'
PROCEDURE `process_data`(
p_user_id INT
)
COMMENT 'process data'
BEGIN
DECLARE l_id INT DEFAULT 0;
DECLARE l_user_id INT DEFAULT 0;
DECLARE l_value INT DEFAULT 0;

DECLARE l_sql_command VARCHAR(50000);

DECLARE done INT DEFAULT FALSE;
DECLARE cur_pd CURSOR FOR SELECT `id`, `user_id`, `value` FROM `v_temp_data`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur_pd;
read_loop: LOOP
FETCH cur_pd INTO l_id, l_user_id, l_value;
IF done THEN
LEAVE read_loop;
END IF;
SELECT IF(p_user_id=l_user_id,'OK','BAD') AS Result, p_user_id, l_user_id, l_value;
END LOOP;
CLOSE cur_pd;
DROP VIEW IF EXISTS `v_temp_data`;
END $$

DROP PROCEDURE IF EXISTS `data_check_user`$$
CREATE DEFINER='root'@'localhost'
PROCEDURE `data_check_user`(
p_user_id INT
)
COMMENT 'Check data for user'
BEGIN
SELECT 'CHECK USER' AS Type, p_user_id AS User;
CALL `create_view` (p_user_id);
CALL `process_data` (p_user_id);
END$$

DROP PROCEDURE IF EXISTS `data_check`$$
CREATE DEFINER='root'@'localhost'
PROCEDURE `data_check`(
)
COMMENT 'Check data'
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE l_id INT DEFAULT 0;
DECLARE cur CURSOR FOR
SELECT 1 UNION SELECT 2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

SELECT 'CHECK ALL' AS Type;
OPEN cur;
read_loop: LOOP
FETCH cur INTO l_id;
IF done THEN
LEAVE read_loop;
END IF;
CALL `data_check_user`(l_id);
END LOOP;
CLOSE cur;
END$$

DELIMITER ;
CALL `data_check`();
CALL `data_check_user`(1);
CALL `data_check_user`(2);

On MySQL 5.1.54 is result:

Type
CHECK ALL
Type User
CHECK USER 1
Result p_user_id l_user_id l_value
OK 1 1 1
Result p_user_id l_user_id l_value
OK 1 1 21
Result p_user_id l_user_id l_value
OK 1 1 31
Type User
CHECK USER 2
Result p_user_id l_user_id l_value
BAD 2 1 1
Result p_user_id l_user_id l_value
BAD 2 1 21
Result p_user_id l_user_id l_value
BAD 2 1 31
Type User
CHECK USER 1
Result p_user_id l_user_id l_value
OK 1 1 1
Result p_user_id l_user_id l_value
OK 1 1 21
Result p_user_id l_user_id l_value
OK 1 1 31
Type User
CHECK USER 2
Result p_user_id l_user_id l_value
OK 2 2 8
Result p_user_id l_user_id l_value
OK 2 2 28
Result p_user_id l_user_id l_value
OK 2 2 38

It shows BAD result when `data_check` repeats calling `data_check_user` with another `user_id`.
On MySQL 5.5.28 it's running correctly:

Type
CHECK ALL
Type User
CHECK USER 1
Result p_user_id l_user_id l_value
OK 1 1 1
Result p_user_id l_user_id l_value
OK 1 1 21
Result p_user_id l_user_id l_value
OK 1 1 31
Type User
CHECK USER 2
Result p_user_id l_user_id l_value
OK 2 2 8
Result p_user_id l_user_id l_value
OK 2 2 28
Result p_user_id l_user_id l_value
OK 2 2 38
Type User
CHECK USER 1
Result p_user_id l_user_id l_value
OK 1 1 1
Result p_user_id l_user_id l_value
OK 1 1 21
Result p_user_id l_user_id l_value
OK 1 1 31
Type User
CHECK USER 2
Result p_user_id l_user_id l_value
OK 2 2 8
Result p_user_id l_user_id l_value
OK 2 2 28
Result p_user_id l_user_id l_value
OK 2 2 38

Is a way to do it correctly on MySQL 5.1.54 ?
Thanks for advice
Dan

Cusors in Stored Procedures + Where Clause (no replies)

Chris Lynch — Tue, 06 Nov 2012 17:23:55 +0000

Hi,

I have a Stored Procedure called sp_import_tax(). It received two input variables from a PHP file.

The Stored Procedure is used to update tables based on a Staging table that data is uploaded too.

Multiple Users can upload data to the table called etl_taxpro - the final column in each row contains an area code. When the user executes the Stored Procedure the area code is passed to it and I want to then use the passed Area Code when I am declaring my cusor so only the specific users rows are used.

See Store Procedure code below

CREATE DEFINER=`root`@`%` PROCEDURE `sp_import_taxpro`(current_user_id VARCHAR(36), areacode VARCHAR(36))
BEGIN

DECLARE tax_number VARCHAR(255);
DECLARE year_end VARCHAR(255);
DECLARE filing_date_f11 DATE DEFAULT '1970-01-01';
DECLARE completion_date_f11 DATE DEFAULT '1970-01-01';
DECLARE submission_date_f11 DATE DEFAULT '1970-01-01';
DECLARE f11_ack VARCHAR(255);
DECLARE submission_date_46g DATE DEFAULT '1970-01-01';
DECLARE 46g_ack VARCHAR(255);
DECLARE prelim_date_paid DATE DEFAULT '1970-01-01';
DECLARE prelim_date DATE DEFAULT '1970-01-01';
DECLARE user_area_code VARCHAR(255);
DECLARE done INT DEFAULT 0;
DECLARE cursor1 CURSOR FOR
SELECT
TaxNumber,
YearEnd,
DATE_FORMAT(STR_TO_DATE(FilingDateF11, '%d/%m/%Y %H:%i:%S'), '%Y-%m-%d') AS filing_date_f11,
DATE_FORMAT(STR_TO_DATE(CompletionDateF11, '%d/%m/%Y %H:%i:%S'), '%Y-%m-%d') AS completion_date_f11,
DATE_FORMAT(STR_TO_DATE(SubmissionDateF11, '%d/%m/%Y %H:%i:%S'), '%Y-%m-%d') AS submission_date_f11,
F11Ack,
DATE_FORMAT(STR_TO_DATE(SubmissionDate46G, '%d/%m/%Y %H:%i:%S'), '%Y-%m-%d') AS submission_date_46g,
46GAck,
DATE_FORMAT(STR_TO_DATE(PrelimTaxDatePaid, '%d/%m/%Y %H:%i:%S'), '%Y-%m-%d') AS prelim_date_paid,
DATE_FORMAT(STR_TO_DATE(PrelimTax, '%d/%m/%Y %H:%i:%S'), '%Y-%m-%d') AS prelim_date,
AreaCode
FROM etl_taxpro
WHERE AreaCode=area_code;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cursor1;
REPEAT FETCH cursor1 INTO tax_number,year_end,filing_date_f11,completion_date_f11,submission_date_f11,f11_ack,submission_date_46g,46g_ack,prelim_date_paid,prelim_date,user_are_code;
IF NOT done THEN

/** The code I execute here will contain all data from the etl_taxpro and not just the date matching area_code
**/

END IF;
UNTIL done END REPEAT;
CLOSE cursor1;

END$$

DELIMITER ;

Is what I want to achieve possible.

Go through two strings with comma in a cursor (no replies)

Tony ding — Fri, 05 Oct 2012 20:09:31 +0000

Hello,

I have a stored procedure to do the follow things

There is a table with 3 columns

ID |Last_Modified | Flag
1 |2011-12-11 02:00:00| 0
2 |2011-12-13 02:00:00| 0
3 |2011-12-02 02:00:00| 0
4 |2011-12-12 02:00:00| 0

the stored procedure “getback(String pId, String pLast_Modified). E.g. pId =”1,2,3” and pLast_Modified = “2011-12-11 02:00:00, 2011-12-13 02:00:00, 2011-12-01 02:00:00”.

The logic is that, for the same ID, if the last_modified in the table is later than the passed last_modified, then set the flag for that record to “1”.

I am trying to find out a simple way to construct a cursor to get the records need to be changed to "1". But it has no luck. Is there anybody who did similar thing like this before??

Very appreciated your help!

Tony

Cursor fetch variable holding null (no replies)

Anoop G — Thu, 20 Sep 2012 05:52:48 +0000

Please help me, am getting null values from cursor

DELIMITER $$

DROP PROCEDURE IF EXISTS `csms_hq_db`.`sp_test`$$
CREATE DEFINER=`csms`@`%` PROCEDURE `sp_test`(IN `tbl_name` VARCHAR(255),IN `rec_id` int,
IN `crud_action` VARCHAR(1),IN `topublish` datetime,IN `updatedat` datetime,IN `is_published` int,IN `table_id` VARCHAR(255))
BEGIN
DECLARE last_fetched_value int;
START TRANSACTION;

BEGIN
DECLARE shop_id BIGINT(20);
DECLARE db_server VARCHAR(255);
DECLARE done INT;
DECLARE cur_shop CURSOR FOR SELECT shop_id,db_server FROM shop_db_settings;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_shop;
read_loop: LOOP
FETCH cur_shop INTO shop_id,db_server;
IF done THEN
LEAVE read_loop;
END IF;
#Insert entity part
INSERT INTO `sync_queue` (`table_name`, `record_id`, `shop_id`, `crud_action`, `publishing_date`, `last_updated`, `sync_status`, `error_msg`)
VALUES (db_server, rec_id, shop_id, crud_action, topublish, updatedat, 0,'');
END LOOP read_loop;
CLOSE cur_shop;
END;

COMMIT;
END$$

DELIMITER ;

how can I extract my base correctly (no replies)

genwa kair — Fri, 24 Aug 2012 18:11:34 +0000

hi,

I work with MySQL Server 5.5 and I have a problem in extracting my data base,this how I used this command:

mysql> mysqldump -uroot -pkail mybase > C:\Program Files\MySQL\MySQL Server 5.5\b
in\base.sql;

and this is the Error that I get:

ERROR:
Unknown command '\P'.
ERROR:
Unknown command '\M'.
ERROR:
Unknown command '\M'.
ERROR:
Unknown command '\b'.
Outfile disabled.
ERROR 1064 (42000): 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 'mysq
dump -uroot -pkail mybase > C:\Program Files\MySQL\MySQL Server 5.5\bin' at
line 1

how can I resolve the problem and thanks for any help :)

Loop over cursor results ends ahead of schedule (1 reply)

Jorge Arévalo — Wed, 22 Aug 2012 17:59:32 +0000

Hello,

I'm executing a loop that iterates over a cursor's results. The code is inside a trigger function, and the part that matters looks like that:

create trigger my_trigger after delete on my_table
for each row
begin

declare my_value int;
declare num_rows int default 0;
declare done int default false;
declare my_cursor cursor for select value from table where condition;
declare continue handler for sqlstate '02000' set done = 1;

open my_cursor;
select found_rows() into num_rows;

-- This is just for debugging
insert into log_table(key, value) values('foo', num_rows);

if num_rows > 0 then:
repeat
fetch my_cursor into my_value;
-- Do stuff
until done end repeat;
end if;

close my_cursor;

end

The loop should be executed 11 times, because the query returns 11 values. This is checked by the 'insert' clause. The var 'num_rows' is equal to 11. But the problem is the loop is only executed 3 times.

These numbers (11, 3) are not important. If I change the query to return a different amount of results, the problem remains: the loop ends before scheduled.

Does it make any sense? I may do something inside the loop (the 'do stuff' part) that causes the end of the loop. It's the only thing that soundslogic for me.

Thanks in advance, and best regards,
Jorge

Create cursor from Varchar or another variable (no replies)

Santiago Del Valle — Fri, 29 Jun 2012 20:48:28 +0000

I have a stored procedure that builds a query based on the parameters of the procedure. I need to iterate through the results later (for some additional processing), so I was thinking on doing so with a cursor. I've read that you can´t create a cursor from a prepared statement (http://forums.mysql.com/read.php?61,116597,116597#msg-116597) and I wanted to avoid the creation of a temporary table for performance issues, is there any way to create a cursor from a variable containing the query?

Greetings and thanks for any help!

Backup Database Error (1 reply)

noah spiegel — Tue, 26 Jun 2012 15:00:16 +0000

mysql> mysqldump -u root -p foobar

mysqldump -u [user] -p [database_name]

ERROR 1064 (42000): 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 'mysqldump -u root -p foobar' at line 1

please help how can i backup a database

store procedure problems (2 replies)

Paolo Rossi — Tue, 26 Jun 2012 14:53:39 +0000

Hi
I had some flouring in sql2000 of MS, but now using MySql I've some problem, particularly on the StoreProcedures.
this code:

DELIMITER //
CREATE PROCEDURE prova (OUT tot INT,OUT c1 INT)
BEGIN
DECLARE cur2 CURSOR FOR SELECT count(*) FROM prove.old WHERE flag <> 0;
OPEN cur2;
FETCH cur2 INTO tot;
CLOSE cur2;
set c1 = tot;
END; //
DELIMITER ;

colling with: CALL prova(@tot,@c1);
select @tot,@c1;
correctly done:
+-------+-------+
| @tot | @c1 |
+-------+-------+
| 33322 | 33322 |
+-------+-------+
1 row in set (0.00 sec)

if I add a CURSOR, nothing work, also the preceding code:
code:
DELIMITER //
CREATE PROCEDURE prova (OUT tot INT,OUT c1 INT, OUT c2 INT)
BEGIN
DECLARE done INT;
DECLARE ip_start BIGINT;
DECLARE ip_to BIGINT;
DECLARE city_code INT;
DECLARE flag TINYINT;

DECLARE cur2 CURSOR FOR SELECT count(*) FROM prove.old WHERE flag <> 0;
DECLARE cur1 CURSOR FOR SELECT * FROM prove.old WHERE flag <> 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur2;
FETCH cur2 INTO tot;
CLOSE cur2;

set c1 = 0; set c2 = 0; set done = 0;

OPEN cur1;
REPEAT
FETCH cur1 INTO ip_start,ip_to,city_code,flag;
IF NOT done THEN
IF flag = 1 THEN
SET c1 = c1 + 1;
ELSE
SET c2 = c2 + 1;
END IF;

END IF;
UNTIL done END REPEAT;
CLOSE cur1;

END; //
DELIMITER ;

when I call: CALL prova(@tot,@c1,@c2);
select @tot,@c1,@c2;
the answer is:

mysql> CALL prova(@tot,@c1,@c2);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @tot,@c1,@c2;
+------+------+------+
| @tot | @c1 | @c2 |
+------+------+------+
| 0 | 0 | 0 |
+------+------+------+
1 row in set (0.00 sec)

why? where am I wrong?
thanks in advance
JeanPaul

how to handle empty data from select statement inside the procedure (no replies)

khadar mohiddin — Tue, 15 May 2012 01:56:03 +0000

In the following code how to handle when there are no rows in select statement like in oracle exception NO_DATA_FOUND ????????
HOW TO HANDLE IN MYSQL ??

SELECT A.STATUS, A.VISIBLE, NVL(A.STATUS_STRING_CODE, -1), NVL(A.STATUS_STRING, ''), NVL(A.LOCALE_STRING, ''),
NVL(A.DEVICE_GROUP, 0), NVL(A.CLIENT_IP_ADDR, ''), NVL(A.CLIENT_PORT_NO, 0), NVL(C.DEVICE_GROUP, 0),
NVL(A.MSISDN, '')
INTO dbStatus, dbVisible, dbStatusStrCode, dbStatusStr, dbLocalStr, dbDeviceGrp, dbIpAddr, dbPortNo, dbOffDevGrp,
dbMsisdn
FROM USERS A, USER_DEVICES B, DEVICES C
WHERE A.USER_ID = dbPalId
AND A.USER_ID = B.USER_ID (+)
AND B.DEVICE_ID = C.DEVICE_ID (+);

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbReturn := PAL_DOES_NOT_EXISTS;
goto END_FUNC;

increment value based on two fields (1 reply)

Long Dog — Sat, 21 Jan 2012 16:07:01 +0000

Hi,

I need to create an incremented value that resets back to 1 based on another field, essentillly a line number per page.

Given data:
page|ID
1|1
1|2
2|3
2|4
3|5
I'd like output of
page|ID|line_number
1|1|1
1|2|2
2|3|1
2|4|2
3|5|1

How would I go about this using purely MySQL?

Any help appreciated!
Thanks

How to use cursor within the trigger? (1 reply)

James ZHAN — Thu, 08 Dec 2011 12:25:32 +0000

the question is: For each student with GPA > 3.5 whose resume contains the keywords “database systems”, create a
notification telling the student about a particular new job announcement that a company has posted.

there's a student table and a job_announcement table. how to write this query?

I try to use cursor, but mysql says it's incorrect.

the sql statement:

CREATE TRIGGER pushdb after INSERT ON job
FOR EACH ROW BEGIN

DECLARE done INT DEFAULT FALSE;
DECLARE temp1 VARCHAR(20);

DECLARE cursor1 CURSOR FOR select sid from student where gpa>3.5 and match(resume) against('database systems');

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cursor1;

read_loop: LOOP

FETCH cursor1 into temp1;

if doen THEN

LEAVE read_loop;
end if;

insert into push values(temp1, new.cid, new.bname, new.jid, timestamp);

end loop;

close cursor1;
end

somebody helps me?

ERROR 1064 (42000): 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 '(id), (2 replies)

PULKIT PATEL — Thu, 12 Jan 2012 07:11:12 +0000

this is that table i want to create:

create table UserDB (
UID varchar(25) NOT NULL primary key (id),
FName varchar(25),
LName varchar(25),
Password varchar(16),
Country varchar(50),
Email varchar(50),
SQues varchar(50),
Ans varchar(50),
CellNos int,
ts timestamp) ENGINE=INNODB;

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
but when ever i run this query it displays:

ERROR 1064 (42000): 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 '(id),
FName varchar(25),
LName varchar(25),
Password varchar(16),
Country varcha' at line 2

1328 Incorrect number of FETCH variables (1 reply)

Wahab Hussain — Tue, 25 Oct 2011 07:44:30 +0000

Getting error
"1328 Incorrect number of FETCH variables"

Should I use a Cursor in my Procedure (1 reply)

Mark O'Donnell — Tue, 25 Oct 2011 05:53:43 +0000

Hi there,

I am wanting to use a procedure to perform a particular calculation based on the Eid number in the example below. When the Eid number is 1 add (Salary + Perks). When Eid is 2 add (Salary + Perks) * 2. So for the sample data below I may want to have 2 different calculations that will return two rows... Thats the basic concept.

Firstly, I'm not sure whether I should be using the curson construct? And secondly, when I run this code below (with the two selects) it only returns 1 row and I am wanting to return two rows (a row for each select statement).

I'm not sure whether I'm on the right track or if I should be using a different control structure? Do I need to use two cursors (as below) or can I use 1 cursor with two Fetch statements? Help!

Thanks, Mark

DELIMITER $$;

DROP PROCEDURE IF EXISTS `test`.`DemoCurs6`$$

CREATE PROCEDURE `test`.`DemoCurs6` ()
BEGIN
DECLARE d INT DEFAULT 0;
DECLARE id,sal,perk, Total INT;
DECLARE name,city,desig VARCHAR(20);
DECLARE cur CURSOR FOR SELECT * FROM Emp;
DECLARE cur1 CURSOR FOR SELECT * FROM Emp;

OPEN cur;

FETCH cur INTO id,name,city,desig,sal,perk;
SELECT Eid, Ename, City, Designation, Salary, Perk, (Salary + Perks) AS Total FROM Emp WHERE Eid = '1';

OPEN cur1;

FETCH cur INTO id,name,city,desig,sal,perk;
SELECT Eid, Ename, City, Designation, Salary, Perk, (Salary + Perks) * 2 AS Total FROM Emp WHERE Eid = '2';

CLOSE cur;
CLOSE cur1;
END$$

DELIMITER ;$$

Example Data

Eid Name City Designation Salary Perks

1 Rahul Delhi Main Manager 10300 853
2 Gaurav Mumbai Assistant 11100 853
3 Chandan Banglore Team Leader 15450 999
4 Tapan Pune Developer 20600 1111
5 Amar Chennai Developer 16000 1124

Unable to write a cursor inside a Stored Procedure (3 replies)

Wahab Hussain — Mon, 24 Oct 2011 13:19:23 +0000

I am trying to write a cursor inside store procedure but it is giving me an error mentioning syntax error

Create procedure test()
begin
insert into accounttype(accounttype) select distinct AccountType from temptableforbulkimport;

select * from temptableforbulkimport;
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;

DECLARE cur1 CURSOR FOR SELECT id,data FROM temptableforbulkimport;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;

read_loop: LOOP
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF done THEN
LEAVE read_loop;
END IF;
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END LOOP;

CLOSE cur1;
end

You Have An Error in you SQL #1064 (no replies)

jose luis antonio de figueiredo — Thu, 20 Oct 2011 13:53:15 +0000

hello im tryin to do this stored procedure but i get this error, i seen it over and over and cannot find where is the error. The code is this:

CREATE PROCEDURE legislacion.resumen (IN parameter_exp VARCHAR(10))

BEGIN

DECLARE var_expediente CHAR(10);
DECLARE var_trami longtext;
DECLARE var_trami2 longtext;

set var_expediente= '195-PL-11';

DECLARE origen CURSOR FOR SELECT identificador, descripcion, cabecera, tipo, descripciontipo, otro, orden, visualizar,internet From legislacion.maeid where tipo = 'OR' and visualizar = 'SI' order by descripcion;*/
DECLARE tipo CURSOR FOR Select * From legislacion.maeid where tipo = 'NR' order by descripcion;
DECLARE estado CURSOR FOR Select * From legislacion.maeid where tipo = 'EE' order by descripcion;
DECLARE exped CURSOR FOR Select * From legislacion.expedientes where expediente = var_expediente;
DECLARE ExpeYtramites CURSOR FOR SELECT tramiteexpediente.tramite,expedientes.expediente, expedientes.ley, expedientes.tema, expedientes.resumen, expedientes.idnorma, expedientes.idorigen, expedientes.idestado, expedientes.autores, expedientes.notas, expedientes.fecingreso, expedientes.idexpte, tramiteexpediente.fechatramite, tramiteexpediente.idtramite
FROM expedientes INNER JOIN tramiteexpediente ON expedientes.idexpte = tramiteexpediente.idexpte WHERE expedientes.expediente = var_exp
ORDER BY expedientes.idnorma, expedientes.idexpte, tramiteexpediente.idtramite;

CREATE TEMPORARY table extras(expediente varchar(10), tramites longtext);

open ExpeYtramites;
fetch ExpeYtramites into var_trami;

while NOT veof do
begin
set var_trami2 = concat(var_trami2,concat('\n',var_trami));
end WHILE;

INSERT INTO extras VALUES (var_exp,var_trami2);

open exped;
open tipo;
open origen;

DECLARE Todosxx CURSOR FOR SELECT b.descripcion as Norma, d.descripcion as Origen, a.expediente, a.fecingreso, a.ley, a.tema, a.resumen, a.idnorma, a.IdEstado, a.autores, a.notas, c.Tramites FROM exped a, tipo b, extras c, origen d WHERE a.idnorma = b.identificador AND a.expediente=c.expediente and a.IdOrigen=d.Identificador ORDER BY a.fecingreso, a.expediente;

open Todosxxx;

DECLARE Todos SELECT estado.descripcion as estado, Todosxx.* FROM Todosxx LEFT JOIN estado ON Todosxx.IdEstado = estado.Identificador;

CLOSE exped;
CLOSE tipo;
CLOSE extras;
CLOSE origen;
CLOSE ExpeYtramites;
CLOSE Todosxxx;

END

i hope you can help me. Regards to everyone

You Have An Error in you SQL #1064 (no replies)

jose luis antonio de figueiredo — Thu, 20 Oct 2011 11:57:13 +0000

Error Number 1415 (1 reply)

Janarthanan S — Tue, 25 Oct 2011 05:56:22 +0000

DELIMITER $$
CREATE TRIGGER `AFTER_USER_INSERT` AFTER INSERT ON `USER`
FOR EACH ROW
BEGIN
DECLARE NEW_USER_ID VARCHAR(25);
DECLARE NEW_USER_TYPE ENUM('ADMIN','PM','QA');
DECLARE NEW_SITE_ID VARCHAR(25);
SELECT USER_ID AS NEW_USER_ID, USER_TYPE AS NEW_USER_TYPE,SITE_ID AS NEW_SITE_ID FROM `USER` WHERE USER_ID=NEW.USER_ID;
IF(NEW_USER_TYPE<>"PM")
THEN
INSERT INTO `SUPPLIERS`(SUPPLIER_ID,SUPPLIER_NAME,CONTACT_NAME,SALES_PHONE,SALES_FAX,SALES_EMAIL,ACTIVE)(SELECT GENERATE_SUPPLIER_ID(),SUPPLIER_NAME,CONTACT_NAME,SALES_PHONE,SALES_FAX,SALES_EMAIL,ACTIVE FROM `SUPPLIERS` WHERE USER_ID=NEW_SITE_ID);
INSERT INTO `USER_PANTRY_LIST`(PRODUCT_ID,USER_ID,SUPPLIER_ID,CATEGORY,CODE,DESCRIPTION,COST,UNIT,STATUS,ACTIVE)(SELECT PRODUCT_ID,NEW.USER_ID,GENERATE_SUPPLIER_ID(),CATEGORY,CODE,DESCRIPTION,COST,UNIT,STATUS,ACTIVE FROM `USER_PANTRY_LIST` WHERE USER_ID=NEW_SITE_ID);
END IF;
END $$
DELIMITER ;

Getting an error as

Not allowed to return a result set from trigger

Can anyone help me to find the solution

PL/SQL or cursors help (1 reply)

NN UU — Tue, 20 Sep 2011 01:35:04 +0000

I need to execute code like this:

select COUNT(*) FROM table_name WHERE table_name.field1 IS NULL;
select COUNT(*) FROM table_name WHERE table_name.field2 IS NULL;
...
select COUNT(*) FROM table_name WHERE table_name.field90 IS NULL;

How can I automate it with cursors or PL/SQL? I have tried make cursor, but it didn't work.

Here is how you can get field1-90:
select column_name from information_schema.columns where table_name='tablename';

ERROR 1064 (42000) (1 reply)

Francisco Hernan — Tue, 13 Sep 2011 08:41:43 +0000

CREATE TABLE cliente(rut VARCHAR(10) not null ,nombre VARCHAR(100) not null ,domicilio VARCHAR(100) not null,provincia VARCHAR(50) not null,PRIMARY KEY(rut))ENGINE=INNODB;

CREATE PROCEDURE insert_cliente(in rutr VARCHAR(10),in nombrer VARCHAR(100),in domicilior VARCHAR(100),in provinciar VARCHAR(50))
BEGIN
DECLARE bandera BOOLEAN;
DECLARE validar BOOLEAN DEFAULT FALSE;
DECLARE rutc VARCHAR(10);
DECLARE nombrec VARCHAR(100);
DECLARE domicilioc VARCHAR(100);
DECLARE provinciac VARCHAR(50);
DECLARE cur_1 CURSOR FOR SELECT rut,nombre,domicilio,provincia FROM almacen.cliente;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET bandera = TRUE;
OPEN cur_1;
cur_1:LOOP
FETCH cur_1 INTO rutc,nombrec,domicilioc,provinciac;
IF bandera THEN
LEAVE cur_1;
END IF;
IF rutc=@rutr THEN
SET @mensaje = 'CLIENTE YA EXISTE ';
SELECT @mensaje,rutc,' ',nombrec,' ',domicilioc,' ',provinciac;
SET validar = TRUE;
END IF;
END LOOP cur_1;
CLOSE cur_1;
IF (validar = FALSE) THEN
INSERT INT0 almacen.cliente(rut,nombre,domicilio,provincia) VALUES (rutr, nombrer, domicilior, provinciar);
END IF;
END

Can I Write Two cursors one inside another (no replies)

ragen sol — Mon, 12 Sep 2011 05:27:23 +0000

I want to write two cursors One inside another. Both are used to insert values into to a table. But my requirement, While the outer procedure executes and fetches one value the inner procedure executes and should get 5 values. This means that the insert statement will execute five times for one value of outer cursor and then the execution goes back to outer procedure.
Do I have to declare two continue handler variable for two procedures. Because any how I am going use loop.

So is it possible. Any One Please suggest.

getting total count (1 reply)

Kelly Brace — Tue, 13 Sep 2011 08:12:54 +0000

I am trying to get a total of each statusType for each agent..

example of data in accts table (there are more columns, but I'm only concerned with these 2):

agent statusType
Kelly New
Tom No Info
Kelly Active
Kelly New
Kelly Exhausted
Mary Sold
Mary New

Data is all in one table called accts.

I would like something similar to this as my output:
Kelly:
New 2
Active 1
Exhausted 1
........

Any help would be greatly appreciated.

Cursor Problems (no replies)

Emiliano Jordan — Sat, 27 Aug 2011 21:40:55 +0000

I'm working on my first cursor right now and can't get it right. Running the cursor I keep getting a syntax error on line 2. Any help would be appreciated.

begin
DECLARE post_id INT;
DECLARE v_not_found BOOL default FALSE;
DECLARE csr_pst CURSOR FOR
SELECT ID
FROM wp_353qq6_posts
WHERE post_title LIKE '%English%'
AND post_parent = '0';
DECLARE continue handler
for not found
set v_not_found := TRUE;
DECLARE exit handler
for sqlexception
close csr_pst;
open csr_pst;
cursor_loop: loop
fetch csr_pst into
post_id
;
if v_not_found then
leave cursor_loop;
end if;
INSERT INTO wp_353qq6_term_relationships VALUES (post_id,666,0);
end loop;
close csr_pst;
end;

(I'm totally new to MySQL in regards to anything this complicated. Thanks to Roland for at least giving me somewhere to start! http://rpbouman.blogspot.com)

re-opening a cursor (1 reply)

Raj Chak — Tue, 23 Aug 2011 08:08:36 +0000

Hi,
Is it possible to reopen a cursor after it has been closed.
My SP has a cursor open-fetch-close inside a loop.
It fetches only in the first pass of the loop.
Please take a look and advice.

BEGIN

declare new_time_id int(10);

declare is_last bool default false;
declare each_day_id, each_time_id int(10);
declare each_start_time, each_end_time, each_duration varchar(5);

DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE c CURSOR FOR SELECT id, start_time, end_time, duration
                     FROM qrt_times
                     WHERE campaign_id = in_campaign_id and day_id = in_day_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;


START TRANSACTION;

-- Iterate over a comma delimited string of numbers. Like '1,2'
def_loop: LOOP

  if locate(',',in_day_ids) = 0 then
    set each_day_id = in_day_ids;
    set is_last = true;
  else
    set each_day_id = substring(in_day_ids, 1, locate(',', in_day_ids)-1);
    set in_day_ids = substring(in_day_ids, locate(',', in_day_ids)+1);
  end if;


  -- Iterate over the cursor of time records for the day to copy from
  OPEN c;

  dep_loop: LOOP

    FETCH c INTO each_time_id, each_start_time, each_end_time, each_duration;

    IF done THEN
      LEAVE dep_loop;
    END IF;

    -- Processing done here

  END LOOP dep_loop;

  CLOSE c;


  if is_last then
    leave def_loop;
  end if;

end loop def_loop;

COMMIT;


END $$

1064 Error on Importing SQL Database file (no replies)

jhon Preston — Sun, 17 Jul 2011 16:56:07 +0000

I have exported my database from a PHP My Admin version 3.4 1 and now i am trying to import that file but it giving me a 1064 error in syntax.

See here

***************************************************
Error

SQL query:

INSERT INTO `wp_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES (181, 1, '2010-12-13 16:03:00', '2010-12-13 16:03:00', 'Touchscreen Pino Polo Mobile Phone Specifications and Review\r\n\r\nToday we are about to tell some of the specifications of the latset mobile phone launced by a Singapore based company.This Pine Polo mobile phone has all the features.You have everything in this mobile phone like internet.It also works with OPera mini browser, now you can browse anything on internet on this mobile just like the Laptop or a Personal Computer.It has great features and applications like Snaptu, Snaptu is one of the most famous applications which helps you t[...]

MySQL said: Documentation
#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 ''<strong>Touchscreen Pino Polo Mobile Phone Specifications and Review</strong>\r' at line 2
*************************************************************

Can anyone help me in solving this MySQL error so that i can successfully import my database.

Error in MySql script (no replies)

Kumar Vikram Dev — Fri, 15 Jul 2011 14:51:12 +0000

Hi,
I am trying to execute a script from my Linux server and it throws below error. When I execute this from mysql workbench, it works fine.

[mysql@dev03 ~]$ mysql --protocol=tcp -u root -pxxxx < /tmp/provisioning_db_dump_04042011.sql
ERROR 1064 (42000) at line 4274: 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 '// DELIMITER ;

DROP PROCEDURE IF EXISTS `spGetOffersForAccountId`;

Can someone please help?

Thanks.