We help IT Professionals succeed at work.
Get Started

Status: 422 (Unprocessable Entity)

144 Views
Last Modified: 2020-02-04
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
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE