http_header component Access-Control-Allow-Origin parameter parsing error on SQL server

[f8dca]

select 'http_header' as component,
'https://cdn.jsdelivr.net' as Access-Control-Allow-Origin

select 'shell' as component,
'https://cdn.jsdelivr.net/npm/mermaid@10/dist/mermaid.esm.min.mjs' as javascript;
		5 as refresh;

select 
	'mermaid' as component,
	'graph TD  
	 Start --> Stop' as diagram_md

Sorry, but we were not able to process your request.

Error:

SQL syntax error before: Control - Allow - Origin select 'shell' as component , 'https://cdn.jsdelivr.net/npm/mermaid@10/dist/mermaid.esm.min.mjs' as javascript ; 5 as refresh ; select 'mermaid' as component , 'graph TD
Start --> Stop' as diagram_md ;

Caused by:
0: sql parser error: Expected an SQL statement, found: -
1: An error occurred during the preparation phase of the SQL

[f8dca]

Generally SQL server does not like column names that contain "-" or any other special characters except "_".

Square brackets can be used to deal with the special characters but SQLPage does not seem to be handling those well.

[lovasoa]

In standard SQL, one can use double quotes to escape identifiers

[f8dca]

I managed making it working by enclosing the column name in single quotes.

MS SQL Server column object naming rules are a mess:

• by default only alphanumeric and _ allowed in column names.
• once it is enclosed in [] or '' then almost anything goes.
• Unlike MySQL or PostgreSQL it is case insensitive.

It would be fantastic if SQLPage would be able to gracefully handle the default square bracket enclosed naming convention of MS SQL Server such as:

[db].[schema].[object]
[db]..[object]
[object].[column]
[column1],[column2]

This would allow straight drag-and-drop and automatic file generation from SSMS straight into the .sql file passed to my MySQL without any need for extensive editing.

[lovasoa]

What error do you see currently when using that syntax?

[f8dca]

select 'table' as component

-- Fails
 select top 1 [id], [page_id], [sequence]
  from [SPIRE_UDF_FIELDS]

-- Fails
select top 1 [id], [page_id], [sequence]
  from [AMTV2].[SPIRE_UDF_FIELDS]


--Works
select top 1 [id], [page_id], [sequence]
from SPIRE_UDF_FIELDS

--Works
select top 1 [id], [page_id], [sequence]
from AMTV2.SPIRE_UDF_FIELDS

[lovasoa]

I just fixed it, thanks for the report, @f8dca !

In previous versions of SQLPage, .sql files were always parsed using a "generic" sql dialect, that covered most of the features of all supported databases. Starting with the next version, SQLPage will parse sql files using the dialect of the active database connection, allowing the use of more database-specific syntax, such as SELECT [x] FROM [y].[z] in mssql.

[f8dca]

Looking forward to test this: Having full support for the square brackets will be a massive efficiency boost because this makes it possible to drag-and-drop straight from SSMS.

[lovasoa]

Closing as I'm pushing v0.10.0 with better mssql sql dialect syntax parsing.

[lovasoa]

Please report any issue you may find with it 🙂