How can I change the delimiter in Query Editor?

Question

Running the following command in Query Editor produces a syntax error. Why?

```
delimiter //
```

```
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter //' at line 1
```

I recognize that 'delimiter' is a feature of the client and not the mysql server but how else is one to create a multiline stored procedure such as this?

```
delimiter //

CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
   SELECT COUNT(*) INTO param1 FROM t;
END//

delimiter ;
```

Answer

I spent a day trying to figure this out so hopefully this helps someone out there...  
  
DELIMITER is a feature of the client, not the mysql server. The RDS Query Editor is a client but it does not support changing the delimiter so attempting to run a multiline script won't work since the first time it sees a semicolon it will interpret that as the end of the command and fail with a syntax error. It also does not support BEGIN and END commands used when creating multiline functions and procedures.  
  
So, how do you create something like a stored procedure that has multiple statements and semicolons in it? You must create it as an .sql file and send it using the Data API from either a Lambda function or the CLI.  
  
First, create your script in a .sql file without any DELIMITER commands or alternate delimiters.  
  
For example: function.sql

```
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
   SELECT COUNT(*) INTO param1 FROM t;
END
```

Then, run the script using the CLI like this:

```
cat function.sql | xargs -0 aws rds-data execute-statement \
    --resource-arn arn:aws:rds:eu-west-1:xxx:cluster:cluster-name \
    --secret-arn arn:aws:secretsmanager:eu-west-1:xxx:secret:secret-name-xxx \
    --database "database_name" \
    --sql
```

Alternatively, you can create a Lambda function that reads the file and uses rds_client.execute_statement() to send the script to the server via the Data API. But again, do NOT use the DELIMITER statement. The server sees the BEGIN and END lines and acts accordingly without the need to change the delimiter.

How can I change the delimiter in Query Editor?

相關內容