By: Ric O. Stewart, Solution Consultant
Hewlett-Packard Company
Network
Server Division (NSD)
10955
Tantau Avenue, MS 45NUA
Cupertino,
CA 95014-0770
Voice: 408-873-5389
Fax: 770-432-5672
Email: ric_stewart@hp.com
This paper presents a pragmatic view of
software application development using the Microsoft Windows Distributed
interNet Application (WinDNA) architecture.
The benefits of using this architecture are as follows:
1.
Rapid development cycle
2.
Rapid implementation of business logic changes
3.
Rapid one-to-many application deployment effect
4.
Consistent and flexible programming methodology for data
access
5.
Consistent User Interface (UI).
For those new to this technology,
WinDNA is an N-tier architecture. For
this paper, the
3-tier form of the architecture will be
presented. The three (3) tiers are:
1.
Presentation tier
(PT)
2.
Business logic tier (BLT)
3.
Data tier (DT).
This 3-tier distributed application
architecture is not unique to Microsoft, rather WinDNA is simply a Microsoft
implementation of the technology. For
example, SAP R/3 is built using the 3-tier approach on the UNIX platform, and
it was obviously developed independent of WinDNA. Nevertheless, WinDNA is widely promoted by Microsoft and is very
well defined in the Windows NT environment.
The Microsoft Component Object Model (COM) is the cornerstone of
WinDNA. It is a technology on which
many Independent Software Vendors (ISV) develop middleware and applications for
the Windows NT environment. COM is a direct
. competitor. of the Common Object Resource Broker Architecture (CORBA)
model, which is available on most UNIX platforms today. This paper does not debate the virtues of
COM versus CORBA, rather it just investigates techniques, capabilities, and
benefits of using COM within the WinDNA framework.
Using the 3-tier architecture, it is
important that the three layers are cleanly separated and the interfaces
between them are well defined. It is
also very important that access to the interfaces be simple and clearly
understood. The placement of a tier on
a specific server is NOT important from an application design standpoint, but
it is very important from a performance standpoint. However, with the underpinnings of a very strong middleware object
provider such as COM and network provider such as RPC (Microsoft. s
Remote Procedure Call provider), performance of the application becomes mainly
dependent on the strength of the client/server hardware and the network
fabric. Performance should always be
considered in the design of a 3-tier application, but the strength of system
and network hardware today gives the application designer the freedom to make
intelligent tradeoffs among rich features, rapid design/development, and
bigger/faster hardware. WinDNA makes it
very easy to separate the application tiers among servers as performance needs
dictate -- e.g., during development having all tiers on the same server and later
deploying them to multiple production servers.
In general, a typical 3-tier
application logic flow is as follows:
1.
User at the client PC establishes a session with the
Internet Server. A session is unique
and protected from all other sessions by the Internet Server. This
frees the developer to design the application
for . one. user, and leaves the multi-user requirements of the
application to be provided by the Internet Server.
2.
Each session has a unique instantiation of certain common
objects (e.g., a Session object containing global variables) that are
immediately available to all web pages (application components) participating
in the session.
3.
The user interacts with the . home. page of the Presentation
Tier (PT) by either navigating to a newly chosen web page or inputting data
into a data entry FORM.
4.
When the user . submits. a FORM, a link is activated to a
Business Logic Tier (BLT) page and the BLT page begins execution.
5.
During execution, the BLT accesses COM components/objects
provided by the session and also instantiates new COM objects as required to
(1) input/output data from/to the client-side, (2) access database records from
the server-side, and (3) perform desired business logic tasks.
6.
The Data Tier (DT) typically is composed of a Structured
Query Language (SQL) server providing access to a Relational Database
Management System (RDBMS). In the
WinDNA environment, multi-user requirements such as synchronizing access to the
database via locking are provided by the SQL server. The DT also performs other data related tasks such as data
conversion and data threshold checking.
7.
Once BLT decisions are made by rationalizing business logic
against input data and database queries, a presentation is made back to the
user via the PT. As one would expect,
session COM objects are available to facilitate passing data from the BLT to
the PT.
The technical details of WinDNA
technology and products are provided in the remaining sections of this paper.
PT Technology/Products Description
The presentation tier is the layer that
directly interfaces with the user of an application. Generically speaking, the user interface is responsible for (1) communicating
to the user the capabilities/functions of the application, (2) directing
the user through the navigational steps necessary to successfully use the
application. s features, (3) soliciting and collecting user inputs, both
navigational and data, (4) checking/validating the integrity of the user
inputs, (5) making the user inputs available to the business logic tier, and
(6) ultimately, presenting to the user results of actions taken by the business
logic and data tiers. The 3-tier
architecture is obviously a modularized approach to application design, and the
PT should simply be perceived as a module/component that has well-defined
interfaces to both the user of the application and the business logic tier.
The prevailing software component for this layer
is an Internet browser, and two of the commonly used browsers today are NetScape.
s Navigator and Microsoft. s Internet Explorer (IE). An Internet browser is an interpreter of HyperText
Markup Language (HTML) pages, which are composed of tags/commands and embedded
text/graphics images to be . presented. to the user. Text and images can also be linked to a
page, and the browser is capable of accessing these remotely linked objects and
presenting them to the user. Browsers
can also enlist the assistance of middleware components such as Visual Basic
and Java Script interpreters when embedded scripts are encountered in HTML
pages. Client-side scripts provide a
much fuller featured presentation layer, but they also require the residence of
more supporting COM classes (object types) on each client PC accessing the
application. This, of course, is directly related
to the ostensibly resolved controversy surrounding Microsoft. s original
position of not supporting Sun Microsystem. s Java Foundation Classes
(JFC) on MS Windows clients.
The WinDNA capability of executing scripts
at the client-side is called ActiveX scripting, and unquestionably this capability
greatly enhances the . intelligence. of the client PC. The executable code of COM classes (object types)
is stored in Dynamically Linked Libraries (DLL) on the client-side and the
Interface Definitions (IDL) are registered in the client. s Windows NT
registry. One form of these components
is named ActiveX Controls. An
example of an
ActiveX Control is a component. s method/function being executed on the client-side
when mouse cursor movement occurs across a visible web page, causing a
descriptive annotation box to pop-up on the viewer. s screen.
To round out the capabilities of
client-side components, it is also possible for executable components to be
downloaded from the server each time they are needed by the browser. Also, an Internet browser is capable of executing
plug-ins whenever certain well-know file extension types are received,
thus turning over the actual . interpretation. of the file to the
plug-in. A plug-in is just another executable
. applet. stored on the client PC and is registered as a plug-in for
the Internet browser. Of course, the
presentation to the user is through the Internet browser interface.
Since the presentation tier is the
layer that interfaces to the user, much consideration has to be given to the
audio-visual effects that this tier is capable of handling. However, much consideration also must be
given to (1) the responsiveness of the application to the user, and (2) the
costs associated with providing and managing the client PC. As always, there are serious tradeoffs that
have to be evaluated. Graphics images can be
very impressive and informative in a visual sense, but if a user access link is
. slow,. the rich visual experience may be severely impaired due to .
painful. response times across such links.
Also, the more application functions that are pushed down to the client PC/browser,
the . thicker. the client has to be . e.g., more disk space to store
COM classes (DLLs) and plug-ins and more powerful processors/larger RAMs to
yield a rich, dynamic graphics effect.
Software distribution and management of 1000s
of . thick. client PCs across an international corporate enterprise can be
a very costly proposition. Thick
clients also can lead to lost revenue because of lengthy delays in deploying
business rule changes (e.g., code changes in DLLs) out to thick clients. On the other hand, placing much of the PT
and all of the business logic tier at the server-side, business rule changes
can be controlled in a much more timely and cost-effective manner. The tradeoff is that the performance
strength of the network fabric (infrastructure) becomes much more critical to
the overall performance of the 3-tier application.
Probably, the single most compelling reason
for moving the bulk of the PT to the server-side is lack of control of the
software residing on the enterprise client PC.
Assuming the network infrastructure is sound and the thick/thin client
has basic Internet browser capabilities, the application developer is able to
design to the minimal capabilities of basic HTML at the client-side. If the feature rich capabilities of the application.
s PT are designed to be on the client-side, software provisioning
for the enterprise client PC becomes a much more costly challenge. Application upgrades become a matter of
assuring that every client PC in the enterprise has all of the correct versions
of the software components that provide the rich featured PT of the application. That is a major challenge.
With the single look-and-feel of the
Internet browser user interface (UI) and the least common denominator of basic
HTML interpreter capabilities, the thin client could be the enterprise PC of
choice. Of course, many enterprise
users need a thick client for other job-related functions, but for the 3-tier
Internet application with PT functions heavy on the server-side, the client
with basic HTML capabilities is sufficient.
Static
HTML Page
<TABLE border="0"
width="90%">
<TBODY>
<TR>
<TD>
<FORM METHOD="post"
ACTION="psfm_open_deal.asp">
<TABLE border="0"
width="100%" cellspacing="0" cellpadding="5">
<TBODY>
<TR>
<TD
width="49%">
<INPUT type="submit" name="submit" value=
.Open>
</TD>
</TR>
|
Explanation: The HTML FORM and INPUT statements above define when the . submit. button is pressed, the action
is to . post. the
FORM. s input data names/values for use
by subsequent pages and begin
executing the . psfm_open_deal.asp. Active Server Page. |
<TR
bgcolor="#ffffcc">
<TD
width="49%"><B>HP Sales Team</B> </TD>
<TD
width="51%"> </TD>
</TR>
<TR>
<TD
width="51%"><font color="blue"><b>Sales Team
Primary
Region:</b> </font>
</TD>
<TD
colspan="2" align="left"><font
color="blue"><b>Specials Required?
<INPUT type="radio" id=radio1
name="big_spec.." value="S" >Yes
<INPUT type="radio" id=radio1
name="big_spec.." value="B">No
</b></font>
</TD></TR>
|
Explanation: The <TR>& </TR> and <TD>& </TD> tags just delineate the beginning and end of TABLE Rows and TABLE Data fields. The INPUT statements with type= .radio. and the same ID allow the user to choose only one of the values listed. When the FORM is . posted,. the value (i.e., . S. or . B. ) for the selected choice is stored in the variable name specified (i.e., big_spec..). To the user, this appears as a RADIO BUTTON on the screen . i.e., when you press the button (circle), it is blackened, but if you press another
button,
that button is blackened and the original is whitened. |
<TR>
<TD
colspan="2">
<select name="sales_region">
<option selected >Select One
<option>North America
<option>Europe
<option >Asia Pacific
<option >Latin America
<option>Japan
</select>
</TD></TR>
|
Explanation: The HTML SELECT statement above provides a variable name . sales_region. which is given a value selected from the pull-down list specified by all of the <option> statements. The <option selected> statement indicates
which
choice initially appears in the input text box. |
Static
HTML intermixed with HTML generated by dynamic Active Server Pages (see BLT
section for more ASP details)
<%Do until rsJoin.Eof%>
<TR VALIGN=top ALIGN=left>
<%If lv_dup_engagements <>
rsJoin.Fields("DealID").value then%>
|
Explanation: The <% and %> tags
delineate the beginning and end of Visual Basic Scripts to be executed on the
server-side (i.e., if they appear in an Active Server Page, which will be
explained in more detail in the Business Logic Tier section). The rsJoin.Fields(. DealID.
).value item is a RecordSet object which will also be
explained in more detail in the BLT section.
Essentially, the RecordSet object provides database row/column (field)
values to the VBS script. Also, the
rsJoin.Eof property indicates when the last record of the RecordSet has been
processed. As the Internet Server interprets
the ASP from . top to bottom,. it goes in and out of VBS scripting
mode based on the tags. This
interleaving
can be very awkward until one realizes that the Internet Server.
s ultimate goal is to send HTML to the client-side browser. In HTML mode, the HTML code is just sent
directly to the browser. In VBS
scripting mode, the Internet Server interprets the script and if actions
taken by the script cause HTML to be generated, it is then sent to the
browser. Intermixing HTML
and VBS within one . code. statement is probably
the most awkward experience and takes some getting used to. Take heart, it does get better with
experience writing ASPs. |
<TD>
<%Response.write(Server.HTMLEncode(ReplaceAccent(rsJoin.Fields("name").Value)))%>
<BR><INPUT type="submit"
value="<%Response.Write(Server.HTMLEncode(rsJoin.Fields("DealID").Value))%>-->More?" id=submit1 name=submit1></TD>
<TD><%Response.write(Server.HTMLEncode(rsJoin.Fields("sales_region").Value))%></TD>
<TD><%Response.write(Server.HTMLEncode(rsJoin.Fields("deal_type").Value))%></TD>
|
Explanation: Response.write . Response object. s . write. method,
which is used to send information to the client. s Internet browser.
Server.HTMLEncode . Server object. s . HTMLEncode.
method, which is used to convert variable values into HTML.
ReplaceAccent -- VBS Function defined in the ASP. Irrelevant to
the generic case.
<BR> --
HTML equivalent of Carriage Return/Line Feed. |
<%End If
lv_dup_engagements = rsJoin.Fields("DealID").value
rsJoin.MoveNext
Loop%>
|
Explanation:
Notice the . If& then. statements apply to both VBS and
HTML . i.e., if the
true branch of the IF is not taken, both the VBS script and HTML
statements
are skipped. This is true whether they are separate
statements or
Intermixed in one statement.
The rsJoin.MoveNext method of the RecordSet object causes the SQL
query
record set to advance to the next record in the set. Within the .
Do
until& Loop. construct, this is required if an infinite loop is
not the goal. |
BLT Technology/Products Descripton
This tier is the one that is traditionally considered the
core of the application. All of the business decisions/rules are coded
into this layer of the application. In the WinDNA architecture, it is common for
this tier to be developed as a series of Active Server Pages (ASP). ASP pages are
typically a combination of intermixed HTML code and Visual Basic/Java scripts in
a file located on the Internet server with a file extension of . asp.. For the purposes of
this paper, Visual Basic Script (VBS) will be the scripting language used. As alluded to
earlier in the paper, the Internet Server interprets ASP pages in the following
manner:
1.
Begin at the top of the page, and if the language is HTML,
forward the text code lines directly to the client PC for interpretation by the
Internet browser
2.
If the tag . <%. is encountered in the ASP, the
subsequent code statements are assumed to be VBS and are interpreted as such at
the Internet server.
Scripting actions are performed on the server, and they may generate HTML
code that is sent to the client. s Internet browser.
3.
When the tag . %>. is encountered, the mode is switched
back to HTML and the subsequent code statements are once again forwarded
unaltered directly to the client. s Internet browser.
Using the Microsoft Internet Information Server (IIS) in the WinDNA environment, there are numerous COM components automatically made available to the ASP pages, as scripting steps are interpreted/executed. Some of the most significant and useful of these components/objects are: (1) Session, (2) Server, (3) Request, and (4) Response.
In general, the Session object is made available by IIS to all
active components of the session between one client PC. s Internet browser and
IIS. The
Session object can be used to pass (preserve) global variable values between ASP
pages. Another
available object is Server, which has many properties and methods. One very helpful Server method
(function) is HTMLEncode, which formats VBS variable values for HTML text
output. The Request object
is the one used to request data item values from an HTML FORM managed by the
Internet browser on the client-side. Another object is Response which
has a very useful method named Write. . Response.Write. provides the capability to
send HTML statements/text from the executing ASP to the client. s Internet
browser.
Other COM classes are made available for instantiation
through a set of classes called ActiveX Data Objects (ADO) that comes standard
with SQL Server 7.
ADO uses Online Linking and Embedding for Databases (OLEDB) to actually
communicate with the SQL Server. ADO/OLEDB is similar in functionality to the
commonly used Online Database Connectivity (ODBC) access method, but is more
state-of-the-art in terms of overall flexibility and capability. A typical
application . protocol. stack for the WinDNA architecture is:
§
ASP (VBS script intermixed with HTML)
§
ADO/OLEDB
§
COM
§
RPC
§
Sockets
§
TCP/IP
§
Ethernet.
Two very commonly used ADO COM classes are ADODB.Connection and
ADODB.RecordSet. These classes must be instantiated in order to
use their methods in a session. In general, the Connection class is used to
establish a network connection to a SQL Server, and RecordSet is used to access
record sets provided by a SQL query.
One of the biggest benefits of developing a WinDNA application using VBS as the scripting language is the short development cycle. The BASIC programming language is very easy to use and yet is powerful enough to perform fairly complex and elaborate business algorithms. Combining the ease and flexibility of VBS scripting with an Interactive Development Environment (IDE) tool such as InterDev6 (part of the Microsoft Visual Studio 6 suite of development tools) and powerful COM classes such as ADO/OLEDB yields a development cycle that actually approaches that of the Microsoft marketing claims. Microsoft has always touted that the development cycle for WinDNA applications adheres to the 3-2-1 formula . i.e., 3-tier, 2 developers, 1 month. The 3-tier application that is used for examples in this paper was developed with one developer and the development cycle was two months. Including all HTML and VBS code, the code lines count for the application totals approximately 12,000. It appears that the Microsoft marketing claims are fairly accurate.
A significant benefit of placing most of the Presentation Tier (PT) and all of the Business Logic Tier (BLT) on the Internet Server is the ease of making business logic code changes only once on the server, rather than on every client PC of the corporate enterprise. The changes immediately take effect on all client PCs in the corporate enterprise. With a PT that is heavily implemented on the client-side, this does not occur because of software distribution complexities. Software distribution to client PCs across the enterprise can be a major challenge, but that is a topic for another paper.
WinDNA provides a consistent, easy to use, and flexible programmatic interface to the data tier . i.e., ADO/OLEDB, which interfaces very nicely and easily to SQL Server 7 (SS7). Using the power of generic Structured Query Language (SQL) statements and stored procedures, and the excellent performance of SS7. s query optimizer, access to the data tier (DT) from the BLT is exceptionally easy and responsive. Ultimately, this also makes a significant contribution to the short development cycle of a WinDNA application.
BLT Application Code Examples
Access to PT Input Data
rs21SmName =
trim(ReplaceApostrophe(Request.Form("sf21_mgr_name..")))
rs21SmPhone =
trim(Request.Form("sf21_mgr_ph...."))
rs21SmCellP =
trim(Request.Form("sf21_mgr_cell_ph...."))
rsConame =
trim(ReplaceApostrophe(Request.Form("co_name.....")))
|
Explanation: The Request.Form method is used to access the named variables. values provided when the SUBMIT button is pressed on an HTML FORM. It is imperative to use the same name in the ASP as that which is used in the HTML FORM. Notice that the . ... characters are actually part of the variable names. Notice also that access to the Request.Form method can be embedded within the scope of locally defined VBS functions such as
ReplaceApostrophe. |
Edit Checks on PT Input Data
<%
field_names(28)
=
"Network Operating System"
field_names(29)
=
"Solution"
field_names(30)
=
"Specials Required?"
For i = 1 to 30
field_names_index (i)
=
0
Next
If trim(rsNos
)
=
"Select One" then field_names_index(28) =
1
If trim(rsSolution) =
"Select One" then field_names_index(29) =
1
If trim(rsDealType) =
""
then
field_names_index(30) =
1
'
Aggregate the MISSING REQUIRED fields.
sum = 0
For i = 1 to 30
sum
=
sum + field_names_index(i)
Next
'
If REQUIRED fields are MISSING, the major body of this ASP is skipped and
'
the requester is told what fields are not completed.
If sum = 0 then
'
Begin the DEAL transaction to the
'
USER database.
***Code to enter DEAL in database is not shown.***%>
'
Some REQUIRED fields are missing off the BIG DEAL form.
<%else%>
<font color="red"><b>The following REQUIRED fields have NOT
been
completed properly.<br> Please use the BROWSER's BACK button to
complete
the form.</b></font><br><br>
<%For i = 1 to 30
If
field_names_index(i) <> 0 then
Response.Write(server.HTMLEncode(field_names(i)))%><br>
<%End If%>
|
Explanation: The above code is just showing one way to edit check required fields from the HTML FORM. The field descriptive names are stored in an array named field_names(i), and the presence indicator is an array named field_names_index(i). Presence is indicated by . 0. and
absence by . 1. . An aggregate
for all required fields is made and if
the result is non-zero, then the field names are presented to
the
user. |
Using ADODB COM Classes to access the DT
<%
'
'
***Function OpenConnection
Function OpenConnection(RecSet)
Set OpenConnection
=
Server.CreateObject("ADODB.Connection")
Set RecSet
=
Server.CreateObject("ADODB.Recordset")
OpenConnection.ConnectionString =
_
"PROVIDER=SQLOLEDB"
&_
";SERVER=hpworld99"
&_
";DATABASE=USER"
&_
";UID=sa;PWD="
OpenConnection.Open
RecSet.ActiveConnection = OpenConnection
End Function
%>
<%
.
This is the beginning of the Main Body of the ASP.
Set cn = OpenConnection(rs) %>
|
Explanation: OpenConnection is a locally defined VBS function that (1) instantiates an ADO/OLEDB Connection object and a RecordSet object, (2) opens the connection to the hpworld99 server using the SQLOLEDB provider and accessing the USER database, and (3) associates the passed in
Recordset name with the newly opened connection.
|
.
This is the beginning of the Main Body of the ASP.
|
Explanation: The local VBS function named dbInsertSalesRep builds a SQL
. INSERT INTO. string from the parameters passed into it. The function
then executes this SQL statement by
invoking the fcn.Execute method
with the SQL string as an argument
(writeSQL).
Notice that the . fcn.
connection is passed into this
function and must be opened before
calling this
function.
|
DT Technology/Products Descripton
Data integrity is characterized by the ACID properties of
relational databases.
ACID is
an acronym that means Atomic, Consistent, Isolated, and Durable. These
properties are guaranteed by SS7. SS7 multi-table
transactions (i.e., a
transaction that requires updates to many tables of a database) are assured to be atomic because of the BeginTrans and CommitTrans methods. For transactions bracketed by these methods, either all the updates to tables of a transaction are completed or none is. Another feature of SS7 is the use of a transaction log to assure the durable property of SS7 databases. If a system failure occurs before a complete transaction gets posted to the database, consistency of the database is retained by SS7 performing a recovery of the transaction log upon system restart. The SS7 recovery processes the transaction log and rolls forward complete transactions to the database and rolls back incomplete transactions.
As stated earlier in this paper, the DT is responsible for synchronizing access to the database by multiple concurrent users. SS7 provides eXclusive and Shared locks, and is generally more efficient than the application in determining what is the most suitable type of lock to use. Also, SS7 has the ability to determine what level of locking best fits the request . i.e., database (DB), table (TAB), page (PAG), or row ID (RID). SS7 also has an Update lock type, and Intent to lock type. A SQL update request starts as a Shared lock (i.e., since it needs to READ the record first), moves to an IX (Intent eXclusive), and then X when the write actually occurs.
DT
Benefits/Tradeoffs
SS7 provides excellent database access performance, and therefore, it is prudent to build a SQL statement string and pass it in its entirety to SS7 via the Connection.Execute method. The SS7 Query Optimizer is very efficient, and for complex single queries, SS7 will split the work among several worker threads. In this manner, capacity of multiple processors in a server is efficiently utilized. Even more efficiency can be obtained if SQL Stored Procedures are used, since the query optimizer analysis time can be greatly reduced because stored procedures are pre-processed and . ready to go.. Of course, this requires that the SQL strings/procedures be pre-defined, which either (1) takes away some of the dynamic capabilities of the DT, or (2) causes a lot of redundancy in the Stored Procedures . i.e., many similar stored procedures with very subtle differences. Always one is reminded of tradeoffs when developing a software application of any magnitude.
SS7 also has the ability through standard SQL statements to retrieve data from the database and order it in the RecordSet according to multiple sort data fields. Once again, SS7 is much more efficient than a VBS script, and this type work should always be pushed to SS7.
The best rule of thumb for the DT layer in a WinDNA application is to push as much of the work as possible to SS7. However, for functions such as data conversion, derivation, and aggregation, much of the work will more than likely be performed in ASPs using VBS.
.
Setup the variable conditions of a complex JOIN query.
lv_sales_region_japan
=
. JAPAN.
lv_sales_region_na
=
. .
lv_sales_region_lat
=
. .
lv_sales_region_eur
=
. .
lv_sales_region_ap
=
. .
lv_sales_region_all
=
. .
lv_state_all =
. .
lv_state_sch =
. .
lv_state_new =
. NEW.
lv_state_act =
. .
lv_state_wait =
. .
lv_state_rej =
. .
lv_state_clo =
. .
lv_condition_clo_won=
" and d.won_lost_flag = 'Y' "
lv_condition_clo_lost
=
" "
lv_condition_specials
=
" and (d.deal_type = 'B' or d.deal_type = 'S')"
lv_sql_condition_date
=
" and d.date_opened between '" & lv_start_date &_
"' and '" & lv_end_date & "' "
lv_sql_condition_cust_name = " and c.name like '"
& lv_cust_name & "%'"
lv_ord_condition_region
=
" st.sales_region ASC"
lv_ord_condition_status
=
" d.state ASC"
.
Execute the complex JOIN query.
rsJoin.Open "Select * from Deal d join engagement e on
d.dealID = e.DealID
"_
&"join customer c on d.dealID = c.DealID
"_
&"join solution s on d.dealID = s.DealID
"_
&"join salesteam st on d.dealID = st.DealID
"_
&"
where (d.state like '" & lv_state_all &
"'"_
&" or d.state = '" & lv_state_new & "'"_
&"
or
d.state
= '" & lv_state_sch &
"'"_
&"
or
d.state
=
'" & lv_state_act &
"'"_
&"
or
d.state
=
'" & lv_state_wait&
"'"_
&"
or
d.state
= '" & lv_state_rej &
"'"_
&"
or
d.state
=
'" & lv_state_clo &
"')"_
&
lv_condition_clo_won
_
&
lv_condition_clo_lost
_
&
lv_condition_specials
_
&"
and (st.sales_region
like '" &
lv_sales_region_all &
"'"_
&"
or
st.sales_region =
'" & lv_sales_region_japan &
"'"_
&"
or
st.sales_region =
'" & lv_sales_region_lat &
"'"_
&"
or
st.sales_region =
'" & lv_sales_region_na &
"'"_
&"
or
st.sales_region =
'" & lv_sales_region_eur &
"'"_
&"
or
st.sales_region =
'" & lv_sales_region_ap &
"')"_
&
lv_sql_condition_date
_
&
lv_sql_condition_cust_name
_
&"
ORDER BY
"_
&
lv_ord_condition_region
_
&
lv_ord_condition_status
|
Explanation: This just shows an example of how a complex SQL JOIN query can be built programmatically by the BLT and passed to SS7
(the
DT) for efficient execution. |
Multiple table INSERTs in one logical transaction
Set cn
=
OpenConnection (rs)
cn.BeginTrans
InsertStatus = dbInsertSalesRep (cn, rs88SrEmail,
rs88SrName, rs88SrPhone,_
rs88SrCellP, "SF88", "")
InsertStatus = dbInsertConsultant (cn, rs88TcEmail,
rs88TcName, rs88TcPhone,_
rs88TcCellP, "SF88","")
cn.Close
|
Explanation: If the call to dbInsertSalesTeam fails, the INSERTs to the SalesRep and Consultant tables will not be committed to the database. This assures consistency of the database. Note that the dbInsertConsultant and dbInsertSalesTeam functions are
local and are similar in definition to the
dbInsertSalesRep function
defined above in this paper. |
The 3-tier WinDNA architecture is very efficient and
flexible. It
lends itself very well to a very rapid development cycle because of COM classes
such as the ActiveX Data Objects (ADO) that are available to the developer. Also, COM classes
that are available immediately to the developer from the Internet Information
Server (IIS) such as Session and Server accelerate the development cycle. Because of strong
COM classes provided on the server, Business Logic Tier (BLT) code can be
developed using a scripting language such as Visual Basic Script (VBS). This accelerates the
development cycle due to ease of programming, and it also allows ease of
modification as business rules change.
. One-time. deployment of the COM classes and the Active
Server Pages (ASP) for the BLT to the Internet server also makes the overall
deployment of WinDNA applications very rapid. The cost effectiveness of thin client PCs
across the corporate enterprise also is very attractive. As long as the thin
client has basic HTML capabilities, a very impressive 3-tier application can be
developed with the client Internet browser as the primary Presentation Tier (PT)
component.
SQL Server 7 provides a very powerful Data Tier (DT) component, and is easy to deploy, has very high performance, and is easy to manage.
Microsoft Transaction Server (MTS) is very powerful middleware capable of efficiently managing COM components (DLLs) used by all three tiers, and especially, the BLT. MTS provides very efficient utilization of network connections between the MTS server (which likely, but doesn. t have to be, the same as the IIS server) and the SS7 server. Multiple users can access the SS7 server via one connection from the MTS server, thus reducing the number of client access licenses needed for SS7. Also, MTS provides a layer of security that is easy to control from a central point. MTS reduces overhead of building and tearing down COM object data structures, since it centrally manages them and reuses them very efficiently. Not much information is provided in this paper about MTS, but it is highly recommended that it be used, especially if a corporation has a common set of COM classes (DLLs) frequently used in the development of its WinDNA applications.