Dear community,

I'd like to use an SQLQuery component in Delphi 7.1 to modify datasets in a
Sybase ASA 9 database. Depending on several facts, I generate an appropriate
SQL string, and add parameters to the Params list.

When inserting new datasets (rows), I know that the best way to leave a
field as NULL is to omit its field name and value in the field lists of the
INSERT SQL statement.

Under some circumstances, I would like to set a field in a dataset to NULL,
where it previously contained specific values; what would you recomment in
this case?

SQLQuery.Params.ParamValues['FieldName'] := Null;

does not work.

SQLQuery.Params.ParamByName('FieldName').Bound := False;
SQLQuery.Params.ParamByName('FieldName').Clear;

Does not work as well (error message: "Missing value for field ...").

Does anyone have experience in such cases, can anyone recommend me a
reliable way to store NULL into a field?

My next try would be to write "NULL" into the values list of the UPDATE
statement...

---
SQLQuery.SQL.Add('UPDATE table SET');
if clearfield
then SQLQuery.SQL.Add('field = NULL')
else begin
SQLQuery.SQL.Add('field = aram');
SQLQuery.Params.ParamValues['param'] := value
end;
---

This obviously worked; still, I would be more satisfied if I could define a
parameter as NULL, so that dbExpress could submit a NULL parameter, instead
of patching the SQL statement text...

Mario *LigH* Rohkrämer wrote:
I don't know dbExpress at all, but in ESQL you use "indicator variables"
along with host variables to indicate whether a parameter is NULL or not:

EXEC SQL INSERT INTO foo VALUES ( aram:my_indicator );

If the value of the "my_indicator" variable is 0, then the value of
"param" is inserted. If the value of "my_indicator" is -1, then NULL is
inserted.

In the ASA 9.x online docs, see:

ASA Programming Guide
Embedded SQL Programming
Using host variables
- Indicator variables

or

http://tinyurl.com/4veky [shortcut to docs on ianywhere.com]

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Hi Greg,

Hi Greg,

unfortunately, Delphi does not support "Embedded SQL" (like some C compilers
probably would, as far as I once read). In Delphi, you have to use component
classes with properties and methods to build an SQL statement, to send it to
the database, to receive the result (datasets, or count of involved
datasets), and handle field values and properties in result datasets.

I just got a reply from Wayne Niddery [TeamB] in
'borland.public.delphi.database.dbexpress':

SQLQuery.Params.ParamByName('FieldName').Bound := True;
SQLQuery.Params.ParamByName('FieldName').Clear;

That means: "Field 'FieldName' was properly set (Bound := True) - to an
empty value (Clear)."