Not passing parameters

[maxsnts]

Hi
I know that this is not at all standard, but a colleague of mine once ported your connector and gave it the ability to not pass all the parameters to a Stored procedure.
The connector would add the missing parameters according to the SP signature.
That colleague since left the company.
We would like to update the connector, but we were trying not to reimplement all those changes.

So, here is my question:

Is your feature request related to a problem? Please describe.
Does the connector since 2.0.3 gained this ability, maybe using an optional configuration, or would this be possible?

Describe the solution you'd like
The idea is that when calling stored procedures, the missing parameters would be added according to the SP signature.
That would require parsing and caching the necessary parameters the first time the SP is called.

Describe alternatives you've considered
We have considered reimplementing this in a fork, but that would prevent updating the connector in the future.

Anywho, i know that this is not a standard feature and if possible would not be (and should not be) active by default.
I personally use your Connector as is, but we have a lot of projects that use this fork that i mentioned.

PS: We interacted before on an isseu, not that it would influence your reply to this.
https://stackoverflow.com/questions/47779128/datareader-mysql-connector-dispose

Best Regards
Max

[maxsnts]

As a added note, this feature allows Database Adminstrators to deploy changes to the Procedures without the need to coordinate with the Application people.
If they don't break existing functionality, they can add extra by adding new parameters and then the Application people can deploy whenever they are ready.

Thank you

[bgrainger]

This sounds possibly related to #1175, although it sounds like you're suggesting that MySqlConnector should cache the missing parameters, which is the opposite of that issue. OTOH, if the DB administrator is changing the stored procedure definition, wouldn't that break a client that's cached an earlier version of the stored procedure with fewer parameters?

I know that this is not at all standard, but a colleague of mine once ported your connector and gave it the ability to not pass all the parameters to a Stored procedure.

Was this modified code pushed to a public repo anywhere? I'd like to make sure I'm understanding the desired behaviour, and seeing the code that implements it would help.

[maxsnts]

Sorry, adding the "cache" on the issue may have been a mistake.

The request is only to add missing parameters and pass them as null.
Example:
call SP(param A, param B, param C)

If the app calls that SP like this: SP (param A, param C), the connector itself would add param B as null because the SP requires it.

Sorry, the changes were made by a colleague and he did not push that to any public repo.

Thank you

[bgrainger]

I think DeriveParameters can be used to solve this problem.

For example, consider this sample program:

using var dataSource = new MySqlDataSourceBuilder("server=localhost;user id=root;password=pass;database=bug1289").Build();
using var connection = await dataSource.OpenConnectionAsync();
using var command = connection.CreateCommand();
command.CommandText = """
drop procedure if exists sp1289;
create procedure sp1289(IN p1 varchar(40), IN p2 varchar(40), IN p3 varchar(40), OUT o1 text) 
begin
	select concat(coalesce(p1, '1'), '-', coalesce(p2, '2'), '-', coalesce(p3, '3'))
	into o1;
end
""";
await command.ExecuteNonQueryAsync();

command.CommandText = "sp1289";
command.CommandType = System.Data.CommandType.StoredProcedure;

// this will be changed
command.Parameters.AddWithValue("@p1", "one");
command.Parameters.AddWithValue("@o1", "").Direction = ParameterDirection.Output;

var result = await command.ExecuteNonQueryAsync();
// end this will be changed

This will throw the exception Parameter 'p2' not found in the collection. because not all parameters were supplied.

Instead, run the following code:

// this is the change
MySqlCommandBuilder.DeriveParameters(command);
command.Parameters["@p1"].Value = "one";

var result = await command.ExecuteNonQueryAsync();
Console.WriteLine(command.Parameters["@o1"].Value); // prints one-2-3

Call DeriveParameters(command) before setting up any parameters, and all needed parameters will be created with default values.