Azure / Azure/data-api-builder
[Bug]: Possible issue with passing date values in env file to stored procedure
- Dominant language
- C#
- Stars
- 1.5k
- Forks
- 370
- Avg merge
- 3d 17h
- Merged PRs (30d)
- 8
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
Contributor guide
Assessment
This issue has not been assessed yet.