Basic SQL Parsing

This tutorial covers the essentials of parsing the four common SQL statement types — SELECT, INSERT, UPDATE, DELETE — and extracting key information from the resulting AST.

Objectives

• Parse SELECT, INSERT, UPDATE, DELETE statements
• Extract table names, column names and conditions
• Work with complex queries (JOINs, subqueries)
• Apply best practices for SQL parsing in C#

Prerequisites

• Completed Getting Started with GSP
• Basic understanding of SQL statement types

Time required

About 45 minutes.

Step-by-step guide

1. Parsing SELECT statements

using gudusoft.gsqlparser;
using gudusoft.gsqlparser.nodes;
using gudusoft.gsqlparser.stmt;

var parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext = """
    SELECT e.name, d.department_name
    FROM   employees e
    INNER  JOIN departments d ON e.dept_id = d.id
    WHERE  e.salary > 50000 AND d.location = 'New York'
    """;

if (parser.parse() == 0)
{
    var select = (TSelectSqlStatement)parser.sqlstatements.get(0);

    // Result columns
    Console.WriteLine("=== SELECT columns ===");
    TResultColumnList columns = select.ResultColumnList;
    for (int i = 0; i < columns.size(); i++)
    {
        TResultColumn column = columns.getResultColumn(i);
        Console.WriteLine($"  - {column.Expr}");
        if (column.AliasClause != null)
        {
            Console.WriteLine($"    AS {column.AliasClause}");
        }
    }

    // FROM clause
    Console.WriteLine();
    Console.WriteLine("=== FROM clause tables ===");
    TFromClause fromClause = select.JoinClause; // alias used in some versions
    // Most code uses select.tables / select.JoinClause depending on the API version.
    foreach (TTable table in select.tables)
    {
        Console.WriteLine($"  - {table.FullName}");
        if (table.AliasClause != null)
        {
            Console.WriteLine($"    Alias: {table.AliasName}");
        }
        Console.WriteLine($"    Type:  {table.TableType}");
    }

    // WHERE clause
    Console.WriteLine();
    Console.WriteLine("=== WHERE clause ===");
    TWhereClause where = select.WhereClause;
    Console.WriteLine(where != null ? $"  {where.Condition}" : "  (no WHERE)");
}
else
{
    Console.Error.WriteLine($"Parse failed: {parser.Errormessage}");
}

Property names

Some accessors are exposed as both methods (getXxx()) and C# properties (Xxx) because the codebase was originally auto-ported from Java. Both styles work; the property style is more idiomatic in C#. Where this tutorial calls a property like select.WhereClause, the equivalent method-style call select.getWhereClause() will also compile.

2. Advanced JOIN analysis

For SQL with nested JOIN expressions, walk TJoinExpr recursively. The C# API mirrors the Java API one-for-one.

using gudusoft.gsqlparser;
using gudusoft.gsqlparser.nodes;
using gudusoft.gsqlparser.stmt;

var parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext = """
    SELECT u.name, o.total, p.product_name, a.address, c.country_name
    FROM   users u
    LEFT JOIN (orders o INNER JOIN order_items oi ON o.id = oi.order_id)
                ON u.id = o.user_id
    INNER JOIN products p ON oi.product_id = p.id
    RIGHT JOIN (addresses a LEFT JOIN countries c ON a.country_code = c.code)
                ON u.id = a.user_id
    """;

if (parser.parse() == 0)
{
    var select = (TSelectSqlStatement)parser.sqlstatements.get(0);
    Console.WriteLine("=== Complex JOIN analysis ===");
    AnalyzeFromClause(select);
}

static void AnalyzeFromClause(TSelectSqlStatement select)
{
    foreach (TTable table in select.tables)
    {
        Console.WriteLine($"  Top-level relation: {DescribeTable(table)} (Type: {table.TableType})");

        if (table.TableType == ETableSource.join && table.JoinExpr != null)
        {
            PrintJoin(table.JoinExpr, indent: "    ");
        }
        else if (table.TableType == ETableSource.subquery && table.Subquery != null)
        {
            Console.WriteLine("    Contains a subquery — recursing.");
            AnalyzeFromClause(table.Subquery);
        }
    }
}

static void PrintJoin(TJoinExpr join, string indent)
{
    Console.WriteLine($"{indent}TJoinExpr ({join.Jointype})");
    if (join.LeftTable != null)
        Console.WriteLine($"{indent}  left:  {DescribeTable(join.LeftTable)}");
    if (join.RightTable != null)
        Console.WriteLine($"{indent}  right: {DescribeTable(join.RightTable)}");
    if (join.OnCondition != null)
        Console.WriteLine($"{indent}  on:    {join.OnCondition}");
    if (join.UsingColumns != null)
        Console.WriteLine($"{indent}  using: {join.UsingColumns}");

    // Recurse into nested joins
    if (join.LeftTable?.JoinExpr is { } leftNested)  PrintJoin(leftNested,  indent + "  ");
    if (join.RightTable?.JoinExpr is { } rightNested) PrintJoin(rightNested, indent + "  ");
}

