json - 尝试使用 express4-tedious POST 或 PUT 时出现 JSON 格式错误

Question

我正在尝试使用的 sql-server-samples 'Todo' 应用程序位于此处。它应该用很少的代码行将 SQL Server 数据库转换为 REST API。

我无法发布或放置。它给出了一个错误

message: "JSON text is not properly formatted. Unexpected character 'o' is found at position 1.",
code: 'EREQUEST',
number: 13609,
state: 4,
class: 16,
serverName: 'SERVER',
procName: 'createTodo',
lineNumber: 4

GET、GET by id 或 DELETE 没有问题。GET 和 DELETE 与 SQL Server 2017 Developer、Express 和 Azure SQL 数据库完美配合。

从 SQL Server 或 Azure 中运行存储过程本身没有错误。只有在尝试从命令行 POST 或 PUT 时，我才会收到错误消息：

curl -X "POST" "http://localhost:3000/todo"
-i
-H 'Content-Type: application/json'
-d '{"title": "test_title", "description": "test_description", "completed": false, "dueDate": "2014-12-14T00:00:00.000Z"}'

JSON 文本本身没有任何问题。在位置 1 看不到任何字符 'o'。我已经尝试了 nvarchar、varchar 的几乎所有变体，在 JSON 字符串的开头使用 N' 与 '，将描述更改为描述，认为它可能是保留的在 SQL Server 2017 下，似乎没有任何效果。

使用 Azure SQL 数据库的错误消息略有不同：

JSON text is not properly formatted. Unexpected character \'o\' is found at position 1.

应用程序.js

var express = require('express');
var config = require('config');
var bodyParser = require('body-parser');
var tediousExpress = require('express4-tedious');

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

app.use(bodyParser.text()); 
app.use('/todo', require('./routes/todo'));

// catch 404 and forward to error handler
app.use(function (req, res, next) {
    var err = new Error('Not Found: '+ req.method + ":" + req.originalUrl);
    err.status = 404;
    next(err);
});
app.set('port', process.env.PORT || 3000);

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

module.exports = app;

todo.js

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

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

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

});

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

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

});

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

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

});

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

    req.sql("exec updateTodo @id, @todo")
        .param('id', req.params.id, TYPES.Int)
        .param('todo', req.body, TYPES.NVarChar)
        .exec(res);

});

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

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

});

module.exports = router;

默认.json

{
    "connection":{
        "server"  : "<name>.database.windows.net",
        "userName": "username",
        "password": "password",
        "options": { "encrypt": "true", "database": "<azure-database-name>" }
    }
}

setup.sql - 用于 POST 和 PUT 的示例 Todo 表和存储过程

/*
CREATE DATABASE TodoDb;
USE TodoDb;
*/
DROP TABLE IF EXISTS Todo
DROP PROCEDURE IF EXISTS createTodo
DROP PROCEDURE IF EXISTS updateTodo
GO

CREATE TABLE Todo (
    id int IDENTITY PRIMARY KEY,
    title nvarchar(30) NOT NULL,
    description nvarchar(4000),
    completed bit,
    dueDate datetime2 default (dateadd(day, 3, getdate()))
)
GO

INSERT INTO Todo (title, description, completed, dueDate)
VALUES
('Install SQL Server 2016','Install RTM version of SQL Server 2016', 0, '2017-03-08'),
('Get new samples','Go to github and download new samples', 0, '2016-03-09'),
('Try new samples','Install new Management Studio to try samples', 0, '2016-03-12')

GO

create procedure dbo.createTodo(@todo nvarchar(max))
as begin
    insert into Todo
    select *
    from OPENJSON(@todo) 
            WITH (  title nvarchar(30), description nvarchar(4000),
                    completed bit, dueDate datetime2)
end
GO

create procedure updateTodo(@id int, @todo nvarchar(max))
as begin
    update Todo
    set title = json.title, description = json.description,
        completed = json.completed, dueDate = json.dueDate
    from OPENJSON( @todo )
            WITH(   title nvarchar(30), description nvarchar(4000),
                    completed bit, dueDate datetime2) AS json
    where id = @id
end
go

select * from todo for json path

标签： jsonsql-serverrestexpressazure-sql-database

Answer 1

我认为有一个小错误。您正在使用body-parser.text()但将数据作为 JSON 发送。更改 curl 请求如下：

 curl -X "POST" "http://localhost:3000/todo" -i -H 'Content-Type: text/plain' -d '{"title": "test_title", "description": "test_description", "completed": false, "dueDate": "2014-12-14T00:00:00.000Z"}'

此外，您可以使用Postman更方便的测试API

确保传递给过程的 JSON 字符串应该是准确的。您可以通过运行以下脚本来验证该过程的输入：

DECLARE @json NVARCHAR(MAX)

SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';

SELECT *
FROM OPENJSON(@json);