Skip to content

Parameterized queries (Databricks)

Databricks SQL accepts parameter markers — typed placeholders that keep values out of the statement text:

1
2
3
4
5
SELECT o.name, o.total
FROM orders o
WHERE o.order_id = :order_id      -- named parameter marker
  AND o.status   = ?              -- positional parameter marker
  AND o.region  IN (:r1, :r2)

GSP parses both marker styles with EDbVendor.dbvdatabricks, exposes each marker as a regular AST expression node you can find and inspect, and lets you go the other way too: rewrite an existing statement's literal constants into parameter markers and get the parameterized SQL text back. This page shows the AST shape and a complete, runnable skeleton for both directions.

What parses (verified)

Syntax Example Parses
Named parameter marker WHERE order_id = :order_id yes
Positional parameter marker WHERE order_id = ? AND status = ? yes
Markers in an IN list region IN (:r1, :r2) yes
Marker in the select list SELECT :param1 AS p FROM t yes
Parameterized identifier SELECT * FROM IDENTIFIER(:mytable) yes
Legacy substitution template SELECT * FROM ${catalog}.${schema}.orders no — syntax error
Widget template WHERE status = {{ status }} no — syntax error
EXECUTE IMMEDIATE ... USING EXECUTE IMMEDIATE 'SELECT ...' USING 5 no — tokenizer error

${var} and {{ var }} are client-side text templates, not SQL: Databricks itself substitutes them before the statement reaches a parser. Substitute them yourself (or switch the SQL to :name markers, which Databricks recommends) before calling parse().

How a marker appears in the AST

A parameter marker is a leaf expression of type EExpressionType.simple_object_name_t, exactly like a column reference — the marker spelling is preserved in the node text:

1
2
3
4
5
WHERE o.order_id = :order_id AND o.status = ?

comparison (=)                    comparison (=)
├─ simple_object_name_t 'o.order_id'   ├─ simple_object_name_t 'o.status'
└─ simple_object_name_t ':order_id'    └─ simple_object_name_t '?'

So a marker is recognized by its text: toString().startsWith(":") for named markers, "?".equals(toString()) for positional ones. The node offers everything any expression leaf offers — getStartToken() for the line/column position, getObjectOperand() for the underlying TObjectName, and setString(...) to replace it in place.

Two behaviors to plan around when you walk the tree:

  • TExpression.inOrderTraverse visits an IN-list (list_t) as one node and does not descend into its items. To see markers inside IN (...), push the members of getExprList() onto your own work list (the skeleton below does this).
  • A positional ? is indistinguishable from an identifier by node type alone; match it by text.

Complete skeleton: find markers, and parameterize literals

Both directions in one runnable program. collectParameterMarkers finds every marker under an expression; parameterizeLiterals replaces every literal constant (EExpressionType.simple_constant_t) with a fresh :name marker and returns the marker → literal bindings, which is the core of SQL normalization, cache-key generation, or injection-hardening rewrites.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.EExpressionType;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.nodes.IExpressionVisitor;
import gudusoft.gsqlparser.nodes.TExpression;
import gudusoft.gsqlparser.nodes.TParseTreeNode;
import gudusoft.gsqlparser.stmt.TSelectSqlStatement;

public class ParameterizedQueryDemo {

    /**
     * Walk one expression tree, visiting IN-list items too. inOrderTraverse
     * visits a list expression as ONE node and does not descend into its
     * items, so list_t members are pushed onto an explicit work list.
     */
    static void walkExpression(TExpression root, final IExpressionVisitor visitor) {
        final List<TExpression> pending = new ArrayList<TExpression>();
        pending.add(root);
        for (int next = 0; next < pending.size(); next++) {
            pending.get(next).inOrderTraverse(new IExpressionVisitor() {
                public boolean exprVisit(TParseTreeNode node, boolean isLeafNode) {
                    TExpression e = (TExpression) node;
                    if (e.getExpressionType() == EExpressionType.list_t && e.getExprList() != null) {
                        for (int i = 0; i < e.getExprList().size(); i++) {
                            pending.add(e.getExprList().getExpression(i));
                        }
                    }
                    return visitor.exprVisit(node, isLeafNode);
                }
            });
        }
    }

    /** Collect every parameter marker (:name or ?) under one expression tree. */
    static List<TExpression> collectParameterMarkers(TExpression root) {
        final List<TExpression> markers = new ArrayList<TExpression>();
        walkExpression(root, new IExpressionVisitor() {
            public boolean exprVisit(TParseTreeNode node, boolean isLeafNode) {
                TExpression e = (TExpression) node;
                if (e.getExpressionType() == EExpressionType.simple_object_name_t) {
                    String text = e.toString();
                    if (text.startsWith(":") || "?".equals(text)) {
                        markers.add(e);
                    }
                }
                return true;
            }
        });
        return markers;
    }