static string DescribeTable(TTable t) =>
    t.TableType switch
    {
        ETableSource.subquery => "(subquery)",
        ETableSource.join     => $"[JOIN: {t.JoinExpr?.Jointype}]",
        _                     => t.FullName
    };

3. Parsing INSERT statements

using gudusoft.gsqlparser;
using gudusoft.gsqlparser.nodes;
using gudusoft.gsqlparser.stmt;

var parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext = """
    INSERT INTO customers (id, name, email, registration_date)
    VALUES (1, 'John Doe', 'john.doe@example.com', SYSDATE)
    """;

if (parser.parse() == 0)
{
    var insert = (TInsertSqlStatement)parser.sqlstatements.get(0);

    Console.WriteLine($"Target table: {insert.targetTable.FullName}");

    // Column list
    TObjectNameList columns = insert.ColumnList;
    if (columns != null)
    {
        Console.WriteLine("Columns:");
        for (int i = 0; i < columns.size(); i++)
            Console.WriteLine($"  {i + 1}: {columns.getObjectName(i)}");
    }
    else
    {
        Console.WriteLine("Columns: (all — no explicit list)");
    }

    // Values
    if (insert.InsertSource == EInsertSource.values && insert.Values != null)
    {
        Console.WriteLine("Values:");
        TMultiTargetList values = insert.Values;
        for (int i = 0; i < values.size(); i++)
        {
            TMultiTarget row = values.getMultiTarget(i);
            Console.WriteLine($"  Row {i + 1}:");
            if (row.ColumnList != null)
                for (int j = 0; j < row.ColumnList.size(); j++)
                    Console.WriteLine($"    {j + 1}: {row.ColumnList.getResultColumn(j).Expr}");
        }
    }
    else if (insert.InsertSource == EInsertSource.subquery)
    {
        Console.WriteLine($"Source: subquery — {insert.SubQuery}");
    }
}

4. Parsing UPDATE statements

using gudusoft.gsqlparser;
using gudusoft.gsqlparser.nodes;
using gudusoft.gsqlparser.stmt;

var parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext = """
    UPDATE employees
    SET    salary       = salary * 1.1,
           last_updated = SYSDATE
    WHERE  department = 'Engineering' AND hire_date < DATE '2020-01-01'
    """;

if (parser.parse() == 0)
{
    var update = (TUpdateSqlStatement)parser.sqlstatements.get(0);

    Console.WriteLine($"Target table: {update.targetTable.FullName}");

    Console.WriteLine();
    Console.WriteLine("SET assignments:");
    TResultColumnList sets = update.ResultColumnList;
    if (sets != null)
        for (int i = 0; i < sets.size(); i++)
            Console.WriteLine($"  {sets.getResultColumn(i)}");

    Console.WriteLine();
    if (update.WhereClause != null)
        Console.WriteLine($"WHERE: {update.WhereClause.Condition}");
    else
        Console.WriteLine("WARNING: no WHERE clause — every row will be updated!");
}

5. Error handling and best practices

using gudusoft.gsqlparser;

var parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext = "SELECT name, email FROM users WHERE invalid syntax here";

if (parser.parse() != 0)
{
    Console.WriteLine("=== Parse errors ===");
    Console.WriteLine($"Total errors: {parser.ErrorCount}");

    foreach (TSyntaxError error in parser.SyntaxErrors)
    {
        Console.Error.WriteLine(
            $"line {error.lineNo}, col {error.columnNo}: " +
            $"{error.tokentext} ({error.errortype})");
    }
}

// Best practice: a small wrapper for safe parsing
public static bool TryParse(EDbVendor vendor, string sql, out TGSqlParser parser)
{
    parser = new TGSqlParser(vendor) { sqltext = sql };
    if (parser.parse() == 0) return true;

    Console.Error.WriteLine($"SQL parse error: {sql}");
    foreach (TSyntaxError error in parser.SyntaxErrors)
        Console.Error.WriteLine($"  line {error.lineNo}: {error.tokentext}");
    return false;
}

Key takeaways

API best practices

1. Use property accessors (select.WhereClause) where available — they're idiomatic C#.
2. Check table.TableType — handle base tables, subqueries, joins, table functions distinctly.
3. Use table.JoinExpr for join analysis when TableType == ETableSource.join.
4. Always check the parse result before accessing AST properties.
5. Reuse parser instances when possible — parser construction is the slowest part.

Common patterns

• Iterate select.tables for each top-level table reference (the iterator returns TTable).
• Detect joins: table.TableType == ETableSource.join.
• Detect subqueries: table.TableType == ETableSource.subquery, then descend into table.Subquery.
• Aliases: table.AliasClause and table.AliasName.

Summary

In this tutorial you learned how to:

• Parse various SQL statement types
• Extract table information from FROM clauses
• Analyse complex JOIN structures with TJoinExpr
• Handle different table types (base, subquery, function, join)
• Extract columns, conditions and values
• Implement proper error handling

What's next?

• Working with Different Databases — vendor-specific parsing features
• Advanced Features — AST modification and re-emission