Handling of parameter values (and types) in AddParam

[mikebeaton]

Dear Frans,

Hi, I have just been looking at the AddParam code across the 4 supported databases.

Partly just so that I could make sure I understand it clearly, even if I want to refactor it just for my own version of Massive.

But not only! I'd be happy to contribute more substantially to this project, if I can convince you that I know what I'm doing! I am an experienced programmer, and I have worked on several in-house light-weight ORMs - I know it's not the same scale as Massive, but it means I have some idea of the issues, and what makes Massive impressive, and useful.

Anyway. Each AddParam method is short. SqlServer and Sqlite are identical. Oracle and PostgreSQL are identical.

Although the layout between the two distinct versions is slightly different, the operation is identical, except for one additonal feature in the Oracle/PostgreSQL version, as follows:

1. If value is null, set the parameter value to DBNull.Value
2. If value is an ExpandoObject, set the value of the parameter to the value of the first item in the expando, or null if there are no items (using FirstOrDefault())
3. If value is a string, set the value of the parameter to the value, and perform one additional check: if the size of the string is > 4,000, set the parameter size to -1 (implies MAX), otherwise set the size to 4,000
4. For Oracle and PostgreSQL only, if the value is a Guid, set the parameter value to the string version of the value, set the parameter DbType to String and set the parameter Size to 36.
5. For all other values, just set the parameter value to the value.

There is one additional, very important thing going on here (applies to every case except rule 4): the DbParameter type is being set, implicitly, by .NET, based on these rules https://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx (which, of note, vary between databases).

I'm putting all this up because I would like to ask, if I may, do you agree with this? Have I missed anything?

And also, I would like to ask (apologies if I am misunderstanding something, here...), doesn't the second of the five steps above embody an error? Wouldn't it fit the purpose of the code (as I currently understand it) better to extract FirstOrDefault from the ExpandoObject, if it is an ExpandoObject, first of all, before everything else, and then apply all the other rules, including the null check, to the result of that?

[FransBouma]

I'm putting all this up because I would like to ask, if I may, do you agree with this? Have I missed anything?

why do you need confirmation for this? It's what the code says it does.

And also, I would like to ask (apologies if I am misunderstanding something, here...), doesn't the second of the five steps above embody an error? Wouldn't it fit the purpose of the code (as I currently understand it) better to extract FirstOrDefault from the ExpandoObject, if it is an ExpandoObject, first of all, before everything else, and then apply all the other rules, including the null check, to the result of that?

You mean, if the value obtained from the expando is null, it should be converted to DBNull.Value? that's a good point. It should, indeed. the value as expando is btw a hack I don't really like but it was in the original code so I kept it in. It would mean one passed an expando with values which are again expandos. Not really sure that is a solid basis for software.

[mikebeaton]

The use of .NET default parameter typing seems pretty fundamental, but pretty implicit. And on the Expando stuff, I wasn't sure if I was missing a trick.

