More about databases and ADO

1
More about databases and ADO

• Views, Stored Procedures, the command object and
  more.

2
Views

• Views are a canned SQL Select Query
• The query can not have
• parameters
• an order by clause
• Views have the advantage of being pre-compiled so
  they are faster then the same statement from the
  client.
• Difficult joins are best done in views
• Views can serve as the inner part of a sub query
• Views can be used to emulate a table of computed
  values
• On some systems the results of a view are cached,
  speeding performance further.

3
View Example
Create View vMaxSales As select top 1
stores.stor_name as Store, (sum(sales.qty)) as
Total from sales inner join stores on
sales.stor_idstores.stor_id group by
stores.stor_name
Store
Total -----------------------
----------------- -----------
Doc-U-Mat Quality Laundry and Books 130 (1
row(s) affected)
4
Using Views

• Views are used exactly as if they were tables.
• E.g. "Select From vMaxSales"
• Depending on the type of join used, views may or
  may not be update-able. In general in an inner
  join where all the elements of both primary keys
  are present the view is update-able.
• Again, views a good for maintaining ready lists
  of computed values.

5
Stored Procedures

• Stored procedures are SQL statements (usually
  extended by additional syntax) that can be
  executed like subroutines or functions.
• Stored procedures are good ways to centralize and
  speed up business logic. They are used as
  functions when they return a recordset.
• If a stored procedure takes no parameters and
  returns a recordset it can be called like a view
  with a select statement.

6
Calling stored procedures with arguments.

• Use the connection object to execute stored
  procedures
• Two cases
• Returns a recordset
• uspGetMaxEmployees(PUBID)
• Does not return a recordset
• uspDeleteAuthor(AUID)

7
uspGetMaxEmployees
CREATE PROCEDURE uspGetMaxEmployees _at_PUBID
char(4) AS Select Publishers.Pub_ID,
publishers.pub_name, count(employee.emp_id)
HeadCount from publishers, employee
where publishers.pub_idemployee.pub_id group
by Publishers.Pub_ID, publishers.pub_name Havi
ng Publishers.Pub_ID _at_PUBID
8
uspDeleteAuthor
CREATE PROCEDURE uspDeleteAuthor _at_AUID
Char(11) As Declare _at_RC int Begin
Transaction Delete From TitleAuthor Where AU_ID
_at_AUID Delete From Authors Where AU_ID
_at_AUID Select _at_RC _at__at_Rowcount Commit Transaction
9
ADO Constants for .Execute

• adCmdUnspecified (-1) Does not specify the
  command type argument.
• adCmdText (1) Evaluates CommandText as a textual
  definition of a command or stored procedure call.
  
• adCmdTable (2) Evaluates CommandText as a table
  name whose columns are all returned by an
  internally generated SQL query.
• adCmdStoredProc (4) Evaluates CommandText as a
  stored procedure name.
• (from http//msdn.microsoft.com/library/psdk/dasd
  k/mdae1oz1.htm )

10
Stored Procedures/No Recordset
'Case 1 Does NOT Returns a RecordSet 'Assume
the connection object exists and is open 'Build a
command string and let the connection object
execute it 'First treat the SP as if it were any
other function 'build a command line mySQL
"uspDeleteAuthor( " AUID ") 'surround the
stored procedure in the required call syntax
mySQL "Call " mySQL "" 'Execute the
stored procedure CN.Execute mySQL,RA,
adCmdStoredProcif RA ltgt 1 then
'Error else 'Success end if
11
Stored Procedures/Recordset
'Case 2 Returns a RecordSet rs
Server.CreateObject("ADODB.Recordset") 'Assume
the connection object exists and is open 'First
treat the SP as if it were any other
function 'build a command line mySQL
"uspGetMaxEmployees ( " PUBID ") 'surround
the stored procedure in the required call syntax
mySQL "Call " mySQL "" 'Execute the
stored procedure, return the recordset Set rs
CN.Execute( mySQL ) 'Use the recordset rs.Close
Set rs nothing