Database Application 10/10/01 We're going to talk about stored procedures in more detail than last year. Front – data validation Middle – we'll talk about more later SQL – we'll use stored procedures They're very efficient. More efficient to call them than to hard code. They are compiled. Most of the work we want to do we will do with stored procedures. The stuff we're going to do tonight would like different in Oracle and other apps. Not standard. Procedural aspects of SQL. We need to write some programming codes – like if, then, elses – to get some stuff done. See handout #6 You can have comments in SQL code. Comments /* alsdkfjalsdf */ This is called block comments. You can have single line comments: - - comments like the apostrophe in VB Compiler ignores comments If-then If (____) ________ If-then-else If (____) ________ begin ___ No "then" If If "else" is only one line, you don't need "begin and end" If () Begin --- --- End Else Begin --- --- end while ( ) begin --- --- --- end SQL syntax is less complicated than VB We'll looking at "delete" Input parameters that have data we want to pass to the procedure Output parameters that have data we want to get back from the procedure We're always going to pass some sort of communication back to the client Our handouts suggest that we send back 2 things … Return some integer that returns success or failure. We'll call that "return code". They like to send back a "1" for success. They do this because "1" represents true in Access. The standard for success is "0". We'll use this. This is what he uses … 0 – Success 1 – Missing data 2 – Object is not in the database We can use our own, but be consistent. We can add more, but for now, this is enough. There is more we can do to write these procedures than what he'll show us. It takes a lot of error code to catch stuff. We're going to validate stuff before we take any action. We just validated stuff in our client, so why do this? That's what happens when we use our client. They may send stuff that uses a client that doesn't validate. Validation occurs in all three places because you don't know how these three things will work together. Return message – a string that explains these return codes "Return message" We need to validate data and use these return messages in order to send it back to the user. You need a block of comments (called a "header block") to show what the inputs are and what the outputs are. This is because 3 different people may be working on different tiers. They may not understand exactly what the procedure does. Create procedure [name] This year, we'll add "who it belongs to". We are dbo Dbo. Some name that makes sense ProcCustomerDelete Proc = procedure Cust = name of object Delete = action Parameters start with the @ sign Declared data type = null (we didn't do this last year) If somebody forgets to send me this parameter, and I try to run something against it, the DB is going to return a bad error message. I can get out gracefully rather than letting the system blow up if I check this before doing anything. By default, a parameter is input if you don't specify other. @@ are built-in system functions @@identity = give me the last identify field generated @@error = give me the last error generated @@rowcount = how many rows were affected by the last thing you ran In BooksONline, there's a list of all this stuff. Look for built-in functions. There's not that many, but they are necessary. You create the procedure with its parameters Keyword "as" Everything below it is SQL code – including the new code we're going to learn, Set nocount on "done in proc" - generates more traffic than we need If you don't put it there, SQL sends messages back to the client every time If you're using ADO, you can't get the "records affected" Use "is null" – not "= null" Select return code = 1 Notice the comma because it's all one statement Notice they are single quotes – not double quotes "Return" means "return from the procedure" It's like an "exit sub" in VB You don't have to put it at the bottom. If you get to the bottom, and you run out of things to do, it automatically returns anyway. It's up to you. "Delete customer …" looks just like you'd expect If it works, it should have deleted one customer Rowcount checks this. If rowcount = 1, then all is okay which returns a return code of 0 In SQL you can't concatenate things of different data types, so you have to physically convert it yourself to a string for the message. If the row count isn't 1, something went wrong. Typically what went wrong is that the customer ID isn't in the database. We're making that assumption here, although in the real world, you'd have to work a little harder for that. Other things that could go wrong … Referential integrity But, we don't have to deal with this right now He's putting in some minimal validation stuff this year – as opposed to last year (we didn't do that). This will catch most of the problems we'll have. The problem it won't catch is if you have a problem with referential integrity. Moving onto the Enforcing ref. Integrity: Restrict delete – no Cascade delete – no!! Nullify – you can do this in code If I delete something that has references, go to the delete and make them null. We'll have to build these into our procedures. We get restrict by default. We can ask for a cascade. Nullify you still have to do yourself. One advantage of using stored procedures is that you can do this. It's not hard to write a procedure that does this. Customers are easy to delete because the only thing that customer points to is the state table. Update … Update looks about the same, but there are a # of issues with it. The problem with updates is that you don't know how many fields you want to update. You won't know until they fill out the form. You have to check each parameter and decide. That takes a lot of code. Ours is just going to replace the entire record. The things that are new will be updated. The things that are old will just be re-copied. This is normally not efficient enough, but it will work for us here. A bunch of input parameters. 2 output parameters If I'm updating 1 person, it's okay. If rowcount is not 1, we assume that the customer is not there or that user didn't provide a customer ID. Other things could go wrong. It could have an invalid state, for example. Use "Alter" to change stored procedures. You can use "alter" instead of "create". A lot of people do this. When it brings it into the alter, it automatically changes it to "alter". If you edit it in the Query Analyzer, it will do this automatically. He thinks that alter will create it if it doesn't already exist. If you're going to change a procedure, you have to change it instead of re-creating it. If you do it in the query analyzer, it's smart enough to know to put "alter" in there. Why do we have all of these "nulls" in this code? This will make them null if no value is passed to them. It looks like an initialization, even though that's what it looks like. It says, if I get no parameter value here, make them null. The "alter" statement does not create it. His handout says "alter" because he edited it and then changed it. Now we'll look at "insert". We're trying to insert a customer into the db. CustomerID is identify field. Part of output is identity field this time. No count. In our DB, we have to have all of these fields except phone #. Our db is going to freak out if we try to enter a customer without an address. Check every parameter to make sure it's there (except phone #) and build a string saying "it's required". I need to tell the client what exactly is missing. String. Concatenate. He uses ; to indicate the end of each. Some people use commas. "last name" required; "first name" required Etc. Send them back a string with delimiters. Our concatenation code reflects this. If any of these statements are true. What the client gets back is this error message. What happens if we violated a referential integrity or check constraint @@error 0 if all was okay After I do insert, I'm going to take the error code and put it in a string that says "Runtime error" It's possible to get more specific messages, but we won't do that here. This will work as part of Lab 3. Nvarchar takes 2 bytes Varchar is a default of like 30 The place to do this kind of work now is in the query analyzer. You can write, edit, debug, and test these procedures. The QA's functionality has been increased a lot since version 7.0. Make sure you're working in the right database. You might put a state table in master, but you don't want any of the others in there. In a key press event, suppose you're trying to restrict things to just digits, but when you say just "digits", that's all you get. You can't use backspace. You can't use decimal. Look at Blackboard regarding this. You can put #'s in the name field. Look at the key press event, decide which keys you want to allow. You might want to include the backspace and delete keys always. Whatever you specify is all you get. When you're dealing with things like currency, a lot of people have tried to validate using masks. You have conversion functions. You can write a function that says convert this string to currency. If it fails, you know it's not a valid currency. If it fails, run this little error routine. If it doesn't fail, you're okay. You can paste bad text in Windows, but we won't worry about this right now. The inventory one will be very different from the first ones. Database Applications Programming 10/10/01 Page 1 of 7