"Accessor is invalid" when executing stored procedure

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for SQL Server in Delphi and C++Builder
Post Reply
yartoo
Posts: 14
Joined: Fri 11 Jun 2010 00:54

"Accessor is invalid" when executing stored procedure

Post by yartoo » Fri 27 May 2022 00:24

Windows 10 all updates applied
RAD Studio 11.1
dbexpsda41.dll version 9.1.1.0 date modified 10/12/2021

When I use a TSQLQuery or TSQLStoredProcedure to execute a store procedure I get told "Accessor is invalid"

Example:
TheQry.SQL.Add ('EXECUTE CheckMyUser 'bob' 'fido');
TheQry.Open;
Jumps to exception handler with Exception.Message 'Accessor is invalid'

The same database connection has been used in the program before this to read data from tables successfully.

I reverted to dbexpsda41.dll version 8.2.1 from 09/06/2020 and that works as it has been for months.

I'd like to update my Devart software as part of the update of the Embarcadero software.

Any suggestions on what I need to do to get dbexpsda41 v9.1.1.0 to work?

yartoo
Posts: 14
Joined: Fri 11 Jun 2010 00:54

Re: "Accessor is invalid" when executing stored procedure

Post by yartoo » Thu 02 Jun 2022 00:32

I did some more work on this.

The statement that fails is Data.DBXDynalink.pas >- line 1157

Code: Select all

  CheckResult(FMethodTable.FDBXCommand_Execute(FCommandHandle, ReaderHandle))
The CheckResult method gets a DBXResult of 65535
This is for the execute of the statement which is

Code: Select all

EXECUTE CheckMyUser 'bob', 'fido'
The TSQLStoreProc object has already asked for the parameter details successfully. It's the actual call to execute the command that fails.

The database connection setup is...

Code: Select all

  TheDb.Params.Clear;
  TheDb.SQLHourGlass := False;
  TheDb.ConnectionName := 'DaisyDb';
  TheDb.DriverName := 'DevartSQLServer';
  TheDb.KeepConnection := True;
  TheDb.LoadParamsOnConnect := False;
  TheDb.LoginPrompt := False;
  TheDb.Name := 'dbAppl';
  TheDb.TableScope := [tsTable, tsView];
  TheDb.LibraryName := 'dbexpsda41.dll';
  TheDb.GetDriverFunc := 'getSQLDriverSQLServerDirect';
  TheDb.Params.Values [TDBXPropertyNames.VendorLib] := 'not used'; // in direct mode
  TheDb.Params.Values [TDBXPropertyNames.DriverName] := 'DevartSQLServer';
  TheDb.Params.Values ['BlobSize'] := '-1';
  TheDb.Params.Values ['LongStrings'] := 'True';
  TheDb.Params.Values ['EnableBCD'] := 'False';
  TheDb.Params.Values ['EnableLargeint'] := 'True';
  TheDb.Params.Values ['FetchAll'] := 'True';
  TheDb.Params.Values [TDBXPropertyNames.HostName] := ServerName;
  TheDb.Params.Values [TDBXPropertyNames.DataBase] := DbName;
  TheDb.Params.Values [TDBXPropertyNames.UserName] := UserName;
  TheDb.Params.Values [TDBXPropertyNames.Password] := Password;
The exe works on another Windows machine, which suggests a DLL mismatch somewhere. I see in the module view that after dbexpsda41.dll is loaded sqlncli11.dll is loaded as expected with the direct mode I have requested.

Any suggestions on where to start looking would be appreciated.

Thanks

pavelpd
Devart Team
Posts: 81
Joined: Thu 06 Jan 2022 14:16

Re: "Accessor is invalid" when executing stored procedure

Post by pavelpd » Thu 16 Jun 2022 04:44

Hi!
Thanks for contacting Devart!

Kindly note, that we were unable to reproduce the issue you mentioned based on the data you provided.

Could you please compose a script for creating a stored procedure?
Also, please compose and send us an sample application reproducing the issue you have specified with all necessary DDL and DML scripts to create and populate database objects.

You can send all the needed samples via the e-support form: https://www.devart.com/company/contactform.html

yartoo
Posts: 14
Joined: Fri 11 Jun 2010 00:54

Re: "Accessor is invalid" when executing stored procedure

Post by yartoo » Wed 22 Jun 2022 06:20

Hi,
I have submitted a test application via the customer support form.
Thanks

pavelpd
Devart Team
Posts: 81
Joined: Thu 06 Jan 2022 14:16

Re: "Accessor is invalid" when executing stored procedure

Post by pavelpd » Fri 01 Jul 2022 14:22

Hi,

Thanks for the info provided!

Please be informed, that we have received your sample and started its investigation.

Once we have an update we will inform you with the results shortly.

Also kindly send us a screenshot of the error you described.

Feel free to reply if you have any questions or need additional information.

yartoo
Posts: 14
Joined: Fri 11 Jun 2010 00:54

Re: "Accessor is invalid" when executing stored procedure

Post by yartoo » Mon 04 Jul 2022 01:01

Hi,
The screen snapshot shows that after the exception handler has captured the "Accessor is invalid" message and written to the on-screen memo log the application gets an access violation when freeing the TSQLConnection.

Image

This is the application I sent you.

An update...
We have identified that the problem is with the sql_variant datatype returned by the Ms SQL Server procedure used to check the password. If that is cast to an integer, we do not get an error. That's a work-around, however, sql_variant is used a lot by Ms SQL Server and it worked before this version 9 driver, so we should be able to utilize it.

The transaction issue is more of a problem. We see that you are overriding our request for a serializable transaction by changing it to read committed. That introduces a risk of "incorrect" data being returned in a select statement during high transaction rate periods. We rely on absolute chronological data consistency.
Is there a setting or option we can use to ensure the transaction isolation level we request is used without this override?

Thanks
Shaun

pavelpd
Devart Team
Posts: 81
Joined: Thu 06 Jan 2022 14:16

Re: "Accessor is invalid" when executing stored procedure

Post by pavelpd » Thu 14 Jul 2022 08:48

Hi,
Thank you for the provided information.

We have reproduced the issue and will investigate its origin.
We will inform you about the results shortly.

pavelpd
Devart Team
Posts: 81
Joined: Thu 06 Jan 2022 14:16

Re: "Accessor is invalid" when executing stored procedure

Post by pavelpd » Wed 20 Jul 2022 13:18

Hi,
Thanks for contacting Devart blog!

Kindly be informed that we've reproduced the issue regarding the "Accessor is invalid" error and fixed it.
The fix will be included in the next build of our product.

As a workaround, we can send you a nightly build of our product including the required changes.

Please specify you license number, IDE version and send us via the contact form: https://www.devart.com/company/contactform.html

Also, please clarify what do mean by saying this - "We see that you are overriding our request for a serializable transaction by changing it to read committed."

Post Reply