Empty strings as NULL

[DepSoft]

Hi,

Using D2010, UniDAC 3.00.0.10, SQL Server 2008 & PostgreSQL

To set empty string parameters to NULL I use modified SQLServerUniProvider & PostgreSQLUniProvider in which I set:

Code: Select all

TOLEDBREcordSet(Obj).SetProp(prEnableEmptyStrings, False)

However, this doesn't apply to calling a TUniSQL.Execute. I can see that I might need to modify the TCustomDASQL.AssignParamValue procedure to accomplish empty strings being written as NULL to the DB.

Would this be the best/safest place to change this?

I think it would be useful if there was an 'EnableEmptyStrings' property available at the connection level so that whether you use a UniTable, UniQuery, UniSQL or UniConnection.ExecSQL it would write any empty string parameters as NULLs.

Regards, Paul.

[bork]

Hello

If you purchase UniDAC with source code, then you can change the TCustomDASQL.AssignParamValue method for converting empty strings to Null. You should add the following code:

Code: Select all

  if ((ParamDesc.GetDataType = dtString) or (ParamDesc.GetDataType = dtWideString)) and
     ((TVarData(Value).VType = varString) or (TVarData(Value).VType = varOleStr)) and
     (Value = '')
  then
     Value := Null;

before lines:

Code: Select all

  ParamDesc.SetValue(Unassigned);
  ParamDesc.SetValue(Value);

This code modification allows to convert empty strings to Null for all parameters in all components: UniTable, UniQuery, UniSQL, and UniConnection.ExecSQL

But after this modification you can have some troubles with records that were stored with empty strings before. For example the following query can be executed incorrectly:

Code: Select all

select * from test_table where name = :Name

if the Name parameter is set to an empty string (the modified AssignParamValue method converts it to Null). So I recommend to create a new function to fill string parameters:

Code: Select all

function EmptyStringToNull(Value: Variant): Variant;
begin
  if ((TVarData(Value).VType = varString) or
      (TVarData(Value).VType = varOleStr)) and
     (Value = '')
  then
    Result := Null
  else
    Result := Value;
end;

And use it:

Code: Select all

  UniQuery.ParamByName('Name').Value := EmptyStringToNull('');

[DepSoft]

Hello

If you purchase UniDAC with source code, then you can change the TCustomDASQL.AssignParamValue method for converting empty strings to Null. You should add the following code:

Code: Select all

  if ((ParamDesc.GetDataType = dtString) or (ParamDesc.GetDataType = dtWideString)) and
     ((TVarData(Value).VType = varString) or (TVarData(Value).VType = varOleStr)) and
     (Value = '')
  then
     Value := Null;

before lines:

Code: Select all

  ParamDesc.SetValue(Unassigned);
  ParamDesc.SetValue(Value);

Thanks bork, yes I have the source code version. I almost had the same code but your version is a little safer

But after this modification you can have some troubles with records that were stored with empty strings before. For example the following query can be executed incorrectly:

Code: Select all

select * from test_table where name = :Name

if the Name parameter is set to an empty string (the modified AssignParamValue method converts it to Null). So I recommend to create a new function to fill string parameters:

Code: Select all

function EmptyStringToNull(Value: Variant): Variant;
begin
  if ((TVarData(Value).VType = varString) or
      (TVarData(Value).VType = varOleStr)) and
     (Value = '')
  then
    Result := Null
  else
    Result := Value;
end;

And use it:

Code: Select all

  UniQuery.ParamByName('Name').Value := EmptyStringToNull('');

Thank you again. Existing data currently does not contain empty strings (all empty strings are nulls from a BDE data conversion) so we only query for IS NULL or IS NOT NULL rather than checking for empty strings.

Regards, Paul.

[bork]

If you will decide to modify the TCustomDASQL.AssignParamValue method then you should keep in mind that standard SQL query without parameters can insert empty strings, for example:

Code: Select all

  insert into my_table (id, name) values (1, '')

If you modify the TCustomDASQL.AssignParamValue method and empty string will be inserted then you can get unpredictable behavior of your application. This is the reason why we haven't implemented this option to DAC components.

[DepSoft]

Hi bork,

Thanks for the tip.

I realize it's a little complicated to cover every possibility, I had hoped it might be possible to implement the idea in my original post but it seems there isn't much interest from other users - or they don't vote in polls

The situation is more to do with our existing applications and utilities being BDE/Paradox based where empty strings are 'automatically' written as NULLs so it seems easier to stay with that.

Are queries such as

Code: Select all

insert into my_table (id, name) values (1, '') 

sent 'as is' to the DB without any further manipulation by UniDAC code?

I am in the habit of always using parameterized queries anyway so it should be OK.

Thanks

Regards, Paul.

[bork]

Yes, the query:
insert into my_table (id, name) values (1, '')
is executed directly into the database without any modifications or manipulation in UniDAC.

[bork]

Hello

We are investigating possibility of adding new feature for replacing empty strings to NULL on set fields and parameters values. We can add managing this behavior to connection options or as global variable. But managing this behavior in the connection options has one limitation: you cannot change behavior of a dataset that doesn't have connection (the connection property is empty). The global variable also has limitation: you cannot change behavior in the design-time. We want to know your opinion: where it will be easy to use - when you can change behavior in the connection options or in the global variable.

[DepSoft]

Hi bork,

My view, given what you say, is that the global variable option would be most suitable in my case. I would set this once in some initialization section and that would be it.

The design time limitation of this method is not a problem for me since I'm not dealing with existing empty strings in the DB. It may be problematic for other users though if there is a possibility of mixed empty strings and NULLs

I can see that you're trying to handle both updates and reads of the DB but I wonder if you can simplify it by having it affect only updates to the DB. i.e. Once you start writing NULL instead of empty string to your DB then you will have no need to convert empty strings to NULL when you select data - there shouldn't be any empty strings there to worry about - only NULLs.

My current (UniDAC) source code changes do not deal with data reads, only updates and it's not causing any problems.

For someone who wanted to start working this way with an existing DB, I think they would have to tidy up existing data by setting empty strings to NULL first. However, I'm not sure they would think the effort involved was worth it to change.

I think the feature is only really going to benefit people migrating older stuff (BDE/Paradox/etc) to a new server DB.

Regards, Paul.
[/quote]

[bork]

Hi

Thank you for the information. We will take into account your suggestions.