- .NET Programming
- SQL
- C#
- Query Syntax
Status: 422 (Unprocessable Entity)
Hi All
I have a MS SQL script that runs a stored procedure called [Freak_APITest2] - see script code below
When I run the above script I get an error message, see below
The stored procedure script code for Freak_APITest2 is below
In the stored procedure, I did add the fields, see below
I don't know why it won't run when the json is formatted correctly. Any support will be greatly appreciated.
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
When I run the above script I get an error message, see below
Status: 422 (Unprocessable Entity)
Response text: {"error":{"type":"INVALID_REQUEST_MI SSING_FIEL DS","messa ge":"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);
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 + '"
}
}
]
}'
I don't know why it won't run when the json is formatted correctly. Any support will be greatly appreciated.
I modified SET @postData = to include casting, see below
When I run the stored procedure again I get the error
Any help is greatly appreciated
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))+'"
}
}
]
}'
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"}}
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);
Premium Content
You need an Expert Office subscription to comment.Start Free Trial