sql-server - 在 Node/Angular 应用程序中创建新 SQL 记录的问题

Question

我正在尝试通过我的 Node/Angular 应用程序在现有的 Azure MSSQL 数据库中创建一个新行。 GET 请求正确触发，我用来生成数据的表单根据我所知正确生成 JSON，但是当 POST 函数触发时，我收到以下错误:

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
      ^

这是我的 app.js 代码:

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;

和我的 sql 路由代码:

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;

最后，我的 mssql 存储过程代码:

/****** 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

编辑:添加了传递给 sql 请求的 JSON 对象，以表明没有我能看到的意外字符:

{ 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' }

我哪里错了？

Answer 1

作为记录，希望这对将来的人有帮助:

我最近遇到了一个非常相似的问题(完全相同的 RequestError)，发现我将 JSON 作为对象传递，而 express4-tedious 需要一个字符串。 JSON.stringify(req.body) 成功了。

换句话说，改变你的等价物

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

到

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

解决了我的问题。