Modal top-level parameter 'close' is a reserved keyword in SQL Server

[charlieglnp]

Introduction

The top-level parameter 'close' of the model component is a reserved word in Transect-SQL and results in an error when used on SQL Server.

To Reproduce

select  'modal' as component,
		'test' || 'modal' as id,
		'Close' as close;

The concatenation is to force the query through SQL server (no error occurs otherwise)

Actual behavior

In "test.sql": The following error occurred while executing an SQL statement:
error returned from database: Incorrect syntax near the keyword 'close'.

Expected behavior

Modal component to not return an error when 'close' top-level parameter is used

Version information

• OS: Windows 11
• Database SQL Server 2019
• SQLPage Version 0.38.0

Additional context

A workaround is to enclose the top-level parameter in square brackets

select  'modal' as component,
		'test' || 'modal' as id,
		'Close' as [close];

[lovasoa]

Hi ! Indeed, SQL has a huge number of keywords, so we don't forbid reusing them as component properties.

You did find the right workaround: quote parameter names when your db does not allow them unquoted.

We can't remove an existing parameter but we can add aliases. Do you want to make a pull request to sqlpage/templates/modal.handlebars and examples/official-site/sqlpage/migrations/63_modal.sql to add an alias for the "close" parameter ?

[charlieglnp]

Would there be any benefit in SQLPage raising a specific error when a property name is a reserved keyword for the connected database?

I regularly prototype using sqlite and then deploy onto sql server, or sqlite and then onto mariadb and things like this (as well as not knowing some of the more esoteric keywords!) catch me out.

[lovasoa]

That would not be trivial to implement correctly, since sqlpage does not know which keywords are reserved on your database... But if you want to give it a try, you're welcome !

[charlieglnp]

I walked into that one! I can see there might be short cuts for some databases but others eg SQL Server would rely on a static list. It would be a good excuse to contribute so I will have a play and see where I get to, though it will take some time as I'm starting from scratch.

Aliasing an existing parameter because its a reserved keyword doesn't feel like the right solution to me (I can only see three aliases, one in the form component and two in the chart component?). I think I prefer quoting the parameter when needed, to not break the elegant simplicity of SQLPage, though at the end of the day it's likely to be such an edge case that its not really a big issue at all.

[kryskool]

Hi @charlieglnp

i made a POC #1088 to use double underscore prefix before the column name, nothing to change on template file.

Can you test it on SQL Server ?

regards,

[lovasoa]

Is

select  'modal' as component,
		'test' || 'modal' as id,
		'Close' as [close];

that much worse than

select  'modal' as component,
		'test' || 'modal' as id,
		'Close' as __close;

?

I think 'close' as close_label is both clearer and more immediately useful to all users.