Automatic update sql using field aliases

betadevart · Post by **betadevart** » Tue 20 Mar 2012 17:55

Is it possible to use simple automatic updates with UniDAC, when I use fields aliases in the basic select sql?

For example:
select partner.id as partner_id, partner.name as partner_name
where partner.id=:partner_id

I usually generate select statements runtime, and in case of joined tables naming the fields like above seems to be necessary for me.

In the example above, query->ApplyUpdates() does nothing for my evaluation so far. (CachedUpdates=true, hacked AutoCommit=false)
I tried to specify KeyFields with no success so far.

Thanks in advance

AlexP · Post by **AlexP** » Wed 21 Mar 2012 08:50

Hello,

We cannot reproduce the problem. The code below works correctly

Code: Select all

  UniQuery1.SQL.Text := 'SELECT e.empno e_empno, e.ename e_name FROM emp e where e.empno = :e_empno';
  UniQuery1.ParamByName('e_empno').AsInteger := 7934;
  UniQuery1.Open;
  UniQuery1.Edit;
  UniQuery1.FieldByName('e_name').AsString := UniQuery1.FieldByName('e_name').AsString + 'test';
  UniQuery1.Post;
  UniQuery1.ApplyUpdates;

Please specify your UniDAC and IDE versions, the DB name and version. Besides, plese check the SQL query sent to the server when ApplyUpdates is executed (for this, the Debug option must be set to true and the UniDACVcl module must be added to the Uses section)

betadevart · Post by **betadevart** » Wed 21 Mar 2012 09:08

Thanks for the quick response!

That's just another thing I forgot to mention. When I use such a statement like "table.field as table_field" NO update statement goes to the server at all (DbMonitor). ApplyUpdates sends no sql. No error messages.
I tried your syntax, without "as", with no success.
When I degrade the stement like that "select id, name from partner" update sql goes through DbMonitor. From the same form save function of course.

Strange: ApplyUpdates does nothing, but I checked UpdatesPending, it is true and turns to false after ApplyUpdates.

Any idea?

CBuilder XE, UniDac 4.0.1, FB 2.5.1 Win32 on Windows 7 64 bit.

(I cannot send example because I don't work in design time, everything is generated by templates and macros inside datamodel dlls.)

betadevart · Post by **betadevart** » Wed 21 Mar 2012 09:25

I also must admint that I do unusual thing with the dataset.
Set SQL first, than call FieldDefs->Update().
Then create some fields manually, and
create fields from FierldDefs manualy as well.
All of this is before Open().
Everithing is because of generated field binds and so.

Does it have anything with your sql generator?

betadevart · Post by **betadevart** » Wed 21 Mar 2012 09:35

I wrote a small form example, and I found that our example works, but I need to set KeyFields to "partner_id".

Is there something with my manual field creation/fielddef updates!?

I use this function:
void CreateFieldsFromDefs(TDataSet *ds)
{
if (!ds->FieldDefs->Updated)
ds->FieldDefs->Update();
int count = ds->FieldDefs->Count;
for (int ix=0; ix FieldDefs->Items[ix]->CreateField(ds);
}

Since I regenerate the queries I also do things:
dataset->Fields->Clear();
dataset->FieldDefs->Clear();

But the other thing is if I step back to use simple "select id,nev" my program emits the update sql.

?

betadevart · Post by **betadevart** » Wed 21 Mar 2012 10:20

A found something!

The very same query skip ApplyUpdates if I insert my unusual manipulations.

void CreateFieldsFromDefs(TDataSet *ds)
{
if (!ds->FieldDefs->Updated)
ds->FieldDefs->Update();
int count = ds->FieldDefs->Count;
for (int ix=0; ix FieldDefs->Items[ix]->CreateField(ds);
}

void __fastcall TForm1::Button1Click(TObject *Sender)
{
// These 2 lines make the difference! Commenting them out brings update sql back.
qry->FieldDefs->Update();
CreateFieldsFromDefs(qry);

qry->Active = true;
}

Can you give me some guide about that?

betadevart · Post by **betadevart** » Wed 21 Mar 2012 10:43

Even worse!
No need to do "things", it is enough to create fields design-time.
If I add all fields on test form design-time - without my CreateFieldsFromDefs - ApplyUpdates does nothing.

betadevart · Post by **betadevart** » Wed 21 Mar 2012 10:56

My "final" diagnosis:
If (you use column aliases AND (create fields manually OR design-time in advance))
then
ApplyUpdates sends no data to the server.

Probably it misses the changed fields?

Please let me know if there is something to do about it!

AlexP · Post by **AlexP** » Wed 21 Mar 2012 14:28

Hello,

Thank you for the information.
We have reproduced the problem.
We will try to fix it in the nearest product version.

betadevart · Post by **betadevart** » Wed 21 Mar 2012 14:38

Thank you.
Just one more question.
Using joins and renaming columns seems very basic requirement for me. Just as creating fields at design-time.

How others are doing their data entry forms?
Whats the workaround?
How do YOU manage this in real-life?

I need to go ahoead and evaluate my architecture together with UniDac, but need some guidance in the meantime.

AlexP · Post by **AlexP** » Thu 22 Mar 2012 12:58

Hello,

Presently, for solving the problem, you can either not to create persistent fields or assign SQL statements explicitly for INSERT,UPDATE,DELETE and REFRESH. We will try to fix the problem as soon as possible.

betadevart · Post by **betadevart** » Tue 10 Apr 2012 17:21

Hello,

do you have any any news (new version) about persistent fields alias problem?

AlexP · Post by **AlexP** » Wed 11 Apr 2012 08:21

hello,

We've already fixed this problem. Please download the latest version of UniDAC 4.1.6 and try once more

Devart Forums

Automatic update sql using field aliases

Automatic update sql using field aliases

aliases AND manual fields