Trigger - File write 1

[RichRuiz]

Is it possible to write a trigger that would write a blob to a file?

 

[Mercury2]

Possibly but clarify your requirement.

 

[RichRuiz]

That was a pretty weak question. I've actually revised the requirement after further research. I have a project where I want to create a trigger (before insert) that calls a UDF that would use a java function to modify a PDF that is stored in a blob field before inserting it into a table. I think it is doable but I've never attempted anything like this before and my DB2 experience was limited and years ago. Im a java guy and I can do the java part. Its' DB2 on AS400.

Here is my first effort at the triggers and udf...

Trigger..

create trigger spawnStamper
before insert of pdfField on pdfReportTable
referencing new as newrow
for each row
mode db2sql
when
(pdfField IS NOT NULL)
(SET newrow.pdfField = stamppdf(newrow.pdfField) )


UDF...

create function stamppdf(pdf BLOB)
returns BLOB
fenced
variant
no sql
external action
language java
parameter style java
external name 'JavaUDF!stamperpdf'

Any comments are appreciated.

 

[Mercury2]

you may use the function straightforwardly on a sql update statement in the trigger as below. I think that you'd better off fire the trigger AFTER insert to allow the update instead.

update MyFile set MyBlobField = stamppdf(MyBlobField)
where ...

 

[RichRuiz]

The problem is another application is inserting the record and I need to add a watermark to the PDF before it is saved. My biggest concern is passing the BLOB field back and forth. I'm not sure if the binary data will be passed as I need it to be. I think I may have to do something to the BLOB before I pass it to the java class.
I'm proposing this solution without access to a DB2 installation to test against so I cannot test anything. I just want to make sure my methodology will work.

Thanks for you help.

 

[Mercury2]

I'm a RPG programmer who doesn't know Java. Anyway this is what I'd do.

1/ Modify the function

create function stamppdf(pdf  [b]varchar([i]length)[/i][/b] )

2/ Cast blob column as varchar like the following

(SET newrow.pdfField = stamppdf( CAST( newrow.pdfField as VARCHAR([i]length)[/i] ) )