Closed yorek closed 1 week ago
this is related to this: https://github.com/Azure/data-api-builder/issues/989. There is a validation error that is done on the runtimeconfig where we check that number of params on stored proc should match number in runtimeconfig, which is incorrect. We had reported this during our sync ups. I have the fix for this and will check it in soon.
@rohkhann and @abhishekkumams can you check if #1847 does indeed resolve this issue too? if so, please link the pr item and close this.
@rohkhann to confirm whether this is resolved.
We're hitting this issue
@simonsabin, which version of DAB are you using?
Whatever you get with the service. We changed a proc and added optional parameters and the service died.
Simon Sabin
From: Sean Leonard @.> Sent: Tuesday, March 26, 2024 4:50:57 PM To: Azure/data-api-builder @.> Cc: Simon Sabin @.>; Mention @.> Subject: Re: [Azure/data-api-builder] [Bug]: Optional stored procedure parameters generate the error "Missing required procedure parameters" (Issue #1748)
@simonsabinhttps://github.com/simonsabin, which version of DAB are you using?
— Reply to this email directly, view it on GitHubhttps://github.com/Azure/data-api-builder/issues/1748#issuecomment-2020971434, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAJHM22LHX67QFWWZOQ6LLDY2GRPDAVCNFSM6AAAAAA5IE2UZWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAMRQHE3TCNBTGQ. You are receiving this because you were mentioned.Message ID: @.***>
Now, if you have parameters in your stored proc, you dont need to duplicate those parameters on the config file. Thats what this fix handles: https://github.com/Azure/data-api-builder/pull/1847. However, we still dont have the capability to detect an optional parameter if it is not specified in the call to the stored proc.
Thats a massive problem as there is no way to deploy the change without breaking the system. Which ever is deployed first, DAB or DB, the result is DAB fails to run until both are deployed
I have hit this issue today. What is a good work-around for optional parameters?
Hitting a similar issue as well, with a "Missing required procedure parameters" during a call to a stored proc with an optional parameter (not declared in the dab config file) without passing this parameter for this call. Please see #2245
I believe this should be removed https://github.com/Azure/data-api-builder/blob/689c3e7affe5b94d3ea5d7452c49235068e42652/src/Core/Resolvers/Sql%20Query%20Structures/SqlExecuteQueryStructure.cs#L71 or some configuration to remove it. Sure there is value at design time if the configuration hasn't been setup correctly to provide a nicer error, but you will still get an error if you don't specify all the required parameters.
On my end, this is the Exception I'm hitting when trying to call a stored procedure without providing a value for a parameter for which the stored procedure has a default value:
That as well then. I'd do a PR but think this needs a discussion on whether its by default or configurable.
@seantleonard, @rohkhann, @abhishekkumams > may I ask you your thoughts on this one?
@benjiiim, agreed with your and @simonsabin's finding. Working on a fix that incorporate both of your experiences encountering errors.
There isn't a straightforward method to determine whether a stored procedure has a default valued parameter and what that value is without parsing the object definition which is error prone.
Instead, the change I'm making essentially defers error handling to the database. In this case, error 201:
"201", // Procedure or function '%.ls' expects parameter '%.ls', which was not supplied.
SELECT message_id AS Error,
severity AS Severity,
[Event Logged] = CASE is_event_logged
WHEN 0 THEN 'No' ELSE 'Yes'
END,
[text] AS [Description]
FROM sys.messages
WHERE language_id = 1033
and message_id = 201
Consequently, DAB will not fail startup or terminate a request when DAB doesn't detect an optional parameter in the request body when DAB runtime config doesn't define a default value for that optional parameter.
What do you think?
Sounds great.
I think there is a case that needs to be dealt with and that’s a parameter that’s defined in the config that’s not supplied and no default.
Ps would personally love to know how to code this change and the associated tests. Would you be interested in working on it with together ?
Simon Sabin
From: Sean Leonard @.> Sent: Thursday, July 25, 2024 8:01:59 PM To: Azure/data-api-builder @.> Cc: Simon Sabin @.>; Mention @.> Subject: Re: [Azure/data-api-builder] [Bug]: Optional stored procedure parameters generate the error "Missing required procedure parameters" (Issue #1748)
@Benjiiimhttps://github.com/Benjiiim, agreed with your and @simonsabinhttps://github.com/simonsabin's finding. Working on a fix that incorporate both of your experiences encountering errors.
There isn't a straightforward method to determine whether a stored procedure has a default valued parameter and what that value is without parsing the object definition which is error prone.
Instead, the change I'm making essentially defers error handling to the database. In this case, error 201:
"201", // Procedure or function '%.ls' expects parameter '%.ls', which was not supplied.
SELECT message_id AS Error, severity AS Severity, [Event Logged] = CASE is_event_logged WHEN 0 THEN 'No' ELSE 'Yes' END, [text] AS [Description] FROM sys.messages WHERE language_id = 1033 and message_id = 201
Consequently, DAB will not fail startup or terminate a request when DAB doesn't detect an optional parameter in the request body when DAB runtime config doesn't define a default value for that optional parameter.
What do you think?
— Reply to this email directly, view it on GitHubhttps://github.com/Azure/data-api-builder/issues/1748#issuecomment-2251207527, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAJHM24ILU5CUEKRCEF57S3ZOFDSPAVCNFSM6AAAAAA5IE2UZWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENJRGIYDONJSG4. You are receiving this because you were mentioned.Message ID: @.***>
Hi Simon,
I have a working branch here: https://github.com/Azure/data-api-builder/tree/dev/sean/sp_opt_1748
I'd be happy to help you work on your suggestion:
I think there is a case that needs to be dealt with and that’s a parameter that’s defined in the config that’s not supplied and no default.
To clarify, when you say parameter defined in the config file, do you mean this? https://learn.microsoft.com/azure/data-api-builder/reference-configuration#parameters
{
"entities": {
"<entity-name>": {
...
"type": "stored-procedure",
"parameters": {
"<parameter-name-1>" : "<default-value>",
"<parameter-name-2>" : "<default-value>",
"<parameter-name-3>" : "<default-value>"
}
}
}
}
a value is required when defining params in the config. Perhaps you encountered a bug where an invalid, empty, or null value is provided for the parameter and DAB isn't handling properly?
It’s just about what gets validated where and what’s controllable and how. Thinking about it, what is validating data types, default values. I as was pointed out there were a number of places in the code base this touched and on my investigation some didn’t have test cases thus the desire to understand how we put tests in place for this stuffs
I see. Do you have a few test cases in mind? That way I can better guide you were those can be added.
What happened?
I have a stored procedure like the following:
for which the
@optionalParam
is, as the name implies, optional.If I configure DAB to use that stored procedure using the following configuration, omitting the
optionalParam
(since it is optional):I get the following error during the startup:
Version
Microsoft.DataApiBuilder 0.8.52+c69925060e1942d28515b9c4b89ea24832da0c7c
What database are you using?
Azure SQL
What hosting model are you using?
Local (including CLI)
Which API approach are you accessing DAB through?
REST, GraphQL
Relevant log output
Code of Conduct