CODEWITHSUNDEEP
mysqlnode.jsstored-proceduresescaping
san
san

Reputation: 3

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

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

Upvotes: 0

Views: 1040

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179124

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.

Upvotes: 1

Related Questions