# runSQL {#runsql}

**Parent topic:**[Functions](../../Functions/category.md)

## Syntax {#syntax}

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

## Arguments {#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 {#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:

<table id="table_jcg_tn5_c2c"><thead><tr><th align="left">

**SQL Dialect**

</th><th align="left">

**Features**

</th><th align="left">

**Functions \(case insensitive\)**

</th><th align="left">

**Syntax Reference**

</th></tr></thead><tbody><tr><td align="left">

Oracle

</td><td align="left">

Comment symbols: --, /\*\*/

 Concatenation operator:\|\|

</td><td align="left">

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.

</td><td align="left">

[SQL Language Reference](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Functions.html#GUID-D079EFD3-C683-441F-977E-2C9503089982)

</td></tr><tr><td align="left">

MySQL

</td><td align="left">



</td><td align="left">

sysdate

</td><td align="left">

[MySQL :: MySQL 8.0 Reference Manual :: 12 Functions and Operators](https://dev.mysql.com/doc/refman/8.0/en/functions.html)

</td></tr></tbody>
</table>**Note**: Scripts written in DolphinDB language can be correctly parsed in Oracle or MySQL mode.

## Examples {#examples}

Example 1: Parsing with DolphinDB syntax with *variables* specified

Suppose we have the following order table t:

``` {#codeblock_nlm_vn5_c2c}
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\).

``` {#codeblock_dtd_xn5_c2c}
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

``` {#codeblock_wmy_yn5_c2c}
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

``` {#codeblock_cnj_zn5_c2c}
runSQL("SYSDATE() + 1", `mysql)
// output: 2025.01.15
```

