Re: Dreaming About Redesigning SQL

Steve Lancour <stevel_at_lancour.com> wrote in message news:<M_GdnSiEV8OzRAKiRVn-ug_at_comcast.com>...
>
> Consider this example:
>
> subroutine UpdatePersonPhone (PersonKey, PhoneKey, ErrorMessage)
> include CommonBlock ;* commonly-used variables and file references
> ErrorMessage=""
> begin work
> readu PersonRec from F.Persons,PersonKey else ;* sets a record lock
> ErrorMessage<-1>="Cannot read Person record ":PersonKey
> end
> readu PhoneRec from F.Phones,PhoneKey else ;* sets a record lock
> ErrorMessage<-1>="Cannot read Phone record ":PhoneKey
> end
> if ErrorMessage#"" then
> release F.Persons,PersonKey ;* release record lock
> release F.Phones,PhoneKey ;* release record lock
> end else
> locate(PhoneKey,PersonRec,2;PhonePos;'AR') else
> PersonRec=insert(PersonRec,2,PhonePos;PhoneKey)
> end
> locate(PersonKey,PhoneRec,2;PersonPos;'AL') else
> PhoneRec=insert(PhoneRec,2,PersonPos;PersonKey)
> end
> write PersonRec on F.Persons,PersonKey ;* write releases lock
> write PhoneRec on F.Phones,PhoneKey ;* write releases lock
> end
> commit work
> return ;* to calling program

How about something like:
===
subroutine UpdatePersonPhone (PersonKey, PhoneKey, ErrorMessage) include CommonBlock ;* commonly-used variables and file references ErrorMessage=""
*
begin work
*
LockFlag=1
call ReadSub(LockFlag,PersonRec,'Persons',PersonKey,'',LockedFlag,FoundFlag) begin case
case LockedFlag
  ErrorMessage<-1>="Unable to obtain item-lock for Persons ":PersonKey case not(FoundFlag)
  ErrorMessage<-1>="Cannot read Persons record ":PersonKey case 1
  call ReadSub(LockFlag,PhoneRec,'Phones',PhoneKey,'',LockedFlag,FoundFlag)   begin case
  case LockedFlag
    ErrorMessage<-1>="Unable to obtain item-lock for Phones ":PhoneKey   case not(FoundFlag)
    ErrorMessage<-1>="Cannot read Phones record ":PersonKey   case 1
    locate(PhoneKey,PersonRec,2;PhonePos;'AR') else       PersonRec=insert(PersonRec,2,PhonePos;PhoneKey)     end
    locate(PersonKey,PhoneRec,2;PersonPos;'AL') else       PhoneRec=insert(PhoneRec,2,PersonPos;PersonKey)     end
    call WriteSub(0,PersonRec,'Persons',PersonKey,WriteError)     if WriteError="" then
      call WriteSub(0,PhoneRec,'Phones',PhoneKey,WriteError)     end
    if WriteError then ErrorMessage<-1>=WriteError   end case
end case
if ErrorMessage#"" then
  rollback work
end else
  commit work else ErrorMessage<-1>='Unable to commit transaction' end
return ;* to calling program
===

This way, we have the ability to insert a data abstraction layer and centrally enforce data integrity. 'Phones' & 'Persons' might be actual file names or they might be Q-pointers, Super-Q-pointers, FSI or OSFI or files on the host OS - whatever. They might not even exist as files in their own right - they could be attributes of some completely different file - or whatever. They could have any integrity constraints to satisfy - of whatever complexity.

Regards
Mike. Received on Thu Oct 30 2003 - 08:46:25 CET