Working with Different Databases¶

Continue the Java learning path by selecting the grammar that matches the database which produced the SQL. You will compare vendor-specific constructs and learn where automatic dialect detection is useful—and where it is ambiguous.

What you will learn¶

Configure GSP for different database vendors
Handle vendor-specific SQL features and extensions
Parse Oracle PL/SQL blocks
Work with SQL Server T-SQL specifics
Manage PostgreSQL and MySQL syntax differences
Switch dialect context effectively

Before you begin¶

Completed Basic SQL Parsing
Familiarity with SQL differences across database systems

Supported vendors¶

The vendor is a member of the EDbVendor enum, and the two editions cover different lists.

JavaC#

EDbVendor declares 45 constants; 43 parse ordinary SQL and none throw. The per-vendor SQL Syntax Support pages are the authoritative list. The vendors used in this tutorial:

Vendor	Constant	Notes
Oracle	`EDbVendor.dbvoracle`	Includes PL/SQL
Microsoft SQL Server	`EDbVendor.dbvmssql`	Full T-SQL
PostgreSQL	`EDbVendor.dbvpostgresql`
MySQL	`EDbVendor.dbvmysql`	Plus MariaDB
Amazon Redshift	`EDbVendor.dbvredshift`	PostgreSQL-derived
Snowflake	`EDbVendor.dbvsnowflake`
Teradata	`EDbVendor.dbvteradata`	Includes BTEQ + SPL
Sybase ASE	`EDbVendor.dbvsybase`	T-SQL family

EDbVendor declares 23 constants, of which 15 are dedicated dialect grammars, all present in the published NuGet package:

Vendor	Constant	Notes
IBM DB2	`EDbVendor.dbvdb2`	DB2 LUW + iSeries
Greenplum	`EDbVendor.dbvgreenplum`	PostgreSQL fork
Apache Hive	`EDbVendor.dbvhive`	HiveQL
Apache Impala	`EDbVendor.dbvimpala`	Impala SQL
IBM Informix	`EDbVendor.dbvinformix`
MDX	`EDbVendor.dbvmdx`	OLAP; not ordinary SQL
Microsoft SQL Server	`EDbVendor.dbvmssql`	Full T-SQL
MySQL	`EDbVendor.dbvmysql`	Plus MariaDB
IBM Netezza	`EDbVendor.dbvnetezza`
Oracle	`EDbVendor.dbvoracle`	Includes PL/SQL
PostgreSQL	`EDbVendor.dbvpostgresql`
Amazon Redshift	`EDbVendor.dbvredshift`	PostgreSQL-derived
Snowflake	`EDbVendor.dbvsnowflake`
Sybase ASE	`EDbVendor.dbvsybase`	T-SQL family
Teradata	`EDbVendor.dbvteradata`	Includes BTEQ + SPL

Building from source lets you drop unused dialects with /p:includeXxx=false.

Three constants throw, five are T-SQL in disguise

dbvbigquery, dbvhana and dbvdax throw NotSupportedException from the constructor. dbvaccess, dbvansi, dbvgeneric, dbvodbc and dbvfirebird resolve to the T-SQL grammar. See Database Compatibility.

dbvaccess is not an alias of dbvmssql

Older versions of this page said it was. They are distinct enum constants in both editions (Java ordinals 0 and 22; .NET values 0 and 11). What dbvaccess actually does is route to the T-SQL grammar.

Work with database-specific SQL¶

1. Switching vendors¶

The vendor is selected at construction time and embedded in the parser. You cannot change it on an existing instance — to analyse the same SQL against several dialects, construct one parser per vendor.

JavaC#

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TGSqlParser;

TGSqlParser oracle = new TGSqlParser(EDbVendor.dbvoracle);
TGSqlParser mssql  = new TGSqlParser(EDbVendor.dbvmssql);
TGSqlParser pg     = new TGSqlParser(EDbVendor.dbvpostgresql);

oracle.sqltext = "SELECT * FROM dual WHERE ROWNUM = 1";
mssql.sqltext  = "SELECT TOP 10 * FROM employees";
pg.sqltext     = "SELECT * FROM employees LIMIT 10 OFFSET 5";

for (TGSqlParser p : new TGSqlParser[] { oracle, mssql, pg }) {
    int ret = p.parse();
    System.out.printf("%-16s -> %s%n", p.getDbVendor(),
                      ret == 0 ? "ok" : p.getErrormessage());
}

Output:

dbvoracle        -> ok
dbvmssql         -> ok
dbvpostgresql    -> ok

