At 20:43 Tuesday, 09.08.2005 +0000, Shaq wrote:

>Can you give me some insight on how you set it up?
>
>I did the following:
>Change DMLUSER to DML_CONNECTION in IBO$DMLCACHE table.
>When my trigger fires it inserts the current connection value into the
>field.
>
>On my client application which listens to this I get the following error:
>conversion error from string "SYSDBA".
>
>Do I have to set up something else to make it work using the current
>connection instead of the current user?

Sorry, I forgot to mention this: Instead of using the TIB_SynchCursor I put
these components on my main datamodule:

Cur_NewDMLRecords: TIB_Cursor
SQL:
SELECT DMLID, DMLACTION, KEYFIELDS,
KEYVALUE1, KEYVALUE2, KEYVALUE3, KEYVALUE4,
DMLUSER, DMLTIMESTAMP
FROM IBO$DMLCACHE
WHERE (DMLID > :xLastID)
AND (DMLCONNECTION <> CURRENT_CONNECTION)
AND ((DMLCONNECTION > 0) OR (DMLCONNECTION = -CURRENT_CONNECTION))

Explanation of SQL where clause:
(DMLID > :xLastID)
-> only get new DML cache records
(DMLCONNECTION <> CURRENT_CONNECTION)
-> ignore updates made by the current connection
((DMLCONNECTION > 0) OR (DMLCONNECTION = -CURRENT_CONNECTION))
-> Normally updates made through the current connection are announced by
the datasets themselves. This allows me to insert a record with negative
ConnectionId into IBO$DMLCACHE from within a stored procedure to force the
current connection's datasets to refresh.

IBEv_AllEvents: TIB_Events
events:
'IBO$DMLCACHE'

procedure TM_DM_Cn.IBEv_AllEventsEventAlert( Sender: TObject;
AEventName: String;
AEventCount: Integer );
var
sActionType : String;
KeyFieldNames : String;
KeyFieldValues : Variant;
DMLCacheItemType: TIB_DMLCacheItemType;
begin
if (AnsiUpperCase(AEventName) = 'IBO$DMLCACHE') then begin

DMLCacheItemType := ditDelete;
with Cur_NewDMLRecords do begin
IB_Transaction.Commit;
Prepared := true;
if (LastProcessed_DML_ID < 1) then
GetLastDMLID;
Params[0].AsInteger := LastProcessed_DML_ID;
First;
while not EoF do begin
// KeyFieldNames
KeyFieldNames := Fields[2].AsString;
// KeyFieldValues
if Fields[4].IsNull and Fields[5].IsNull and Fields[6].IsNull then
begin
KeyFieldValues := Fields[3].Value;
end else begin
// using only KeyFieldValues[0] since
// all PKs are one Integer field.
DoWarnMultipleKeyFields( KeyFieldNames );
KeyFieldValues := VarArrayCreate( [0, 3], varVariant );
KeyFieldValues[0] := Fields[3].Value;
KeyFieldValues[1] := Fields[4].Value;
KeyFieldValues[2] := Fields[5].Value;
KeyFieldValues[3] := Fields[6].Value;
end;
// DMLCacheItemType
sActionType := AnsiUpperCase( Fields[1].AsString );
if (sActionType = 'I') then
DMLCacheItemType := ditInsert
else if (sActionType = 'U') then
DMLCacheItemType := ditEdit
else if (sActionType = 'D') then
DMLCacheItemType := ditDelete
else begin
LastProcessed_DML_ID := Max( Fields[0].AsInteger,
LastProcessed_DML_ID );
Cur_NewDMLRecords.Next;
Continue;
end;
// process item
AnnounceDMLCacheItemKey( KeyFieldNames, KeyFieldValues,
DMLCacheItemType );
LastProcessed_DML_ID := Max( Fields[0].AsInteger,
LastProcessed_DML_ID );
Next;
end;
Close;
// cursor has its own transaction
IB_Transaction.Commit;
end;
end;
end;

Here LastProcessed_DML_ID is a private integer property of the datamodule
initialized to -1.
The method GetLastDMLID does something like
LastProcessed_DML_ID := SELECT MAX(DMLID)-1 FROM IBO$DMLCACHE;

HTH,
Markus