Azure / Azure/data-api-builder

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

Open
#2,067 0 comments 0 reactions 0 assignees View on GitHub
bug good first issue triage
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.