We help IT Professionals succeed at work.

Status: 422 (Unprocessable Entity)

Hi All

I have a MS SQL script that runs a stored procedure called [Freak_APITest2] - see script code below

DECLARE @LeaderboardName		varchar		(20)
DECLARE @LeaderboardNameFull	varchar		(20) 
DECLARE @PlayerCount			varchar		(20)
DECLARE @PlayerName				varchar		(20)
DECLARE @Score					nvarchar	(20)
DECLARE @ZombiesKilled			varchar		(15)
DECLARE @Accuracy				varchar		(50)
DECLARE @DamageTaken			varchar		(20)
DECLARE @SecondsInLevel			varchar		(30)
DECLARE @Deaths					varchar		(50)

SELECT top 1 @LeaderboardName        = LeaderboardName		from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'
SELECT top 1  @LeaderboardNameFull	 = LeaderboardNameFull	from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'
SELECT top 1 @PlayerCount	 = PlayerCount					from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'	
SELECT top 1 @PlayerName	 = PlayerName					from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'	
SELECT top 1 @Score	         = Score						from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'	
SELECT top 1 @ZombiesKilled	 = ZombiesKilled				from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'	
SELECT top 1 @Accuracy	     = Accuracy						from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'		
SELECT top 1 @DamageTaken	 = DamageTaken					from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'	
SELECT top 1 @SecondsInLevel = SecondsInLevel				from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'
SELECT top 1 @Deaths		 = Deaths						from ScoreBoard		where date	 = '2020-02-04 14:04:38.970'	


EXEC [Freak_APITest2]  @LeaderboardName, @LeaderboardNameFull,@PlayerCount, @PlayerName, @Score,@ZombiesKilled,@Accuracy,@DamageTaken,@SecondsInLevel,@Deaths

Open in new window


When I run the above script I get an error message, see below


Status: 422 (Unprocessable Entity)
Response text: {"error":{"type":"INVALID_REQUEST_MISSING_FIELDS","message":"Could not find field \"fields\" in the request body"}}

The stored procedure script code for Freak_APITest2 is below

alter procedure [dbo].[Freak_APITest2]
(
 @LeaderboardName			varchar		(20)
, @LeaderboardNameFull		varchar		(20) 
, @PlayerCount				varchar		(20)
, @PlayerName				varchar		(20)
, @Score					nvarchar	(20)
, @ZombiesKilled			varchar		(15)
, @Accuracy				varchar		(50)
, @DamageTaken				varchar		(20)
, @SecondsInLevel			varchar		(30)
, @Deaths					varchar		(50)
 --,@Date					datetime	
)
as

--DECLARE @authHeader NVARCHAR(64);
DECLARE @contentType NVARCHAR(64);
DECLARE @postData NVARCHAR(2000);
DECLARE @responseText NVARCHAR(2000);
DECLARE @responseXML NVARCHAR(2000);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
DECLARE @url NVARCHAR(256);

DECLARE @key varchar(100) = 'keyxxxxxxxxx';DECLARE @authHeader varchar(64) = 'Bearer '+@key; 


SET @contentType = 'application/json';
SET @postData =  
'{
  "records": [
    {
      "fields": {
        "LeaderboardName": "' + @LeaderboardName + '",
        "LeaderboardNameFull": "' + @LeaderboardNameFull + '",
        "PlayerCount":  "' + @PlayerCount + '",
        "PlayerName": "' + @PlayerName + '",
        "Score": "' + @Score + '",
        "ZombiesKilled": "' + @ZombiesKilled + '",
        "Accuracy":  "' + @Accuracy + '",
        "DamageTaken":  "' + @DamageTaken + '",
        "Deaths":  "' + @Deaths + '"
      }
    }
  ]
}'

SET @url = 'https://api.airtable.com/v0/appvFNyUQi9WZWSFH/ScoreTestTable';



-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authorization', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;

-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;

-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;

-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);

Open in new window


In the stored procedure, I did add the fields, see below

