Subject Re: [firebird-php] [info/tip] dynamically created generators (inside stored procedures).
Author Jochem Maas
Lester Caine wrote:
> Jochem Maas wrote:


>>I'm not talking about creating a database and it's 'fixed' generators (and
>>relevant 'auto-inc'-like triggers) - by 'fixed' I mean that a table such as COMPANY
>>would probably have it's own generator calld something like GEN_COMPANY_ID - such
>>a generator doesn't need to be created on the fly.
>>what I am talking about is created a generator on the fly as a reaction to the
>>insertion of a record in a particular table (in my case each row represents a
>>prizedraw and each prizedraw requires it's own generator, given that the client
>>only starts creating 'prizedraws' after I deliver the app means I can't create
>>the generators for the 'prizedraws' (pretty obvious given thgat the 'prizedraws'
>>don't exist when I ship the app.) - another important thing to consider is atomicity:
>>I want my record to be inserted and the generator created as one operation
>>(therefore inserting the record and then letting php extension/code create the
>>generator is not an option - and if it had been it would have been easy).
> Don't see why creating the required generator THEN inserting the record
> would cause a problem? If the generator can't be created for some reason
> you can flag that? That is the approach that is used for polls. Each has
> it's own generator. Even works without wrapping it in a transaction.
> The code you had so much trouble with looks similar to that contained in
> ADOdb - so since you had talked about not re-inventing the wheel .....

its a 32 line implementation of a stored procedure (including comments) -
I got stuck on 2 issues which we're due a lack of knowledge/understanding regarding
stored procedures (which is often the case ;-)

I don't consider what I built as re-inventing the wheel. I also think (in far as I
can tell from your description) that my implementation is technically better than the adoDB
implementation (granted that the adoDB implementation is generic andf mine is bound to
a specific table in my DB) because mine garantees atomicity where as the adoDB one
does not (it can't given that it's a 2 stage process that occur in different places, namely
inside the firebird process and inside the php process).

for instance what happens if you succesfully create the generator then the insert fails?
then you have to remove the generator, right? what if that fails? - I'd rather
let firebird sort that out.