Azure / Azure/data-api-builder

[Bug]: Possible issue with passing date values in env file to stored procedure

オープン
#2,067 コメント 0 件 リアクション 0 件 担当者 0 名 GitHub で見る
bug good first issue triage
主要言語
C#
スター
1.5k
フォーク
370
平均マージ
3日 22時間
マージ済み PR(30日)
9

説明

### What happened?

env file has these values:
````
year-start=2023-07-01
year-end=2024-06-30
````

Referencing these values in config file:
```
"entities": {
"GetCreated": {
"source": {
"type": "stored-procedure",
"object": "GetCreated",
"parameters": {
"YearStartDate": "@env('year-start')",
"YearEndDate": "@env('year-end')"
}
},...
}
```

Stored proc is configured to accept date time values.

When I GET from the endpoint. I get this error:
````
{
"error": {
"code": "BadRequest",
"message": "Parameter \"@env('year-end')\" cannot be resolved as stored procedure parameter \"YearEndDate\" with type \"DateTime\".",
"status": 400
}
}
````

If I change the parameter to a hard coded value like below, the GET returns data as expected:
````
"parameters": {
"YearStartDate": "2023-07-01",
"YearEndDate": "2024-06-30"
````

Is this a bug or am I doing something wrong?

### Version

0.10.23

### What database are you using?

Azure SQL

### What hosting model are you using?

Local (including CLI), Static Web Apps (SWA), Container Apps

### Which API approach are you accessing DAB through?

REST

### Relevant log output

```Text
2024-03-01 17:51:35 fail: Azure.DataApiBuilder.Service.Controllers.RestController[0]
2024-03-01 17:51:35 c885d8ca-439e-4745-bc16-b292b1223f25 Error handling REST request.
2024-03-01 17:51:35 Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Parameter "@env('year-end')" cannot be resolved as stored procedure parameter "YearEndDate" with type "DateTime".
2024-03-01 17:51:35 ---> System.FormatException: String '@env('year-end')' was not recognized as a valid DateTime.
2024-03-01 17:51:35 at System.DateTimeParse.Parse(ReadOnlySpan`1 s, DateTimeFormatInfo dtfi, DateTimeStyles styles, TimeSpan& offset)
2024-03-01 17:51:35 at System.DateTimeOffset.Parse(String input, IFormatProvider formatProvider, DateTimeStyles styles)
2024-03-01 17:51:35 at Azure.DataApiBuilder.Core.Resolvers.BaseSqlQueryStructure.ParseParamAsSystemType(String param, Type systemType) in /src/src/Core/Resolvers/Sql Query Structures/BaseSqlQueryStructure.cs:line 364
2024-03-01 17:51:35 at Azure.DataApiBuilder.Core.Resolvers.BaseSqlQueryStructure.GetParamAsSystemType(String fieldValue, String fieldName, Type systemType) in /src/src/Core/Resolvers/Sql Query Structures/BaseSqlQueryStructure.cs:line 534
2024-03-01 17:51:35 --- End of inner exception stack trace ---
2024-03-01 17:51:35 at Azure.DataApiBuilder.Core.Resolvers.BaseSqlQueryStructure.GetParamAsSystemType(String fieldValue, String fieldName, Type systemType) in /src/src/Core/Resolvers/Sql Query Structures/BaseSqlQueryStructure.cs:line 568
2024-03-01 17:51:35 at Azure.DataApiBuilder.Core.Resolvers.SqlExecuteStructure..ctor(String entityName, ISqlMetadataProvider sqlMetadataProvider, IAuthorizationResolver authorizationResolver, GQLFilterParser gQLFilterParser, IDictionary`2 requestParams) in /src/src/Core/Resolvers/Sql Query Structures/SqlExecuteQueryStructure.cs:line 63
2024-03-01 17:51:35 at Azure.DataApiBuilder.Core.Resolvers.SqlQueryEngine.ExecuteAsync(StoredProcedureRequestContext context, String dataSourceName) in /src/src/Core/Resolvers/SqlQueryEngine.cs:line 159
2024-03-01 17:51:35 at Azure.DataApiBuilder.Core.Services.RestService.DispatchQuery(RestRequestContext context, DatabaseType databaseType) in /src/src/Core/Services/RestService.cs:line 225
2024-03-01 17:51:35 at Azure.DataApiBuilder.Core.Services.RestService.ExecuteAsync(String entityName, EntityActionOperation operationType, String primaryKeyRoute) in /src/src/Core/Services/RestService.cs:line 195
2024-03-01 17:51:35 at Azure.DataApiBuilder.Service.Controllers.RestController.HandleOperation(String route, EntityActionOperation operationType) in /src/src/Service/Controllers/RestController.cs:line 214
```

### Code of Conduct

- [X] I agree to follow this project's Code of Conduct

コントリビューションガイド

コントリビューションガイドを開く

調査の方向性

BaseSqlQueryStructure.cs の ParseParamAsSystemType と GetParamAsSystemType から始め、SqlExecuteQueryStructure.cs を通るストアドプロシージャの経路を追ってください。Azure SQL と示されている環境値を使用して REST リクエストを再現します。完了条件は、DateTime パラメーターについて環境の日付値が正しく解決され、リクエストが parsing error を返さなくなることです。

索引モデルが issue の本文から書いたものです。

評価

技術スタック
azure, csharp, sql
領域
api, backend, databases
issue の種類
バグ
難易度
3/5
見積もり時間
1〜2日
活発さ
停滞
明瞭さ
明確に書かれている
初心者へのやさしさ
42/100

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。