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:

[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:

[DepSoft]

[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:

[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

[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.