Set Parameter to Null Workaround

[jrodenhi]

The instructions in the Delphi 2006 help say:

Whenever a value is assigned to the TParam object, Bound is automatically set to true. Set Bound to false to undo the setting of a value. The Clear method replaces the value of the parameter with NULL, but does not set Bound to false. However, if the Clear method is used to bind the parameter to a NULL value, Bound must be separately set to true.

Following those instructions, if you want to set an integer variable to Null, you should be able to do something like this:

Code: Select all

    FQuery.ParamByName('hRec').Clear;
    FQuery.ParamByName('hRec').Bound := True;

When I do that using my DevArt DBXpress driver for Interbase, I get an error message that says "No value for parameter 'hRec'." I have been able to get my query to work using this workaround code:

Code: Select all

    FQuery.ParamByName('hRec').AsInteger := 0;
    FQuery.ParamByName('hRec').Clear;

I included this workaround for anyone else it might help but it would be helpful if the code like the Delphi help states.

I really appreciate having your driver just the same. Thanks for all your work.

Jack

[Dimon]

To solve the problem you should set DataType of parameters before opening a query.

Code: Select all

FQuery.ParamByName('hRec').DataType := ftInteger;

[jrodenhi]

Okay. That will work. It does not seem to match the Delphi help for TParam.DataType, though.

Code: Select all

Description 
DataType is set automatically when a value is assigned to the parameter. Do not set DataType for bound fields, as that may cause the assigned value to be misinterpreted.
 
Read DataType to discover the type of data that was assigned to the parameter. Each possible value of DataType corresponds to a type of database field.

What I did works and what you suggested makes more sense than my approach. And this entry in the forum may help someone else out.

Jack

[Dimon]

Code: Select all

DataType is set automatically when a value is assigned to the parameter.

But if you don't assign value, then you should set DataType.

[jrodenhi]

Ok. I see. The Delphi help should really have said to set the datatype. So their suggestion should have been more like:

Code: Select all

    FQuery.ParamByName('hRec').Clear;
    FQuery.ParamByName('hRec').DataType := ftInteger;
    FQuery.ParamByName('hRec').Bound := True;

Thanks for your help and thanks for following up.

Jack