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