Devart Forums

Unidac does not determine table aliases of joined tables, so it generates invalid refresh sql.

select t2.* from table1 t1
left outer join table2 t2 on t1.id=t2.parent_id
where t1.id=1

unidac fids alias for table1 but table2.

(Using Firebird Sql Server 2.5)

Hello,

For such SELECT statement, UniDAC correctly generates the REFRESH statement that refreshes all fields of the table2 table using its primary key. Here is an example:If you want to use another statement for the REFRESH operation, you should use it in the TUniQuery.SQLRefresh property. For more information about the SQLRefresh property, please refer to the UniDAC documentation.

unidac generates refresh sql without table alias for joined tables. here is an example.

UniQuery1.Sql.Text :=
'select t2.* from table1 t1 '+
'left outer join table2 t2 on t1.id=t2.parent_id ';
UniQuery1.UpdatingTable := 'TABLE2';
UniQuery1.KeyFields := 'ID';
UniQuery1.Open;
UniQuery1.Refresh;

unidac generates statement for refresh operation:
select t2.* from table1 t1
left outer join table2 t2 on t1.id=t2.parent_id
where (t1.id=1) and (TABLE2.ID=:OLD_ID)

When exetute this statement Firebird gives that error
Dynamic SQL Error.
SQL error code = -206.
Column unknown.
TABLE2.ID

Correct refresh sql must be:
select t2.* from table1 t1
left outer join table2 t2 on t1.id=t2.parent_id
where (t1.id=1) and (t2.ID=:OLD_ID)

unidac ignores table aliases of joined tables.

Please specify the following:
- the script to create the table1 and table2 tables;
- the exact version of UniDAC. You can learn it from the About sheet of TUniConnection Editor.

UniDac version is 4.5.10
This is the sql script for table1 and table2 below.

CREATE TABLE TABLE1 (
PK INTEGER NOT NULL,
FIELD1 VARCHAR(30),
FIELD2 INTEGER
);

CREATE TABLE TABLE2 (
PK INTEGER NOT NULL,
PARENT_PK INTEGER NOT NULL,
NAME VARCHAR(30)
);

Please note that when the Refresh method is called, a dataset is closed and opened again. In this case, the query that is executed does not change. So, when you open UniQuery1 and when you refresh it, the SQL code is the same: I have created your tables:, used you code:, and there were no problems with refreshing. Please modify this code to demonstrate the problem.