distal-attribute
distal-attribute
distal-attribute
distal-attribute

New to HeidiSQL - DECLARE keyword not recognising

anil803 posted 10 months ago in General
Hi, I am new to HeidiSQL. I am moving my scripts from MSSQL to HeidiSQL and DECLARE is not being recognized in my version - 8.0.0.4396. Please help...

Thanks
anil803 posted 10 months ago
I am using it MariaDB.
kalvaro posted 10 months ago
Do you have a question regarding HeidiSQL or about migrating your SQL code from SQL Server to MariaDB?

- If your script is not valid SQL there's nothing that HeidiSQL can do about it.

- If your script is valid and runs flawlessly from the command-line, they you'd you need to provide some info (the bare minimum: code, expected output, actual output).
jfalch posted 10 months ago
If you move an SQL script from MSSQL to MariaDB and expect it to work there: good luck; the systems are very different.
(Especially true for triggers and stored routines.)
anil803 posted 10 months ago
Thanks for trying to help me. I am trying to write a basic procedure to use a cursor. I am attaching a screen shot,

In the screen shot you will see that heidisql doesn't treat declare or cursor as keywords.

BEGIN
DECLARE 'crs' cursor for
select itemnumber from salesorder;
END

kalvaro posted 10 months ago
You've tried to link a picture from your computer:

C:\Users\DMS1\Desktop\capture.png



It looks like you are talking about the syntax highlighter. That'd be a cosmetic issue but it doesn't happen in my copy of HeidiSQL: DECLARE shows up bold and blue :-?
bachy posted 10 months ago
Looks like I also have the same problem if I understand it correctly, on my HeidiSQL too the keywords DECLARE or CURSOR does not show up in bold and blue which results into compilation error/syntax error of the stored procedure.

@kalvaro do we have to change any default settings on HeidiSQL?
kalvaro posted 10 months ago
Syntax errors are triggered by MySQL Server, not HeidiSQL. If you get them, it probably means that your code is not valid. The syntax highlighting is just a cosmetic feature that doesn't affect code parsing or execution.

If you provide further details (a code sample and instructions on what you do with it in HeidiSQL) we can determine if everything's correct on HeidiSQL side.
ansgar posted 10 months ago
MSSQL and MySQL connections activate a slightly different syntax highlighting. But in both, DECLARE is bold, only in MySQL it's black and in MSSQL it's blue. Same with CURSOR.

However, anil803, do you get any error when executing your procedure? If yes, please paste the error message here.
anil803 posted 10 months ago
Here my MSSQL script

DECLARE @Cursor2 CURSOR
DECLARE @itemNum varchar(50)

SET @Cursor2 = CURSOR FAST_FORWARD
FOR
Select ItemNumber
From @Result

OPEN @Cursor2
FETCH NEXT FROM @Cursor2
INTO @itemNum

WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM @Cursor2
INTO @itemNum
END

CLOSE @Cursor2
DEALLOCATE @Cursor2



I am trying to convert this into MariaDB, so I tried as shown below

DECLARE tempCursor2 CURSOR
FOR
Select ItemNumber
From tt_Result;

OPEN tempCursor2
FETCH NEXT FROM tempCursor2
INTO itemNum

WHILE @@FETCH_STATUS = 0
BEGIN


FETCH NEXT FROM tempCursor2
INTO itemNum
END

CLOSE tempCursor2
DEALLOCATE tempCursor2



however, my stored procedure does not save, it throws me the below error

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE tempCursor2 CURSOR
FOR
Select ItemNumber
From tt_Result;

Thanks for help

kalvaro posted 10 months ago
Alright, I believe I mistakenly typed DECLARE into a SQL Server query tab. It's actually bold and black as Anse says.

Whatever, I understand you're using the "Create new/ Stored routine" feature. When you open the store route editor you get a default body that contains this:

BEGIN

END



You're expected to type your code inside. Otherwise, it isn't a valid procedure body.
Narfman posted 10 months ago
I too have the same problem. I am copying the examples out of the MariaDB Crash Course book and am getting this error.

QL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 12.
(FYI: Line 12 is the line where the first DECLARE statement is)

I have looked SQL Keywords dropdown in Heidi, and DECLARE is not in that list. It goes from DEC to DEFAULT.

I am using the same version as above. My code is below:

#Name : ordertotal
#Parameters: onumber = order number
#taxable = 0 if not taxable, 1 if taxable
#ototal = order total variable
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
# Declare variable for total
DECLARE total DECIMAL(8,2);
# Declare tax percentage
DECLARE taxrate INT DEFAULT 6;
# Get the order total
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
#
# Is THIS TAXABLE?
IF taxable THEN
# Yes, so add taxrate to the total
SELECT total+(total/100*taxrate) INTO total;
END IF;
#
# And finally, save to out variable
SELECT total INTO ototal;
END;
ansgar posted 10 months ago
Look at line 12. There is a semicolon, which is normally used as delimiter. In HeidiSQL, you can either a) use the routine editor to create a procedure, or b) use the query tab. In the latter case, you must set a delimiter, by clicking the button on the main toolbar with the red semicolon on it, and then use that instead of semicolon in your SQL code.

Assumig you set the delimiter to //, you need the following code:

#Name : ordertotal
#Parameters: onumber = order number
#taxable = 0 if not taxable, 1 if taxable
#ototal = order total variable
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
# Declare variable for total
DECLARE total DECIMAL(8,2)//
# Declare tax percentage
DECLARE taxrate INT DEFAULT 6//
# Get the order total
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO total//
#
# Is THIS TAXABLE?
IF taxable THEN
# Yes, so add taxrate to the total
SELECT total+(total/100*taxrate) INTO total//
END IF//
#
# And finally, save to out variable
SELECT total INTO ototal//
END;

Narfman posted 10 months ago
Thank you for your quick reply. Works like a charm.

Please login to leave a reply, or register at first.