mapage2 mapage2 - 2 years ago 111
SQL Question

Using MySQL stored procedures with Javascript objects

I have been searching for an answer on how to use mySQL stored procedures with javascript form data as parameters.

This is what I have thus far:

var first_name = req.body.firstName,
last_name= req.body.lastName,
password= req.body.password,
gpa= req.body.gpa,
major = req.body.major,
classification= req.body.classification;
var query = connection.query("CALL new_student()", function (err, result) {
if (err) {

Here is the stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_student`(IN first_name VARCHAR(45),
IN last_name VARCHAR(45), IN email VARCHAR(45), IN password VARCHAR(45), IN gpa DECIMAL(3,2),
IN major INT(10), IN classification VARCHAR(45))
INSERT INTO users (first_name, last_name, email, password)
VALUES (first_name, last_name, email, password);
INSERT INTO student (user_id, gpa, major, classification)
VALUES (LAST_INSERT_ID(),gpa, major, classification);

What I want to do is take the variables and pass them in through "new_student()" or possibly just create a javascript object and pass them in that way. Either way, I could use some assistance.

Answer Source

You need to provide arguments to the function. If you're using the node-mysql-native you can provide the parameters using syntax like a prepared statement.

var query = connection.query("CALL new_student(?, ?, ?, ?, ?, ?, ?)", [first_name, last_name, email, password, gpa, major, classification], 
    function (err, result) {
    if (err) {

For more information about this, see Preventing SQL injection in Node.js

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download