Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can you disable result output for the mysql EXECUTE command in workbench

I'm trying to use a prepared statement in mysql workbench in a cursor. The cursor works on a very big data set so it is executed many times. Every time a new result is shown for the EXECUTE step. This results eventually in mysql workbench crashing because of too many open result windows.

In the cursor I do something like this:

PREPARE stmt2 FROM @eveningQuery;
EXECUTE stmt2; 
DEALLOCATE PREPARE stmt2;

Normally I use stuff like

set aVar = (EXECUTE stmt2); 

to silence the query but EXECUTE doesn't work like that.

Does anybody know how you can disable the output for the EXECUTE command in mysql?

Note: I understand how i can retrieve the data in a variable, however what I want to prevent is that it is displayed in the results overview like this enter image description here

This will make mysql-workbench crash when looped too much.

edit because it was asked an example of the @eveningQuery.

SET @eveningQuery = CONCAT('select @resultNm := exists (select idSplitBill from tb_SplitDay where idSplitBill =', idSplitBillVar, ' and ', @columnNameEv ,' = 1 and softdelete = 0)');

idSplitBillVar = the id coming from the cursor. @columnNameEv = a column that i am filling in variably.

I added this info because it was asked, however it doesn't really matter in my opinion because the question still stands even with the most simple query. When you execute a prepared statement, you will get a output result. I just want to disable this behaviour.

like image 475
Jeroen Coupé Avatar asked Mar 07 '13 22:03

Jeroen Coupé


1 Answers

The query you use creates new result-set, and GUI client show it (...many times) -

SELECT @resultNm:=EXISTS(
  SELECT idSplitBill FROM tb_SplitDay
  WHERE idSplitBill =', idSplitBillVar, ' AND ', @columnNameEv ,' = 1 AND softdelete = 0
)

You can rewrite this query, and result-set won't be created -

SELECT EXISTS(
  SELECT idSplitBill FROM tb_SplitDay
  WHERE idSplitBill =', idSplitBillVar, ' AND ', @columnNameEv ,' = 1 AND softdelete = 0
)
INTO @resultNm
like image 64
Devart Avatar answered Sep 25 '22 11:09

Devart