Devart Forums

Hi,
Setup is:
MS SQL Server full version 2016, Delphi 2007, unsure about the UniDAC version.

I have a situation in an application where I have code like:
aQry := TUniQuery.Create(nil);
aQry.Connection := connection;
aQry.SQL := 'SELECT [ID], [Field2], [Field3]....[Field160] FROM [Transactions] WHERE [Field20] = '201707251014''';
aQry.Open;

This opens the query, and I retrieve a row ok. [Field20] is Unique, so I retrieve one row.

For some reason the Status field in DBMonitor is "pending" even after I go to the line after "aQry.open;"

I then change some values in the aQry;
aQry.Edit;
aQry.Fields[150].asString := 'a New string'; //Note, this has probably not changed since the old record
aQry.Fields[151].asString := 'Another string'; //Note, this has probably not changed since the old record

I then call Post on the dataset:

aQry.Post;
Calling Post is my actaul problem, as this sometimes takes 20 - 30 seconds, most of the times 0.015 seconds.
I have now downloaded dbMonitor to see what is going on here, and when I in the debugger go past the aQry.Post line, I would expect to see the SQL that is actually sent to the database in the dbmonitor, I do however see

UPDATE [Transactions]
SET
[Timestamp] = ?, [Status] = ?, [InitialsApproved] = ?, [AccountingSerialNo] = ?
WHERE
[Id] = ?

I have copied and pasted the above SQL from DBMonitor.
Why do I see questionmarks (?) and not values?
I understand that ID being the primary key of the table, this information is provided in the metadata on the TUniQuery.

What I actually was looking for, was to see if the query's where clause included all the fields in the SELECT statement, but that doesn't seem to be the case.

So my question in this context is really, why do I see questionmarks and not values?
Can I see the values?

But my problem is really why is "aQry.Post" sometimes 20-30 seconds. This happens mainly (only?) with the first time I fire this SQL.

Any help is highly appreciated
Kind Regards
Jens Fudge

To see the parameter values of the parameterized SQL query in dbMonitor, go to the Parameters tab (You may find it above the Statusbar).

If the behavior you specified is stably reproduced, please create a small test project for its reproduction and send it to us using the contact form on our website: http://www.devart.com/company/contactform.html . Include the backup (BAK) of the test database in the example

The query is not parameterized, but I did find the values in that tab. Sorry I missed that.
I cannot send the database to you. So we can close the issue here, I got it working.. Sort of.

The SELECT query you are opening does not have parameters... but the UPDATE one UniDAC is using to update the database does

FYI:
I figured out why the query was sometimes 20-30 seconds.
It turned out, that the field called ID was NOT Primary Key after all. There is another field in the table that is Primary Key (TransactionNo).
By setting KeyFields := 'TransactionNo' I now have everything running smoothly. So maybe the metadata in the UniQuery is wrong.
Never mind, my problem is solved.

Jens