SF Bay Area
This ASP class addresses advanced topics in database and XML integration. You'll learn the optimal way to connect to databases in different circumstances, and some of the advanced features of recordsets. This class will also investigate XML in the context of databases and Active Server Pages: exporting database rows as XML and using VBScript to parse the contents of XML text.
User, System, and File Data Sources
Instead of connecting directly to the database with a connection string indicating the driver in your ASP code, you may wish instead to create a "Data Source" as an intermediary that modularizes the connection to the database. Once you've created a data source, you can connect to it through VBScript, Perl, ColdFusion, or even through a database client program -- so in many ways data sources are more flexible than driver connection strings covered in the ASP Intermediate class. Configuring a data source is done through the Windows Control Panel called "ODBC Data Sources". This class will show you how to set up both Access and SQL Server data sources and the pros ands cons of each type of data source (user/system/file).
OLE DB Providers and Microsoft Data Links
In addition to driver connection strings, you can use OLE DB (Object Linking and Embedding for Databases) provider connection strings to designate a particular database. This class will show you how these provider connections differ from DSNless connections. You'll also learn how to use the Microsoft Data Link to figure out what the OLE DB provider should be for a particular database.
Cursors and Lock Types
Providing the database supports them, your ASP code can use different "cursors" to retrieve recordsets. Each cursor has advantages and disadvantages, whether in terms of speed or functionality. We'll explain how to choose the right cursor and specify how it should be used. You'll learn how to move up and down a recordset that's been retrieved from a database and update the field values in it. You'll see how to avoid the common pitfall of accidentally inserting text with apostrophes (e.g., the last name "O'Reilly"), that would otherwise conflict with the apostrophes delimiting parts of your SQL statement. This class will show you how to use a particular cursor type to easily retrieve a new, auto-numbered primary key from a freshly inserted row in a database. Along with cursors, another important property of recordsets is their "locktype", which specifies whether other scripts can concurrently update your recordset. We'll explain these and show you the best ones to use in different circumstances.
Sometimes it is necessary to ensure that an entire group of actions on a database is "rolled back" after already having performed some of them. For example, if a bank customer writes a check to another bank customer, the account of one must be debited, and the other credited -- but if only one database update succeeds, there will be an imbalance of funds, and the other update would have to be canceled. This class will show you how to implement this kind of transactionality in your code.
Exporting a Recordset as XML
XML is text that is marked up with your own chosen tags, whether they're particular to your industry, or perhaps just to your company. As text, it has the advantage of being understood by many different types of programs on many operating systems, just like text email messages or web pages can be understood by Windows, Unix, or Macintosh computers. We'll look at the example of using an ASP to output an Access database recordset as text XML.
Executing a SQL Statement on an XML Recordset
Given a recordset in XML format -- that is, text fields surrounded by tags designating field names -- it would be useful to run a SQL statement on that text. It turns out that ASP has a facility for doing just this. Through this class, you'll see how this can be used in conjunction with XML recordset output to turn a remote Access database into a full-fledged server that can be queried from across the web.