I can't quite decide if it's too much abuse of keywords or not... I like the concept though. What do you guys think?
I can't quite decide if it's too much abuse of keywords or not... I like the concept though. What do you guys think?
for qry in Transaction(SQLQuery1) do
begin
qry.SQL.Text := 'delete from foo';
qry.ExecSQL;
// raise Exception.Create('Oops, lets do a rollback');
qry.SQL.Text := 'insert into foo (bar) values (42)';
qry.ExecSQL;
end;
Instead of iterating, the enumerator starts a transaction, and if an exception is raised inside the for..in block it performs a rollback, otherwise it will commit. So there's no actual iteration going on.
The enumerator returns the dataset that is passed to it and is generic, so qry is of the same type as SQLQuery1.
for qry in Transaction(SQLQuery1) do
begin
qry.SQL.Text := 'delete from foo';
qry.ExecSQL;
// raise Exception.Create('Oops, lets do a rollback');
qry.SQL.Text := 'insert into foo (bar) values (42)';
qry.ExecSQL;
end;
Instead of iterating, the enumerator starts a transaction, and if an exception is raised inside the for..in block it performs a rollback, otherwise it will commit. So there's no actual iteration going on.
The enumerator returns the dataset that is passed to it and is generic, so qry is of the same type as SQLQuery1.
IMHO, you are obfuscating what the code does. Bad idea.
ReplyDeleteUwe Raabe Fair comment. I find the "plain" approach very verbose and in it's way also obscures what's going on inside the transaction:
ReplyDeletetry
transaction := SQLQuery1.SQLConnection.BeginTransaction;
try
SQLQuery1.SQL.Text := 'delete from foo';
SQLQuery1.ExecSQL;
// raise Exception.Create('Oops, lets do a rollback');
SQLQuery1.SQL.Text := 'insert into foo (bar) values (42)';
SQLQuery1.ExecSQL;
except
SQLQuery1.SQLConnection.RollbackFreeAndNil(transaction);
raise;
end;
finally
SQLQuery1.SQLConnection.CommitFreeAndNil(transaction);
end;
I guess the last few years of C++ has tained my mind :)
Why not just a try-except and the CommitFreeAndNil inside?
ReplyDeletetransaction := SQLQuery1.SQLConnection.BeginTransaction;
try
SQLQuery1.SQL.Text := 'delete from foo';
SQLQuery1.ExecSQL;
// raise Exception.Create('Oops, lets do a rollback');
SQLQuery1.SQL.Text := 'insert into foo (bar) values (42)';
SQLQuery1.ExecSQL;
SQLQuery1.SQLConnection.CommitFreeAndNil(transaction);
except
SQLQuery1.SQLConnection.RollbackFreeAndNil(transaction);
raise;
end;
Uwe Raabe Because I'm a bit too tired ;)
ReplyDeleteAsbjørn Heid take Uwe Raabe 's comments as constructive criticism, I for one, love seeing new approaches, keep 'em coming!
ReplyDeleteI use an TInterfacedObject subclass that implements an IQuery interface to wrap my query objects and provide several fluent-style helper methods. The data access layer (another interface which manages the connection pool) has a GetQuery method to construct an query instance and a transaction object for the connection.
ReplyDeleteThe IQuery helper methods do most of the work of setting up SQL text, populating parameters, logging details of what statement is being run, what the parameter values are, how long it takes to execute, etc. Most of the helper methods have a return type of IQuery, and return self.
The code ends up looking like the examples below. Variable 'conn' is the interface that wraps the connection object. GetQuery creates and returns an instance of something that supports IQuery (I'm using IBX right now). The log message goes to a logger interface that the GetQuery message gives the IQuery wrapper when it creates it, which is passed in to the factory that is used to get a value for conn. Transaction handling is all automatic, but if I need more control the IQuery interface exposes the wrapped SQL object, so I can do whatever I want.
For a very simple query:
conn.GetQuery().Exec( 'a log msg', 'delete from foo');
Exec handles doing the commit or rollback/log/raise. The query is an interface, so it's is released when the method scopes out, and it owns it's own transaction, which is also released. There is an option to pass in a transaction, in which case it won't release it when the query destroys, but I almost never use it (my queries almost never need to see what is happening in another transaction).
For a parameterized query, there is a Prep helper method that takes an open array of parameters:
conn.GetQuery()
.Prep( 'a log msg', 'delete from foo where bar = :bar', [barVal])
.Exec( 'doing the delete');
In the IQuery implementation the type of the open array members is checked so that the correct .AsXxxx method can be called to set the value.
For handling result sets there is an Open method, and the IQuery interface has a GetEnumerator which just returns the IQuery, but also does a Next and checks for EOF on each iteration:
with conn.GetQuery()
.Prep( 'a log msg', 'select baz from foo where bar = :bar', [barVal])
.Open() do
for r in Rows() do
result.Add( r.Fields[0].AsString );
For-In variable r is just the IQuery instance, if omitted in the loop body it all works just the same because of the with statement context. The For-In and GetEnumerator() doesn't really do anything that a While and a MoreRows() helper couldn't. Something like:
with conn.GetQuery()
.Prep( 'log msg', 'select a from foo where bar = :bar', [barVal])
.Open() do
while MoreRows() do
result.Add( Fields
I'd like for the GetEnumerator to return something more useful, but I haven't come up with something useful yet. I was thinking maybe I could make the Rows() method take a reference to a function, and then the loop variable type would be whatever the function returns, but without lambda syntax that would be pretty clunky, and I haven't needing anything like that.
Anyway, last example, full syntax to show how clean it tends to be. The app code usually doesn't have much in the way of try..expect/finally blocks, because the interfaces release themselves, and the transaction processing is all handled by the query wrapper:
procedure TMyClass.UpdateCows( const inParams : TList );
begin
with conn.GetQuery().Prep( 'cow example',
'update or insert into Cows (ID, Name) values (:i, :n) matching (ID)') do
for p in inParams do
Exec([p[0], p[1]]);
end;
I also hate commit (or rollback) in the finally..end part. I prefer Uwe Raabe style.
ReplyDeleteDavid Knaack Interesting approach, thanks for sharing!
ReplyDeleteDorin Duminica Well, half the point was to throw it to the wolves :P
ReplyDeleteAnother thing I've played with using this for..in abuse is safe locking:
procedure SyncedProc(proc: TProc; cs: TCriticalSection);
var
lock: TLock;
begin
for lock in Synchronized(cs) do
begin
// In critical section
proc();
// raise Exception.Create('Doh!');
end;
end;
This was actually the first thing I tried, sorely missing the RAII style locking in C++.
Anyway, thanks for the feedback :)
Asbjørn Heid It seems like using 'using' in C#, isn't it ?
ReplyDeleteIgor Schevchenko Yes, that's what I was trying to mimic. Would have been nice with the actual keyword, given that the infrastructure is essentially there :)
ReplyDeleteFor a 'using' style pattern, maybe something like the sample program below? The lack of type inference makes it kind of clunky, and you can't declare a generic method with out class wrapper, so I just named the class 'Using' and gave it a method called 'a', so the syntax reads nicely.
ReplyDeleteprogram Project1;
{$APPTYPE CONSOLE}
uses
SysUtils,
TypInfo,
Classes,
SyncObjs;
type
Using = class
public class procedure a(i:T; p :TProc);
end;
ITest = interface
procedure Go;
end;
TITest = class( TInterfacedObject, ITest )
procedure Go;
destructor Destroy; override;
end;
TCSWrap = class( TObject )
strict private
fcs : TCriticalSection;
public
constructor Create( cs : TCriticalSection );
destructor Destroy; override;
end;
{ g }
class procedure Using.a(i: T; p: TProc);
begin
try
p(i);
finally
// If i is a class, free it. Interfaces will
// scope out at the end of this method.
if PTypeInfo(TypeInfo(T))^.Kind = tkClass then
FreeAndNil( i );
end;
end;
var
cs : TCriticalSection;
{ TCritSec }
constructor TCSWrap.Create( cs : TCriticalSection );
begin
fcs := cs;
fcs.Acquire;
writeln( 'CS Acquired' );
end;
destructor TCSWrap.Destroy;
begin
fcs.Release;
writeln( 'CS Released' );
inherited;
end;
{ TITest }
destructor TITest.Destroy;
begin
writeln( 'TITest.Destroy' );
inherited;
end;
procedure TITest.Go;
begin
writeln( 'interface test' );
end;
begin
cs := TCriticalSection.Create;
try
// using a class. Construct an instance and pass in,
// the 'using' provides a var ref in the anon method,
// and frees the instance when done.
Using.a( TStringList.Create, procedure(b:TStringList)
begin
b.Add('test');
end);
// Interfaces scope out in the 'using' routine and so
// will be released there rather than hanging around
// until the end of this method.
Using.a( TITest.Create, procedure(i:ITest)
begin
i.Go;
end);
// Automatic acquire and release of a critical section
// with a critical section wrapper.
Using.a( TCSWrap.Create(cs), procedure(c:TCSWrap)
begin
WriteLn( ' crit sec');
end);
Writeln( 'Done' );
finally
cs.Free;
end;
ReadLn;
end.
Using it for a CS like that is less efficient that the SyncedProc() example above though, I was mostly looking for a nail to use as an example with that, you wouldn't actually use it that way.
ReplyDeleteMaybe:
class procedure Using.CS(i: TCriticalSection; p: TProc);
begin
try
i.Acquire;
p;
finally
i.Release;
end;
end;
so:
Using.CS( existingCS, procedure() begin
DoStuff;
end);