runSQL

Syntax

runSQL(X, [sqlStd='ddb'], [variables])

Arguments

X is a string indicating the script to be executed. X does not support SQL DDL operations. When sqlStd is set to 'oracle' or 'mysql', SQL DML operations are not supported.

sqlStd (optional) is a string that specifies the dialect for parsing. It can be ‘ddb' (default), ‘oracle’, or 'mysql’.

variables (optional) is a dictionary for passing parameters dynamically. Keys are strings representing variable names (which can contain letters, numbers, and underscores, but must start with a letter), and values are the corresponding objects to bind to the variables.

Details

The runSQL function parses and executes a script based on the specified context. It provides parameterized SQL support, allowing variables to be passed directly as parameters in SQL queries. This eliminates the need for manual string concatenation to construct SQL statements, significantly reducing the risk of SQL injection attacks and improving code maintainability.

Note: When runSQL is called within a user-defined function, it cannot access the complete context. Hence, it is advisable to avoid calling runSQL within user-defined functions.

Only part of the functions or features of Oracle/MySQL are supported:

SQL Dialect Features Functions (case insensitive) Syntax Reference
Oracle

Comment symbols: --, /**/

Concatenation operator:||

asciistr, concat, decode, instr, length, listagg, nvl, nvl2, rank, regexp_like, replace, to_char, to_date, to_number, trunc, wm_concat

Note: to_char only accepts numeric, DATE, DATEHOUR, and DATETIME types.

SQL Language Reference
MySQL sysdate MySQL :: MySQL 8.0 Reference Manual :: 12 Functions and Operators

Note: Scripts written in DolphinDB language can be correctly parsed in Oracle or MySQL mode.

Examples

Example 1: Parsing with DolphinDB syntax with variables specified

Suppose we have the following order table t:

n=10
customerId="DB00"+string(1..5)
orderDate=2025.01.01..2025.01.10
t=table(take(orderDate, n) as orderDate, 1..10 as orderId, take(customerId,n) as customerId, rand(1000,n) as volume)
orderDate orderId customerId volume
2025.01.01 1 DB001 435
2025.01.02 2 DB002 134
2025.01.03 3 DB003 483
2025.01.04 4 DB004 867
2025.01.05 5 DB005 708
2025.01.06 6 DB001 291
2025.01.07 7 DB002 663
2025.01.08 8 DB003 254
2025.01.09 9 DB004 386
2025.01.10 10 DB005 653

We want to query the orders of a specific customer (DB001) from the last 10 days (assuming the query date is 2025.01.14).

sql_script ="SELECT orderId,customerId, orderDate, volume FROM t WHERE customerId==customer AND orderDate>=(date(now())- days)"
variables={"customer":`DB001,"days": 10}
re=runSQL(sql_script, , variables)
print(re)

Output:

orderId customerId orderDate volume
6 DB001 2025.01.06 291

Example 2: Parsing with Oracle syntax

runSQL("concat(CONCAT(`14`mysql, `22`oracle),`11`33)", 'oracle')
// output: ["142211","mysqloracle33"]

runSQL("string(1 2 3) || string(4 5 6)", 'oracle')
// output: ["14","25","36"]

runSQL("TO_DATE('2023-05-18', 'YYYY-MM-DD')",`oracle)
// output: 2023.05.18

Example 3: Parsing with MySQL syntax

runSQL("SYSDATE() + 1", `mysql)
// output: 2025.01.15