SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[11.5.362 Pro] - Custom query macro $$ in MariaDB

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[11.5.362 Pro] - Custom query macro $$ in MariaDB
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [11.5.362 Pro] - Custom query macro $$ in MariaDB Reply with quote
I have a snippet that uses the custom query macro. The code in it either displays the contents of a table or truncates it, depending on the context (contents of the current line) where it was executed. The snippet Action is set to Execute and Display Output Results because I want to see the results (if there are any) in a neat table. I also don't want them put into the code editor. The first part is okay, when there is a result (the table is queried) that result is shown in a result pane. However, when the truncate part is run, there's no result returned, and SA throws an error message:
Quote:
An error occurred in user-defined query, see Messages pane for details.


The Messages pane isn't shown to check what the error could be. But I guess there aren't any errors as the actual code for the truncate table is run, it's effects (the empty table) can be observed. My guess is that SA stubbornly wants to display something when there's nothing to be displayed and it comes out as an error, instead of simple feedback that there's, well, nothing to be displayed.

Here's the snippet in case you want to check its behavior:
Code:

$$
SET @statement = '';

SET @line := trim('$CURRENT(line, dont_replace)$');
SET @alter_line := CASE WHEN LEFT(trim(@line), 3) = '-- ' THEN substring(@line, 4, 1000) ELSE (@line) END;

SELECT CASE
           WHEN @line in ('truncate', '-- truncate', '-- truncate')  THEN 'TRUNCATE TABLE `mlog`; '
           WHEN @alter_line in ('truncate', '-- truncate', '-- truncate')  THEN 'TRUNCATE TABLE `mlog`; '
           WHEN @line != ''  THEN
                'SELECT * FROM `mlog` AS m WHERE 1 = 1 AND m.`tid` >= (SELECT max(tid) FROM `mlog` AS mi WHERE 1 = 1
                AND (mi.`msg` like concat(@line, ''%'') OR mi.`msg` like concat(@alter_line, ''%''))) ORDER BY m.`tid` DESC;'
           ELSE 'SELECT * FROM `mlog` AS m WHERE 1 = 1 ORDER BY m.`tid` DESC;'
           END
    INTO @statement;

PREPARE stmt FROM @statement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @statement := NULL;
SET @line := NULL;
SET @alter_line := NULL;
-- $$

Wed Aug 18, 2021 6:49 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
I guess it's trying to tell, "I'm expecting a result that I'm told to display, but got none, there might be an issue with the code", which leads to generic error "An error occurred in user-defined query" not following by any details, because there is no database side error, just an unexpected case of no results.
Wed Aug 18, 2021 9:01 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Yes, that's exactly what I suspected was happening. I guess not having anything to display was not foreseen for that Action.

I can change the Action to Insert Output Into Code in which case it does not bother me with any error messages in case it only executes the truncate part but it also puts the results into the editor when the select part is executed. That is both ugly and hard to read, not to mention it ruins the existing code in the editor. I can extend the code so that it does select something even if the "truncate" branch is the winner, but that's cumbersome to make it so for each snippet of this kind. I'm currently sticking with the error message as it is easier to remove from the screen than the results pane, but that "workflow" is ugly as well. I really don't need feedback on that, except if there's actually some error, which it wouldn't show anyway since the Messages pane is not shown.

Any hints for a workaround?
Wed Aug 18, 2021 10:06 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Can you add an additional result at the end, something like below to make it always return at least one result

SELECT @line AS that_was_the_line;
Wed Aug 18, 2021 1:36 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I could, but that would kinda defeat the goal of not-having-to-do-that. It would also add another (surplus) result set in case the table is queried if it is applied without checking first. Checking it causes the code to be overly complicated to solve such a minor cause for such a big nuisance because MariaDB does allow conditional execution outside of stored programs (which is really just flogging a dead horse). Both the error message and the unwanted result set pane has to be removed to continue the work as they both obstruct the screen. The error message is a bit worse because it is a modal dialog but is a clear winner since it is easier to remove. It only takes a single hit on the space key to send it to oblivion. The problem is that it will not stay there, it will resurrect on the next invocation of those snippets and it is a bit disturbing to see an error message when there's no error.

Any kind of feedback is unwanted in case there isn't anything expected. It can be achieved by using the other type of Action but that Action is incompatible with the other branch (select) of the conditional execution). If this cannot be omitted and displaying something/anything is a must, it would be much better if SA only displayed the Messages pane which it refers to anyway. After all, that's what normally happens when queries not yielding any results are run.
Thu Aug 19, 2021 7:08 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.