using System;
using gudusoft.gsqlparser;

var oracle = new TGSqlParser(EDbVendor.dbvoracle);
var mssql  = new TGSqlParser(EDbVendor.dbvmssql);
var pg     = new TGSqlParser(EDbVendor.dbvpostgresql);

oracle.sqltext = "SELECT * FROM dual WHERE ROWNUM = 1";
mssql.sqltext  = "SELECT TOP 10 * FROM employees";
pg.sqltext     = "SELECT * FROM employees LIMIT 10 OFFSET 5";

foreach (var p in new[] { oracle, mssql, pg })
    Console.WriteLine($"{p.DbVendor,-16} -> {(p.parse() == 0 ? "ok" : p.Errormessage)}");

Output:

dbvoracle        -> ok
dbvmssql         -> ok
dbvpostgresql    -> ok

Each of those three statements is a syntax error under the other two vendors, which is the point: the parser is strict about the dialect you asked for.

2. Oracle PL/SQL¶

Oracle is the vendor with a separate PL/SQL grammar. TGSqlParser reaches for it automatically when it sees a BEGIN ... END; block, a CREATE PROCEDURE/FUNCTION/PACKAGE, or an anonymous block.

JavaC#

TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext =
    "CREATE OR REPLACE PROCEDURE raise_salary (\n" +
    "    emp_id      IN NUMBER,\n" +
    "    increment   IN NUMBER\n" +
    ") AS\n" +
    "BEGIN\n" +
    "    UPDATE employees\n" +
    "       SET salary = salary + increment\n" +
    "     WHERE employee_id = emp_id;\n" +
    "END raise_salary;";

if (parser.parse() == 0) {
    System.out.println("Parsed: " + parser.sqlstatements.get(0).sqlstatementtype);
}

Output:

1	`Parsed: sstplsql_createprocedure`

var parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext = """
    CREATE OR REPLACE PROCEDURE raise_salary (
        emp_id      IN NUMBER,
        increment   IN NUMBER
    ) AS
    BEGIN
        UPDATE employees
           SET salary = salary + increment
         WHERE employee_id = emp_id;
    END raise_salary;
    """;

if (parser.parse() == 0)
    Console.WriteLine($"Parsed: {parser.sqlstatements.get(0).sqlstatementtype}");

Output:

1	`Parsed: sstplsql_createprocedure`

See Parse Oracle PL/SQL for the deep dive.

Java 8 and text blocks

The C# examples use raw string literals. The equivalent Java feature, text blocks ("""), needs Java 15+. The parser JAR is Java 8 bytecode and this documentation targets Java 8, so the Java examples use concatenation. Use text blocks freely if your own project targets a newer JDK.

3. SQL Server T-SQL¶

The MSSQL vendor handles full T-SQL — variables, control-of-flow, OUTPUT clauses, MERGE, CTEs, OPENJSON, and so on.

JavaC#

TGSqlParser parser = new TGSqlParser(EDbVendor.dbvmssql);
parser.sqltext =
    "DECLARE @threshold INT = 1000;\n" +
    "MERGE INTO target_table AS t\n" +
    "USING source_table AS s ON t.id = s.id\n" +
    "WHEN MATCHED AND s.amount > @threshold THEN\n" +
    "    UPDATE SET t.amount = s.amount\n" +
    "WHEN NOT MATCHED BY TARGET THEN\n" +
    "    INSERT (id, amount) VALUES (s.id, s.amount)\n" +
    "OUTPUT $action, inserted.id, deleted.amount;";

if (parser.parse() == 0) {
    System.out.println("Parsed " + parser.sqlstatements.size() + " statement(s).");
    for (int i = 0; i < parser.sqlstatements.size(); i++) {
        System.out.println("  [" + i + "] " + parser.sqlstatements.get(i).sqlstatementtype);
    }
}

var parser = new TGSqlParser(EDbVendor.dbvmssql);
parser.sqltext = """
    DECLARE @threshold INT = 1000;
    MERGE INTO target_table AS t
    USING source_table AS s ON t.id = s.id
    WHEN MATCHED AND s.amount > @threshold THEN
        UPDATE SET t.amount = s.amount
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (id, amount) VALUES (s.id, s.amount)
    OUTPUT $action, inserted.id, deleted.amount;
    """;

