Link to home
Start Free TrialLog in
Avatar of Stevie Zakhour
Stevie Zakhour

asked on

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.
Avatar of Stevie Zakhour
Stevie Zakhour

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Stevie Zakhour
Stevie Zakhour

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial