ASA 9 - how to store NULL into a field via dbExpress? - 11-23-2004 , 05:05 AM
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
SQLQuery.Params.ParamValues['FieldName'] := Null;
does not work.
SQLQuery.Params.ParamByName('FieldName').Bound := False;
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
Re: ASA 9 - how to store NULL into a field via dbExpress? - 11-23-2004 , 05:43 AM
SQLQuery.SQL.Add('UPDATE table SET');
then SQLQuery.SQL.Add('field = NULL')
SQLQuery.SQL.Add('field = aram');
SQLQuery.Params.ParamValues['param'] := value
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...
Re: ASA 9 - how to store NULL into a field via dbExpress? - 11-23-2004 , 08:44 AM
Mario *LigH* Rohkrämer wrote:
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
In the ASA 9.x online docs, see:
ASA Programming Guide
Embedded SQL Programming
Using host variables
- Indicator variables
http://tinyurl.com/4veky [shortcut to docs on ianywhere.com]
Hope this helps,
Consultant, Solution Services, iAnywhere Solutions
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
Re: ASA 9 - how to store NULL into a field via dbExpress? - 11-23-2004 , 10:11 AM
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.
Re: ASA 9 - how to store NULL into a field via dbExpress? - 11-23-2004 , 10:20 AM
I just got a reply from Wayne Niddery [TeamB] in
SQLQuery.Params.ParamByName('FieldName').Bound := True;
That means: "Field 'FieldName' was properly set (Bound := True) - to an
empty value (Clear)."