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?

You might want to take a look at this in your error: 'JSON text is not properly formatted. Unexpected character \'o\' is found at position 1.', — Chris
– Chris, Commented Aug 15, 2018 at 14:42
I've tried, but when I log the req.body that's being sent, theres no character like that. Edited my post to include what is logged to the terminal when I initiate the submit — John Lea
– John Lea, Commented Aug 15, 2018 at 14:51
you might want to try looking at your JSON in a debugger instead of logging it out. — Chris
– Chris, Commented Aug 15, 2018 at 15:03
Right. It's clear the JSON object thats being delivered isn't formatted correctly, its why/where its happening that's escaping me. — John Lea
– John Lea, Commented Aug 15, 2018 at 17:03
How did you solve this? I am getting the same first error (with the character 'o' at position 1).... — ontheway
– ontheway, Commented Aug 11, 2020 at 13:29

ontheway · Accepted Answer · 2020-08-12 16:49:26Z

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.

edited Aug 12, 2020 at 16:49

answered Aug 12, 2020 at 16:42

ontheway

1252 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Issue Creating New MSSQL Record in Node/Angular App

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related