TMSSQLConnection - sqlDB component for accessing MS SQL Server

[ludob]

You know something is strange... as you said: "...for mssql, we'll need probably an easier way of doing this.
I'll propose a patch."

Meaning: I'll make a patch that I propose to Lacak2, Big Chimp and whoever wishes to comment, for inclusion. This is cooperative development I won't have time tonight, though.
The idea is indeed to have a mode that emulates the ADO behavior: autocommit per default, suspend autocommit when a transaction is started, resume autocommit when commit or commitretaining. No change to commit = close datasets since that is not on the mssql level. But, commit retaining doesn't start a new transaction. 

[mdbs99]

You know something is strange... as you said: "...for mssql, we'll need probably an easier way of doing this.
I'll propose a patch."

Meaning: I'll make a patch that I propose to Lacak2, Big Chimp and whoever wishes to comment, for inclusion. This is cooperative development I won't have time tonight, though.
The idea is indeed to have a mode that emulates the ADO behavior: autocommit per default, suspend autocommit when a transaction is started, resume autocommit when commit or commitretaining. No change to commit = close datasets since that is not on the mssql level.

I think is very easy, but this will be in disagreement with the standard SQLdb way. 

Well, I resolved my problem. Was easy, becaus I always have a layer (my own Connection, Query, StoredProc components. I have these layers for Zeos, SQLdb, etc)  to others tecnologies, so:

1. After Query.Open or Query.ExecSQL, I call ExecCommitRetaining;

2.: protected ExecCommitRetaining

Code: [Select]

begin
  if not FInTransaction then
    FTran.CommitRetaining;
end
3. New Transaction:

Code: [Select]

begin
  FInTransaction := True;  // just it ;-)
end
4. Commit:

Code: [Select]

begin
  FTran.CommitRetaining;
  FInTransation := True;
end
5. Rollback

Code: [Select]

begin
  FTran.RollbackRetaining;
  FInTransation := True;
end
Ah, I have counters for nested transactions (simulate), but this is other thing.

But, commit retaining doesn't start a new transaction.

I just do not know how do this... but you said it does not matter!!

[ludob]

