Auto convert @param to {<name>:<data type>}

[bnuzhouwei]

The @param is the defualt paramter format for ADO.NET, can auth covert @params to {:}, so that the application could use CH same as other databases.

[DarkWanderer]

@param is not ADO.NET syntax, it is SQL Server syntax. Each database has their own syntax for parameterized queries, e.g.:

SQL Server: @param Oracle: :param PostgreSQL: $1 SQLite: ? or @param or $param ClickHouse (as you can see here): SELECT {param}

I don't think I should change SQL dynamically to match another database's syntax

[bnuzhouwei]

In @param works well in NPGSQL, System.Data.SQLite, Mysql.data.client, system.data.sqlclient, and etc. but except oracle.

implemeted in the driver, it will be easier to migration databases.

[DarkWanderer]

Does any of those providers rewrite the SQL for it to work? Can you point me to examples in code?

[bnuzhouwei]

In Npgsql (ADO.NET driver for PostgreSQL), it use a function to change prefix with '@' or ':'

    internal void ChangeParameterName(string? value)
    {
        if (value == null)
            _name = TrimmedName = PositionalName;
        else if (value.Length > 0 && (value[0] == ':' || value[0] == '@'))
            TrimmedName = (_name = value).Substring(1);
        else
            _name = TrimmedName = value;
    }

[DarkWanderer]

This doesn't rewrite SQL though, it just changes parameter name if you try to assign :name or @name to NpgsqlParameter.ParameterName. Is there an example which rewrites SQL, as you suggest?

[bnuzhouwei]

ProcessRawQuery in NpgsqlCommand with EnableSqlRewriting rewite the sql from '@' and ‘:’ to '$' with Query analysis

ParseRawQuery in SqlQueryParser includes doing rewriting named parameter placeholders to positional (@p => $1), and splitting the query up by semicolons.