SET Empty String To NULL

pickepique · Post by **pickepique** » Fri 04 Sep 2009 15:52

What are the different ways to set Empty TStringField to NULL when updating a MsQuery with SQLServer 2005, i begin to make this in BeforeUpdateExecute method with a SQLUpdate set to a StoredProc

Code: Select all

for i := 0 to Params.Count - 1 do
if (Params[i].DataType = ftString)  and not Sender.FieldByName(Params[i].Name).IsNull and (Length(Sender.FieldByName(Params[i].Name).value) = 0) then
        Params[i].Bound := false;

The varchar column has a default value to NULL and a stored proc has default param to NULL .... and finally it works but THERE is no easier way?

pickepique · Post by **pickepique** » Fri 04 Sep 2009 15:57

with "the simpler" I mean without a stored proc and even vithout a update query

Dimon · Post by **Dimon** » Mon 07 Sep 2009 06:48

I don't understand what stored procedure you use.
You don't need to use any stored procedure and set an update query manually. Use your code only in the BeforeUpdateExecute method.

pickepique · Post by **pickepique** » Mon 07 Sep 2009 12:34

Okay I do not know why I talked about this case when I use a procedure,.
My goal is usually to filter all the empty string on a SQLServer varchar(x)column without NOT NULL constraint, I may be wrong, but it seems many implementation of delphi DB controls leaves no opportunity to set a stringField to NULL. I just want to consider that in some cases an empty string automatically becomes a null string

Dimon · Post by **Dimon** » Tue 08 Sep 2009 08:26

A string field contains a null string unless you change its value. But if you change a value to any string and even to empty string, then use the event handler to set this field value to null.

pickepique · Post by **pickepique** » Tue 08 Sep 2009 12:33

Ok thank you; with the tfield event, You're right!, but not really clean for me. I would have preferred a global setting of client SDAC client or even SQLServer 2005 setting for varchar handling
Did I make my point? my english is not very well...
all the best

Dimon · Post by **Dimon** » Wed 09 Sep 2009 06:34

SDAC does not provide such functionality, therefore use the event handler.