How to call a SQL stored procedure using Node.js and get the results

Node.js, as a server-side JavaScript runtime environment, has become a very popular technology. For Node.js developers, calling database stored procedures is an effective way to perform complex database queries. However, how does Node.js correctly call SQL stored procedures compared to other programming languages?

This article will introduce how to use Node.js to call SQL stored procedures and obtain the results.

Connecting to the database

Before using Node.js to connect to the database, you need to install the corresponding Node.js database driver. This article takes the mysqljs library as an example. First, you need to introduce this library into the Node.js project:

var mysql = require('mysql');

Before connecting to the database, you need to create a MySQL connection pool.

var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
});

Please make sure to replace host, user, password, and database with the correct database connection information. After the connection pool is successfully created, you can use the following code to connect to the database.

pool.getConnection(function(err, connection) {
  // 在此处处理连接错误
  connection.query('SELECT * FROM customers', function(err, rows) {
    // 在此处处理查询结果
    connection.release(); // 释放连接
  });
});

Before releasing the connection, you need to pay attention to one thing: when using MySQL stored procedures, there is no need to release the connection. Because the stored procedure will automatically close the connection. In the next example, you'll learn how to call a stored procedure.

Calling a stored procedure

Suppose you have a stored procedure named getCustomers with the following parameter list:

• ##in_start : Zero-based page number.
• in_amount: The number of records to be displayed.
• out_total_rows: Total number of rows.
• out_total_pages: Total number of pages.

The following example demonstrates how to call a stored procedure using Node.js.

pool.getConnection(function(err, connection) {
  // 在此处处理连接错误
  connection.query('CALL getCustomers(?, ?, @total_rows, @total_pages)', [0, 10], function(err, rows) {
    if (err) throw err;

    // 执行第二个查询以获取返回值
    connection.query('SELECT @total_rows AS total_rows, @total_pages AS total_pages', function(err, rows) {
      if (err) throw err;

      var totalRows = rows[0][0].total_rows;
      var totalPages = rows[1][0].total_pages;

      console.log('Total rows: ' + totalRows);
      console.log('Total pages: ' + totalPages);

      connection.release();
    })
  });
});

In the first line, we call the stored procedure. Note that we use

? instead of every parameter in the stored procedure. In the example, we passed two parameters to the stored procedure: 0 and 10. @total_rows and @total_pages are the two output parameters of the stored procedure. After the first query is executed, we need to execute another SQL query to get the values ​​with the output parameters. After you obtain the output parameters, you can operate on the values.

Conclusion

Using Node.js to connect to the database and call stored procedures requires careful handling of connection pooling and connection release. There is no need to manually close or release the connection during the execution of the stored procedure. Using the above example, the SQL stored procedure can be executed correctly and the results obtained.

The above is the detailed content of How to call a SQL stored procedure using Node.js and get the results. For more information, please follow other related articles on the PHP Chinese website!