Re: Navigation question
Date: 14 Feb 2007 12:22:59 -0800
Message-ID: <1171484579.648015.63160_at_a75g2000cwd.googlegroups.com>
<Do large, production-quality, highly usable and useful, data-based,
There are also been a lot of success with using XML both as output and
input with stored procedures as in this example:
create view CustomerOrderProducts as
<Customers CustomerID="VINET" CompanyName="Vins et alcools Chevalier">
read-and-write software applications actually exist where there is no
code in the software that navigates around the database?
>
Yes, there are many large, production-quality, highly usable and
useful that do not have any database navigation performed by the
application.
>
Picture a data entry screen with header information about a company
and a list of orders presented to the user. Are applications written
where the exclusive approach would be to read all of the data in and
write the data back using a single view?>
Typically not using views, although that is possible, but instead
persistent stored modules (ISO term) are used.
SQL DBMS vendors have implemented three type of persistent stored
modules:
Stored procedures that be invoked from an application
Triggers that automatically run when a table action occurs (insert,
update or delete)
User-derfined function that can be referenced in a SQL statement but
cannot be called by the application.
SELECT Customers.CustomerID
, Customers.CompanyName
, Orders.OrderDate
, Orders.ShippedDate
, OrderProducts.OrderID
, OrderProducts.ProductId
, Products.ProductName
, OrderProducts.UnitPrice
, OrderProducts.Quantity
, OrderProducts.DiscountPct
FROM Customers
join Orders
on Orders.CustomerID = Customers.CustomerID
join OrderProducts
on OrderProducts.OrderID = Orders.OrderID
join Products
on Products.ProductId = OrderProducts.ProductId
go
create procedure CustomerOrderProducts_CustomerId ( _at_CustomerId
nvarchar(4)) as
select * from CustomerOrderProducts
where CustomerId = _at_CustomerId
order by Customers.CustomerID , OrderProducts.OrderID ,
OrderProducts.ProductId
as XML AUTO
go
create procedure CustomerOrderProducts_OrderId (_at_OrderID integer)
as
select * from CustomerOrderProducts
order by Customers.CustomerID , OrderProducts.OrderID ,
OrderProducts.ProductId
as XML AUTO
go
The output of the CustomerOrderProducts_OrderId for orderId = 10248
is:
<Orders OrderDate="1996-07-04T00:00:00"
ShippedDate="1996-07-16T00:00:00">
<OrderProducts OrderID="10248" ProductId="11" UnitPrice="14.0000"
Quantity="12">
<Products ProductName="Queso Cabrales" DiscountPct="0.0000000e+000"/></
OrderProducts>
<OrderProducts OrderID="10248" ProductId="42" UnitPrice="9.8000"
Quantity="10">
<Products ProductName="Singaporean Hokkien Fried Mee"
DiscountPct="0.0000000e+000"/></OrderProducts>
<OrderProducts OrderID="10248" ProductId="72" UnitPrice="34.8000"
Quantity="5"><Products ProductName="Mozzarella di Giovanni"
DiscountPct="0.0000000e+000"/></OrderProducts>
</Orders></Customers>
As you can see, there is no application navigation of the database.
For inputs, an application can accept messages, convert to XML and
then provide XML to a stored procedure which will:
Convert the XML into virtual relations
Perform the appropriate Insert, Updates and Deletes to the underlying
tables - in this example, Customers, Orders and OrderProducts
Received on Wed Feb 14 2007 - 21:22:59 CET