SET @postData =  
'{
  "records": [
    {
      "fields": {
        "LeaderboardName": "' + @LeaderboardName + '",
        "LeaderboardNameFull": "' + @LeaderboardNameFull + '",
        "PlayerCount":  "' + @PlayerCount + '",
        "PlayerName": "' + @PlayerName + '",
        "Score": "' + @Score + '",
        "ZombiesKilled": "' + @ZombiesKilled + '",
        "Accuracy":  "' + @Accuracy + '",
        "DamageTaken":  "' + @DamageTaken + '",
        "Deaths":  "' + @Deaths + '"
      }
    }
  ]
}'

Open in new window


I don't know why it won't run when the json is formatted correctly. Any support will be greatly appreciated.
Comment
Watch Question

Author

Commented:
I modified SET @postData = to include casting, see below


SET @postData =  
'{
  "records": [
    {
      "fields": {
        "LeaderboardName":"'+cast(@LeaderboardName as varchar(20))+'",
        "LeaderboardNameFull":"'+cast(@LeaderboardNameFull as varchar(20))+'",
        "PlayerCount": "'+cast(@PlayerCount as varchar(20))+'",,
        "PlayerName": "'+cast(@PlayerName as varchar(20))+'",
        "Score": "'+cast(@Score as varchar(20))+'",
        "ZombiesKilled":"'+cast(@ZombiesKilled as varchar(15))+'",
        "Accuracy": "'+cast(@Accuracy as varchar(50))+'",
        "DamageTaken": "'+cast(@DamageTaken as varchar(30))+'",
        "Deaths": "'+cast(@Deaths as varchar(50))+'"
      }
    }
  ]
}'

Open in new window


When I run the stored procedure again I get the error

Status: 422 (Unprocessable Entity)
Response text: {"error":{"type":"INVALID_REQUEST_MISSING_FIELDS","message":"Could not find field \"fields\" in the request body"}}

Open in new window


Any help is greatly appreciated
OK, I worked it out. See below
alter procedure [dbo].[Freak_APITest2]
(
 @LeaderboardName			varchar		(20)
, @LeaderboardNameFull		varchar		(20) 
, @PlayerCount				varchar		(20)
, @PlayerName				varchar		(20)
, @Score					nvarchar	(20)
, @ZombiesKilled			varchar		(15)
, @Accuracy				varchar		(50)
, @DamageTaken				varchar		(20)
, @SecondsInLevel			varchar		(30)
, @Deaths					varchar		(50)
 --,@Date					datetime	
)
as

--DECLARE @authHeader NVARCHAR(64);
DECLARE @contentType NVARCHAR(64);
DECLARE @postData NVARCHAR(2000);
DECLARE @responseText NVARCHAR(2000);
DECLARE @responseXML NVARCHAR(2000);
DECLARE @ret INT;
DECLARE @status NVARCHAR(32);
DECLARE @statusText NVARCHAR(32);
DECLARE @token INT;
DECLARE @url NVARCHAR(256);

DECLARE @key varchar(100) = 'hidden';DECLARE @authHeader varchar(64) = 'Bearer '+@key; 


SET @contentType = 'application/json';
SET @postData =  
'{
  "records": [
    {
      "fields": {
        "LeaderboardName": "' + @LeaderboardName + '",
        "LeaderboardNameFull": "' + @LeaderboardNameFull + '",
        "PlayerCount":  ' + @PlayerCount + ',
        "PlayerName": "' + @PlayerName + '",
        "Score": ' + @Score + ',
        "ZombiesKilled": ' + @ZombiesKilled + ',
        "Accuracy":  ' + @Accuracy + ',
        "DamageTaken":  ' + @DamageTaken + ',
        "Deaths":  ' + @Deaths + '
      }
    }
  ]
}'

SET @url = 'https://api.airtable.com/v0/appvFNyUQi9WZWSFH/ScoreTestTable';



-- Open the connection.
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT;
IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', 10, 1);

-- Send the request.
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Authorization', @authHeader;
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType;
EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData;

-- Handle the response.
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;
EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;
EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;

-- Show the response.
PRINT 'Status: ' + @status + ' (' + @statusText + ')';
PRINT 'Response text: ' + @responseText;

-- Close the connection.
EXEC @ret = sp_OADestroy @token;
IF @ret <> 0 RAISERROR('Unable to close HTTP connection.', 10, 1);

Open in new window