Formatting issue when using named parameter(Java sql syntax)

[gauravjhs]

Input data

select supplier_name,city from
(select * from suppliers join addresses on suppliers.address_id=addresses.id)
as suppliers
where supplier_id>:supplier_id
order by supplier_name asc,city desc;

Expected Output

select
  supplier_name,
  city
from
  (
    select
      *
    from
      suppliers
      join addresses on suppliers.address_id = addresses.id
  ) as suppliers
where
  supplier_id > :supplier_id
order by
  supplier_name asc,
  city desc;

Actual Output

select
  supplier_name,
  city
from
  (
    select
      *
    from
      suppliers
      join addresses on suppliers.address_id = addresses.id
  ) as suppliers
where
  supplier_id >: supplier_id
order by
  supplier_name asc,
  city desc;

Usage

Default query on the example page.
For using sql in java with parameterized statements, ":supplier_id" syntax is used to pass named parameter's value
After formatting this is changed with a space in between ": supplier_id".

[nene]

This seems to be an issue similar to #323.

The main problem being that :name syntax is not part of the SQL language you're formatting. Currently SQL Formatter supports this syntax for DB2, PL/SQL and SQLite.

As I understand, you're using a Java API/driver to access some SQL server (don't know which, perhaps it's Postgres, perhaps it's MySQL, perhaps...).

[gauravjhs]

We are currently using it for SQL Server. You are right, this is very specific to Java as these named parameters are converted to PreparedStatements. I presume that =: is not a valid SQL operator. Would it be possible to not apply formatting to it?

[nene]

I think the best option here is to make this configurable.

The only potential source of conflict in MS SQL Server is the :: operator (also in PostgreSQL). But that really shouldn't be a problem. There's definitely a conflict in N1QL with its use of : in JSON-like data structures. So we can't add this syntax blindly to all dialects.

A slight hurdle is that currently the Tokenizer has been built with an assumption that the types of tokens supported by a language is fixed (e.g. not effected by any configuration options). But I think this shouldn't be too big of a problem. Just a bit of reorganization of the code needed.

[nene]

Regarding the configuration option itself, there's currently four internal settings to configure this:

type TokenizerOptions = {
  // True to allow for positional "?" parameter placeholders
  positionalParams?: boolean;
  // Prefixes for numbered parameter placeholders to support, e.g. :1, :2, :3
  numberedParamTypes?: ('?' | ':' | '$')[];
  // Prefixes for named parameter placeholders to support, e.g. :name
  namedParamTypes?: (':' | '@' | '$')[];
  // Prefixes for quoted parameter placeholders to support, e.g. :"name"
  // The type of quotes will depend on `identifierTypes` option.
  quotedParamTypes?: (':' | '@' | '$')[];
};

One possibility is to simply expose these four settings directly. Probably better to group them inside one parent config option, like:

type TokenizerOptions = {
  paramTypes: {
    positional?: boolean;
    numbered?: ('?' | ':' | '$')[];
    named?: (':' | '@' | '$')[];
    quoted?: (':' | '@' | '$')[];
};

I'm not sure though whether we should allow one to completely override the parameter settings (e.g. turning ? placeholders off in dialect that supports them by default) or only allow addition of new placeholders. Probably better to allow complete override. Like the Transact-SQL documentation pretty much defines all the possible placeholders based on the driver used:

• @name with SqlClient
• ? with OldeDb or Odbc
• :name with OracleClient

But to make matters worse, @name syntax is also used for variables. Happy times :)

[gauravjhs]

I think there are very limited variations across various programming languages which probably you are covering. These configurations should suffice.

[gauravjhs]

Also, just as a note... this used to work properly till version 3.1.0

[nene]

This is now released in 9.2.0.