1

I am trying to write a simple cursor from inside my DBVisualizer tool against MySQL. Note that I am not writing a stored procedure. I am just writing a SQL Script which I plan to execute against the DB.

my script looks like

block1: BEGIN
        declare my_id varchar(200);
        declare foo cursor for select id from TableA where code in ('A', 'B');
        open foo;
        loop1: LOOP
                fetch foo into my_id;
                select my_id;    
        END LOOP loop1;
        close foo;
END block1;

I found many many many examples on the web and everyone wrote the same code, but somehow this is not working for me. it says

3:39:42  [CLOSE - 0 rows, 0.010 secs]  [Code: 1064, SQL State: 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 'close foo' 
at line 1

My mysql version is

5.6.27-75.0-log

why is this not working for me? Once again, the above code was executed from inside DBVisualizer and this is not a stored procedure.

Knows Not Much
  • 30,395
  • 60
  • 197
  • 373
  • Is that you have tried in stored procedure procedure. – khalid Sep 30 '16 at 18:58
  • No I don't want a stored procedure. I want to write a script. – Knows Not Much Sep 30 '16 at 18:59
  • use `DELIMITER //` before begin It might help – khalid Sep 30 '16 at 19:00
  • nah that stuff only works in stored programs like procs and events – Drew Sep 30 '16 at 19:00
  • Basically you are thinking this whole thing wrong. There are a bunch (well not many) calls that can only exist in a store proc like stored program. What you are doing is the equivalent of those same set of calls run directly in mysql command line tools. They dont work – Drew Sep 30 '16 at 19:02
  • So how to loop through data without a stored proc? – Knows Not Much Sep 30 '16 at 19:03
  • you can't. It is not like a `REPL` in Python or Scala interactive. – Drew Sep 30 '16 at 19:04
  • It is no big deal. Just in that script do a `drop if exists` then `create` for the proc. next call is the `CALL myProc(a,b,c);` then the `drop`. If you get stuck or need help just ping me in the [Campaigns](http://chat.stackoverflow.com/rooms/95290) chat room – Drew Sep 30 '16 at 19:06
  • Test file works fine for me (everything there except last 2 lines that are run at shell, depending on o/s) [Link](http://pastebin.com/hN3kYyrA) – Drew Sep 30 '16 at 19:29
  • So in the manual like here for [DECLARE](http://dev.mysql.com/doc/refman/5.7/en/declare.html) or here for [IF](https://dev.mysql.com/doc/refman/5.7/en/if.html), you see at the top of those page the mention of "programs". Those types of sql cmds are off limits outside of programs (programs such as stored procs, events, functions, triggers). Then there are some calls that are just illegal in programs (such as `LOAD DATA INFILE` in all, and you can't have `PREPARE`s and `EXECUTE`s in triggers). So it depends all in what you are doing. – Drew Sep 30 '16 at 19:45

0 Answers0