About the Author
Alex Homer lives and works in the idyllic rural surroundings of Derbyshire, UK. His software company, specializes in office integration and Internet-related development, and produces a range of vertical application software. He has worked with Wrox Press on several projects including Professional Active Server Pages 2.0.
Read an Excerpt
This manuscript is an abridged version of a chapter from the Wrox Press book Professional Active Server Pages 3.0. Connections, Commands And Procedures examines the different ways in which ADO can access data. This chapter covers the most important topics, and those methods and properties that are most applicable for an ASP developer.
Professional Active Server Pages 3.0 is the next edition of the number one selling ASP book in the world; Professional Active Server Pages 2.0. This is a next edition covering all the new features that appear as part of Win2000 but it is also a completely new book in terms of content, recycling essentially nothing from the previous edition. Instead all the concepts are taken a step further for a more mature audience and ASP is considered in terms of an N-tier enterprise environment including extensive coverage of components, Index Server, ADO 2.5, XML, CDO, ADSI, and much more. For further details about the book, and other books in our range, visit the Wrox Press Web Site (www.wrox.com).
- The Connection Object
- The Command Object
- Stored Procedures
- Refreshing Paramenters
- Data Shaping
Connections, Commands And ProceduresIn the previous chapter, we looked at the basics of ADO, concerning ourselves mainly with the Recordset object and the manipulation of data. In most of the examples, we obtained data by simply specifying the name of a database table, but as you've seen from the object model, ADO has other objects that allow data to be accessed.
In this chapter we are going to look at some of these in more detail. In particular, we shall examine:
- The Connection object, and how it can be used to retrieve data and run commands
- The Command object, why it is necessary, and what facilities it has
- How to run stored procedures, especially those that require parameters
- Some simple optimization techniques for improving ADO performance
- What Data Shaping is, and how it can be used
Like the Recordset object, we're not going to cover all of the methods and properties of the objects involved. Instead, we're going to cover the most important topics, and those methods and properties that are most applicable for an ASP developer.
The Connection ObjectWe mentioned in the previous chapter that the Connection object is what gives us a connection to the data store, but that's not all the Connection object does. As well as storing details of the connection (such as the type of data store and the features it supports), we can use the connection to run commands.
These commands can be action queries, such as updates, inserts or deletes, as well as commands that return a recordset. You might wonder what use this is, since we have the Recordset object, but it's all part of the flexibility of ADO, that allows you to use whichever object is the most convenient, and most suited to the task in hand.
The commands run from the Connection object are generally action queries, but it's useful to know that you can get recordsets returned too.
Returning a RecordsetTo return a recordset from the Connection object you use the Execute method. The syntax of this method is:
Connection.Execute CommandText, [RecordsAffected], [Options]
The arguments are:
The Execute method optionally returns a recordset, in which case you simply assign the Recordset variable as the return value. For example:
Set conPubs = Server.CreateObject ("ADOB.Connection")
Set rsAuthors = conPubs.Execute ("Authors")
You might wonder what the difference is between using the Execute method of the Connection object and the Open method of the Recordset object. It may not seem that there's much difference, but remember that with the Open method of the Recordset you have the ability to change the cursor type and lock type of the resulting recordset. These options are not available for the Execute method of the connection, so you will always get a forward-only, read-only recordset.
If you are running action commands, such as a SQL UPDATE statement, then you can use the RecordsAffected argument to find out how many records were affected by the command. For example:
Dim strSQL As String
Dim lngRecs As Long
strSQL = "UPDATE Titles SET Price = Price * 1.10 & _ " WHERE Type='Business' "
conPubs.Execute strSQL, lngRecs, adCmdText
Response.Write lngRecs & " records were updated. "
This updates the price for all business books by 10%. Once the Execute command has completed, the number of titles affected by the update will be available in lngRecs - this is the RecordsAffected argument.
Notice that we've specified adCmdText for the options of this command - this tells ADO that the command text is a text command. Whilst this isn't strictly necessary, it does allow ADO to know ahead of time what sort of command is being executed, and therefore improves performance. Remember that this value can be one or more of the values from the CommandTypeEnum values.
No Recordset Returned
If no recordset is being returned, as in the example above, then it's also best to add another option to the Execute statement:
conPubs.Execute strSQL, lngRecs, adCmdText + adExecuteNoRecords
Using adExecuteNoRecords tells ADO that the command being executed does not return any records. ADO therefore doesn't bother building a recordset. If you omit this option then ADO builds an empty recordset, which is clearly a waste of time, so adding the option will speed up the statement.
The Command ObjectThe Command object is designed specifically to deal with commands of any sort, but especially those that require parameters. Like the Connection object, the Command object can run both commands that return recordsets as well as those that don't. In fact, if your command doesn't have parameters, then it really doesn't matter whether you use a Connection, a Command, or a Recordset.
For a recordset-returning command you would use the Execute method. However, unlike the Connection object, you do not specify the command text in the Execute method itself - you have to use the CommandText property:
Set cmdAuthors = Server.CreateObject("ADODB.Command")
cmdAuthors.CommandText = "Authors"
Set rsAuthors = cmdAuthors.Execute
This is the simplest way to tell the Command object to run a simple command that returns a read-only recordset.
The Execute method also has some optional arguments:
The RecordsAffected and Options are as previously explained, although you can set the command type by using the CommandType property:
Set cmdAuthors = Server.CreateObject("ADODB.Command")
cmdAuthors.CommandText = "Authors"
cmdAuthors.CommandText = adCmdTable
Set rsAuthors = cmdAuthors.Execute
You can also set this on the Execute line itself - if you're not setting the other arguments though, you must still include the commas for them:
Set rsAuthors = cmdAuthors.Execute(, , adCmdTable)
We'll look at the use of the Parameters argument a little later in the chapter, when we deal with stored procedures and parameters.
Changing the Cursor Type
One important thing to note about recordsets that are returned from the Execute method is that they have the default cursor and lock types. This means they are forward-only, read-only recordsets. There's no way to change this using the Execute method, but there is a way around this problem.
If you need to use a command, and require a different cursor or lock type, then you should use the Open method of the Recordset, but use the Command as the source of the Recordset. For example:
cmdAuthors.ActiveConnection = strConn
cmdAuthors.CommandText = "Authors"
cmdAuthors.CommandType = adCmdTable
rsAuthors.Open cmdAuthors, , adOpenDynamic, adLockOptimistic
Notice that the connection details on the Open line have been omitted, because the connection is set in the Command. The connection details could also have been set in the ActiveConnection property of the Command before it is opened.
For action commands, such as those that update data without returning any records, the procedure is similar - you just leave off the bits that set the recordset:
Set cmdUpdate = Server.CreateObject("ADODB.Command")
strSQL = "UPDATE Titles SET Price = Price * 1.10" & " WHERE Type='Business' " cmdUpdate.ActiveConnection = strConn
cmdUpdate.CommandText = sSQL
cmdUpdate.CommandType = adCmdTable
cmdUpdate.Execute , , adExecuteNoRecords
The use of stored procedures is the one area where the Command object comes into its own. A stored procedure (or stored query as it's sometimes called) is a predefined SQL query stored on the database.
So why should we create and use a stored procedure instead of just creating a SQL string on the fly, as in the example shown above? Well, there are several reasons:
- A stored procedure is compiled by the database. This produces an execution plan, so the database knows exactly what it's going to do. This makes the execution of the procedure faster.
- Stored procedures are often cached by the database, thus making them faster to run, as they don't have to be read from disk. Not all databases support this caching mechanism - Microsoft Access doesn't, but SQL Server does.
- You can make your data a little bit more secure by specifying that your database tables can be modified only by stored procedures. This means that potentially dangerous SQL operations generated on the fly may not be performed.
- You avoid cluttering your ASP code with lengthy SQL statements. This makes the ASP code easier to maintain.
- You can keep all of the SQL code together, on the server.
- You can use output parameters in a stored procedure, which allows you to return both a recordset and other values.
Table of ContentsIntroduction
Chapter 1: ASP Fundamentals
Chapter 2: Handling Requests and Responses
Chapter 3: ASP Applications and Sessions
Chapter 4: Server Processes and the ASP Server Object
Chapter 5: The Scripting Runtime Library Objects
Chapter 6: Active Server Components
Chapter 7: Debugging and Error Handling
Chapter 8: ADO 2.5 Basics
Chapter 9: Connections, Commands And Procedures
Chapter 10: ASP and Data on the Client
Chapter 11: Working with XML Data
Chapter 12: Universal Data Access
Chapter 13: Components and Web Application Architecture
Chapter 14: COM, COM+ and ASP
Chapter 15: COM+ Applications
Chapter 16: ASP Script Components
Chapter 17: Building ASP Components in C++
Chapter 18: More C++ Component Issues
Chapter 19: ASP and Transacted Web Applications
Chapter 20: ASP and Message Queue Server
Chapter 21: Introducing ADSI and Active Directory
Chapter 22: ASP and Collaboration Data Objects for NT Server
Chapter 23: ASP, CDO and Exchange Server
Chapter 24: Securing Your Server
Chapter 25: Working With Certificates
Chapter 26: Optimizing ASP Performance
Chapter 27: Building Multiple Server Web Sites
An XML-Driven Newspaper Case Study
Appendix A: The ASP 3.0 Object Model
Appendix B: The Scripting Runtime Library Objects
Appendix C: Microsoft Server Components
Appendix D: Error Codes
Appendix E: The ADO 2.5 Object Model
Appendix F: ADO 2.5 Constants
Appendix G: Useful Information
Appendix H: P2P.WROX.COM
One of our colleagues firmly believes that the introduction of Microsoft's Active Server Pages (ASP) technology, or Denali as it was called then, was one such important event. He backs this up by remembering exactly what he was doing at the time. He'd just finished work on a book about Web database connectivity techniques, ending with the jewel-in-the-crown at that time-the Internet Database Connector JDQ. The rapid addition of an extra chapter before going to press, to cover this exciting new ASP technology, was the order of the day. In fact that single chapter was probably the reason for most of the book's sales, because suddenly every developer wanted to be 'into' ASP.
All this sounds like it was a long time ago, and we have all become quite blase about Active Server Pages and whole range of techniques that have built up around it. But (at the time of writing) this was less than three years ago. Yet here we are, with version 3.0 of ASP just released, and with a feature set, performance and capabilities that leave the original version 1.0 standing in the dust.
What Is This Book About?
This book is about Active Server Pages 3.0, as included with Windows 2000. However, because ASP is now a core part of so many Web-oriented features within Windows, this book covers a far wider area than just how ASP works. ASP ismaturing all the time to encompass more integration with other Windows services and software, and so there are many other areas that impinge directly on the use and performance of ASP.
In particular this involves the Windows operating system itself, including the new security features of Windows 2000, and the Internet server software that comes with Windows 2000 - Internet Information Server (IIS). On top of this are the other less obvious services, which also have a direct or indirect effect on the way that ASP works. These include COM+, the various Internet service administration tools, and - indirectly - the many other services and installed software packages that either provide additional functionality to ASP, or which have interfaces that are available for use in ASP.
In fact, this is really where ASP comes into its own, and why it has become such an integral part of working with Windows on the Internet. ASP introduced the concept of using ActiveX interfaces or discreet component objects (separate controls that provide a COM interface) within Web scripts, rather than running external executable programs as had previously been the norm in other Web scripting languages (such as Perl).
This integration with COM and ActiveX means that ASP can effectively access anything on the Web server, or a connected network, which provides a suitable interface. From this alone, a huge market has grown up for components and objects that implement or encapsulate specific functions. More than that, almost all installed software and services in Windows either include a set of specific ActiveX components, or directly exposes a COM interface, to allow ASP to access it.
So, as well as chapters all about the roots of ASP, the base object structure, and how it's used, you'll also see chapters that demonstrate the many different ways that ASP integrates seamlessly with other software and services in Windows. One of the most obvious of these is access to data in a relational database or other type of data store (such as Active Directory), and you'll see several chapters devoted to these topics.
We'll also explore the intimate relationship between Internet Information Server and COM and the new COM+, and see how ASP has changed the way that it hosts and executes external components s provide better performance and scalability. This also affects the way that components are designed and built, and we'll be exploring this topic in some depth as well.
Who Is This Book For?
When Active Server Pages was first introduced, we produced a book covering that initial version from the point of view of a beginner to the technology. Fair enough, because at that point everyone was a beginner. However, the runaway success of ASP means that this is no longer the case. There are many millions of knowledgeable and active ASP developers out there who want hard-core technical coverage of ASP in its new version.
Then, when version 2.0 of ASP was released, we produced a separate beginner's guide, and this is also the case with version 3.0. This book is the Professional- level version, and is aimed at two categories of developers. Firstly, it is designed to satisfy the needs of those who are already well practiced in the skills of ASP and server-side Web application development. Secondly, it will be a useful and fasttrack guide to those who are less familiar with ASP, but have a server-side Web application development background - perhaps in the use of Perl, IDC, etc. For newcomers to Web applications and dynamic Web page creation, we recommend you look at Beginning Active Server Pages 3.0 (ISBN 1-861003-38-2) first.
Version 3.0 of ASP in itself is not an earth-shattering upgrade. In fact, ASP is reaching the point where there isn't much more that can be done with it. However, the wish lists of most developers should be fulfilled with the new version. There are many subtle changes to the way that it works, in the scripting engines that are included, in the Web server (IIS) itself and in the administration tools. There are also some fundamental changes in the IIS/COM(+) relationship, which it's important that you grasp. If you didn't really do much with COM and NITS in version 2.0 of ASP (a lot of people managed to ignore them), then now is the time to get to grips with them and start building pages and components to integrate with it.
Most Helpful Customer Reviews
Unlike the other reviews, I do not believe a beginner should jump into this book. Don't get me wrong, I absolutely love this book and I still reference it from time to time. However, anyone already familiar with other programming languages would benefit by starting with this book because you will already be familiar with the syntaxes and simple programming logic.
If you've already experienced ASP power this book is just what you need. DO NOT STICK WITH anything else but this book if you want to become real ASP programmer ...but know be carefull...
An excellent book like other WROX series covering vast areas like ADO 2.5, Directory services, XML etc. The case study is an excellent example to learn the practical application of XML and there is also a worth of information on ADO. Readers may find some problem in COM+ section if they are not very much familiar with COM technology and that is fair too.
I think its a very good book for beginners and i have enjoyed reading through it.
Its a great book for the beginner programmer but its a terrible book for the advanced programmer. The fact that the books dosen't have a section only to NEW FEATURES makes it worthless to an advanced programmer. You need to read the whole book just to see the new features. I was very disappointed.
This book is a must for all serious ASP programmers who want to have the latest info. on this emerging technology. Not easy but well worth getting into. Not for beginners!!