Database Application 11/14/01 Ch. 13 (test the week after Thanksgiving) ODBC Architecture Services Don't memorize code ADO – applications programmer interface Has architecture Don't memorize code This is all you need to get out of Ch. 13. The rest you can safely ignore. We will talk about this more next week. Services What can you get out of ADO Database connections Recordsets – with fields you can recognize Error messages You can get back the same kind of stuff in ODBC You have a program or something that needs to access a DB, but it can't access it directly. So, you have to go through something else to get there. The purpose of that thing in there (ODBC or ADO or OLEDB) is to provide a generic side over here so your program can say things like add a record, delete a record, etc. But then the DB on the other side can be anything. DB2, Oracle, SQL Server, Informix, Sybase, Access, etc. Once you know which one you're talking to, you load a driver. Driver takes generic commands and turns them into something specific for that object. It discusses in the chapter how to look at the drivers you have on your machine. Understand this connectivity. ADB fits well with certain languages – like C++, but it doesn't fit well with things like VB. So, they put a wrapper around it Api (applications programmer interface). ADO is one of those API's. Some of the things we've been doing in SQL can be done in ADO. That's good for programmers who don't want to mess with the SQL side. ADO (Microsoft) gives you an easy way to tap into this. ODBC is a generic standard. Most DB manufacturers make ODBC drivers. You can buy them. So, if you have something that needs to connect to DB2, somewhere out there you can find a driver that will allow you to connect to it. So that's the simple message out of that chapter. Everything else in the chapter is elaboration. Read it for the simple message. Figure out what ADO and ODBC are and what you can get from them and understand how they are used as a middle tier. Programming languages were not written to connect to databases. Ch. 11 (Final Exam) Database-driven websites In here, all you need to know is some sort of typical architecture for building a database for a website. Typical architectures are java or ADO, or whatever. Browser is front-end. How do you do things on the browser that we did on our front end? How do you do, for example, data validation. Understand the role of client side scripting. It is a client side programming facility. To send over the web, you need to know about client side scripting. Don't try to memorize the language. Most people use java script or some version of it. VB can also be used for this. Then, we have all of these protocols. What's html? What's xml? What's TCP/IP? Those types of things. Dhtml What do those protocols add to the architecture. HTML is a mark-up language for your webpage. XML is a way of dealing with structured data. Plain ordinary text. Anything that knows how to interpret this can use it. It's becoming an important protocol. TCP/IP is the protocol for Internet communication. There's some stuff on the server side. The problem on the server side is that Internet applications are stateless. It doesn't remember anything. The next time you access that server, it has no idea that you've ever accessed it before. State information. Typically it's done through cookies, but cookies are limited in what they can do. State information is provided through ASP. ASP, for example, allows you to create ADO objects. Look at the role of ASP. Architecture: Client Middle (protocols) Server All you need out of that chapter is a picture in your mind of an architecture and how it all fits together. In both 13 and 11, we're looking for big pictures – not tons of little details. If you have questions, ask. 2 easy tests. Transaction log Presentation tier (web browser). Needs to include data validation. The things available here are not as rich as what we've been doing. Middle stuff. What connects the client and server sides. Server side now has 2 pieces. Web server and database server. ASP allows you to create objects and save variables and other state information. The objects you create can be all kinds of things – including ADO objects. Lab 6 Good news and bad news. It's mostly a mechanical process, but it's easy to get lost in the details. Be careful when you do this lab. You'll figure out what he left out of the directions when you do it. Right now, you have a presentation tier in VB. So, you have: VB forms And, you have at least a form for inserting customers, inserting suppliers, inserting inventory, and processing orders. Those are the minimum forms you have. They don't actually insert anything yet. They do data validation and formatting. You have a globals.bas Utility routines Connect to database, product table, state table, etc. Database tier -- SQL Stored procedures You're going to hook them up in Lab 5. The goal is to get all of the Access stuff out of the Any place where you have a Connect-to-database, we want to get that out. So, we're going to build data access classes in Lab 5, and the classes will have a connect-to-database. Lab 5 Presentation tier VB forms Globals.bas Physically in the same project, build data access classes. Build one class for each object you're going to access in the DB. Classes: Customer class Inventory class Supplier class Order class Each class contains methods – ADO procedures you can use to execute stored procedures. Each will have: Insert Update Delete Get In the order class, you don't have that. Procedures are called "methods" in classes. In order class, you'll have process order method. Now, to make your forms work, you go to button, you So, you're going after data access through the classes. It's still in your VB project. It says that you should debug it there. You're 90% of the way there. By the time these are working, you'll be interacting with the database. You'll be calling a class method that's calling a stored procedure. Lab 6 Logical separation of functions. You have a logical separation of the functions here. Physically they're not separated yet. Lab 6, the point is to yank them out. If everything is okay in Lab 5, this is a mechanical procedure. We want to get all data access out of the VB application. 90% is in classes, but we still need to worry about: ? where we make lookup tables (because they actually access the DB) o Put those make tables commands inside a class. If you have a supplier class, put make supplier table method in there. So, you would pull it out of anywhere where it's in your VB code. You're now calling that method, and you have to get back a recordset, so you have to pass a parameter. See page 2 of Lab 6 handout. o It's not calling the stored procedure. It's executing a o Where do you put the state and the product? Make other classes. Make a state class, and you'll have one method in it – make state table. Make a product class with only one method – make product table. o How do you get them in your application. Look at Sub Main. These are minor adjustments. Fiddle with supplier first and get it to work. And then do the others. o Then, anyplace you have VB code, yank the VB code out of the form. Leave the validation on the forms. Some of the stuff we put in to get the validation to happen we can now take out. ? There's some functions in there that classes use, but they don't belong to any class. For example, the connect-to-database class. Blank-to-null. Make another class and put those things in – or you can give yourself a procedural module (data global.bas – see copy on p. 3). See picture on p. 5. Connect-to-database is in globals now. You're going to pull it out of there. Globals.bas will no longer have connect-to-db or blank-to-null. Yank out all the class modules and the data global and put it somewhere else. Before you yank it out, make sure it all works. Don't yank it out before you've made it all work. To yank it out, go off and start a new VB project (ActiveX DLL project type). DLL is a library of routines that other programs can access. There won't be any forms in there. What goes in that DLL is: Classes DataGlobal Module Reference to ADO There are things you have to set (explains in lab). Then, you compile it using "Make DLL". Give it a name. He called it MDAppServer. If it compiles okay, you will then have this DLL. DLL has all of the data access routines in it. Now when you go to your VB project, the DLL you just made will be listed there. You check it – just like you check ADO on that side. Then you can safely yank all of the classes out of your VB. See page 7 for MDAppServer. See page 9. Now, all your VB project has is one globals module and forms. It will look just like it did before. The difference is that you'll be using a middle tier. It will look the same to you, though. If Lab 5 works, Lab 6 will be easy. You need to make it run in front of him. You'll also turn in code including small VB project and DLL. He can see that they've been separated. Snagit is what he used to make the video. Run it through a MS compression program. www.techsmith.com Snagit Dubit Camtasia A bunch of tools for documenting. When you build classes, if you get errors about data type, it's probably the order of your parameters (or the # of them). If you're building a parameter for an SQL procedure, make sure you're changing it to the right data type. Make sure that they're being converted to the right thing for your stored procedure.. Database Applications Management 11/14/01 Page 1 of 5