Subject Re: add timestamp to text BLOB
Author Rick Roen
Thanks Helen,

I'll avoid the "unhappy life" senario and not attempt to change the
BLOB in an SP.

I considered the extra fields for timestamp and varchar for user,
but anyone can update this note BLOB and I want some automatic way
of "stamping" the message.

I think I will write it in from the Delphi program where it checks
for a modified variable on the BLOB control and adds the extra info


> A blob - even a text blob - isn't a string. You can't edit a blob
> all: "editing" a blob always means "replacing an existing blob
> a new blob".
> Your null logic for the UPDATING test is going to come to grief if
> you have OLD.NOTE null and NEW.NOTE not null or vice versa.
> When dealing with your string variable, the whole thing is going
> fall apart if your substring returns the whole 8190 bytes to the
> string variable, since appending the username and timestamp will
> ~ 60 bytes and overflow the variable. Doing what you want to do
> also add other problems like truncating the blob and/or reaching a
> point where an ever-increasing number of user/timestamp appendages
> will eventually cause shorter strings to overflow.
> If you still want to persevere with this idea, you could try a UDF
> such as Str2Blob in fbudf (making sure that you have defined the
> function to your database to take an input string of adequate
> and declare a local TEXT blob variable into which to feed the
> string, finally assigning this blob to NEW.NOTE. There are
> gotchas with this if you allow the application to update the
> more than once in the same transaction (or allow both insert and
> update of the record without enforcing a commit between the ops).
> OTOH, plan a happy life and consider adding a timestamp and a
> column to the table for last update date and updater,
> and don't try to tinker with NOTE at all. Users will be a lot
> happier to know that their creation is not going to be blown away
> some invisible malevolent hand....
> ./heLen