Call I / O type Save procedure in Sequelize

Question

I created a MySQL storage procedure that expects some inputs and returns some result. To call the storage procedure in MySQL, I run

CALL createCoupon(1236,321, @message); SELECT @message AS message 

and receiving the result in the message object.

Now a situation arises when I need to call this SP in sequelize. I am working on a sailsjs project and use the secelize module for concern.

I created a database connection in config / db_config, my connection string is:

 var sequelize = new Sequelize(db.name, db.user, db.pass, { host: db.host, dialect: "mysql", // or 'sqlite', 'postgres', 'mariadb' port: 3306, // or 5432 (for postgres) maxConcurrentQueries: 100, pool: { maxConnections: 50, maxIdleTime: 2000 }, queue: true }) 

and I call it in the controller, for example:

 var Sequelize = require('sequelize'); var sequelize = require('../../config/db_config').dbase; function setCoupon(couponCode, userId, setCouponResponse) { var createCouponSQL = "some raw query"; sequelize.query(createCouponSQL, null, { raw: true }).success(function(createCoupon) { sails.log.info(createCoupon); setCouponResponse(null, createCoupon); }).error(function(err) { sails.log.error(err); setCouponResponse(err, null); }); } module.exports = { 'createCoupon': function(req, callback) { setCoupon(req.param('coupon_code'), req.session.userSession, function(err, setCouponResponse){ }) } } 

now I need to call SP in sequelize, so I just try:

  var createCouponSQL = "CALL createCoupon(1236,321, @message);"; createCouponSQL += "SELECT @message AS message"; sequelize.query(createCouponSQL, null, { raw: true }).success(function(createCoupon) { sails.log.info(createCoupon); setCouponResponse(null, createCoupon); }).error(function(err) { sails.log.error(err); setCouponResponse(err, null); }); 

but sequlize raises an error:

 Executing (default): CALL createCoupon(1236,321, @message);SELECT @message AS m essage error: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the m anual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @message AS message' at line 1 at Query.Sequence._packetToError (C:\Users\asd\Desktop\CardCash P2\Website\ node_modules\mysql\lib\protocol\sequences\Sequence.js:30:14) 

I did R&D, but could not find anything appropriate to call the repository procedure, which sends me a response in @message, and I need to execute another select statement to get the result.

Preach to me how I can do this correctly using the database connection method.

Thanks.

Answer 1

Although quite late, we are doing something similar in the project. The whole trip looks like

We created all the SPs in separate SQL files, and there is a deployment step that inserts all the SPs into the database, extracting them from this particular place.

All SPs are basically returned by running the SELECT at the end of the SP. And having no separate choice outside of SP.

So, the basic structure is like

 DELIMITER $$ DROP PROCEDURE IF EXISTS sp_copy_cleansheet; $$ CREATE PROCEDURE `take_over_the_world` (#Some Input Args) BEGIN DECLARE method_to_be_used VARCHAR(1000); # Define how to take over the world, # SET method_to_be_used = "Approach A"; ... ... SELECT method_to_be_used as Result; END 

Now we call SP using syntactic raw queries,

 const query = 'CALL take_over_the_world(:someArg)'; return Model.sequelize.query(query, { replacements : { someArg}, type : Model.sequelize.QueryTypes.SELECT }) .then((response) => { //Access response here. Its of the form [{Result: /*How to take over*/}] }); 

Everything seems to work just fine: still :)