    /**
     * Replace every literal constant under the expression with a named
     * parameter marker. Returns marker name -> original literal text.
     */
    static Map<String, String> parameterizeLiterals(TExpression root, final String prefix) {
        final Map<String, String> bindings = new LinkedHashMap<String, String>();
        walkExpression(root, new IExpressionVisitor() {
            public boolean exprVisit(TParseTreeNode node, boolean isLeafNode) {
                TExpression e = (TExpression) node;
                if (e.getExpressionType() == EExpressionType.simple_constant_t) {
                    String marker = ":" + prefix + (bindings.size() + 1);
                    bindings.put(marker, e.toString());
                    e.setString(marker);
                }
                return true;
            }
        });
        return bindings;
    }

    public static void main(String[] args) {
        // ---- 1. Parse a query that already uses parameter markers ----------
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvdatabricks);
        parser.sqltext = "SELECT o.name, o.total\n"
                + "FROM orders o\n"
                + "WHERE o.order_id = :order_id AND o.status = ? AND o.region IN (:r1, :r2)";
        if (parser.parse() != 0) {
            System.out.println(parser.getErrormessage());
            return;
        }
        TSelectSqlStatement select = (TSelectSqlStatement) parser.sqlstatements.get(0);
        System.out.println("-- markers found in WHERE:");
        for (TExpression marker : collectParameterMarkers(select.getWhereClause().getCondition())) {
            System.out.println("   " + marker.toString()
                    + "  (expression type: " + marker.getExpressionType() + ")");
        }

        // ---- 2. Rewrite literals into a parameterized form -----------------
        TGSqlParser parser2 = new TGSqlParser(EDbVendor.dbvdatabricks);
        parser2.sqltext = "SELECT name, total FROM orders\n"
                + "WHERE order_id = 12345 AND region IN ('EMEA', 'APAC') AND ts > '2026-01-01'";
        if (parser2.parse() != 0) {
            System.out.println(parser2.getErrormessage());
            return;
        }
        TSelectSqlStatement select2 = (TSelectSqlStatement) parser2.sqlstatements.get(0);
        Map<String, String> bindings =
                parameterizeLiterals(select2.getWhereClause().getCondition(), "p");

        String rewritten = select2.toString();
        System.out.println("-- parameterized SQL:");
        System.out.println(rewritten);
        System.out.println("-- extracted bindings:");
        for (Map.Entry<String, String> b : bindings.entrySet()) {
            System.out.println("   " + b.getKey() + " = " + b.getValue());
        }

        // ---- 3. Round-trip: the rewritten SQL must parse again -------------
        TGSqlParser parser3 = new TGSqlParser(EDbVendor.dbvdatabricks);
        parser3.sqltext = rewritten;
        System.out.println("-- re-parse of rewritten SQL: "
                + (parser3.parse() == 0 ? "OK" : parser3.getErrormessage()));
    }
}

Actual output (GSP Java 4.2.6):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- markers found in WHERE:
   :order_id  (expression type: simple_object_name_t)
   ?  (expression type: simple_object_name_t)
   :r1  (expression type: simple_object_name_t)
   :r2  (expression type: simple_object_name_t)
-- parameterized SQL:
SELECT name, total FROM orders
WHERE order_id = :p1 AND region IN (:p3, :p4) AND ts > :p2
-- extracted bindings:
   :p1 = 12345
   :p2 = '2026-01-01'
   :p3 = 'EMEA'
   :p4 = 'APAC'
-- re-parse of rewritten SQL: OK

Building your own transformation on this skeleton

The three moving parts generalize to most query-transformation tasks:

  1. Locate the nodes you care about with walkExpression (markers, literals, column references — switch on getExpressionType() and, for name-vs-literal decisions, on the node text or getObjectOperand().getDbObjectType()).
  2. Rewrite in place with TExpression.setString(...). The change is a token-level edit, so the rest of the statement — comments, casing, whitespace — is preserved when you re-serialize.
  3. Re-serialize and validate: stmt.toString() returns the modified SQL; feeding it into a fresh TGSqlParser proves the rewrite is still valid SQL. Always keep this round-trip step — it turns a silent bad rewrite into a loud one.

Variations on the same pattern:

  • Deparameterize (markers → sample values) by matching markers in walkExpression and calling setString(binding.get(text)).
  • Cache keys / statement fingerprints: run parameterizeLiterals and use the rewritten SQL as the key, the bindings map as the values.
  • Cover more clauses by applying the walker to other expressions of the statement (getGroupByClause(), getHavingClause(), join conditions, each select-list item's getExpr()), not just getWhereClause().getCondition().
  • Other dialects: the same skeleton works for every vendor GSP supports; only the marker spelling differs (? JDBC style, :name Oracle style, @name SQL Server variables, and so on).

Limitations

  • ${var} / {{ var }} template substitution and EXECUTE IMMEDIATE ... USING do not parse in the Databricks dialect today (see the table above). Pre-substitute templates before parsing, or contact support if you need these forms parsed natively.
  • A positional ? marker is classified as an ordinary object name; use the text match shown above rather than expecting a dedicated marker node type.