Anyway, if the value from the expando is null, then it should be converted to DBNull.Value, yes, but also the rest: if it is a string, it should have its size set, if it is a Guid it should have its type and size set (in the DB's which need it), yes.

More generally - to refactor the code correctly, and also fix this bug (if it is a bug, sounds like it is) - the Expando expansion should be done first, in shared code, then passed to a much smaller DB specific part, which sets the value and does the DB specific overrides or additions to the .NET default typing, in the few cases where it is needed.

I wasn't clear why taking the value from an Expando would ever be needed, either. I was wondering if I was missing a clever calling scenario that I hadn't thought of, e.g. from elsewhere within Massive. Or perhaps based on using objects previously generated by Massive? The only other reason I could think of - a big guess, and it does seem a bit of a strange reason if it's right - is that maybe Rob Conery thought that it was weird to provide a library which is largely based around Expandos, but then refuses to accept them in this context? But if that's all it is, it seems more likely to be a mistake by the user than anything else if an Expando arrives as the value to this method.

[FransBouma]

but also the rest: if it is a string, it should have its size set, if it is a Guid it should have its type and size set (in the DB's which need it), yes.

DbParameter.Value will do that already, so there's no need to do that. (it will set the DbType value under the hood.

I kept the expando type handling as it wouldn't break anything.

[mikebeaton]

DbParameter.Value will do that already, so there's no need to do that. (it will set the DbType value under the hood.

Misunderstanding between us, I think? I mean that, in addition to setting the .Value, we need to do the database specific .Type and .Size overrides (i.e. the code which is specific for string (all dbs) and for Guid (two dbs)) just as much if the value came from inside an Expando as if it didn't. (According to what the current code says it will do ;) this will currently NOT be done for strings or Guid's which have been extracted from an Expando, but I think that is effectively a bug.)

[mikebeaton]

Looking at the code even more carefully, in Oracle and PostreSQL the needed extra Guid handling will NOT be done (but the needed extra the string handling will be done correctly), if the value comes from inside an Expando. In SqlServer and Sqlite, there is no special Guid handling needed (for reasons which look reasonably clear on looking at the .NET table), but the needed string handling is NOT done, even though it should be, if the value comes from inside an Expando, in these two dbs. The bugs are slightly different between the two versions.

So it turns out my summary of what the code does isn't what the code says it does after all ;) since my summary incorrectly summarises the precise pattern of these bugs.

(Update - I was right the first time. For even more subtle reasons the string support will not operate for strings from inside an Expando, even in the two DBs where the code to check whether to run it is reached for objects from inside an Expando - because that code then effectively checks whether an ExpandoObject is a string, and it isn't!)

[FransBouma]

If you mean, the current code doesn't do type conversion for values extracted from an expando if the type of that value is a guid for Oracle/PostgreSQL and doesn't do length conversion for strings for all DBs, then you're correct, that is a small glitch. I doubt anyone will run into it though, as it requires having an expando as the field type inside an expando.

I was referring to DbParameter.Value: setting that to a value also sets the type. In some cases this isn't the right one and it needs correction (same goes for length/precision/scale btw). For strings this is particular awkward as it produces string parameters with lengths as long as the value, if you're not careful, which will ruin your execution plan cache (hence the hardcoded length of 4000 so unicoded strings will fit too. Not ideal, but it's all there is with limited type info).

[mikebeaton]

Yes, I see this. I was referring to the small glitch(es). It seems if anyone was using Expando support inside AddParam they undoubtedly would run into this. If no one is using it, perhaps it should just be removed?

I suggest the glitches need fixing (or the Expando support needs removing), and anyway the code needs refactoring so that the shared part is shared, and only the databases specific corrections are not shared. (Of course this would need doing anyway, if parameter name and direction support were ever to be added to AddParam, or equally if anyone ever wanted to update the database specific type corrections.)

I am more than happy to do this - or to let you do it at some point, and just be the one filing the bug report.

[mikebeaton]

I also do understand that the parameter typing support in Massive is lightweight. (And I understand that it works the way you said above, that's also part of what I summarised in my first post in this issue.) And the lightweight typing support looks suspiciously weak, if one looks into it. But it works so well, in so many cases (with such lightweight code in Massive and, especially, with such lightweight calling code required from the user of Massive) that I think this really should be seen as a strength not a weakness. Even if one added explicit typing support somehow, in future, to Massive I think it would be really, really useful and important to continue to carefully support implicit typing as well as possible, in as many cases as possible. (And I'm still not convinced that this could not be done, in a similarly lightweight, highly useful way, even for output and return values from stored procedures. And convinced that it should be done, if it can be! I am running stored procedures, doing it this way, in a live project, on SQL Server, and it works, and it's like Massive, and it's much, much more lightweight and pleasing to use than it would be to have to create objects containing the type and the value. I do intend to try to refactor this support to the other DBs, at least for myself, and report back.)

[FransBouma]

The expando usage here is actually bad: it does a FirstOrDefault on the values, but as it's a dictionary, there's no ordering. So it's actually random which value is picked! I doubt this has ever worked for anyone.

So this doesn't need to be there, it can be removed. (but as it's not really harming anyone but the poor sole who decided to use it, again I have no evidence there is such a person, it's not really high priority)

I don't think AddParam needs much refactoring, as you can't avoid dealing with the values anyway: so you have to have checks on the type and deal with them on a per-database bases. The problem with trying to cram this small method into Shared and the DB specific portions in the files per database, you'll end up with fragmented code which combined is actually bigger, but additionally, if you need to add further specific handling of a type for a specific DB you very likely have to refactor it again.

There's currently no problem, the code works fine, the method is very small, so I don't see why work has to be done on this: the end result won't be much better than what's there now.

[mikebeaton]

No, it won't be much better than it is now if just this is refactored, I agree.

But we have just identified a bug which no one had reported before, right? Which it turns out doesn't matter, because it is in some code which (we think) no one is using, and so which can be removed? So that is good, at least.

I want to refactor AddParam (for my own cross-db SP support project updating #284, #281 which I am aware may never make it into Massive proper; also because I want to try refactoring TryInvokeMember in the way I suggested in #287, so that it merely adds name:value support to other methods - which I'm aware may also never make it into Massive proper; both these need a refactored AddParam, with direction and name support, which is actually very easy to do: on an already refactored version, only some tiny, non-breaking changes, exactly as per the SQL Server-only changes to AddParam in #281, would be needed). So it has helped me, certainly, to get agreement from you that I've understood how it works, not missed anything, and got your confirmation that the apparently useless Expando support is useless.