Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

After Insert Trigger (Delphi DLL) won't work in ADS 8.0

2 posts in Trigger Last posting was on 2006-06-01 20:07:46.0Z
Jim Muir Posted on 2006-05-30 20:16:18.0Z
Date: Tue, 30 May 2006 16:16:18 -0400
From: Jim Muir <jpmuirNoSpamPlease@adelphia.net>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: After Insert Trigger (Delphi DLL) won't work in ADS 8.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 65.23.16.202
Message-ID: <447ca786@solutions.advantagedatabase.com>
X-Trace: 30 May 2006 14:13:58 -0700, 65.23.16.202
Lines: 118
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!65.23.16.202
Xref: solutions.advantagedatabase.com Advantage.Trigger:241
Article PK: 1136308

This is my second issue with triggers today..

Still cannot get my after insert dll trigger to work.

I have tested the code today in a simple unit outside of the dll using a
simple compiled unit ok.

I have tested the trigger on a non-production table identical to the
production table in the same Database with ARC and it works ok (creates
the folder - which is its function)

But on the production table it fails to work. I checked Ads_Err.adt and
it logs no error.

This is a similar problem that I was having with a script trigger, it
worked on copy tables but not the ones in use.


Here's my function code in the Delphi dll that used to work flawlessly
on Remote server 7.1 but which will not work on 8.0. The only change I
made was the root of the folder change to the new server on which we put
Advantage Database Server 8.0.0.8.



function FolderCreate //inside a compiled Delphi 7.1 Dll
(
ulConnectionID : UNSIGNED32; // (I) Unique ID identifying the user
causing this trig
hConnection : ADSHANDLE; // (I) Active ACE connection handle user
can perform
// operations on
pcTriggerName : PChar; // (I) Name of the trigger object in the
dictionary
pcTableName : PChar; // (I) Name of the base table that
caused the trigger
ulEventType : UNSIGNED32; // (I) Flag with event type (insert,
update, etc.)
ulTriggerType : UNSIGNED32; // (I) Flag with trigger type (before,
after, etc.)
ulRecNo : UNSIGNED32 // (I) Record number of the record being
modified
) : UNSIGNED32;
{$IFDEF WIN32}stdcall;{$ENDIF}{$IFDEF LINUX}cdecl;{$ENDIF} // Do not
change the prototype.
var
oConn : TAdsConnection;
oQuery : TAdsQuery;
sCaseno : String;
sName : String;
sCnslr : String;
sGroup : String;
dAdmit : TDateTime;
fldr : String;
sYr : String;

begin
// Result is currently reserved and not used. Always return zero.
Result := 0;

// Allocate a connection object using an active connection, no need
to open it after this.

oConn := TAdsConnection.CreateWithHandle( nil, hConnection );
oQuery := TAdsQuery.Create( nil );

try
try
oConn.Name := 'conn';

// This trigger obtains basic data from a new admission (just added)
// and creates a client folder at a specific location on the server
// This uses a forcedirectory type function which ensures that
the folder is made

oQuery.DatabaseName := 'conn';
// Get Client name and case number just added
oQuery.SQL.Text := 'SELECT LName,MName,FName,Name, CaseNo,
Counselor, [Group] grp, AdmitDate FROM __new';
oQuery.Open;

// Set local vars.. then close the query.

sCaseno := oQuery.fieldbyName('Caseno').asString;
sName :=
TrimRight(oQuery.fieldbyName('LName').asString)+',
'+TrimRight(oQuery.fieldbyName('FName').asString)+'
'+LeftStr(oQuery.fieldbyName('MName').asString,1)+'.';
sCnslr := oQuery.fieldbyName('Counselor').asString;
sGroup := oQuery.fieldbyName('Grp').asString;
dAdmit := oQuery.fieldbyName('AdmitDate').asDateTime;
sYr:= IntToStr(Yearof(dAdmit));
fldr:= '\\Optimus-Prime\D$\IBHiS\Medical
Records\'+sYr+'\'+sName+' '+trimRight(sCaseno);

oQuery.Close;

// Now lets attempt to create the folder using a recursive
// function that checks if the folder exists and creates it
//(and its subfolders) if it does not exist)

JasSutils.EnsureFolders(fldr);

except
on E : EADSDatabaseError do
SetError( oConn, E.ACEErrorCode, E.message );
on E : Exception do
SetError( oConn, 0, E.message );
end; // Try Except

finally
oConn.Free;
oQuery.free;
end; // Try Finally

end;


Jim Muir Posted on 2006-06-01 20:07:46.0Z
Date: Thu, 01 Jun 2006 16:07:46 -0400
From: Jim Muir <jpmuirNoSpamPlease@adelphia.net>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: After Insert Trigger (Delphi DLL) won't work in ADS 8.0
References: <447ca786@solutions.advantagedatabase.com>
In-Reply-To: <447ca786@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 65.23.16.202
Message-ID: <447f483d@solutions.advantagedatabase.com>
X-Trace: 1 Jun 2006 14:04:13 -0700, 65.23.16.202
Lines: 74
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!65.23.16.202
Xref: solutions.advantagedatabase.com Advantage.Trigger:245
Article PK: 1136311


Jim Muir wrote:
> This is my second issue with triggers today..
>
> Still cannot get my after insert dll trigger to work.
>

My solution...

I took the code I had in the orignal trigger that worked in 7.1
and created a working stored procedure (called sp_CreateClientFolder)

After finally getting some sense to writing trigger scripts I wrote the
trigger that would fire the stored procedure which in turn created a
folder path on the server. Everything works slick as a whistle so far.

here is the trigger code that fires the stored procedure. If you need
the stored procedure code, let me know.

Jim



declare @Caseno String, @name string;
declare RosterNew CURSOR;
declare CaseNoNew CURSOR;


@Caseno = '';

OPEN RosterNew AS SELECT * from __New;
OPEN CaseNoNew AS Select IFNULL(__New.caseno,'') caseno FROM __New;


TRY
FETCH RosterNew;
FETCH CaseNoNew;
@caseno = CaseNoNew.caseno;
@name = RosterNew.Name;

if @caseno > '' then

EXECUTE PROCEDURE sp_CreateClientFolder( @caseno );\

// this section creates an alert message for my staff...

INSERT INTO LinkTo_IBHiS_System.AlertMsg (
Due,
ShortMsgText,
MsgText,
Scope,
Category,
Priority,
MsgType,
Active)

Values (
Now(),
'Client Status Update',
'Admission Notice: '+' '+@Name+' '+@Caseno,
'Domain Users',
'CeNotice',
'A1',
1010,
TRUE);

else return;
endif;

FINALLY

CLOSE RosterNew;
CLOSE CaseNoNew;

END TRY;