if (parser.parse() == 0)
{
    Console.WriteLine($"Parsed {parser.sqlstatements.size()} statement(s).");
    for (int i = 0; i < parser.sqlstatements.size(); i++)
        Console.WriteLine($"  [{i}] {parser.sqlstatements.get(i).sqlstatementtype}");
}

Output, identical in both editions:

Parsed 2 statement(s).
  [0] sstmssqldeclare
  [1] sstmerge

Two statements, not one — the DECLARE is a statement in its own right. Any code that assumes sqlstatements.get(0) is "the interesting one" will pick up the variable declaration instead of the MERGE.

Sybase ASE shares much of the MSSQL grammar; use EDbVendor.dbvsybase for ASE-specific syntax (raiserror, sp_* extensions).

4. PostgreSQL, Greenplum and Redshift¶

These three share grammar ancestry. If your SQL targets a fork, pick the matching vendor — they recognise different reserved-word sets and dialect extensions.

JavaC#

// PostgreSQL — RETURNING inside a CTE feeding an INSERT
TGSqlParser pg = new TGSqlParser(EDbVendor.dbvpostgresql);
pg.sqltext =
    "WITH upd AS (\n" +
    "  UPDATE employees SET sales_count = sales_count + 1\n" +
    "  WHERE id = (SELECT sales_person FROM accounts WHERE name = 'Acme')\n" +
    "  RETURNING *\n" +
    ")\n" +
    "INSERT INTO employees_log SELECT *, current_timestamp FROM upd;";
System.out.println("pg       -> " + pg.parse()
    + "  " + pg.sqlstatements.get(0).sqlstatementtype);

// Redshift — SUPER columns, IDENTITY, DISTKEY/SORTKEY
TGSqlParser rs = new TGSqlParser(EDbVendor.dbvredshift);
rs.sqltext =
    "CREATE TABLE events (\n" +
    "    id   BIGINT IDENTITY(1,1),\n" +
    "    body SUPER\n" +
    ") DISTKEY(id) SORTKEY(id);";
System.out.println("redshift -> " + rs.parse()
    + "  " + rs.sqlstatements.get(0).sqlstatementtype);

// PostgreSQL — RETURNING inside a CTE feeding an INSERT
var pg = new TGSqlParser(EDbVendor.dbvpostgresql);
pg.sqltext = """
    WITH upd AS (
      UPDATE employees SET sales_count = sales_count + 1
      WHERE id = (SELECT sales_person FROM accounts WHERE name = 'Acme')
      RETURNING *
    )
    INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
    """;
Console.WriteLine($"pg       -> {pg.parse()}  {pg.sqlstatements.get(0).sqlstatementtype}");

// Redshift — SUPER columns, IDENTITY, DISTKEY/SORTKEY
var rs = new TGSqlParser(EDbVendor.dbvredshift);
rs.sqltext = """
    CREATE TABLE events (
        id   BIGINT IDENTITY(1,1),
        body SUPER
    ) DISTKEY(id) SORTKEY(id);
    """;
Console.WriteLine($"redshift -> {rs.parse()}  {rs.sqlstatements.get(0).sqlstatementtype}");

Output:

pg       -> 0  sstinsert
redshift -> 0  sstcreatetable

The PostgreSQL example reports sstinsert, not a CTE type: the WITH clause hangs off the INSERT that consumes it.

5. MySQL specifics¶

JavaC#

TGSqlParser mysql = new TGSqlParser(EDbVendor.dbvmysql);
mysql.sqltext =
    "SELECT *\n" +
    "FROM   employees\n" +
    "LIMIT  5, 10;";          // MySQL's offset-first LIMIT
System.out.println("mysql -> " + mysql.parse());

var mysql = new TGSqlParser(EDbVendor.dbvmysql);
mysql.sqltext = """
    SELECT *
    FROM   employees
    LIMIT  5, 10;
    """;                      // MySQL's offset-first LIMIT
Console.WriteLine($"mysql -> {mysql.parse()}");

LIMIT 5, 10 means "skip 5, take 10" — the reverse of the LIMIT 10 OFFSET 5 form. Backtick-quoted identifiers and FORCE INDEX hints also parse only under dbvmysql.

6. Snowflake¶

JavaC#

TGSqlParser sf = new TGSqlParser(EDbVendor.dbvsnowflake);
sf.sqltext =
    "SELECT employee_id,\n" +
    "       AVG(salary) OVER (PARTITION BY dept) AS dept_avg\n" +
    "FROM   employees\n" +
    "QUALIFY ROW_NUMBER() OVER (ORDER BY hire_date DESC) <= 10;";
