Mysql Functions - VisualCron - Forum

Community forum

radiomax
2022-02-01T13:05:11Z
Hi. Unfortunately Visualcron seems not to be able to run queries where you define a function. Because: inside a function there are several ; but the sql query should still be sent as one query to the server. Unfortunately VisualCron stubbornly uses the ; to split the query into several queries. But the ; inside the definition of the function needs to be one query although there are some ; in it. in mysql itself you can get around this problem with the DELIMITER function. You can change th definition of the delimiter before the definition of the function and then back. Why is this not possible with visual cron? This also means that VisualCron is not fully compatible with sql. please help. We have very complex queries and cannot go without functions otherwise there would be a lot of repeating code and it would be 10 times as large and very confusing and hard to read.

thx.

greetings Stephan
Sponsor
Forum information
thomas
2022-02-01T13:14:26Z
Is it like this even if you uncheck this box?

image.png
Support
2022-02-01T15:51:13Z
Originally Posted by: radiomax 

Hi. Unfortunately Visualcron seems not to be able to run queries where you define a function. Because: inside a function there are several ; but the sql query should still be sent as one query to the server. Unfortunately VisualCron stubbornly uses the ; to split the query into several queries. But the ; inside the definition of the function needs to be one query although there are some ; in it. in mysql itself you can get around this problem with the DELIMITER function. You can change th definition of the delimiter before the definition of the function and then back. Why is this not possible with visual cron? This also means that VisualCron is not fully compatible with sql. please help. We have very complex queries and cannot go without functions otherwise there would be a lot of repeating code and it would be 10 times as large and very confusing and hard to read.

thx.

greetings Stephan



Do you still have that issue after unchecking the box Thomas suggested?

Michael
Support
http://www.visualcron.com 

Please like  VisualCron on facebook!
radiomax
2022-02-02T14:28:02Z
Hi. I would not have this problem but at the same time I need to execute several queries after another. first I need to define the functions which are used in the next one. This would always be the case if you use function because if you defined them once they are lost after the session is closed on the mysql. so you need to define them before using them.
radiomax
2022-02-02T14:33:09Z
in my option the only solution would be to be able to set the deilimiter in visual cron to something else as well (and also more than one char like ++ or ##). you basically disable the possibility given by mysql itself to redefine the delimiter for the query itself (delimiter cmd) to solve this problem with your fixed delimiter.
Support
2022-02-02T14:45:51Z
Originally Posted by: radiomax 

in my option the only solution would be to be able to set the deilimiter in visual cron to something else as well (and also more than one char like ++ or ##). you basically disable the possibility given by mysql itself to redefine the delimiter for the query itself (delimiter cmd) to solve this problem with your fixed delimiter.



You can change the delimiter in visualcron. For example change the delimiters from a '|' to a comma or a '#' or something similar.
Michael
Support
http://www.visualcron.com 

Please like  VisualCron on facebook!
radiomax
2022-02-02T15:04:27Z
Thx. But how can I change the delimiter of visualcron for the this one sql query?

i also tried to split the query into multiple tasks but when I remove the split at ; option I get the error: Exception in Task: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

radiomax
2022-02-02T15:06:36Z
this Is the query for example:
DELIMITER //
CREATE FUNCTION CreateField ( length INT, content varchar(500))
RETURNS VARCHAR(500)
BEGIN
RETURN SUBSTRING(CONCAT(COALESCE(NULLIF(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(IFNULL(content, ''), 'ä', 'ae'), 'ö', 'oe'), 'ü', 'ue'), 'Ä', 'Ae'), 'Ö', 'Oe'), 'Ü', 'Ue'), ''), '$'), SPACE(length)),1,length);
END //
DELIMITER ;

--> error as stated above.
radiomax
2022-02-08T09:04:16Z
please I got no reply so far? what should i do? in this form you are never ever able to use mysql queries with functions in visualcron.

Support
2022-02-08T14:13:42Z
Originally Posted by: radiomax 

please I got no reply so far? what should i do? in this form you are never ever able to use mysql queries with functions in visualcron.



We support DML queries only. For DDL (Data definition language) queries, I recommend that you make a feature request post in our FR section on the forums.
Michael
Support
http://www.visualcron.com 

Please like  VisualCron on facebook!
thomas
2022-02-09T16:00:41Z
Out of curiosity: Why don't you create the function in the database, and just call the function from VC? Why create it in VC?
radiomax
2022-02-13T15:55:48Z
Hi. i tried this but although the functions are persistant and also usable with all my other mysql clients (also the same user as visual cron uses) I get an exception in visual cron with the sql error: Function Not Found. no matter what I do it stays. even on the same machine with another sql client it works.

mysql seems to be buggy in visualcron.

greetings Stephan
thomas
2022-02-14T14:48:55Z
That's weird. I dont' use MySql myself, but if I had to guess it could be that the VC user may not have the necessary rights to execute that function, or access to the schema where it resides. But I assume you have checked this:)
Users browsing this topic
Scroll to Top