powershell Azure REST API插入操作中的OData类型有什么问题？

tquggr8v  于 2023-03-12  发布在  Shell
关注(0)|答案(2)|浏览(118)

考虑下面的代码，它使用的是与example from MSLearn完全相同的JSON字符串，只是获取的是数值型的Partition Key和Row Key。

sTableName = "timstesttable"
tableSASToken = "sv=2021-10-04&ss=btqf&srt=sco&se=2123-02-16T22%3A02%3A00Z&sp=rwdxftlacup&sig={sig}"

sJSON = "{""TableName"":""" & sTableName & """}"
sURL = "https://mystorage.table.core.windows.net/Tables?" & tableSASToken
Set oRequest = CreateObject("MSXML2.XMLHTTP.6.0")
oRequest.Open "POST", sURL
oRequest.setRequestHeader "Date", getUTC
oRequest.setRequestHeader "Content-Type", "application/json"
oRequest.setRequestHeader "Accept", "application/json;odata=nometadata"
oRequest.setRequestHeader "Prefer", "return-content"
oRequest.setRequestHeader "Content-Length", Len(sJSON)
oRequest.Send sJSON
WScript.Echo oRequest.Status & ": " & oRequest.statusText
WScript.Echo oRequest.responseText
Set oRequest = Nothing

sURL = "https://mystorage.table.core.windows.net/Tables('" & sTableName & "')?" & tableSASToken
Set oRequest = CreateObject("MSXML2.XMLHTTP.6.0")
oRequest.Open "GET", sURL
oRequest.setRequestHeader "Date", getUTC
oRequest.setRequestHeader "x-ms-version", "2021-10-04"
oRequest.setRequestHeader "Accept", "application/json;odata=nometadata"
oRequest.Send
WScript.Echo oRequest.Status & ": " & oRequest.statusText
WScript.Echo oRequest.responseText
Set oRequest = Nothing

sJSON = "{""Address"":""MountainView"",""Age"":23,""AmountDue"":200.23,""CustomerCode@odata.type"":""Edm.Guid"",""CustomerCode"":""c9da6455-213d-42c9-9a793e9149a57833"",""CustomerSince@odata.type"":""Edm.DateTime"",""CustomerSince"":""2008-0710T00:00:00"",""IsActive"":true,""NumberOfOrders@odata.type"":""Edm.Int64"",""NumberOfOrders"":""255"",""PartitionKey"":""12345"",""RowKey"":""6789""}"

sURL = "https://mystorage.table.core.windows.net/" & sTableName & "?" & tableSASToken
Set oRequest = CreateObject("MSXML2.XMLHTTP.6.0")
oRequest.Open "POST", sURL
oRequest.setRequestHeader "Date", getUTC
oRequest.setRequestHeader "x-ms-version", "2021-10-04"
oRequest.setRequestHeader "Content-Type", "application/json"
oRequest.setRequestHeader "Content-Length", Len(sJSON)
oRequest.setRequestHeader "Accept", "application/json;odata=nometadata"
oRequest.setRequestHeader "Prefer", "return-content"
oRequest.Send sJSON
WScript.Echo oRequest.Status & ": " & oRequest.statusText
WScript.Echo oRequest.responseText

--------------------------------------------------------------------------------------
Output:

201: Created
{"TableName":"timstesttable"}

200: OK
{"TableName":"timstesttable"}

400: Bad Request
{"odata.error":{"code":"InvalidInput","message":{"lang":"en-US","value":"An error occurred while processing this request.\nRequestId:74bcc6ea-b002-000d-5521-43570a000000\nTime:2023-02-17T22:44:18.4850227Z"}}}

但是，如果我从JSON字符串中删除OData数据类型，它就可以工作。

sTableName = "timstesttable"
tableSASToken = "sv=2021-10-04&ss=btqf&srt=sco&se=2123-02-16T22%3A02%3A00Z&sp=rwdxftlacup&sig={sig}"

sJSON = "{""TableName"":""" & sTableName & """}"
sURL = "https://mystorage.table.core.windows.net/Tables?" & tableSASToken
Set oRequest = CreateObject("MSXML2.XMLHTTP.6.0")
oRequest.Open "POST", sURL
oRequest.setRequestHeader "Date", getUTC
oRequest.setRequestHeader "Content-Type", "application/json"
oRequest.setRequestHeader "Accept", "application/json;odata=nometadata"
oRequest.setRequestHeader "Prefer", "return-content"
oRequest.setRequestHeader "Content-Length", Len(sJSON)
oRequest.Send sJSON
WScript.Echo oRequest.Status & ": " & oRequest.statusText
WScript.Echo oRequest.responseText
Set oRequest = Nothing

sURL = "https://mystorage.table.core.windows.net/Tables('" & sTableName & "')?" & tableSASToken
Set oRequest = CreateObject("MSXML2.XMLHTTP.6.0")
oRequest.Open "GET", sURL
oRequest.setRequestHeader "Date", getUTC
oRequest.setRequestHeader "x-ms-version", "2021-10-04"
oRequest.setRequestHeader "Accept", "application/json;odata=nometadata"
oRequest.Send
WScript.Echo oRequest.Status & ": " & oRequest.statusText
WScript.Echo oRequest.responseText
Set oRequest = Nothing

sJSON = "{""Address"":""MountainView"",""Age"":23,""AmountDue"":200.23,""CustomerCode"":""c9da6455-213d-42c9-9a793e9149a57833"",""CustomerSince"":""2008-0710T00:00:00"",""IsActive"":true,""NumberOfOrders"":""255"",""PartitionKey"":""12345"",""RowKey"":""6789""}"
sURL = "https://mystorage.table.core.windows.net/" & sTableName & "?" & tableSASToken
Set oRequest = CreateObject("MSXML2.XMLHTTP.6.0")
oRequest.Open "POST", sURL
oRequest.setRequestHeader "Date", getUTC
oRequest.setRequestHeader "x-ms-version", "2021-10-04"
oRequest.setRequestHeader "Content-Type", "application/json"
oRequest.setRequestHeader "Content-Length", Len(sJSON)
oRequest.setRequestHeader "Accept", "application/json;odata=nometadata"
oRequest.setRequestHeader "Prefer", "return-content"
oRequest.Send sJSON
WScript.Echo oRequest.Status & ": " & oRequest.statusText
WScript.Echo oRequest.responseText

--------------------------------------------------------------------------------------
Output:

201: Created
{"TableName":"timstesttable"}

200: OK
{"TableName":"timstesttable"}

201: Created
{"PartitionKey":"12345","RowKey":"6789","Timestamp":"2023-02-17T22:39:21.1436354Z","Address":"MountainView","Age":23,"AmountDue":200.23,"CustomerCode":"c9da6455-213d-42c9-9a793e9149a57833","CustomerSince":"2008-0710T00:00:00","IsActive":true,"NumberOfOrders":"255"}

如果我的所有数字都是数值数据类型，这将很好，但情况并不总是如此。使用Azure REST API插入实体时，指定数据类型的正确方法是什么？
附录：这不仅仅是VBScript代码的问题。使用PowerShell也会发生同样的问题。正确指定odata数据类型会导致插入操作失败。省略它们也可以（但可能会导致推断错误的数据类型）。

$sTableName = "timstesttable"
$tableSASToken = "sv=2021-10-04&ss=btqf&srt=sco&st=2023-02-15T22%3A02%3A13Z&se=2123-02-16T22%3A02%3A00Z&sp=rwdxftlacup&sig={sig}"
$sURL = "https://mytable.table.core.windows.net/"+$sTableName+"?$tableSASToken"

$badJSON = '{"Address":"MountainView","Age":23,"AmountDue":200.23,"CustomerCode@odata.type":"Edm.Guid","CustomerCode":"c9da6455-213d-42c9-9a793e9149a57833","CustomerSince@odata.type":"Edm.DateTime","CustomerSince":"2008-0710T00:00:00","IsActive":true,"NumberOfOrders@odata.type":"Edm.Int64","NumberOfOrders":"255","PartitionKey":"12345","RowKey":"6789"}'
$goodJSON = '{"Address":"MountainView","Age":23,"AmountDue":200.23,"CustomerCode":"c9da6455-213d-42c9-9a793e9149a57833","CustomerSince":"2008-0710T00:00:00","IsActive":true,"NumberOfOrders":"255","PartitionKey":"12345","RowKey":"6789"}'
$headers = @{
    "Date" = $(Get-Date)
    "Accept" = "application/json;odata=nometadata"
    "x-ms-version" = "2021-10-04"
    "Content-Type" = "application/json"
    "Prefer" = "return-content"
    "Content-Length" = $badJSON.length
}
Invoke-RestMethod -uri $sURL -Method Post -Headers $headers -Body $badJSON 

$headers = @{
    "Date" = $(Get-Date)
    "Accept" = "application/json;odata=nometadata"
    "x-ms-version" = "2021-10-04"
    "Content-Type" = "application/json"
    "Prefer" = "return-content"
    "Content-Length" = $goodJSON.length
}
Invoke-RestMethod -uri $sURL -Method Post -Headers $headers -Body $goodJSON

Invoke-RestMethod : The remote server returned an error: (400) Bad Request.
At line:15 char:1
+ Invoke-RestMethod -uri $sURL -Method Post -Headers $headers -Body $ba ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

PartitionKey   : 12345
RowKey         : 6789
Timestamp      : 2023-02-20T16:45:45.3443571Z
Address        : MountainView
Age            : 23
AmountDue      : 200.23
CustomerCode   : c9da6455-213d-42c9-9a793e9149a57833
CustomerSince  : 2008-0710T00:00:00
IsActive       : True
NumberOfOrders : 255
powershell

2条答案

按热度按时间
yvfmudvl

yvfmudvl1#

不幸的是，我认为您遇到的问题是一个简单的复制/粘贴打字错误。当您指定类型时，在POST到API的过程中，它会对指定的类型进行验证，因为值中有打字错误，它将失败验证，并返回400 Invalid Input。
排印错误位于CustomerCode GUID中，其中缺少-：

"CustomerCode":"c9da6455-213d-42c9-9a793e9149a57833"

应为：

"CustomerCode":"c9da6455-213d-42c9-9a79-3e9149a57833"

此外，在CustomerSince中还缺少一个-：

"CustomerSince":"2008-0710T00:00:00"

应为：

"CustomerSince":"2008-07-10T00:00:00"

为您提供：

$FixedJSON = '{"Address":"MountainView","Age":23,"AmountDue":200.23,"CustomerCode@odata.type":"Edm.Guid","CustomerCode":"c9da6455-213d-42c9-9a79-3e9149a57833","CustomerSince@odata.type":"Edm.DateTime","CustomerSince":"2008-07-10T00:00:00","IsActive":true,"NumberOfOrders@odata.type":"Edm.Int64","NumberOfOrders":"255","PartitionKey":"12345","RowKey":"6789"}'

它在删除类型时起作用，因为值默认为字符串，因此将按观察结果工作并保存。
来自Microsoft Learn page的示例（截至2023-02-21）具有正确的值，但该页面最近已于2023-01-31更新，因此可能已在最近得到更正。
@Sridevi Postman解决方案成功的原因是他们直接从Microsoft Learn page复制了JSON，而Microsoft Learn page具有正确的JSON，并且成功了。通过使用此问题中提供的JSON，我可以在Postman中复制400 Invalid Input结果，并确认这是JSON问题。

赞(0）分享  回复(0）举报  2023-03-12
eqqqjvef

eqqqjvef2#

使用Azure REST API插入实体时，指定数据类型的正确方法是什么？
使用Azure REST API插入实体时，您需要以@odata.type指定数据类型，如下所示：

{property_name}@odata.type: "Edm.{data_type}"

可能的值为Edm.String、Edm.Int32、Edm.Int64、Edm.Guid、Edm.Boolean等，您需要根据数据类型和Accept标头中的odata值将其包括在内。
我尝试通过Postman在我的环境中重现相同的问题，结果如下：

我在我的存储帐户中创建了一个名为**timstesttable**的表，如下所示：

要生成SAS令牌，我在允许的服务中选择了Table，如下所示：

单击Generate SAS and connection string选项后，我成功生成了SAS令牌，如下所示：

现在我尝试通过Postman调用REST API来插入实体，标题如下：

POST https://<storageaccname>.table.core.windows.net/<table name>?sv=2021-06-08&ss=t&srt=sco&sp=rwdlacu&se=2023-02-18T14:47:19Z&st=2023-02-18T06:47:19Z&spr=https&sig=<sig>

Date:18 February 2023
x-ms-version:2021-10-04
Content-Type:application/json
Accept:application/json;odata=nometadata
Prefer:return-content

当我在Body节中包含相同的JSON字符串沿着 *OData数据类型 * 时，实体插入成功，并显示response，如下所示：

POST https://<storageaccname>.table.core.windows.net/<table name>?sv=2021-06-08&ss=t&srt=sco&sp=rwdlacu&se=2023-02-18T14:47:19Z&st=2023-02-18T06:47:19Z&spr=https&sig=<sig>
{  
   "Address":"Mountain View",  
   "Age":23,  
   "AmountDue":200.23,  
   "CustomerCode@odata.type":"Edm.Guid",  
   "CustomerCode":"c9da6455-213d-42c9-9a79-3e9149a57833",  
   "CustomerSince@odata.type":"Edm.DateTime",  
   "CustomerSince":"2008-07-10T00:00:00",  
   "IsActive":true,  
   "NumberOfOrders@odata.type":"Edm.Int64",  
   "NumberOfOrders":"255",  
   "PartitionKey":"12345",  
   "RowKey":"6789"  
}

答复：

要确认这一点，您可以调用以下列出表实体的GET请求：

GET https://<storageaccname>.table.core.windows.net/<table name>?sv=2021-06-08&ss=t&srt=sco&sp=rwdlacu&se=2023-02-18T14:47:19Z&st=2023-02-18T06:47:19Z&spr=https&sig=<sig>

Date:18 February 2023
x-ms-version:2021-10-04
Content-Type:application/json
Accept:application/json;odata=nometadata
Prefer:return-content

答复：

请注意，您需要显式地包含 *OData数据类型 *，仅当它不是通过OData JSON类型检测启发式推断出来的时候。
因此，即使不包含 OData数据类型 ，插入实体也不会出现任何错误，因为OData启发式在某些情况下会检测到数据类型。

参考资料：

插入和更新实体（REST API）- Azure存储|微软
表服务操作的有效负载格式（REST API）- Azure存储|微软

赞(0）分享  回复(0）举报  2023-03-12
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com