[bug_fixed] Shortcut for Execute in TZConnection

[wahono]

Hi, mdaems.

I am MSEide users. As I tell you in MSEide forum, I want to use ZeosLib in MSEide. AFAIK, sometimes we need to execute SQL directly to database without care the resultset. For example for sql syntax : update, insert, delete, create database, create table, etc.

AFAIK, currently we should use IZStatement to do it. like this.
var conn: TZConnection;
stmt: IZStatement;
res1: Boolean;
...
conn.Connect;
stmt := conn.DbcConnection.CreateStatement;
res1:= stmt.Execute('update employee set active=true);
stmt:=nil;

My propose is cut these syntax to TZConnection only, like this :
var conn: TZConnection;
res1: Boolean;
...
conn.Connect;
res1:= conn.Execute('update employee set active=true);

I add new code like this in ZConnection.pas:
TZConnectio = class(.....
protected
FStatement: IZStatement;
public
function Execute(const SQL: string): Boolean;

I modified this procedure :

procedure TZConnection.Connect;
...
if not FConnection.IsClosed then
FStatement:= FConnection.CreateStatement;
DoAfterConnect;
end;

procedure TZConnection.Disconnect;
....
try
...
FStatement:=nil;

I hope it can be usefull efficient coding.

regards,
wahono

[mdaems]

Hi Wahono,

You've the honor to be the first one to post a MSE* related question.
Actually, it's more a feature request, I suppose.

And just like you I've been looking for this feature recently. (I think I've read on MSE too much already )

I just don't understand why you would add a Connection variable FStatement to do this. As it's just a 'one shot' query without resultset there's no need to remember it until the connection is destroyed.
What would you think about a function like this (schematic) :

Code: Select all

function TZConnection.ExecuteDirect(SQL : String) boolean;
  stmt : IZStatement;
begin
  stmt := conn.DbcConnection.CreateStatement;
  result:= stmt.Execute(SQL);
  stmt:=nil; 
end;

So I'll move this post to the feature requests forum now. If you can provide and test the patch, please do so and post it here. If not, you'll have to wait a little more and keep doing it the more 'inefficient' way. I'm concentrating on the 6.6 release now... New features aren't added anymore, sorry. When I get your patch I can add it to SVN testing branch, however.

Just an other remark : the 'normal' method you did describe using the IZStatement object isn't as normal as you think. For most Zeos users this is 'advanced programming using zeos'.
The normal way of doing this
- Create TZConnection object (design or runtime)
- Create TZQuery object (design or runtime)
- Set ZQuery1.SQL.Text
- Call ZQuery1.ExecSQL

Mark

[mdaems]

I added this feature to SVN testing branch. It's not intended to be included in release 6.6, but the patch is easy for those who want to use it right now.

I also added this feature to the test suite to avoid it gets broken in the future.

SVN rev. 335.

Mark

[wahono]

I agree with you.

thanks

[cipto_kh]

After a short discussion with Mark, I have an idea of this:
maybe it's better to use function overloading than using different name for one function that have same meaning, I mean it should be like this:

function ExecuteDirect(SQL:string):boolean;
function ExecuteDirect(SQL:string; var rowAffected:integer):boolean;

The return value is same, if the execute is success then it return true otherwise it's false. The second is special feature if user want to know how many row affected by insert, update or delete command for example.

[wahono]

It look more perfect, thanks. Hi. cipto_kh, where are you living in Indonesia?

[mdaems]

Reworked the code a little to reflect cipto's idea.
Other changes : un-duplicated the code and added a check to avoid an av when not connected (now there's an exception).

Now the functions signatures are :

function ExecuteDirect(SQL:string):boolean;
function ExecuteDirect(SQL:string; var RowsAffected:integer):boolean;

SVN rev. 336
Also available for download in the most recent TESTING branch snapshot.

Please test and report problems.

Normally Transaction behaviour should be identical to the behaviour of the Query components. i.e. Taking into account Transaction isolation levels, Autocommit, and the like. But... it's not tested.