MySQL Forums
Forum List  »  Microsoft Access

Re: Stored Procedure And Corsor
Posted by: Robert Tulloch
Date: March 30, 2018 10:56AM

Here is what I am trying and the SQL failure.

The scrolling logic in a perl module and the existing functionality fails when it hits multiple instances of the same lastname.

I have not written a stored procedure for 15 years so rust is in the way.

CREATE PROCEDURE `previousRecord`(IN inID INT, IN searchfield VARCHAR(8), OUT outId INT)
BEGIN

declare inId int;
declare cur1done int;
declare cur2done int;

declare cur1 cursor for
select id from users where id = inID ORDER BY searchfield DESC;

declare continue handler for not found set cur1done = 1;

set cur1done = 0;

declare cur2 cursor for
select id from users where id = inId ORDER BY searchfield DESC;

declare continue handler for not found set cur2Done = 1;

set cur2done = 0;

open cur1;
fetch cur1 into inId;
open cur2;
fetch cur2 into outId;

close cur1;
close cur1;

END

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 'declare cur2 cursor for
select id from users where id = inId ORDER BY ' at line 15

I tried commenting out DESC and that makes no difference. Looks very straightforward to me.

The select works directly in MyAdmin SQL as is expected.

The idea here is that you send the procedure a lastname. That is used to get the record id. The next fetch gets you the record id of the next record which is returned to the caller.

Options: ReplyQuote


Subject
Views
Written By
Posted
196
March 30, 2018 05:23AM
114
March 30, 2018 10:20AM
Re: Stored Procedure And Corsor
121
March 30, 2018 10:56AM
122
March 30, 2018 12:45PM
114
March 30, 2018 01:53PM
111
March 30, 2018 02:04PM
114
March 31, 2018 07:25PM
113
March 31, 2018 08:00PM
114
April 01, 2018 05:27PM
116
April 02, 2018 09:48AM
107
March 30, 2018 12:35PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.