Issue Creating New MSSQL Record in Node/Angular App

Question

I am trying to create a new row in an existing Azure MSSQL database through my node/angular app. The GET request fires correctly, and the form I am using to generate the data generates the JSON correctly from what I can tell, but when the POST function fires, I get the following error:

Trace: { RequestError: JSON text is not properly formatted. Unexpected character 'o' is found at position 1.
    at RequestError (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\tedious\lib\errors.js:34:12)
    at Parser.<anonymous> (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\tedious\lib\connection.js:614:36)
    at Parser.emit (events.js:182:13)
    at Parser.<anonymous> (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\tedious\lib\token\token-stream-parser.js:54:15)
    at Parser.emit (events.js:182:13)
    at addChunk (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\readable-stream\lib\_stream_readable.js:291:12)
    at readableAddChunk (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\readable-stream\lib\_stream_readable.js:278:11)
    at Parser.Readable.push (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\readable-stream\lib\_stream_readable.js:245:10)
    at Parser.Transform.push (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\readable-stream\lib\_stream_transform.js:148:32)
    at doneParsing (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\tedious\lib\token\stream-parser.js:110:18)
  message:
   'JSON text is not properly formatted. Unexpected character \'o\' is found at position 1.',
  code: 'EREQUEST',
  number: 13609,
  state: 4,
  class: 16,
  serverName: 'xxxxxxx',
  procName: 'createReport',
  lineNumber: 5 }
    at Object.fnOnError (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\express4-tedious\index.js:104:25)
    at Request.userCallback (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\express4-tedious\index.js:59:64)
    at Request._this.callback (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\tedious\lib\request.js:60:27)
    at Connection.endOfMessageMarkerReceived (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\tedious\lib\connection.js:1922:20)
    at Connection.dispatchEvent (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\tedious\lib\connection.js:1004:38)
    at Parser.<anonymous> (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\tedious\lib\connection.js:805:18)
    at Parser.emit (events.js:182:13)
    at Parser.<anonymous> (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\tedious\lib\token\token-stream-parser.js:54:15)
    at Parser.emit (events.js:182:13)
    at addChunk (C:\Users\jlea\Desktop\Code\tnplan-boot\node_modules\readable-stream\lib\_stream_readable.js:291:12)
events.js:167
      throw er; // Unhandled 'error' event
      ^

Here is my app.js code:

const express = require('express');
const config = require('config');
const bodyParser = require('body-parser');
const tediousExpress = require('express4-tedious');
const cors = require('cors');
const path = require('path');

const app = express();
app.use(function (req, res, next) {
  req.sql = tediousExpress(config.get('connection'));
  next();
});

const corsOptions = {
  origin: '*',
  optionsSuccessStatus: 200
};

app.use(express.static(__dirname + '/dist/tnplan-boot'));
app.use(bodyParser.json());
app.options('*', cors(corsOptions));
app.use('/monthlyReport', require('./routes/monthlyReport'));


// "index" route, which serves the Angular app
app.get('/', function (req, res) {
  res.sendFile(path.join(__dirname, '/dist/tnplan-boot/index.html'));
});

// catch 404 and forward to error handler
app.use(function (req, res, next) {
  const err = new Error('Not Found: ' + req.method + ":" + req.originalUrl);
  err.status = 404;
  next(err);
});

app.set('port', process.env.PORT || 8080);

const server = app.listen(app.get('port'), function () {
  console.log('Express server listening on port ' + server.address().port);
});

module.exports = app;

and my sql routes code:

const router = require('express').Router();
const TYPES = require('tedious').TYPES;

/* GET reports. */
router.get('/', function (req, res) {

  req.sql("select * from jacksonwaste for json path")
    .into(res, '[]');

});

/* GET single report. */
router.get('/:id', function (req, res) {

  req.sql("select * from jacksonwaste where id = @id for json path, without_array_wrapper")
    .param('id', req.params.id, TYPES.Int)
    .into(res, '{}');

});

/* POST create report. */
router.post('/', function (req, res) {

  req.sql("exec createReport @report")
    .param('report', req.body, TYPES.NvarChar)
    .exec(res)
    console.log(req.body);
});

/* PUT update report. */
router.put('/:id', function (req, res) {

  req.sql("exec updateReport @id, @report")
    .param('id', req.params.id, TYPES.Int)
    .param('report', req.body, TYPES.NvarChar)
    .exec(res);

});

/* DELETE single report. */
router.delete('/:id', function (req, res) {

  req.sql("delete from jacksonwaste where id = @id")
    .param('id', req.params.id, TYPES.Int)
    .exec(res);

});

module.exports = router;

and finally, my mssql stored procedure code:

/****** Object:  StoredProcedure [dbo].[createReport]    Script Date: 8/15/2018 9:13:31 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[createReport](@report nvarchar(max))
as begin
SET NOCOUNT ON
    insert into jacksonwaste (reportuser, reportdate, percentcomp, err, errdate, acquisition, acqdate, pns, pnsdate, bidtabs, bidtabsdate, constructionstart, constartdate, constructionend, conenddate, monitored, monitorready,
    contractoractivity, lastcafsubmission, mostrecentinvoice, phases, phaseupdate, hasoccured, willoccur, issues, tnecdhelp)
    select *
    from OPENJSON(@report) 
            WITH (  
            reportuser nvarchar(128), 
            reportdate date,
            percentcomp int,
            err bit,
            errdate date,
            acquisition bit,
            acqdate date,
            pns bit,
            pnsdate date,
            bidtabs bit,
            bidtabsdate date,
            constructionstart bit,
            constartdate date,
            constructionend bit,
            conenddate date,
            monitored bit,
            monitorready bit,
            contractoractivity bit,
            lastcafsubmission date,
            mostrecentinvoice date,
            phases bit,
            phaseupdate nvarchar(1000),
            hasoccured nvarchar(1000),
            willoccur nvarchar(1000),
            issues nvarchar(1000),
            tnecdhelp nvarchar(1000)
            )
end

EDIT: added the JSON object that is being delivered to the sql request to show that there's no unexpected character that I can see:

{ reportuser: 'john',
  reportdate: '2018-08-11',
  percentcomp: '4',
  err: true,
  pns: true,
  pnsdate: '2018-08-04',
  errdate: '2018-08-16',
  constructionstart: true,
  constartdate: '2018-08-29',
  lastcafsubmission: '2018-08-23',
  mostrecentinvoice: '2018-08-24',
  phaseupdate: 'test',
  hasoccured: 'test',
  willoccur: 'test',
  issues: 'test',
  tnecdhelp: 'test' }

Where am I going wrong?

Answer 1

For the record, in hopes this helps someone in the future:

I recently had a very similar issue (Exact same RequestError) and found out that I was passing the JSON as an object, whereas express4-tedious was expecting a string. JSON.stringify(req.body) did the trick.

In other words, changing the equivalent of your

    req.sql("exec updateReport @id, @report")
       .param('id', req.params.id, TYPES.Int)
       .param('report', req.body, TYPES.NvarChar)
       .exec(res);

to

    req.sql("exec updateReport @id, @report")
       .param('id', req.params.id, TYPES.Int)
       .param('report', JSON.stringify(req.body), TYPES.NvarChar)
       .exec(res);

solved my problem.