Empty String vs. Null for Oracle using UniDAC

[FarshadV]

I have an application which can run against Oracle, MS SQL or mySQL. I am using uniDAC to populate tables and all is working fine Except when I get to Oracle.

Some of the fields which I have to fill are Oracle type "NVARCHAR2" and in some cases the value is an empty string, which Oracle converts to NULL.

The problems is when I do a query against the same data and pass an empty string to one of my Parameters, it is not converted to NULL and a match is not found.

Is there a workaround for this problem or do I need to compensate for Oracle and such empty strings within my code?


Thanks,

Farshad R. Vossoughi

[tobias_cd]

Hi,
you should make sure, that these columns you use in WHERE clause always get filled within your INSERT statements (if your application actually inserts data itself), even with an empty string in order to avoid the NULL value.

This behavior is the same with Oracle as well as MS SQL Server and depends on your column definitions within the original CREATE TABLE:
columns can be defined as "NOT NULL" to never have a NULL value, but as mentioned before, INSERT statements then always have to explicitely reference this column, even when empty.

In the WHERE clause you probably should replace any parameter, which you may want to be "empty", with an IF statement similar to this:
if edit1.text = ''
then sSQL := sSQL + 'AND COLUMN1 IS NULL'
else sSQL := sSQL + 'AND COLUMN1 = ' + QuotedString(edit1.text);

However, it depends on your application logic: if it is the case that you have no value for e.g. "column1", does it need to be used at all?
Above example would then be:
if edit1.text ''
then sSQL := sSQL + 'AND COLUMN1 = ' + QuotedString(edit1.text);

A different approach is to use "nvl" for Oracle or "isnull" for MS SQL Server by use of UniDAC macros or textual replacement.
The WHERE clause for column1 could read like:
"WHERE ... AND nvl(column,'') = :a"
For above you then would need to replace nvl with isnull for SQL Server (and obviously take care of the quotes).

Regards,
Tobias

P.S.: for already existing data you could use UPDATE the columns like UPDATE mytable SET column1 = '' WHERE column1 IS NULL

[FarshadV]

Thanks for the information. However, I changed the CREATE TABLE statement and set my columns to NOT NULL, now I can't add any data to the tables as within each row, there are one or more fields which will have "Empty String" and although they are passed as empty strings within the INSERT statement, it looks like Oracle converts the empty string to NULL which now raises an exception which does not allow the value of the field to be NULL.

The problem is: How do I get Oracle to stop converting empty strings to NULL?

Thanks,

Farshad R. Vossoughi

[bork]

Hello

You cannot forbid Oracle to convert empty string to NULL. It is an Oracle feature and cannot be turned off. I recommend you to create a simple function like this:

Code: Select all

function StringToDBString(Value: string): Variant;
begin
  if Value = '' then
    Result := Null
  else
    Result := Value;
end;

And use this function for inserting strings to any database (Oracle, MS SQL, MySQL, etc.). Also you can use same function to fill string parameters. In this case same string value will be inserted to any database and string parameters will be filled correctly.

[FarshadV]

Hi,

Thank you very much for your suggestion.

Delphi 2007, would not accept a function as you had shown, so I created the following:

Code: Select all

Procedure SetQueryParamAsString(aQuery: TUniQuery; aParamName: String; aValue: String);
Begin
  If(aValue  '') Then
  Begin
    aQuery.ParamByName(aParamName).AsString:= aValue;
  End Else aQuery.ParamByName(aParamName).Clear;
End;

Which works great when used for insert or update, the problem is when I have to do a select statement and am using a string Parameter? If I use this procedure to set the parameter's value to NULL it does not find any records. It works if I change my query to use "IS NULL" which defeats the purpose of using Parameters on a query.

Thanks,

Farshad R. Vossoughi

[tobias_cd]

Hi Farshad,
unless your application can assure to never have NULL values in your searchable columns, you should not use a parameter for it.
This obviously requires more code (IF-conditions), but would be versatile for most (if not all) circumstances and puts your application in control.
For my customer I have a similar approach as mentioned earlier by using the "nvl()" function for Oracle, if required using textual replacement if the actual connection is MS SQL Server (replacing "nvl(" with "isnull("). I haven yet tried to use Macros's for it, which seems to be a convenient way, too.
Regards,
Tobias

[bork]

Hello

You can use the following ways to solve the issue.

The first way. You can change you query like this:

Code: Select all

select *
from test_table
where  (:name is null and name is null) or (name = :name)

In this case you can define :name parameter as null or as string and query will return the correct value.

The second way. You can write function for generating the WHERE clause or set Macro inside the WHERE clause:

Code: Select all

function GetStringCondition(FieldName: string; Value: variant): string;
begin
  if (Value = null) or (Value = '') then
    Result := FieldName + 'IS NULL'
  else
    Result := FieldName  + '=''' + Value + '''';
end;

When ODAC generates an Update query automatically it uses similar function.

[FarshadV]

Thank you very much. I will implement the first suggesting using Parameters as I use parameters all over my code which was developed for mySQL and now has to run with Oracle and MS SQL as well.