DataAbstract now giving me ambiguous column and invalid column name exceptions

Data Abstract
1

I have an application that hasn’t been touched in two years. My former employer asked me to figure out why suddenly the data-abstract based server has started taking SQL that used to work, and not working anymore with it.

The original SQL that didn’t work contained a left join, I rewrote it without the left join, and used an inner-select instead. Now instead of an “ambiguous column name” error coming back from ADO, I am getting EDAException message “invalid column name ‘event_desc’”.

It seems to me that Data Abstract will not allow me to do things like:

select T2.Field1,
(select B from Table2 T where T.ID=TableA.ForeignKey1) AValue
from Table2 T2
WHERE {WHERE}

Note the use of a {WHERE} expression.

Even hacks like adding a SELECT T.* ( ORIGINAL_QUERY_HERE) T are not helping me.

What seems to happen is that the AValue or as AValue syntax is NOT respected or understood. I’m using an SQL Server Database in this case, and this worked fine two years ago.

This is DataAbstract 7.0.71, with a Delphi server side, and several different clients.

Here’s my whole query:

---- Delivery And Pickup Freight Codes V2015.1
declare @Del TABLE
( ID int,
freightDesc varchar(255)
)
insert @Del select ID,freightDesc from tblFreight
insert into @Del (ID,freightDesc) VALUES (-2,‘We Deliver’)
insert into @Del (ID,freightDesc) VALUES (-1,‘Customer Freight’)

---- Get BarcodeBookings V2015.1
SELECT T.* FROM (
SELECT B.ID,
B.booking_no,
B.showName,
B.dDate,
B.rDate,
B.TOutDate,
B.TInDate,
C.OrganisationV6,
A.VenueName,
B.VenueRoom,
B.status,
CASE
WHEN B.status=0 THEN 'Booked’
WHEN B.status=1 THEN 'Checked Out’
WHEN B.status=2 THEN 'Returned’
WHEN B.status=3 THEN 'Closed’
WHEN B.status=4 THEN 'Pull’
END status_str,
B.BookingProgressStatus,
B.booking_type_v32,
B.order_no,
B.delivery_address_exist,
B.event_code,
(select event_desc from dbo.tblEvent P where P.event_code = B.event_code) as event_desc,
B.delivery_time,
B.pickup_time,
B.del_time_h,
B.del_time_m,
B.ret_time_h,
B.ret_time_m,
B.From_locn,
(select locn_name from tblLocnlist where locn_number=B.from_locn) as From_locn_name,
B.return_to_locn,
(select locn_name from tblLocnlist where locn_number=B.return_to_locn) as Return_to_locn_name,
B.Trans_to_locn,
A.Address1,
A.Address2,
A.City,
A.State,
A.Country,
A.ZipCode AS PostCode,
B.ProjectManager,
B.ConDate,
B.bBookingIsComplete,
B.Division,
B.delivery_viav71,
B.pickup_viav71,
ISNULL(FD.freightDesc,'TYPE '+CAST(delivery_viav71 as char)) as Delivery_via_desc,
ISNULL(FP.freightDesc,'TYPE '+CAST(pickup_viav71 as char)) as Pickup_via_desc
FROM dbo.tblbookings AS B
LEFT JOIN dbo.tblVenues AS A ON B.VenueID = A.ID
INNER JOIN dbo.tblCust AS C ON B.CustID = C.ID
LEFT JOIN @Del FD on FD.ID=B.delivery_viav71
LEFT JOIN @Del FP on FP.ID=B.pickup_viav71
WHERE {WHERE}
) T
ORDER BY T.dDate

2

Okay, an UNACCEPTABLE workaround is to change the SQL above to move the {WHERE} expression OUTSIDE of the inner query, ie:

SELECT T.* FROM ( SELECT B.ID, … ) T WHERE {WHERE}

Suddenly my fields are being FOUND where Data Abstract doesn’t find them before.

Here is what really burns my biscuits:

  1. The DA Schema tool has a Verify button. The SQL is FINE when it runs inside the DA Schema Tool. The SQL is Also Fine when I run it in SQL Management studio.

  2. The failure occurs ONLY at runtime, in the Delphi DA server.

3

You may receive ambiguous column name exception if dynamic where expression contains only field names and doesn’t table names.

For validating generated SQL after Dynamic Where expression you can use OnBeforeOpen event:

procedure TDataService.MyBeforeOpen(const Sender: IDADataset);
var
  sql: string;
begin
  sql := (Sender as IDAServerDataset).SQL;
  Log(sql);  //<<<<<<<<<<<<<
end;

procedure TDataService.DataAbstractServiceBeforeGetDatasetData(aSender: TObject;
  const aDataset: IDADataset; const aIncludeSchema: Boolean;
  const aMaxRecords: Integer);
begin
  aDataset.OnBeforeOpen := MyBeforeOpen;
end;

Also in some cases you need to enable AllowAllFieldsInDynamicWhere. Without this option, an exception like given field can’t be used inside the where clause can be raised.

in DASM, empty DynamicWhere expression is used, so it just used “1 = 1” as {WHERE}

1 Like
4

That works great. Thanks