Wait for stored procedure to completely finish before executing python script from node/express/tedious

Question

I'm trying to execute a python script from node/express/tedious after a SQL Server stored procedure has finished running.

When the user clicks submit, a post request is sent to node/express:

const onSubmit = async (data) => {
  await fetch('http://example.com:4001/foo/post/create', {
      method: 'POST',
      headers: authHeader(),
      body: JSON.stringify({
          fid: data.fid,
          geomwkt: data.geomwkt,
          srid: data.srid,
          .
          .
          .
        })
      }).then(res => {
         return res.text();
      })
      .then(data => console.log('Success:', data))
      .catch(error => console.log('Error:', error))
      history.push('/foo');
    }

which in turn runs a SQL Server stored procedure via a router.post (express4/tedious):

router.post('/post/create', textParser, function (req, res) {

    req.sql("exec create_alignment @align")
        .param('align', req.body, TYPES.NVarChar)
        .exec(res);

});

So now I want to execute a python script once the stored procedure has completed:

router.post('/post/create', textParser, function (req, res) {
    
    req.sql("exec create_alignment @align")
        .param('align', req.body, TYPES.NVarChar)
        .exec(res);

    const abc = JSON.parse(req.body);

    exec('python3 /home/ubuntu/scripts/runthis.py ' + param1 + ' ' + param2, (error, stdout, stderr) => {
        if (error) {
          console.error(`error: ${error.message}`);
          return;
        }
      
        if (stderr) {
          console.error(`stderr: ${stderr}`);
          return;
        }
      
        console.log(`stdout:\n${stdout}`);
    });
});

But python script runs before the stored procedure has completed. So I've tried adding async/await into the mix:

router.post('/post/create', textParser, async function (req, res) {
    
    await req.sql("exec create_alignment @align")
        .param('align', req.body, TYPES.NVarChar)
        .exec(res);

    const abc = JSON.parse(req.body);

    await exec('python3 /home/ubuntu/scripts/runthis.py ' + param1 + ' ' + param2, (error, stdout, stderr) => {
        if (error) {
          console.error(`error: ${error.message}`);
          return;
        }
      
        if (stderr) {
          console.error(`stderr: ${stderr}`);
          return;
        }
      
        console.log(`stdout:\n${stdout}`);
    });
});

But this doesn't seem to change anything. How do I run the python script after the stored procedure has completely finished?

Answer 1

From my understanding tedious doens't support ter async/await (but I'm quite new to node so maybe things have changed lately and I just don't know). You have to make sure the code you want to be executed after the procedure by manually coding the result of the promise.

I've wrote an article on this specifically, here:

Promises, Node, Tedious, Azure SQL. Oh My!