I’m no expert on this database… I really dislike it, but it’s not my choice… so I’m definitely no expert. I’m trying to throw an exception inside a function, but Xojo is ignoring it. I use a line like:
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This is an error.';
And in Xojo will use
db.ExecuteSQL("SELECT function_name();")
And I don’t get a DatabaseException from Xojo. The Xojo code continues, but the database function (correctly) does not complete.
Do I need to raise the exception some other way for Xojo to react?
I will confirm, but this is either the database library ignoring signal raised inside functions or an issue with certain versions of MariaDb.
I think what you are attempting is the ‘correct’ modern way using ‘signal’.
DELIMITER //
CREATE FUNCTION GetUserAge(birth_date DATE)
RETURNS INT
DETERMINISTIC
BEGIN
-- Check if the birth date is in the future
IF birth_date > CURDATE() THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Invalid date: Birth date cannot be in the future.';
END IF;
RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
END //
DELIMITER ;
However, If that is not working as expected for you from within Xojo, there is a universal fallback to force a deliberate native database error, which you should be able to catch in Xojo:
DELIMITER //
CREATE FUNCTION DivideNumbers(numerator DECIMAL(10,2), denominator DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
-- If denominator is zero, force an error to stop execution
IF denominator = 0 THEN
-- This will fail immediately and return a native MariaDB error
TRUNCATE TABLE `ERROR: Cannot divide by zero`;
END IF;
RETURN numerator / denominator;
END //
DELIMITER ;
Maybe use something less dramatic than truncate table though…
Then, without trying to sound patronising (you are a far better dev than I am)…
Try
' try and run your function
Catch e As DatabaseException
' do something with e
End Try
Note in the second case, you still get to set custom error message text.
Yeah you’ve got exactly the right idea. Catching the error with a try block is precisely what I want to do. But Xojo isn’t firing an exception, so there is nothing to catch.
The workaround I’ve had to use is a useless return value in the executed function. If I get the expected value, I know it worked. If I get null, it must have failed. I can’t get the message, but it’s at least something.
@Thom_McGrath, I tried a few variations inside Functions in MariaDb. All techniques throw errors correctly in Navicat Premium Lite (database client), but are ignored as DatabaseExceptions in Xojo.
So I suggest you convert your Functions (that return a value or values) to Stored Procedures (that return a value or values).
From my example this morning.
DELIMITER //
CREATE PROCEDURE CalculateDiscountProc(
IN price DECIMAL(10,2),
IN discount_pct INT,
OUT final_price DECIMAL(10,2) -- This parameter returns the value
)
BEGIN
-- Standard, clean error signaling (No workarounds needed!)
IF discount_pct > 100 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'ERROR: Discount percent cannot exceed 100%';
END IF;
-- Perform the calculation and assign it to the OUT variable
SET final_price = price - (price * (discount_pct / 100));
END //
DELIMITER ;
I can’t remember why, but at some stage, I began favouring stored procedures because they reported errors correctly.
Try
db.ExecuteSQL("CALL CalculateDiscountProc(" + tfAmount.Text + "," + tfDiscount.Text + ",@result)")
Var rs AS RowSet = db.SelectSQL("SELECT @result")
TextArea1.Text = rs.ColumnAt(0).StringValue
Catch e As DatabaseException
TextArea1.Text = e.Message
End Try
You can see the error being caught in the debugger.
At least now you can catch errors as DatabaseExceptions, with roughly the same workflow as Functions.
Maybe it works with stored functions instead of stored procs?
So you could do something more direct as:
DELIMITER //
CREATE FUNCTION CalculateDiscountFunc(
price DECIMAL(10,2),
discount_pct INT
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
-- Standard error signaling works the same way in functions
IF discount_pct > 100 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'ERROR: Discount percent cannot exceed 100%';
END IF;
-- Perform the calculation and return the value directly
RETURN price - (price * (discount_pct / 100));
END //
DELIMITER ;
And simpler to use as:
Try
Var calcParms As String = tfAmount.String + " , " + tfDiscount.String
Var sql As String = "SELECT CalculateDiscountProc(" + calcParms + ") As result;")
Var rs AS RowSet = db.SelectSQL(sql)
TextArea1.Text = rs.ColumnAt(0).StringValue
Catch e As DatabaseException
TextArea1.Text = e.Message
End Try
And yes, I use Functions to return some special columns I do calculate
