🚀 go-pugleaf

"Gelein van de Voorde" <gelein@gvsnet.nl> wrote in message 
news:463b8892$1@newsgroups.borland.com...
> Dan Palley wrote:
>
>> "Gelein van de Voorde" <gelein@gvsnet.nl> wrote in message
>> news:463a3fa9@newsgroups.borland.com...
>> > See revision history for details.
>> > http://www.gvsnet.nl/ibconsole
>>
>> Gelein, I love the fact that queries now run in a separate thread.
>> It makes the system much more responsive.
>>
>> If you don't mind, I'd like to make a few more requests that would
>> make my life much easier:
>>
>> - The ability to cancel a running query.  Since the query itself is
>> already running in a separate thread, this should be straightforward.
>> - Can we specify the type of transaction to create in the ISQL
>> window? Right now, it's read-committed, read/write, but a lot of
>> times I'm just running queries and read-only would be much better.
>>
>> Thanks,
>>
>> Dan Palley
>> TRAMS, Inc.
>
> Dan,
> Cancelling a running query is not so easy as it might look. The IBX
> does not support it, so it has to be done the hard way. I have been
> working on that, but haven't find a reliable solution yet. Meanwhile
> you can use the performance monitor to disconnect the thread. IBConsole
> should handle this gracefully.
> I hope to implement the possibility of specifying the transaction type
> in the next beta release.

I have lots of experience in query cancellation as we've supported it in our 
product for a couple of years now.  I have an approach that works either via 
the API or via the monitoring tables.  It's pretty consistent for me.

Cancelling via the API is pretty easy because you just need to provide the 
statement handle:

Procedure CancelStatement(StatementHandle:TISC_STMT_HANDLE);
Var SV : TStatusVector;
Begin
  If IBClientMajorVerNo<7 Then Exit;
 {$IFDEF VER140}
  isc_dsql_free_statement(@SV, @StatementHandle, DSQL_Cancel);
 {$ELSE}
  GetGDSLibrary.isc_dsql_free_statement(@SV, @StatementHandle, DSQL_Cancel);
 {$ENDIF}
End;

Cancelling via the monitoring tables is trickier because you have to get a 
transaction id first and you have to create a separate DB connection:

function isc_transaction_info(
    status_vector             : PISC_STATUS;
    tran_handle               : PISC_TR_HANDLE;
    item_list_buffer_length   : Short;
    item_list_buffer          : PChar;
    result_buffer_length      : Short;
    result_buffer             : PChar
  ): ISC_STATUS; stdcall; External 'gds32.dll';

function GetTransactionId(tr_handle:TISC_TR_HANDLE):Integer;
Var Tra_Info:Array[0..31] of Char;
    Status:Array[0..20] of ISC_Status;
    Tra_ItemList:Array [0..1] of Char;
Begin
  Tra_ItemList:=^D^@;
  FillChar(tra_Info, SizeOf(tra_info), #0);
  isc_transaction_info(@Status, @tr_handle, 1, @Tra_ItemList, 32, 
@Tra_info );
  Result:=Integer(Ord(tra_info[6])*$1000000)+Ord(tra_info[5])*$10000+Ord(tra_info[4])*$100+Ord(tra_info[3]);
End;

Function CancelTransactionStatement(Const db:TIBDatabase; 
tr_id:Integer):Boolean;
Var CancelQuery:TIBSQL;
    CancelTrans:TIBTransaction;
    CancelDB:TIBDatabase;
begin
  If (IBClientMajorVerNo<7) Or (IBODSVersion<11) Then
  Begin
    Result:=False;
    Exit;
  End;
  CancelDB:=TIBDatabase.Create(Nil);
  CancelQuery:=TIBSQL.Create(Nil);
  CancelTrans:=TIBTransaction.Create(Nil);
  Try
    CancelDB.DatabaseName:=db.DatabaseName;
    CancelDB.Params:=db.Params;
    CancelDB.LoginPrompt:=False;
    CancelDB.Connected:=True;
    CancelQuery.Database:=Canceldb;
    CancelTrans.DefaultDatabase:=Canceldb;
    CancelQuery.SQL.Text:='update tmp$statements set tmp$state=''CANCEL'' 
where tmp$transaction_id='+IntToStr(tr_id);
    CancelQuery.Transaction:=CancelTrans;
    CancelTrans.StartTransaction;
    Try
      CancelQuery.ExecQuery;
      CancelTrans.Commit;
      Result:=True;
    Except
      CancelTrans.Rollback;
      Result:=False;
    End;
  Finally
    CancelTrans.Free;
    CancelQuery.Free;
    CancelDB.Free;
  End;
end;

Hope this helps,

Dan