D-XE3, 2.15 Build 3

I have a table that contains multiple occurrences of a client name, plus data unique to each record. I have a common ClientID that I wish to apply to all of the records where the LastName, GivenName and DateOfBirth are the same, using a Stored Procedure. I create the SP like so...

function RunSPMatchClient(const IClient: integer; ADataset: TDataset): boolean;
var aProc: TEDBStoredProc;
begin
Result := False;
aProc  := TEDBStoredProc.Create(nil);
try
 InitialiseProc('SPMatchClient', aProc); // sets the DatabaseName, Session, SP name and Prepares
 aProc.Params[0].AsInteger := IClient;
 aProc.Params[1].Value     := ADataset.FieldByName('LastName').Value;
 aProc.Params[2].Value     := ADataset.FieldByName('GivenName').Value;
 aProc.Params[3].Value     := ADataset.FieldByName('MidName').Value;
 aProc.Params[4].Value     := ADataset.FieldByName('DoB').Value;
 try
  aProc.ExecProc;
 except
  raise;
  Exit;
  end;
 Result := aProc.ParamByName('AResult').AsBoolean;
finally
 aProc.Free;
end;
end;

The SP is...

CREATE PROCEDURE "SPMatchClient" (IN "ClientID" INTEGER, IN "LastName" VARCHAR,
IN "GivenName" VARCHAR, IN "MidName" VARCHAR, IN "DateOfBirth" DATE,
OUT "AResult" BOOLEAN)
BEGIN
SET AResult = False;
START TRANSACTION ON TABLES 'InboundText';
BEGIN
 EXECUTE IMMEDIATE 'UPDATE "InboundText" SET "ClientID" = ? WHERE ("LastName" = ?)
 AND ("GivenName" = ?) AND ("MidName" = ?) AND ("DateOfBirth" = ?)'
 USING ClientID, LastName, GivenName, MidName, DateOfBirth;
COMMIT;
 SET AResult = True;
EXCEPTION
 ROLLBACK;
END;
END
VERSION 1.00!

...which works just fine until the InboundText table (in the ADataset) has a NULL field, wherein no records are found and no updates are made.
The example I am working on has a NULL in the MidName field, and I would like to find out how I use COALESCE to ensure that those records are updated. The problem is that the parameter that comes from the ADataset object is NULL, but I cannot see how to ensure the records are located and updated. The data looks like this:

LastName   GivenName   MidName   DateOfBirth
Smith      Rodney      NULL   1952-10-14 <<< records with NULL aren't updated
Smith      Rodney      NULL   1952-10-14 <<< records with NULL aren't updated
Smith      Rodney      NULL   1952-10-14 <<< records with NULL aren't updated
Harrison   Gloria      Mabel   1942-12-24   <<< records like this are Ok

I can solve it by testing each param's relevant ADataset.Field for IsNull, but there must be a better way. I think.

Is there such a way?

Regards & TIA

Peter

Peter


Interesting one. I can think of three ways that might work. Here they are, all untested

1. Set StandardNullBehaviour to False in the engine - from the manual

By default, Elevate exhibits strict ANSI-standard NULL behaviors, as documented in the NULLs topic in
the SQL manual. By setting the TEDBEngine StandardNulLBehavior property to False you can enable a
relaxed NULL behavior for SQL comparisons and other binary operations. It is important to realize that
this property does not affect the storage of column values, and only affects how such comparisons and
binary operations are made with respect to NULL values.

2. Use a branch in SPMatchClient

IF MidName IS NULL THEN
 EXECUTE IMMEDIATE 'UPDATE "InboundText" SET "ClientID" = ? WHERE ("LastName" = ?)
 AND ("GivenName" = ?) AND ("MidName" = ?) AND ("DateOfBirth" = ?)'
 USING ClientID, LastName, GivenName, MidName, DateOfBirth;
ELSE
as above but without the MidName test
END;

3. COALESCE column and parameter

 EXECUTE IMMEDIATE 'UPDATE "InboundText" SET "ClientID" = ? WHERE ("LastName" = ?)
AND ("GivenName" = ?) AND (COALESCE("MidName",'...') = COALESCE(?,'...')) AND ("DateOfBirth" = ?)'
USING ClientID, LastName, GivenName, MidName, DateOfBirth;

Personally I'd go for number 2

Roy Lambert

How do you handle a name like "Cher"? (Has only last name)

Another alternative is to have LastName, GivenName, MidName columns defined as Not Null and in a Before Update/Insert trigger, change any NULL value to '' for these columns. (I would also trim the blanks from the names as well).

Then the parameterized queries will work because it is searching for '' instead of NULL.

Barry

Silly me, I wasn't comparing apples with apples; I used COALESCE on one side of the comparison, but not the other. I followed Roy's suggestion #3 and used COALESCE on the column and the parameter and all is well.

The data comes from an externally supplied ASCII file and I have no guarantees that all the fields will be populated, so Barry's suggestion is very pertinent. I handled the date with:
(COALESCE("DateOfBirth", DATE ''1899-12-30'') = COALESCE(?, DATE ''1899-12-30''))

The SP is now twice the size, but an external app will be able to call it without knowledge of special IsNull tests that I would do in Delphi to produce the same result.

Thanks people, I appreciate your help.

Regards

Peter