Unsigned Stored Procedure Parameters

Mass's profile image Mass posted 5 years ago in General Permalink

I have a table that has a BIGINT UNSIGNED primary key column.

I need to reference entries in this table using a stored procedure, with the value for the key column passed in as a parameter.

I also need to be able to support values greater than 9223372036854775807 (BIGINT(MAX)), but when I attempt to pass a value to the stored procedure parameter that is greater, I get a value out-of-range error. Because the parameter is defined as BIGINT not BIGINT UNSIGNED.

If I manually recreate the stored procedure, using a Query tab, and specify BIGINT UNSIGNED for the parameter, the procedure is created fine, and everything works.

However, HeidiSQL (10.2.0.5599) still shows the parameter, in the "Create Code" tab, as being just BIGINT and not BIGINT UNSIGNED (as it actually is in the database).

Worse, if I then use the "Export Database as SQL" command, the create code for the stored procedure, in the generated SQL script file, is also now using just BIGINT and not BIGINT UNSIGNED. So when the database is created by importing the generated SQL script, the parameter type is now incorrect, and value out-of-range errors begin appearing once again.

I'm really hoping that I'm missing something, or doing something wrong, as HeidiSQL has been my go-to database tool for a long time now. But this is a breaking issue for the project I'm currently working on. If anyone can help me out, it would be massively appreciated!

ansgar's profile image ansgar posted 5 years ago Permalink

Are you using the latest HeidiSQL release v10.2 ?

I was just trying to reproduce that issue, without luck. I created a new procedure with one BIGINT unsigned parameter, successfully:

Description

Then I called it with a huge number, successfully:

Description

Then I opened some other table, reopened the procedure and still there is the unsigned flag.

Mass's profile image Mass posted 5 years ago Permalink

Hi Ansgar,

Thank you for the quick reply.

I am using 10.2, yes. But with your screenshot, I figured out a few things.

First, I didn't realise I could just write "UNSIGNED" into the "Datatype" column, after selecting BIGINT. Which I should have tried, as I know I can add limits to VARCHAR, for example, after selecting it.

Second, when I ran a script in a Query tab, to drop and recreate my stored procedure, with the parameter set manually to UNSIGNED, I had the stored procedure loaded in the other tab, and did not change away, and back, to see any changes. So I still saw BIGINT, not BIGINT UNSIGNED.

Though it was at this point that I exported the SQL, using the "Export database as SQL" command, and the exported script also had just BIGINT, not BIGINT UNSIGNED too. But maybe "reloading" the stored procedure is also required to get the script export up-to-date too.

I will keep this in mind for the future.

So you have solved my issue, I now see how to use UNSIGNED parameters in stored procedures, and how to make sure everything is in-sync with MySQL, before exporting scripts.

Many thanks, again, for the quick response.

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