Dialects
If your database is PostgreSQL or SQL Server, set the dialect to postgres or mssql and stop reading. The rest of this page is for the output sql on another database, and for SQL that SqlSource reads differently from your database.
The dialect matters because databases disagree about where a comment or a string ends. SqlSource removes comments from your SQL, so it has to know which rules your SQL follows. This line, read by three dialects, is three different statements:
SELECT 1 # x [y--z] -- c
| Dialect | The SQL that is kept | Why |
|---|---|---|
ansi |
SELECT 1 # x [y |
# is SQL, [ is SQL, and the first -- starts a comment |
mysql |
SELECT 1 |
# starts a comment |
mssql |
SELECT 1 # x [y--z] |
[y--z] is a quoted identifier, and the last -- starts a comment |
The dialect is the set of rules by which SqlSource reads a .sql file. It connects to nothing, and it is not the logical database. This page covers the dialect names, the options, what each dialect changes, and what SqlSource still reads differently from the database. The settings page says where you set the dialect, and which place wins.
Dialect names
| Dialect | Also accepted | Use it for |
|---|---|---|
ansi |
The default. Any database without a dialect of its own, such as Db2 | |
mssql |
sqlserver, tsql |
SQL Server, Azure SQL |
postgres |
postgresql |
PostgreSQL, DuckDB |
cockroachdb |
cockroach |
CockroachDB |
mysql |
MySQL | |
mariadb |
MariaDB | |
sqlite |
SQLite | |
oracle |
Oracle, Firebird |
Names are not case-sensitive.
Set the dialect named for your database. A database that has no dialect of its own can be read correctly by one that has: the last column says which. For example, postgres reads DuckDB, whose files then need the output sql (see database support). For any other database, use ansi.
ansiis the default. A project that sets nothing is read byansi.ansireads the SQL of every database by one set of rules. So it misreads each construct of the table below where its column says No and the column of your database says Yes.ansialso reads the quoting forms of PostgreSQL and MySQL that are rarely anything else:`...`,$tag$...$tag$andE'...'.- The fix for a misread is to set the dialect.
The dialect also decides which databases the tool can ask about a query: see database support.
Options
-- dialect: mysql, no-backslash-escapes
-- name: GetPath
SELECT 'C:\temp\' AS path;
MySQL and MariaDB have SQL modes that change how a string is read. If your server runs with one, write its option after the dialect, with a comma:
| Option | Also accepted | SQL mode | What it changes |
|---|---|---|---|
ansi-quotes |
ansi_quotes |
ANSI_QUOTES |
"..." is a quoted identifier, and a backslash does not escape in it |
no-backslash-escapes |
no_backslash_escapes |
NO_BACKSLASH_ESCAPES |
A backslash does not escape in '...' or in "..." |
// sqlsource.json, in the project's folder
{
"root": true,
"dialect": "mysql,ansi-quotes",
}
// Legacy/sqlsource.json
{
"dialect": "mariadb,ansi-quotes,no-backslash-escapes",
}
- Options are not case-sensitive, and come in any order. Spaces around the name and each option are ignored.
- Only
mysqlandmariadbhave options. An option of another dialect, or one that does not exist, is the same error as a name that is not a dialect. So is an empty option, as inmysql,. The settings page gives the errors by place. - In a
-- dialect:marker, the value is the rest of the line: the name, then the options, separated by commas. Nothing else can follow it on the line, not even a comment. - A value written in several places, options included, follows the rules of the dialect setting.
What each dialect changes
ansi |
mssql |
postgres |
cockroachdb |
mysql |
mariadb |
sqlite |
oracle |
|
|---|---|---|---|---|---|---|---|---|
A backslash escapes in '...' and "..." |
No | No | No | No | Yes | Yes | No | No |
A backslash escapes in E'...' |
Yes | No | Yes | Yes | Yes | Yes | No | No |
`...` is a quoted identifier |
Yes | No | No | No | Yes | Yes | Yes | No |
[...] is a quoted identifier |
No | Yes | No | No | No | No | Yes | No |
$tag$...$tag$ is a string |
Yes | No | Yes | Yes | Yes | No | No | No |
q'[...]' is a string |
No | No | No | No | No | No | No | Yes |
A /* inside a block comment needs its own */ |
Yes | Yes | Yes | Yes | No | No | No | No |
-- is a comment with no white space after it |
Yes | Yes | Yes | Yes | No | No | Yes | Yes |
# starts a comment |
No | No | No | No | Yes | Yes | No | No |
Kept as hints, besides /*+ ... */ and /*! ... */ |
/*M! ... */ |
--+ ... |
||||||
A name declared with DECLARE @name is a local variable, not a parameter (declared variables) |
No | Yes | No | No | No | No | No | No |
Five details:
- In
mssql, a]]inside brackets stands for one]. Insqlite, the first]ends the identifier. - In
mysqlandmariadb, an option changes the first row (see options). - In
postgresandcockroachdb, anE'...'string that is continued on the next line is one string. Each later part takes backslash escapes too. - In
postgres,--comments can stand between the parts of such a string, as PostgreSQL allows. They are removed like any other comment. Incockroachdb, only white space can stand there. - In
cockroachdb, a bytes literal,b'...', takes backslash escapes as anE'...'string does. So doesb'...'inmysqlandmariadb, where every string does.
Markers under mysql and mariadb
Under mysql and mariadb, -- name: GetUser is a marker, and --name: GetUser is not a comment at all. A marker is a -- comment, so under these dialects it needs white space after the two dashes, as these databases need it.
A # comment is never a marker, even where # starts a comment: # name: GetUser is a comment.
What is still read differently
SqlSource reads these differently from the database, whatever the dialect:
| Construct | Database | How SqlSource reads it |
|---|---|---|
A versioned comment whose body holds a string that contains */, such as /*!50700 SELECT '*/' */ |
MySQL, MariaDB | The comment ends at the first */, so the quote after it opens a string that is never closed: the error SQLSRC101. Where the server ends the comment depends on the server's version |
| A block comment that is still open at the end of the file | SQLite | The error SQLSRC102. SQLite accepts it, but it would take every later query of the file with it |
| A file whose lines end with a carriage return alone, with no line feed | MySQL, SQLite, CockroachDB | A line ends there, as in every dialect. These databases end a -- comment only at a line feed |
A client command that is not SQL: DELIMITER, GO, SQL*Plus PROMPT and REM, a psql \ command |
All | As SQL, so a quote in it can open a string |
A name that is not a parameter to the database: a user variable, @x for an absolute value, the parameter of a procedure that the SQL creates |
MySQL, MariaDB, PostgreSQL, SQL Server | As a parameter: see names that are read as parameters |
A misread has one of two results:
- A build error that reports an unclosed quote or comment (SQLSRC101, SQLSRC102) in SQL that is valid for your database.
keep-commentsdoes not help. - Missing SQL. The misread quotes happen to balance, and the SQL after them is taken for a comment and removed. Under
ansi,SELECT 'a\'b -- c', 2becomesSELECT 'a\'b. Nothing is reported.keep-commentson the query prevents the removal.
If your SQL uses one of these constructs, check the generated SQL: hover over the member, or read its documentation.