Duh....I'm not actually going to open the VFP Box
But once you register it
you get the rights to distribute - MSDE 1.0
Which despite it's unassuming name is the best thing that MS have done
recently
(which is why no-one seems to know about it)
MSDE is a full MS SQL 7 Server that runs on a 95/98/2000 Workstation - and
supports multiusers
(MS save no more than 5 but they would wouldn't they....)
Sort of like Interbase Workstation but....
a/ Faster
b/ More Capable
c/ FREE!
May I never have to deal a stupid ISAM again
bye Btrieve, DBase, Paradox, Jet
Now I get to code like
CREATE TABLE AddressLine
(
AddressID INT NOT NULL,
LineNumber INT NOT NULL IDENTITY,
AddressLine Description NULL,
AddressLineTypeID ShortObjectID NOT NULL,
CONSTRAINT PK_AddressLine PRIMARY KEY NONCLUSTERED
(AddressID,LineNumber)
)
go
CREATE NONCLUSTERED INDEX AddressLineType ON AddressLine (AddressLineTypeID)
go
CREATE TABLE AddressLineType
(
AddressLineTypeID ShortObjectID NOT NULL,
Description Description NULL,
CONSTRAINT PrimaryKey2 PRIMARY KEY NONCLUSTERED
(AddressLineTypeID)
)
go
CREATE TABLE Address
(
AddressID INT NOT NULL IDENTITY,
CreateDate DATETIME NOT NULL,
CONSTRAINT PrimaryKey3 PRIMARY KEY NONCLUSTERED
(AddressID)
)
go
/* Insert Trigger 'T_I_AddressLine' for Table 'AddressLine' */
CREATE TRIGGER T_I_AddressLine ON AddressLine FOR INSERT AS
BEGIN
DECLARE
@row_count INT,
@null_row_count INT,
@error_number INT,
@error_message VARCHAR(255)
SELECT @row_count = @@rowcount
IF @row_count = 0
RETURN
/* When inserting a row in child 'AddressLine' ,the Foreign Key must be Null or exist
in Parent 'AddressLineType' */
IF UPDATE(AddressLineTypeID)
BEGIN
SELECT @null_row_count =
(
SELECT COUNT(*)
FROM inserted
WHERE AddressLineTypeID is null
)
IF @null_row_count != @row_count
IF (
SELECT COUNT(*)
FROM AddressLineType p, inserted i
WHERE p.AddressLineTypeID = i.AddressLineTypeID
)
!= @row_count - @null_row_count
BEGIN
SELECT @error_number=30001,
@error_message='Cannot insert child in
"AddressLine" as its Foreign Key does not exist in "AddressLineType".'
GOTO error
END
END
/* When inserting a row in child 'AddressLine' ,the Foreign Key must be
Null or exist in Parent 'Address' */
IF UPDATE(AddressID)
BEGIN
SELECT @null_row_count =
(
SELECT COUNT(*)
FROM inserted
WHERE AddressID is null
)
IF @null_row_count != @row_count
IF (
SELECT COUNT(*)
FROM Address p, inserted i
WHERE p.AddressID = i.AddressID
)
!= @row_count - @null_row_count
BEGIN
SELECT @error_number=30001,
@error_message='Cannot insert child in
"AddressLine" as its Foreign Key does not exist in "Address".'
GOTO error
END
END
RETURN
/* Error Handling */
error:
RAISERROR @error_number @error_message
ROLLBACK TRANSACTION
END
go
/* Update Trigger 'T_U_AddressLineType' for Table 'AddressLineType' */
CREATE TRIGGER T_U_AddressLineType ON AddressLineType FOR UPDATE AS
BEGIN
DECLARE
@row_count INT,
@null_row_count INT,
@error_number INT,
@error_message VARCHAR(255)
SELECT @row_count = @@rowcount
IF @row_count = 0
RETURN
/* Cascade the Update of the Primary Key of 'AddressLineType' into its
children in 'AddressLine' */
IF UPDATE(AddressLineTypeID)
BEGIN
UPDATE AddressLine
SET AddressLineTypeID = i.AddressLineTypeID
FROM AddressLine c, inserted i, deleted d
WHERE c.AddressLineTypeID = d.AddressLineTypeID
AND (i.AddressLineTypeID != d.AddressLineTypeID)
END
RETURN
/* Error Handling */
error:
RAISERROR @error_number @error_message
ROLLBACK TRANSACTION
END
go
/* Delete Trigger 'T_D_AddressLineType' for Table 'AddressLineType' */
CREATE TRIGGER T_D_AddressLineType ON AddressLineType FOR DELETE AS
BEGIN
DECLARE
@row_count INT,
@error_number INT,
@error_message VARCHAR(255)
SELECT @row_count = @@rowcount
IF @row_count = 0
RETURN
/* Parent in 'AddressLineType' cannot be deleted if children exist in
'AddressLine' */
IF EXISTS (
SELECT 1
FROM AddressLine c, deleted d
WHERE c.AddressLineTypeID = d.AddressLineTypeID
)
BEGIN
SELECT @error_number=30005,
@error_message='Children exist in "AddressLine". Cannot
delete parent "AddressLineType".'
GOTO error
END
RETURN
/* Error Handling */
error:
RAISERROR @error_number @error_message
ROLLBACK TRANSACTION
END
go
/* Update Trigger 'T_U_Address' for Table 'Address' */
CREATE TRIGGER T_U_Address ON Address FOR UPDATE AS
BEGIN
DECLARE
@row_count INT,
@null_row_count INT,
@error_number INT,
@error_message VARCHAR(255)
SELECT @row_count = @@rowcount
IF @row_count = 0
RETURN
/* The Primary Key of 'Address' cannot be modified if children exist in
'AddressLine' */
IF UPDATE(AddressID)
BEGIN
IF EXISTS (
SELECT 1
FROM AddressLine c, inserted i, deleted d
WHERE c.AddressID = d.AddressID
AND (i.AddressID != d.AddressID)
)
BEGIN
SELECT @error_number=30004,
@error_message='Children exist in "AddressLine". Cannot
modify Primary Key in "Address".'
GOTO error
END
END
/* The Primary Key of 'Address' cannot be modified if children exist in
'StoreAddress' */
IF UPDATE(AddressID)
BEGIN
IF EXISTS (
SELECT 1
FROM StoreAddress c, inserted i, deleted d
WHERE c.AddressID = d.AddressID
AND (i.AddressID != d.AddressID)
)
BEGIN
SELECT @error_number=30004,
@error_message='Children exist in "StoreAddress". Cannot
modify Primary Key in "Address".'
GOTO error
END
END
/* The Primary Key of 'Address' cannot be modified if children exist in
'ApplicAddress' */
IF UPDATE(AddressID)
BEGIN
IF EXISTS (
SELECT 1
FROM ApplicAddress c, inserted i, deleted d
WHERE c.AddressID = d.AddressID
AND (i.AddressID != d.AddressID)
)
BEGIN
SELECT @error_number=30004,
@error_message='Children exist in "ApplicAddress".
Cannot modify Primary Key in "Address".'
GOTO error
END
END
/* The Primary Key of 'Address' cannot be modified if children exist in
'ClientAddress' */
IF UPDATE(AddressID)
BEGIN
IF EXISTS (
SELECT 1
FROM ClientAddress c, inserted i, deleted d
WHERE c.AddressID = d.AddressID
AND (i.AddressID != d.AddressID)
)
BEGIN
SELECT @error_number=30004,
@error_message='Children exist in "ClientAddress".
Cannot modify Primary Key in "Address".'
GOTO error
END
END
RETURN
/* Error Handling */
error:
RAISERROR @error_number @error_message
ROLLBACK TRANSACTION
END
go
/* Delete Trigger 'T_D_Address' for Table 'Address' */
CREATE TRIGGER T_D_Address ON Address FOR DELETE AS
BEGIN
DECLARE
@row_count INT,
@error_number INT,
@error_message VARCHAR(255)
SELECT @row_count = @@rowcount
IF @row_count = 0
RETURN
/* Delete all children in 'AddressLine' of parent 'Address' */
DELETE AddressLine
FROM AddressLine c, deleted d
WHERE c.AddressID = d.AddressID
/* Delete all children in 'StoreAddress' of parent 'Address' */
DELETE StoreAddress
FROM StoreAddress c, deleted d
WHERE c.AddressID = d.AddressID
/* Delete all children in 'ApplicAddress' of parent 'Address' */
DELETE ApplicAddress
FROM ApplicAddress c, deleted d
WHERE c.AddressID = d.AddressID
/* Delete all children in 'ClientAddress' of parent 'Address' */
DELETE ClientAddress
FROM ClientAddress c, deleted d
WHERE c.AddressID = d.AddressID
RETURN
/* Error Handling */
error:
RAISERROR @error_number @error_message
ROLLBACK TRANSACTION
END
go
create proc spAllocBalance @ContractID ObjectID
as
declare
@AllocationIncrement int,
@BalanceSiteWeeks int,
@StoresWeeksAlloc int,
@AllocWeeks int,
@Distribute char(1)
select
@Distribute = Distribute from Contract where ContractID = @ContractID
if @Distribute = 'Y' begin
select
@AllocationIncrement = b.AllocationIncrement from Contract a, Service b
where a.ContractID = @ContractID and a.ServiceID = b.ServiceID
exec spContractInfo @ContractID, @BalanceSiteWeeks OUTPUT
select
@StoresWeeksAlloc = Sum(DateDiff(week,b.ApplyDate,b.RemovalDate))
from Allocation a, ContractMedia b
where a.ContractID = @ContractID and
a.ContractID = b.ContractID and
a.ContractMediaID = b.ContractMediaID
/* Only Process if less than an allocation increment for each site */
if (@StoresWeeksAlloc * @AllocationIncrement > @BalanceSiteWeeks) and
(@BalanceSiteWeeks > 0) begin
declare AllocCursor Cursor for
select DateDiff(week,b.ApplyDate,b.RemovalDate)
from Allocation a, ContractMedia b
where a.ContractID = @ContractID and
a.ContractID = b.ContractID and
a.ContractMediaID = b.ContractMediaID
order by a.NumberOfSites
open AllocCursor
fetch next from AllocCursor into @AllocWeeks
while (@@FETCH_STATUS=0 and @BalanceSiteWeeks > 0)
begin
update Allocation set NumberOfSites = NumberOfSites +
@AllocationIncrement where current of AllocCursor
select @BalanceSiteWeeks = @BalanceSiteWeeks - (@AllocWeeks *
@AllocationIncrement)
select @BalanceSiteWeeks, (@AllocWeeks * @AllocationIncrement)
fetch next from AllocCursor into @AllocWeeks
end
close AllocCursor
deallocate AllocCursor
end
end
return(0)
go
CREATE VIEW vApplicPaySummary AS
SELECT ALL ApplicPayment.ApplicatorID , Applicator.Name ,
ApplicPayment.Amount ,
'Tax' = ApplicPayment.TaxAmount , 'Gross' = Amount + TaxAmount ,
ApplicPayment.ServiceID , ApplicPayment.Date
FROM ApplicPayment , Applicator
WHERE ApplicPayment.ApplicatorID = Applicator.ApplicatorID
go
CREATE VIEW vCurrentPayDate AS
SELECT ALL ServiceCalendar.Date , ServiceCalendar.ServiceID
FROM ServiceCalendar
WHERE PayApplicator = 'Y' and PayApplicatorComplete <> 'Y'
go
CREATE VIEW vValidPayDate AS
SELECT ALL ServiceCalendar.Date , ServiceCalendar.ServiceID
FROM ServiceCalendar
WHERE PayApplicator = 'Y'
go
CREATE VIEW vCurrentCommDate AS
SELECT ALL ServiceCalendar.ServiceID , ServiceCalendar.Date
FROM ServiceCalendar
WHERE PayCommission = 'Y' and PayCommissionComplete <> 'Y'
go
CREATE VIEW vActiveCalendar AS
SELECT ALL ServiceCalendar.*
FROM ServiceCalendar
WHERE StoreVisitComplete <> 'Y' or
PayApplicatorComplete <> 'Y' or
PayCommissionComplete <> 'Y' or
SalesReportComplete <> 'Y'
go
CREATE VIEW vMediaPaidtoStores AS
SELECT ALL ContractMedia.ContractID , ContractMedia.ContractMediaID ,
ContractMedia.ApplyDate ,
ContractMedia.RemovalDate , Contract.ServiceID ,
ContractMedia.ProductDescription ,
Store.PayHeadOffice
FROM ContractMedia , Contract , Allocation , Store
WHERE ContractMedia.ContractID = Contract.ContractID
AND ContractMedia.ContractID = Allocation.ContractID
AND ContractMedia.ContractMediaID = Allocation.ContractMediaID
AND Allocation.StoreID = Store.StoreID
AND ( Store.PayHeadOffice = 'N' )
go
CREATE VIEW vMediaPaidtoHO AS
SELECT ALL ContractMedia.ContractID , ContractMedia.ContractMediaID ,
ContractMedia.ApplyDate ,
ContractMedia.RemovalDate , Contract.ServiceID ,
ContractMedia.ProductDescription ,
Store.PayHeadOffice
FROM ContractMedia , Contract , Allocation , Store
WHERE ContractMedia.ContractID = Contract.ContractID
AND ContractMedia.ContractID = Allocation.ContractID
AND ContractMedia.ContractMediaID = Allocation.ContractMediaID
AND Allocation.StoreID = Store.StoreID
AND ( Store.PayHeadOffice = 'Y' )
go
CREATE VIEW vChainPaid AS
SELECT ALL Chain.ChainID , Chain.Name
FROM Store , Chain
WHERE exists
(select 1 from Store
where Store.ChainID = Chain.ChainID
and Store.PayHeadOffice = 'Y')
go
CREATE VIEW vStorePaid AS
SELECT ALL Store.StoreID , Store.Name
FROM Store
WHERE PayHeadOffice = 'N'
go
CREATE VIEW vAllocUpdate AS
SELECT ALL Allocation.NumberOfSites ,
ServiceResource.SitesPerMediaAllocation ,
Contract.Multiplier , Allocation.ContractID
FROM Allocation , ServiceResource , Contract
WHERE Allocation.StoreID = ServiceResource.StoreID
AND Allocation.ContractID = Contract.ContractID
AND ( Contract.ServiceID = ServiceResource.ServiceID )
go
CREATE VIEW vAllocDetail AS
SELECT ALL Allocation.ContractID , Allocation.ContractMediaID ,
Allocation.NumberOfSites ,
Store.Name
FROM Allocation , Store
WHERE Allocation.StoreID = Store.StoreID
go
CREATE VIEW vValidServiceFeeCode AS
SELECT ALL ApplicPaymentType.ApplicPaymentTypeID ,
ApplicPaymentType.Description ,
ApplicPaymentType.ServiceID
FROM ApplicPaymentType
WHERE not exists
(select 1
from
ServiceItem
where
ApplicPaymentType.ApplicPaymentTypeID = ServiceItem.ApplicPaymentTypeID
and
ApplicPaymentType.ServiceID = ServiceItem.ServiceID
)
go
CREATE VIEW vValidMediaApplyFeeCode AS
SELECT ALL ApplicPaymentType.ApplicPaymentTypeID ,
ApplicPaymentType.Description ,
ApplicPaymentType.ServiceID , Service.ServiceFeePayTypeID
FROM ApplicPaymentType , Service
WHERE ApplicPaymentType.ServiceID = Service.ServiceID
and not exists
(select 1
from
ServiceItem
where
ApplicPaymentType.ApplicPaymentTypeID = ServiceItem.ApplicPaymentTypeID
and
ApplicPaymentType.ServiceID = ServiceItem.ServiceID
)
go
Regards
Neven
----- Original Message -----
From: Alex Kouznetsov <[EMAIL PROTECTED]>
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
Sent: Wednesday, 8 September 1999 17:10
Subject: Re: [DUG]: Reason to buy Visual Fox Pro
> > I've finally found a reason to buy Visual Fox Pro - MSDE 1.0
> >
> > If you are considering DB dev you should look at this
>
> Look at what ?
>
> Also are you still able to cut Fox code like that ?
>
> USE CUSTOMER
> SELECT CUSTOMER
> CUSTOMER = 1
> SELECT CUSTOMER
> SELECT NAME FROM CUSTOMER
> CUSTOMER = CUSTOMER * 5
> DROP CUSTOMER
> CUSTOMER = 'I like Fox. It is OOP'
> CUSTOMER = CUSTOMER + 'sssss'
>
>
>
>
>
>
>
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
