TZDBLibConnection.SetReadOnly throws an exception

[aehimself]

To safeguard data used in specific workers I occasionally set SQLConnection.ReadOnly to true. All seems to work fine, until I launched a worker like this on an MSSQL database. Upon calling SQLConnection.Connect, the whole thing went down, because of the following:

Code: Select all

procedure TZDBLibConnection.SetReadOnly(Value: Boolean);
begin
  //sql server and sybase do not support RO-Transaction or Sessions
  //all we have is a readonly database ...
  if Value then
    raise EZSQLException.Create(SUnsupportedOperation);
end;

I have a couple of issues with this approach.
First, you get an error elsewhere than it actually makes sense (should be raised when you set TZConnection's property). I know it is not possible at that stage as the IZConnection doesn't necessarily exist.
Second, it throws a generic EZSQLException instead of EZUnsupportedException, making this indistinguishable from exception handling perspective.

#2 is easy to fix, but I started to wonder. As my workers have no idea what kind of connection they are working on (as it1s irrelevant for them) but calling SQLConnection.Connect fails, should I simply try to set .ReadOnly to false and try again? What happens if something else will throw an unsupported exception?

As there is no way to ask the driver if .ReadOnly is supported or not, there is no way to determine this beforehand (unless we hard-code it, which is a bad practice).

Should an exception be raised at all for this?

Please, feel free to throw your ideas and opinions at me :)

[aehimself]

A pull request has been created to change all exceptions which raise SUnsupportedOperation to EZUnsupportedException.

This way I can write

Code: Select all

 Try 
   SQLConnection.Connect;
 Except
   On E:EZUnsupportedException Do
     If SQLConnection.ReadOnly Then
     Begin
       SQLConnection.ReadOnly := False;
       SQLConnection.Connect;
     End
     Else
       Raise;
 End;

I still don't feel this as a proper solution, but at least we are getting closer.

Topic is still open for discussion :)

[marsupilami]

Well - the problem is that MS SQL Server doesn't seem to support readonly connections. So we either create a readwrite conenction and ignore the readonly setting or we raise an exception.

It would be nice to have a SupportsReadonlyConnections property or something similar in the DatabaseMetadata classes or somewhere. Problem is - we most probably need a connection to get access to these classes. So there would be a chicken and egg problem...

[aehimself]

I'm against this metadata thing as to be able to use it consistently you need to hardcode trues or falses. One day something will get implemented and I bet someone (me especially) will forget to update it.

As I submitted my pull request to have EZUnsupportedException raised my question is mostly about how to detect and handle this condition in a worker. I feel my code above quite dirty.

[marsupilami]

Well - the code above is dirty Honestly: An EZUnsupportedException could be raised for a number of reasons. Your code works only when the reason for the EZUnsupportedException is the ReadOnly property. If you request a not supported transaction isolation level, which also might raise an EZUnsupportedException, your code will fail.

So - for me there are two possibilities:
1) We change the behavior of the dblib driver to silently create a read/write connection and documet this - in the wiki maybe and in the upcoming release notes and maybe in the documentation collection.
2) We find a way to enable user software to detect the capabilities of a driver. We currently don't do that.

Otherwise this is what will happen: Users will hard code the capabilities in their software (which they probably already do) and they will make more mistakes there than we will.
On the other hand there will be capabilities that are server dependent. So checking on them might require to do an initial connection - check the capabilities and then do a connection that really has the capabilities needed. Example: The WebServiceDriver doesn't know wether the remote side supports readonly connections or not. There is only one way to find out. Connect and check it.

[Fr0sT]

Hmm, doesn't the issue come from the fact that in Zeos connection and transaction are united? At least in Firebird the whole connection could be RO so as any transaction could (obviously if connection is RO, no RW tx is allowed). Of course the flag must be set before connect or tx starts.

[marsupilami]

Hmm, doesn't the issue come from the fact that in Zeos connection and transaction are united?

Hmm - yes and no. Most of the Zeos supported client libraries only support one transaction per connection. I am pretty sure that this is true for dblib. I am also pretty sure, this is true for PostgreSQL and SQLite. I do know that Interbase and Firebird are the exception to the rule because they allow for more than one transaction per connection. This leads to most drivers simulating transactions by establishing new connections.

At least in Firebird the whole connection could be RO so as any transaction could (obviously if connection is RO, no RW tx is allowed). Of course the flag must be set before connect or tx starts.

I didn't check this myself but as far as I know, there is no possiblity to mark a connection as read only in Firebird. A read only connection (or transaction) in Firebird is a transaction where you didn't write yet. Some ressources on the server get assigned when the first write operation is done. After the first modification the transaction will continue to be a read/write transaction.

[Fr0sT]

I didn't check this myself but as far as I know, there is no possiblity to mark a connection as read only in Firebird. A read only connection (or transaction) in Firebird is a transaction where you didn't write yet. Some ressources on the server get assigned when the first write operation is done. After the first modification the transaction will continue to be a read/write transaction.

Yep, there's no RO connections controlled by client in FB - I was slightly confused by Read-only database property. However, this "A read only connection (or transaction) in Firebird is a transaction where you didn't write yet" is a bit incorrect - tx isolation parameter "read" (isc_tpb_read) starts RO tx which will raise server error on any write attempt.