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

New to HeidiSQL - DECLARE keyword not recognising

User, date Message
Written by anil803
7 months ago
Category: General
4 posts since Tue, 11 Feb 14
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
Written by anil803
7 months ago
4 posts since Tue, 11 Feb 14
I am using it MariaDB.
Written by kalvaro
7 months ago
595 posts since Thu, 29 Nov 07
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).
Written by jfalchMoney, Euro
7 months ago
406 posts since Sat, 17 Oct 09
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.)
Written by anil803
7 months ago
4 posts since Tue, 11 Feb 14
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
Written by kalvaro
7 months ago
595 posts since Thu, 29 Nov 07
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 :-?
Written by bachy
7 months ago
2 posts since Tue, 11 Feb 14
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?
Written by kalvaro
7 months ago
595 posts since Thu, 29 Nov 07
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.
Written by ansgar
7 months ago
5023 posts since Fri, 07 Apr 06
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.
Written by anil803
7 months ago
4 posts since Tue, 11 Feb 14
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
Written by kalvaro
7 months ago
595 posts since Thu, 29 Nov 07
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.
Written by Narfman
7 months ago
2 posts since Thu, 20 Feb 14
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;
Written by ansgar
7 months ago
5023 posts since Fri, 07 Apr 06
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;

Written by Narfman
7 months ago
2 posts since Thu, 20 Feb 14
Thank you for your quick reply. Works like a charm.
 

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