System.out.println("snowflake -> " + sf.parse());

var sf = new TGSqlParser(EDbVendor.dbvsnowflake);
sf.sqltext = """
    SELECT employee_id,
           AVG(salary) OVER (PARTITION BY dept) AS dept_avg
    FROM   employees
    QUALIFY ROW_NUMBER() OVER (ORDER BY hire_date DESC) <= 10;
    """;
Console.WriteLine($"snowflake -> {sf.parse()}");

QUALIFY filters on a window function without a wrapping subquery. It is a syntax error under every other vendor in this tutorial.

7. Teradata¶

Teradata has its own SPL (stored procedure language) and BTEQ commands; both are handled.

JavaC#

TGSqlParser td = new TGSqlParser(EDbVendor.dbvteradata);
td.sqltext =
    "CREATE PROCEDURE sample_sp (IN inp INT, OUT outp INT)\n" +
    "BEGIN\n" +
    "    DECLARE total INT;\n" +
    "    SELECT SUM(amount) INTO total FROM orders WHERE customer_id = inp;\n" +
    "    SET outp = total;\n" +
    "END;";
td.parse();
System.out.println(td.sqlstatements.get(0).sqlstatementtype);

Output:

1	`sstcreateprocedure`

var td = new TGSqlParser(EDbVendor.dbvteradata);
td.sqltext = """
    CREATE PROCEDURE sample_sp (IN inp INT, OUT outp INT)
    BEGIN
        DECLARE total INT;
        SELECT SUM(amount) INTO total FROM orders WHERE customer_id = inp;
        SET outp = total;
    END;
    """;
td.parse();
Console.WriteLine(td.sqlstatements.get(0).sqlstatementtype);

Output:

1	`sstteradatacreateprocedure`

One statement type differs between the editions

The same Teradata procedure reports sstcreateprocedure in Java and sstteradatacreateprocedure in .NET. Both parse successfully and produce the same tree; only the ESqlStatementType constant differs. If you switch on statement type across both editions, do not assume the names match.

Building a vendor auto-detector¶

When you do not know the dialect ahead of time, try each vendor and take the first that parses cleanly.

JavaC#

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TGSqlParser;

public static EDbVendor detectVendor(String sql) {
    EDbVendor[] candidates = {
        EDbVendor.dbvoracle, EDbVendor.dbvmssql, EDbVendor.dbvpostgresql,
        EDbVendor.dbvmysql,  EDbVendor.dbvsnowflake, EDbVendor.dbvteradata,
        EDbVendor.dbvdb2,    EDbVendor.dbvhive, EDbVendor.dbvredshift,
    };
    for (EDbVendor v : candidates) {
        TGSqlParser parser = new TGSqlParser(v);
        parser.sqltext = sql;
        if (parser.parse() == 0) return v;
    }
    return null;   // nothing parsed it
}

using gudusoft.gsqlparser;

public static EDbVendor? DetectVendor(string sql)
{
    EDbVendor[] candidates =
    {
        EDbVendor.dbvoracle, EDbVendor.dbvmssql, EDbVendor.dbvpostgresql,
        EDbVendor.dbvmysql,  EDbVendor.dbvsnowflake, EDbVendor.dbvteradata,
        EDbVendor.dbvdb2,    EDbVendor.dbvhive, EDbVendor.dbvredshift,
    };
    foreach (var v in candidates)
    {
        var parser = new TGSqlParser(v) { sqltext = sql };
        if (parser.parse() == 0) return v;
    }
    return null;
}

This is deliberately a sketch. For real workloads, weight the candidate order by the mix of SQL you actually expect, and remember that a plain SELECT parses identically under most vendors — so first-match wins and tells you very little. Detection only discriminates when the SQL contains something dialect-specific.

Note also that on .NET the candidate list must avoid dbvbigquery, dbvhana and dbvdax, since constructing those throws rather than returning an error.

What you can do now¶

You learned how to:

Pick the right EDbVendor for each dialect, and why you cannot change it later
Parse Oracle PL/SQL through the same TGSqlParser entry point
Handle T-SQL constructs (MERGE, OUTPUT, variables) and spot that DECLARE counts as its own statement
Distinguish PostgreSQL from Greenplum and Redshift
Recognise MySQL and Snowflake specific syntax
Sketch a vendor auto-detector, and see why first-match detection is weak

Continue learning¶

Advanced Features — AST modification, custom visitors
Vendor-specific how-to guides:
Database Compatibility — the conceptual picture behind per-vendor grammars