If you are using your own layer why not simply call SQLConnector.ExecuteDirect('BEGIN TRANSACTION'); to start a transaction and SQLConnector.ExecuteDirect('COMMIT); or SQLConnector.ExecuteDirect('ROLLBACK'); instead of using the SQLTransaction.commit, etc. ?  Using SQLConnector.Params  'Autocommit=TRUE' and above ExecuteDirect's, your program will behave the same as ADO, without closing your data sets.

[mdbs99]

WT... That is right!
Very much more simple!

I did not have more the problem with datasets closed, as I said before, but the transaction continued open.
Now I can use as ADO, using transactions only if necessary. Thank you! 

So, anyway, do you will give up to propose the patch to use AUTOCOMMIT more simple that we have today?

[ludob]

I've been looking at a patch. The idea was to maintain a transaction nesting level (FTransactionNest) and only send a BEGIN TRANSACTION if not(AutoCommit) or (FTransactionNest>0);
Problem is that sqldb doesn't support nested transactions. It raises an exception when starting a transaction when there is already one active. Cheating and return false in StartDBTransaction when no BEGIN TRANSACTION is sent (as is down now when Autocommit) won't work if multiple queries are attached to the same TSQLTransaction since every TSqlquery.open will check if a transaction is active and start one if not. No way to keep track of the correct transaction nesting level So, I'm at a dead end in finding a solution that only affects mssql.pas and is transparent for the user.

The other possible route is to add new methods to TMSSQLConnection: StartExplicitTransaction(name:string), CommitExplicit(name:string) and RollBackExplicit(name:string) that would wrap the SQLConnector.ExecuteDirect('BEGIN TRANSACTION name');, etc. A nesting level counter in these routines would be maintained and an exception raised if the counter drops below zero. Published as property trancount.
A SavePointExplicit(savepoint_name:string) would be another useful add-on. Your opinion?

[mdbs99]

I've been looking at a patch. The idea was to maintain a transaction nesting level (FTransactionNest) and only send a BEGIN TRANSACTION if not(AutoCommit) or (FTransactionNest>0);
Problem is that sqldb doesn't support nested transactions. It raises an exception when starting a transaction when there is already one active. Cheating and return false in StartDBTransaction when no BEGIN TRANSACTION is sent (as is down now when Autocommit) won't work if multiple queries are attached to the same TSQLTransaction since every TSqlquery.open will check if a transaction is active and start one if not. No way to keep track of the correct transaction nesting level So, I'm at a dead end in finding a solution that only affects mssql.pas and is transparent for the user.

I think you're on track.
In my own layer, I have a simulate of "transaction nesting" (FTranCount) that I use even on Delphi + ADO. This is not only a "problem" of SQLdb.
Eg:

Code: [Select]

procedure FooA:
begin
  conn.StartTransaction;
  [...]
  conn.Commit;
end;

procedure FooB:
begin
  conn.StartTransaction;
  [...]
  conn.Commit;
end;

procedure FooExec;
begin
  conn.StartTransaction;
  FooA;
  FooB;
  conn.Commit;
end;

FooA and FooB doesn't know if a transaction was started.
My layer only starts the first transaction (first StartTransaction) and sum the others. When I call Commit, I need to see if FTranCount = 1 to call the real Commit, like you said.
This is transparent for the user and, using AutoCommit=True, it is very easy to use for mostly Delphi programmers. 

The other possible route is to add new methods to TMSSQLConnection: StartExplicitTransaction(name:string), CommitExplicit(name:string) and RollBackExplicit(name:string) that would wrap the SQLConnector.ExecuteDirect('BEGIN TRANSACTION name');, etc. A nesting level counter in these routines would be maintained and an exception raised if the counter drops below zero. Published as property trancount.
A SavePointExplicit(savepoint_name:string) would be another useful add-on. Your opinion?

IMHO create new methods is not good way. The SQLdb was created to be one generical solution for many DBMS, right? If you put new methods will complicate the use of TSQLConnector, e.g.

[Lacak2]

Or another route is change how sqlDB works 
Make it more compatible with Delphi's DB-Express.

I am not expert for DBX, but IMO DBX works like this:

1. transactions are not started implicitly

2. if transaction is not explicitly started then when ApplyUpdates, transaction is started and if no errors occurc during applying updates transaction is commited after applyupdates (something like batch-autocommit mode)

3. if transaction is started explicitly (by calling StartTransaction) then transaction is under control of user (something like explicit transactions) and user must Commit or Rollback

Plus:
- Signal if connection support multiple transactions per connection
- Count transactions (StartTransaction +1, Commit,Rollback -1)

Edit:
But AFAIU DBX does not support "virtual nested" transactions.
Transactions can be nested only if driver support it (property MultipleTransactionsSupported)

[Lacak2]

IMHO create new methods is not good way. The SQLdb was created to be one generical solution for many DBMS, right? If you put new methods will complicate the use of TSQLConnector, e.g.

I agree here.

[mdbs99]

Or another route is change how sqlDB works 
Make it more compatible with Delphi's DB-Express.

You're right but I doubt that change will be made by FPC team! 

I am not expert for DBX, but IMO DBX works like this:

1. transactions are not started implicitly

2. if transaction is not explicitly started then when ApplyUpdates, transaction is started and if no errors occurc during applying updates transaction is commited after applyupdates (something like batch-autocommit mode)

3. if transaction is started explicitly (by calling StartTransaction) then transaction is under control of user (something like explicit transactions) and user must Commit or Rollback

Plus:
- Signal if connection support multiple transactions per connection
- Count transactions (StartTransaction +1, Commit,Rollback -1)

Perfect.   

[Lacak2]

Or another route is change how sqlDB works 
Make it more compatible with Delphi's DB-Express.

You're right but I doubt that change will be made by FPC team! 

Yes I think so also 
But if there will be good arguments and good proposal ... But personaly I do not want go into this "war"

[mdbs99]

They will talk about backward compatibility... but no cost to try! 

[ludob]

I think you're on track.
In my own layer, I have a simulate of "transaction nesting" (FTranCount) that I use even on Delphi + ADO. This is not only a "problem" of SQLdb.

The difference is that you build a layer on top of sqldb and I'm trying (and fail so far) to find a solution below sqldb (in mssql).
As explained before, there is no way for mssql to make a distinction between an explicit start transaction and one triggered by a TSQLQuery.Open.

Or another route is change how sqlDB works 
Make it more compatible with Delphi's DB-Express.
1. transactions are not started implicitly

See a few posts before. That matches better MS but not the SQL standard nor most other databases.   

But if there will be good arguments and good proposal ... But personaly I do not want go into this "war"

Me neither 

IMHO create new methods is not good way. The SQLdb was created to be one generical solution for many DBMS, right? If you put new methods will complicate the use of TSQLConnector, e.g.

I agree here.

There are precedents. A few properties in TODBCConnection,  TMySQLxxConnection and method GetInsertID in TSQLite3Connection. 

Personally I'm perfectly happy with calling SQLConnector.ExecuteDirect. So, I guess we'll better leave it as is. A working solution is documented in this thread for posterity 

[mdbs99]

The difference is that you build a layer on top of sqldb and I'm trying (and fail so far) to find a solution below sqldb (in mssql).
As explained before, there is no way for mssql to make a distinction between an explicit start transaction and one triggered by a TSQLQuery.Open.

But we can do the same in SQLdb!
We can have a parameter to work in "SQL standard" or "MSSQL standard". The first is it as is. The second we can implement using the techniques presented here: autocommit is default; counter transactions; etc.
The methods are same; interface same; no backward compatibility problems. Just a different mode to use. Your opinion?

[ludob]

The difference is that you build a layer on top of sqldb and I'm trying (and fail so far) to find a solution below sqldb (in mssql).
As explained before, there is no way for mssql to make a distinction between an explicit start transaction and one triggered by a TSQLQuery.Open.

But we can do the same in SQLdb!
We can have a parameter to work in "SQL standard" or "MSSQL standard". The first is it as is. The second we can implement using the techniques presented here: autocommit is default; counter transactions; etc.
The methods are same; interface same; no backward compatibility problems. Just a different mode to use. Your opinion?

I hear you saying. I'm trying to explain that at the mssql layer there is no way to count the number of transactions the user requests. sqldb will begin a transaction as soon as a TSQLQuery is prepared and TSQLTransaction allows only one active transaction.
If we change at the sqldb layer, everything can be done while remaining backward compatible. Unfortunately I don't see a volunteer here to get that sold to the FPC core team. Or are you suggesting we make an sqldb branch...

[mdbs99]

We do not to count the number of real transactions, just simule that! 
See the Lacak's comment: "- Count transactions (StartTransaction +1, Commit,Rollback -1)".  That I was talked about.

We do not need a SQLdb branch for implement only the TMSSQLConnection. But, if the new features we want to TMSSQLConnection get better than original SQLdb... yes, maybe a branch, why not?