Example of calling mysql stored procedure in Node.js

Example of calling mysql stored procedure in Node.js_node.js

WBOY

Release： 2016-05-16 16:25:28

Original

2735 people have browsed it

The example was only tested under windows and was not tested under linux. If you have any questions, you can email me~

1. Install node.js, mysql, which is omitted here (search for it yourself)…;

2. Create a database named test, and then create a table named user_info (for testing only)...

It is assumed that the user name used by mysql is root and the password is 123456

The corresponding mysql is as follows:

Copy code The code is as follows:

/**
* Create a database named test
*/
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

/**
* Create user_info table
*/
DROP TABLE IF EXISTS `user_info`;

CREATE TABLE `user_info` (
`userId` int(10) NOT NULL AUTO_INCREMENT,
`userName` varchar(20) DEFAULT NULL,
PRIMARY KEY (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/**
* Insert three records
*/
INSERT INTO user_info VALUES (NULL, 'Zhang Yi'), (NULL, 'Zhang Er'), (NULL, 'Zhang San');

3. Create a stored procedure (written very redundantly, on purpose... just in time to learn the syntax>_<);

Copy code The code is as follows:

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`proc_simple`$$
CREATE PROCEDURE proc_simple(IN uid INT(10), OUT uName VARCHAR(2), OUT totalCount INT)
BEGIN

DECLARE str_name VARCHAR(20);

SET @str_name = '';
SET totalCount = 0;
SELECT COUNT(1),userName INTO totalCount,@str_name FROM user_info WHERE userId = uid;
SET uName = @str_name;
SELECT uName, totalCount;

END$$
DELIMITER ;

4. Write a program to call (assuming it is saved as a file named sql.js);

Copy code The code is as follows:

/**
* Created with JetBrains WebStorm.
* User: Meteoric_cry
* Date: 12-12-28
* Time: 上午00:18
* To change this template use File | Settings | File Templates.
*/
var mysql = require('mysql');

var connection = mysql.createConnection({
Host : 'localhost',
Port: 3306,
User: 'root',
Password : '123456',
Database : 'test',
charset: 'UTF8_GENERAL_CI',
debug : false
});

connection.connect();

connection.query('CALL proc_simple(1, @a, @b);', function(err, rows, fields) {
If (err) {
throw err;
}

var results = rows[0];
var row = results[0];
console.log("userName：",row.uName, "count：", row.totalCount);
});

connection.end();

5. Run the sample program;