Azure / Azure/data-api-builder
[Bug]: Possible issue with passing date values in env file to stored procedure
- Langage dominant
- C#
- Étoiles
- 1.5k
- Forks
- 370
- Merge moyen
- 3 j 22 h
- PR mergées (30 j)
- 9
Description
### 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
Guide de contribution
Ouvrir le guide de contribution
Piste de recherche
Commencez par BaseSqlQueryStructure.cs au niveau de ParseParamAsSystemType et GetParamAsSystemType, puis suivez le chemin de la procédure stockée via SqlExecuteQueryStructure.cs. Reproduisez la requête REST avec Azure SQL et les valeurs d’environnement indiquées ; le travail est terminé lorsque les valeurs de date de l’environnement sont correctement résolues pour les paramètres DateTime et que la requête ne renvoie plus l’erreur d’analyse.
Rédigé par le modèle d'indexation à partir du texte de l'issue.
Évaluation
- Stack technique
- azure, csharp, sql
- Domaine
- api, backend, databases
- Type d'issue
- Bug
- Difficulté
- 3/5
- Temps estimé
- 1-2 jours
- Activité
- À l'abandon
- Clarté
- Clairement spécifiée
- Accessibilité débutants
- 42/100