How to Determine the Size of MSADO Command Parameters
- by Adam
I am new to MS ADO and trying to understand how to set the size on command parameters as created by the
command.CreateParameter (Name, Type, Direction, Size, Value)
The documentation says the following:
Size Optional. A Long value that
specifies the maximum length for the
parameter value in characters or
bytes.
...
If you specify a variable-length data
type in the Type argument, you must
either pass a Size argument or set the
Size property of the Parameter object
before appending it to the Parameters
collection; otherwise, an error
occurs.
1.) What should one pass for fixed-size parameters? Is it a "don't care"?
I was a bit confused by the example found here, in which they set size to 3 for an adInteger parameter with Value set to a variant of type VT_I2
pPrmByRoyalty->Type = adInteger;
pPrmByRoyalty->Size = 3;
pPrmByRoyalty->Direction = adParamInput;
pPrmByRoyalty->Value = vtroyal;
VT_I2 implies two bytes. A tagVARIANT struct is 16 bytes. How did they land on three? I see that the enum value for adInteger happens to be three, but I suspect that is just a coincidence.
So it's a bit confusing what to pass for fixed-size parameters. The team I'm working with has always passed sizeof(int) for adInteger, and it seems to work. Is that correct?
Now, for "variable-length" parameters: we are instructed by the documentation to pass "the maximum length .. in characters or bytes".
2.) For adVarChar, is it sufficient to pass the max width as defined in the database?
3.) What about the Wide types (e.g. adVarWChar)? Is it characters or bytes?
4.) How about adVariant, which could contain fixed- or variable-length data?
5.) Do arrays ever come into play here? (we don't pass them as parameters, just curious)
Any references or personal insights are welcome.
