Execute MySql Stored Procedure from TypeScript

user_15797317 0
Viewed 61

I’m new to node.js as well as TypeScript and have been tasked to fetch data from MySql using TypeScript and stored procedures in MySql. I have searched far and wide for an example of how to execute a stored procedure from TypeScript and have not found a single mention about it. The closest thing I’ve found is data access examples in node.js. Furthermore, I’m not interested in ORM packages, and want to use a low level driver and simply execute stored procedures from Typescript, then I can take care of everything else.

I have sample node.js code calling a stored procedure as shown below:

connection.query("CALL spMIJobFilter_lst('4261ebf7-614c-4b6c-9dff-39044a048b26');", function (error, results, fields) {
    if (error)
        throw error;

    results.forEach(result => {
        console.log(result);
    });
});

But don’t know how to convert this to TypeScript.

Lastly, this code returns json and I would like an option to return a simple object array and skip the process somewhere between the database and here that’s converting the data to json.

Thanks.

typescript mysql
1 Answers

Found the answer, but perhaps someone can explain why there seems to be little information about this.

//We need mysql2 and NOT mysql
const mysql = require('mysql2');

let connection = mysql.createConnection({
    host: '127.0.0.1',
    user: 'root',
    password: 'myPW',
    database: 'myDB'
});

connection.connect(function(err: any) {
    if (err) {
      return console.error('error: ' + err.message);
    }
  
    console.log('Connected to the MySQL server.');
  });
  console.log('step 3');

//Execute the stored procedure the same way we would in an SQL script.
//The only examples I found were in node.js, so here we need to add types    to the prameters
//Note:The resultset in Array<any>, however, some procedures return a scalar value instead 
//  so this type would have to change.  I have not test that yet.
connection.query('CALL spix_Trace_lstForReview(\"2022-1-1\", \"2022-7-7\" )', function (error: any, results: Array<any>, fields: any) {
    if (error)
        throw error;

        console.log(results);
        results.forEach(result => {
          console.log(result.Name);
    });
});
user_157973170
Related