#350 Qualified routine parameter names

[Heinrich Bohne]

I have been given to understand that it is not possible to qualify routine parameter names with the name of the routine in HSQLDB as it seems to be in some other SQL-based database management systems, like this:

"RoutineName"."ParameterName"

This is most unfortunate in situations such as this one:

CREATE PROCEDURE "PUBLIC"."UpdateItems" (
      IN "NewProperty" VARCHAR(100)
    , IN "SomeOtherProperty" VARCHAR(100)
)
MODIFIES SQL DATA
UPDATE
    "PUBLIC"."Items"
SET
    "SomeProperty" = "NewProperty"
WHERE
    "Items"."SomeOtherProperty" = "SomeOtherProperty"

Here, the identifier "SomeOtherProperty" in the WHERE clause is treated as a reference to the table column "Items"."SomeOtherProperty" instead of the routine parameter with the same name. The most obvious workaround is giving the routine parameter a distinct name, unequal to any of the column names in the table "Items", for example by adding a suffix "_param":

CREATE PROCEDURE "PUBLIC"."UpdateItems" (
      IN "NewProperty" VARCHAR(100)
    , IN "SomeOtherProperty_param" VARCHAR(100)

...

WHERE
    "Items"."SomeOtherProperty" = "SomeOtherProperty_param"
)

However, I said "workaround" instead of "solution", because it is possible that a column named "SomeOtherProperty_param" is added to the table "Items" after this routine is created, which will render the routine broken. This may be unlikely, but still, the responsibility to prevent this from happening lies with the programmer instead of the database engine, which makes this a workaround and not a solution, in my opinion. In fact, adding a column named "NewProperty" to "Items" will also break the routine.

So as far as I understand, without qualified routine parameter names, there is no 100% reliable way to reference a routine parameter in such a context, which seems very frustrating to me. It would therefore be great if support for qualified routine parameter names could be added to HSQLDB in the future.

[Fred Toussi]

We may add this feature for compatibility with other databases.

Are routine variable names also accessible via routine name prefix?

CREATE PROCEDURE "PUBLIC"."UpdateItems" (
      IN "NewProperty" VARCHAR(100)
    , IN "SomeOtherProperty_param" VARCHAR(100)

...
DECLARE "SomeOtherProperty_var" VARCHAR(100);
SET "SomeOtherProperty_var" = UPPER("SomeOtherProperty_param");
...
WHERE
    "Items"."SomeOtherProperty" = "SomeOtherProperty_var"
)

[Heinrich Bohne]

Thanks for your reply. Do you mean if routine variable names are accessible via routine name prefix in other database management systems? I have no idea, I am only acquainted with HSQLDB, I inferred the fact that routine parameter names can be qualified from Google results when looking for a solution to this problem, before discovering that it is not possible in HSQLDB.

[Fred Toussi]

I am adding this for future reference. Because the name-spaces for routines and table are separate, you can have a routine and a table of the same name. So even allowing the prefix for parameter names is not future proof as a table with the same name as the routine may be created.