Unable to find record

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Unable to find record

Post by radub » Fri 10 Jan 2014 13:11

Hello,

We have an n-tier application, with the BL server based on SDAC.
Until upgrade from version 4.80 to 6.9 -upgraded yesterday-, everything have worked ok.
Starting with 6.9 version, begins to throw that error, Unable to find record when trying either reading a table or trying to update it.

Having tenths of application in production this is a serious problem!

It appears that something was changed in the mechanism of reading records between the 2 versions.

More problems: after saving a master-detail relationship, when refreshing apears where clause with something like this "WHERE ( ITEMEQID IS NULL AND @P1 IS NULL) ',N'@P1 int',NULL", where "ITEMEQID" is the name of the foreign key field. It appears that the provider insert that where clause.

I am waiting for a solution.

Thank you,
Radu B.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Unable to find record

Post by AlexP » Fri 10 Jan 2014 15:02

Hello,

Please send the script for creating the table and a small application demonstrating the problem to alexp@devart.com

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: Unable to find record

Post by radub » Fri 10 Jan 2014 19:17

I have send 2 mails. The last contains the complete examples for both problems.
I want to emphasize that these errors start occurring after upgrading from version 4.80 to 6.9.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Unable to find record

Post by AlexP » Sat 11 Jan 2014 13:44

Hello,

1) Thank you for the information. We have reproduced the issue with the "Unable to find record" error and we will fix it as soon as possible.
2) This condition is added to the WHERE clause in case when the parameter value is Null. We add this condition in order to display the detail records that does not have the corresponding master records.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: Unable to find record

Post by radub » Sat 11 Jan 2014 17:51

More...

Unfortunately I discovered another problem reproducible on the same example.
After having inserted a pair of master-detail records, press "Get Last Rec" and it displays the last records on both grids.
Then append another record in the detail grid and press ApplyUpds.
This time, no matter how are the options set for the provider, the detail shows nothing even the first time it displays correctly the record that have been already in the table.

And to worsen the situation, all problems are same on ODAC.

I have also noticed in SDAC, the statement used for inserting, uses varchar(8000) parameters, even the fields are less then 8000 in length. I suppose this will be a problem in inserting several thousands records with couple of tenths of varchar fields.
For ODAC the length are correctly set.

On both implementions (SDAC/ODAC) we have several customers running with the old versions.
For the time being, we are blocked on updating new develops to our customers, so for the moment I am forced to use the old versions.
So we need urgently the corrections.
Is there any deadline for this?

Thank you,
Radu B.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: Unable to find record

Post by radub » Fri 17 Jan 2014 07:03

Hello,

Still no news?
We are in a very ingrate position of doing nothing with all the new DAC modules.
Really we are loosing money couldn't develop the new platform for PostGreSQL.

The issues are urgent to us.

Thank you,

Radu.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Unable to find record

Post by AlexP » Fri 17 Jan 2014 14:58

Hello,

We couldn't reproduce the problem with sending varchar(8000) parameters. Please describe the situation, when this issue occurs.

We fix all detected problems until a new version releases, but, as soon as we fix the problems you described, we can send you a night build of the products, including these fixes.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: Unable to find record

Post by radub » Sat 18 Jan 2014 12:08

Hello,

Just push "Insert new rec", fill in both grids with some data, open a SQL Profiler, push "ApplyUpds" an watch on the sql statements from Profiler.
It should resemble with this:

exec sp_executesql N'insert into "TBLM"
("FCODE", "FNAME")
values
(@P1, @P2)',N'@P1 varchar(8000),@P2 varchar(8000)','FCO','FNUME'

Please notice the varchar(8000)

Thank you,
Radu B.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Unable to find record

Post by AlexP » Mon 20 Jan 2014 13:15

To solve the problem, you should set the PreventPSKeyFields global variable, declared in the DBAccess module, to False

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: Unable to find record

Post by radub » Mon 20 Jan 2014 16:57

Hello,

I set that variable to false in the OnCreate event of the Form1 and has no effect.
Please be more specific where should I have to set it, probably I didn't understand correctly, or it should be in another place of the application.


Thank you,

Radu B.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Unable to find record

Post by AlexP » Tue 21 Jan 2014 10:18

This variable should be set before opening the DataSet. If you set the Active property of the DataSet at design-time, then at the time of calling the OnCreate event, the DataSet of the form will be already open. You can set this variable in the Initialization section.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: Unable to find record

Post by radub » Wed 22 Jan 2014 07:49

Sir!

With all respects, please give me a solution that you have tested before!
This doesn't work, no matter where I'd put that variable.
It is very frustrating installing/removing all DAC components to test another try and to see that doesn't function.

We have bought 3 components and we cannot use them!

Waiting for a solution ASAP.

Thank you,
Radu B.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Re: Unable to find record

Post by Dimon » Thu 23 Jan 2014 10:24

Please note, setting the PreventPSKeyFields variable solves the issue with the "Unable to find record" error.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Re: Unable to find record

Post by Dimon » Thu 23 Jan 2014 10:25

We have investigated the problem with determination of varchar parameters as varchar(8000) by SQL Profiler more deeply. We found out, that this behaviour is correct and connected with the SQL Native Provider specificity, but not with SDAC. The length of really transmitted data is correct.
You can assure yourself of this using the OLEDB provider instead of the Native Client provider. For this, set the TMSConnection.Options.Provider property to prSQL.

radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

Re: Unable to find record

Post by radub » Thu 23 Jan 2014 14:20

Yes, prSQL works correctly. It put the exact lenght of the varchar.
Anyway, it's strange taking into consideration the option prAuto, which in the 4.80 version
works correctly.

Post Reply