Board index » delphi » How to set Null values using parametrized SQL

How to set Null values using parametrized SQL

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 ?

 

Re:How to set Null values using parametrized SQL


Quote
In article <3266AFB9.4...@trendsoft.nl> Tauvic Ritter <trit...@trendsoft.nl> writes:
>From: Tauvic Ritter <trit...@trendsoft.nl>
>Subject: How to set Null values using parametrized SQL
>Date: Thu, 17 Oct 1996 22:14:17 GMT
>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 ?

have you tried appending a new record, and only assigning values to those
columns that are non- null ie don't do anything you to those you want as null ?

HTH

Boris Ingram, Cyborg Software
boris...@iafrica.com
100076.3...@compuserve.com
http://www.pcb.co.za/users/borising/cyborg.htm

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

Other Threads