Unidac does not determine table aliases of joined tables

[erayhanoglu]

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)

[AndreyZ]

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:

Code: Select all

SELECT all_fields_of_table2 FROM table2
WHERE ID = 2

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.

[erayhanoglu]

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.

[AndreyZ]

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.

[erayhanoglu]

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)
);

[AndreyZ]

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:

Code: Select all

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

I have created your tables:

Code: Select all

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

CREATE TABLE TABLE2 (
 ID INTEGER NOT NULL,
 PARENT_ID INTEGER NOT NULL,
 NAME VARCHAR(30)
);

, used you code:

Code: Select all

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

, and there were no problems with refreshing. Please modify this code to demonstrate the problem.