Mysql: ER_PARSE_ERROR: while sending create procedure code from node js

Question

I am trying to send create procedure query from node js to MYSQL. I am reading Mysql.proc table to get the stored procedure definitions.

Mysql code working fine when submitting from mysql client like workbench or hedis

Getting following error while submitting code from NodeJS

{ Error: ER_PARSE_ERROR: 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 'CREATE PROCEDURE `qwe`.`USP_GET_ALL_STOCK_WITH_INDICATORS`( IN `IPV_DATE` DATE )' at line 1
    .
    .
    .
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage:
   '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 \'CREATE PROCEDURE `qwe`.`USP_GET_ALL_STOCK_WITH_INDICATORS`( IN `IPV_DATE` DATE )\' at line 1',
  sqlState: '42000',
  index: 0,
  sql:
   'USE qwe;    CREATE PROCEDURE `qwe`.`USP_GET_ALL_STOCK_WITH_INDICATORS`( IN `IPV_DATE` DATE )BEGIN IF NOT EXISTS(SELECT 1 FROM StockCandle where `DATE`=IPV_DATE) THEN SET @D:=(SELECT MAX(`DATE`) FROM StockCandle); ELSE SET @D:=IPV_DATE; END IF; -- select @D; SELECT  SM.Symbol, SM.MA13, SM.MA8, SM.MA5, CASE WHEN SM.MA5>SM.MA8 AND SM.MA8>MA13 THEN \'>>>\' WHEN SM.MA5<SM.MA8 AND SM.MA8<MA13 THEN \'<<<\' ELSE NULL
END ALLIGATOR ,SM.RSI, SM.BHAVTIME, SM.CANDLEINDICATOR FROM StockMaster SM  where SM.DATE=@D; END // ' }

tried below code to pares sql in node

formattdSQL = formattdSQL.replace(/(?:\\[rtn]|[\r\t]+)+/g, ' ');

Query to get procedure

SELECT `name`, CONVERT(param_list USING utf8), CONVERT(body USING utf8)
INTO @spname, @spparams, @spbody
FROM mysql.proc WHERE `name` = 'USP_GET_ALL_STOCK_WITH_INDICATORS' AND db = v_oldDB;
SET @sql = CONCAT(@sql, '\r\n', 'DELIMITER //','\r\n','CREATE PROCEDURE `', v_newDB, '`.`', @spname, '`(', @spparams,')',@spbody, ' //', '\r\n','');

I expect that create procedure query should be executed successfully from node

Michael - sqlbot · Accepted Answer · 2019-05-04 22:34:16Z

1

The DELIMITER directive is not part of MySQL Server SQL. That is used only with interactive client utilities that parse an input stream or the contents of an on-screen text box into individual statements -- like workbench or (presumably) "hedis" (whatever that may be).

You don't use it when you are using a programming library to send a query.

Just send the procedure declaration as a single query. Send the USE statement as a separate query, before that.

First query:

USE qwe

(A trailing semicolon in the first query is not actually expected by the server but is allowed if you send it.)

Second query:

CREATE PROCEDURE ...
...
END

There should be no DELIMITER // before, nor // after. Those are all client-side constructs, not meaningful to the server.

There is no need to use your formattdSQL.replace(...) statement to remove newlines. That only serves to make your code unreadable, and isn't needed.

answered May 4, 2019 at 22:34

Michael - sqlbot

180k28 gold badges388 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

san Over a year ago

Thank you @Michael removing USE DB worked, I need to send 100 of sprc definition in one go, What delimiter i should use at end of each proc definition? Right now i m able to send single proc only.

Michael - sqlbot Over a year ago

You might be able to set multipleStatements: true in the connection options and then use a ; but that option is an exceptionally bad idea because it makes certain SQL injection vulnerabilities possible that are not possible by default. If you turn on your MySQL server general query log, you'll find that workbench, et al, do not send multiple queries at a time. Each proc definition will be sent as an individual query.

Collectives™ on Stack Overflow

Mysql: ER_PARSE_ERROR: while sending create procedure code from node js

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related