0

I'm trying to use dbup to automate running db scripts in my .NET 7 app. So far the scripts to create tables are fine (ofcourse after making lots of tweeks), but the stored procedures are being problematic.

...
BEGIN
    IF (SELECT COUNT(*) FROM mytable WHERE REPLACE(TRIM(name), ' ', '') = trimmed_name) > 0 
    AND (SELECT REPLACE(TRIM(name), ' ', '') WHERE id=categoryId) != trimmed_name
    THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Category with given name already exists';
    ELSE
        UPDATE mytable SET name=categoryName, description=description
        WHERE id = categoryId;
    END IF;
END

Having the error message below: 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 '' at line 21

Line 21 is the SET MESSAGE_TEXT line

Generally, I want to know if there are keywords I can't use with dbup, cos I it throws errors same error if I declare a variable with "DECLARE"

I have refactored, taken out spaces, still no positives

Able
  • 49
  • 4
  • 1
    I think you didn't set a DELIMITER. Read https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html – Bill Karwin Apr 03 '23 at 05:35

1 Answers1

0

I think there is an issue with the MySQL syntax for setting the error message text using SIGNAL -- SET isn't required in this context

SIGNAL SQLSTATE '45000' 
    'Category with given name already exists';
  • regarding keywords that can't be used with DbUp -- I am not aware of any specific limitations on the use of SQL keywords with DbUp

PS. make sure that you have properly configured your DbUp project to use the correct database provider and connection string + installed any required dependencies or packages

Lemonina
  • 712
  • 2
  • 14
  • Thank you Lemonina. My setup was configured properly, but like @BillKarwin noticed, setting a DELIMETER fixed it. – Able Apr 03 '23 at 14:55