Re:How to set Null values using parametrized SQL
In article <3266AFB9.4...@trendsoft.nl>, Tauvic Ritter <trit...@trendsoft.nl>
wrote:
I want to insert or update reccord using parameterized SQL. When I
insert or update a record i can't get Null values into the record. When
I use Query.Params[i].Value := Null or Query.Params[i].asInteger := Null
or Query.Params[i].Clear, i get an error about the BDE not knowing the
parameter type (ftUnknown). When i manualy set the parameter type after
setting the paramater value i get a 0 (zero) value for an integer
instead of a NULL value.
I use Delphi 2.0 and Oracle 7.2. Anyone wo has tackled this problem ?
Presuming you've done something like this:
...SQL.Add('update MyTable set MyField = :Value where ID = :ID')
When assigning to the SQL property of a TQuery, Delphi will parse the text
looking for parameters (here, :Value and :ID) and create parameters in the
Params property. However, it doesn't know what type those parameters should
be, therefore it can't initialize them to be NULL (which afterall is still
typed). The solution then is to first specify the types of those parameters
you wish to set to NULL:
with ParamByName('Value') do
begin
DataType := ftInteger; {Set type to integer}
Clear; {Set value to NULL}
end;
Using the AsInteger, AsString, etc. methods automatically sets the DataType
value, so an explicit assignment is not necessary:
with ParamByName('ID') do
AsInteger := 1234;
Hope this helps.
G.
Gerard M. Averill
Assistant Researcher
Center for Health Systems Research and Analysis
University of Wisconsin, Madison
E-mail: gaver...@chsra.wisc.edu
Phone: (608) 263-1331
