"Accessor is invalid" when executing stored procedure

[yartoo]

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]

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]

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]

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

[pavelpd]

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]

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.



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]

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]

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."

[yartoo]

Hi,
I can confirm that the "Accessor is invalid" issue with the use of the sql_variant datatype is fixed.

However, the transaction isolation level issue still exists.
In Ethereal we see that when we ask for a transaction isolation level of "serializable" it is being actioned as "read committed" to Ms SQL Server.

Let me know if you need any further details

Thanks

[yartoo]

Hi,

Correction; we are not using Ethereal. We are using SSMS XEvent Profiler.

Screen snapshots and descriptions are...

For read/write transactions we do an execute direct "Set transaction isolation level serializable"
then TheDb.BeginTransaction (TDBXIsolations.Serializable);

We shouldn’t actually need the first execute direct in the future, but with the previous dll we do because the Devart driver doesn’t actually set the isolation levels at all.

So you get the attached screen shot



in the profiler…

When the execute direct is removed you get the behaviour as in the second screen shot - just the read committed before the insert statement



And the 3rd screen shot is with the previous (pre Alexandria) driver - you can see it doesn’t do any isolation commands at all - they’re all ours..


I hope this helps to explain what we are seeing.

Let me know if you need anything else.

Shaun

[pavelpd]

Hi Shaun,
Thanks for your request!

Please be informed, that the issue with setting the transaction level has been fixed in driver version 9.1.1

An example of setting and checking the isolation level:

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
var
  TransDesc: TDBXTransaction;
begin
  Memo1.Lines.Add('');

  SQLConnection1.BeginTransaction(TDBXIsolations.SnapShot);
  try
    Memo1.Lines.Add('SELECT * FROM Dept');
    SQLQuery1.SQL.Text := 'SELECT * FROM Dept';
    SQLQuery1.Open;

    SQLQuery1.Close;
    SQLQuery1.SQL.Text :=
      ' SELECT ' +
      '   CASE transaction_isolation_level '+
      '       WHEN 0 THEN ''Unspecified'' '+
      '       WHEN 1 THEN ''ReadUncommitted'' '+
      '       WHEN 2 THEN ''ReadCommitted'' '+
      '       WHEN 3 THEN ''Repeatable'' '+
      '       WHEN 4 THEN ''Serializable'' '+
      '       WHEN 5 THEN ''Snapshot'' '+
      '   END AS TRANSACTION_ISOLATION_LEVEL '+
      ' FROM sys.dm_exec_sessions '+
      ' WHERE session_id = @@SPID';
    SQLQuery1.Open;
    Memo1.Lines.Add('TRANSACTION_ISOLATION_LEVEL = ' + 		 
         SQLQuery1.FieldByName('TRANSACTION_ISOLATION_LEVEL').AsString);

    SQLConnection1.CommitFreeAndNil(TransDesc);
  except
    SQLConnection1.RollbackFreeAndNil(TransDesc);
    raise;
  end;

  Memo1.Lines.Add('Success');
end;

As a result, there should be such a result with the driver version 9.1.1:
"
SELECT * FROM Dept
TRANSACTION_ISOLATION_LEVEL = Snapshot
Success
"

Could you please clarify, do you have an issue with setting the isolation level in the latest version of our dbExpress for SQL Server 9.1.1 driver?

[yartoo]

Hi,
I added your transaction identification code to the test program and started a transaction of each type. The output ids as follows...

Start of tranmsaction test
TDBXIsolations.ReadCommitted
Select count (*) from FAQs
Count=7
TRANSACTION_ISOLATION_LEVEL = ReadCommitted

TDBXIsolations.RepeatableRead
Select count (*) from FAQs
Count=7
TRANSACTION_ISOLATION_LEVEL = Repeatable

TDBXIsolations.DirtyRead
Select count (*) from FAQs
Count=7
TRANSACTION_ISOLATION_LEVEL = ReadUncommitted

TDBXIsolations.Serializable
Select count (*) from FAQs
Count=7
TRANSACTION_ISOLATION_LEVEL = ReadCommitted

TDBXIsolations.Snapshot
Select count (*) from FAQs
Count=7
TRANSACTION_ISOLATION_LEVEL = Snapshot

End of transaction test

You can see that starting a TDBXIsolations.Serializable transaction results in a ReadCommitted transaction being reported by Ms SQL Server. That should be "Serializable".

I have submitted an updated version of the test application via the customer support form. That is what I used to generated this result.

Let me know if you need anything else

Cheers
Shaun

[pavelpd]

Hi Shaun,

We've reproduced the issue and fixed it.

Please note that we have released a new release of dbExpress Driver for SQL Server and have included this fix in it.

[yartoo]

pavelpd,

Thanks for the update.

Great work

I'll grab the update

Cheers