"Accessor is invalid" when executing stored procedure
"Accessor is invalid" when executing stored procedure
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?
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?
Re: "Accessor is invalid" when executing stored procedure
I did some more work on this.
The statement that fails is Data.DBXDynalink.pas >- line 1157
The CheckResult method gets a DBXResult of 65535
This is for the execute of the statement which is
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...
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
The statement that fails is Data.DBXDynalink.pas >- line 1157
Code: Select all
CheckResult(FMethodTable.FDBXCommand_Execute(FCommandHandle, ReaderHandle))
This is for the execute of the statement which is
Code: Select all
EXECUTE CheckMyUser 'bob', 'fido'
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;
Any suggestions on where to start looking would be appreciated.
Thanks
Re: "Accessor is invalid" when executing stored procedure
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
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
Re: "Accessor is invalid" when executing stored procedure
Hi,
I have submitted a test application via the customer support form.
Thanks
I have submitted a test application via the customer support form.
Thanks
Re: "Accessor is invalid" when executing stored procedure
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.
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.
Re: "Accessor is invalid" when executing stored procedure
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
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
Re: "Accessor is invalid" when executing stored procedure
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.
Thank you for the provided information.
We have reproduced the issue and will investigate its origin.
We will inform you about the results shortly.
Re: "Accessor is invalid" when executing stored procedure
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."
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."
Re: "Accessor is invalid" when executing stored procedure
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
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
Re: "Accessor is invalid" when executing stored procedure
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
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
Re: "Accessor is invalid" when executing stored procedure
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:
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?
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?
Re: "Accessor is invalid" when executing stored procedure
Hi,
I added your transaction identification code to the test program and started a transaction of each type. The output ids as follows...
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
I added your transaction identification code to the test program and started a transaction of each type. The output ids as follows...
You can see that starting a TDBXIsolations.Serializable transaction results in a ReadCommitted transaction being reported by Ms SQL Server. That should be "Serializable".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
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
Re: "Accessor is invalid" when executing stored procedure
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.
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.
Re: "Accessor is invalid" when executing stored procedure
pavelpd,
Thanks for the update.
Great work
I'll grab the update
Cheers
Thanks for the update.
Great work
I'll grab the update
Cheers