CS433: Stored Procedure Assignment

Professor:� Johannes Gehrke (johannes@cs.cornell.edu)

TA�s: Vincent Gu (vg23), Scott Selikoff(sms65), Lin Zhu (lz26)

�� Stored procedures (SPs) are an important part of commercial database systems because they are an easy way to extend data storage and retrieval functionality.� SPs can be a collection of SQL statements, or can be SQL statements combined with other programming languages, such as Java.� SPs offer many efficiency advantages because they execute on the server computer and are precompiled.� This means there is no network delay when they are executed, nor is there any runtime compilation required.

�� In this assignment, you will be developing SPs for your database application.� Since XML is becoming the dominant form of data exchange, you will be writing SPs that converts several relational tables into XML.� There are several advantages to transferring your data as XML.� The first is that XML is semi-structured and so can easily handle hierarchal data.� For most database administrators, this means you can transmit normalized data in a single, compact piece.� The alternative is to transmit either a collection of normalized relational tables and do a join on the application layer, or to do the join on the server and to transmit a cursor object that contains many repetitions.� However, XML data must be parsed when it is to be used, which adds some cost to using XML.

Phase I: Creating a New Stored Procedure

�� You can create a stored procedure in Microsoft SQL using Query Analyzer.� Connect to your database in Query Analyzer as usual,� you can type the CREATE PROCEDURE command just like you would do for a normal SQL Statement.

The following is an example of a stored procedure named GetAllProducts that retrieves all the information for each product:

�� CREATE PROCEDURE GetAllProducts

�� AS

�� SELECT *

�� FROM PRODUCTS

�� ORDER BY PID

You can browse all of the stored procedures in your database in the Object Browser on the left.� Right click on a stored procedure gives you options to open/edit/delete it. Here�s a sample screenshot:

You can create very powerful stored procedures in MS SQL Sever.� Since it�s precompiled, it runs much faster than normal SQL statements.� Consult online sources for more information about creating SPs in Microsoft SQL, as there is much information available.� A good place to start is the �Books Online� in the Microsoft SQL Server directory of your start menu.

Phase II: XML Stored Procedures

�

�� In this phase of the project, you will be writing two stored procedures that convert a set of relational tables into XML.� When finished, you will have at least two stored procedures: one for Part A, and one for Part B.

Part A:� Single Table Translation

To begin, you will create a stored procedure and accompanying simple JSP that translates a single relational table into XML.� This can be seen in the following example:

Sailors Relational Table

SID	Name	Rank
1	Fred	5
5	Barney	4
12	Wilma	7

�� Sample output XML:

<all_sailors>

�� <sailor id=�1�>

�� <name>Fred</name>

�� <rank>5</rank>

�� </sailor>

�� <sailor id=�5�>

�� <name>Barney</name>

�� <rank>4</rank>

�� </sailor>

�� <sailor id=�12�>

�� <name>Wilma</name>

�� <rank>7</rank>

�� </sailor>

</all_sailors>

You may create a SP for any SELECT query you would use in your project.� Do not use our example.� Create a SP you for your project that you might need later on.

What to do:

For the Stored Procedure:� Create a simple JSP that calls a SELECT query on your table, in this case, Sailors, and returns the results as a result set.� Do not use the �FOR XML [option]� command in Microsoft SQL; else, you will get zero points.� We want your JSP to do the conversion.

For the JSP file:� Open a JDBC connection to your database and call the stored procedure you just created.� See the example file at the end of this document for how to call a stored procedure in JDBC.� Then, for each row in the result set, wrap the data in various XML elements and attributes.� Finally, return this result and display it to text.� Make sure to wrap all your results in tag similar to the <all_sailors> tags.

Note:� In order for most browsers to display the resulting output as XML directly, you need the following two lines at the head of your JSP file:

<%@ page contentType="text/xml" %>

<?xml version="1.0" encoding="UTF-8"?>

You may need to close and re-open the web browser while testing for it to recognize these commands.� If you are getting a blank page, try viewing the web page source to make sure there is data being returned.

One final note.� Use the �ORDER BY� option where applicable in SQL.� Its a simple command that can make your output look a lot cleaner.

Part B: Multiple Table Translation

�� In this exercise, you will create a more complex query than the one you did in the previous part.� For this SP, you will use nested looping to join two relational tables together and create XML output.� For example, below are two relational tables, Songs and Albums.� Albums contains an AlbumID an AlbumName, and a Price.� Songs contains a SongID, an AlbumID, and a SongName.� The high level description is that you have a table containing music albums, and a separate table containing the songs that belong to these albums.� Here is some sample data:

Albums Table

AlbumID	AlbumName	Price
10	Human Clay	13.99
12	Best of Tom Petty	15.99

Songs Table

SongID	AlbumID	SongName
888	10	Higher
889	10	Arms Wide Open
900	12	Learning to Fly
901	12	Free Falling

In SQL, one way to join these tables is using a join condition on AlbumID as in the following query:

SELECT A.AlbumID, A.AlbumName, A.Price, S.SongID, S.SongName

FROM Albums A, Songs S

WHERE A.AlbumID = S.AlbumID

However, this returns multiple copies of AlbumName and Price (one for each song in that album), and we do not want to do this.� Another way to retrieve this data, as we want you to do using JDBC, is to use a nested loop:

For each tuple A in Albums

� Retrieve the Album Attributes and Format it as XML

� Select all of the songs S� where A.AlbumID = S.AlbumID

For each tuple S in S�

Format the song output as XML

End For

In this example, you would call one SELECT statement for all the albums than one inner SELECT statement for each album.� For the above example data, the output might look like this:

<music>

� <album id=�10�>

�� <name>Human Clay</name>

�� <price>13.99</price>

�� <tracks>

�� <song id=�888�>Higher</song>

�� <song id=�889�>Arms Wide Open</song>

�� </tracks>

� </album>

� <album id=�12�>

�� <name> Best of Tom Petty </name>

�� <price>15.99</price>

�� <tracks>

�� <song id=�900�>Learning to Fly </song>

�� <song id=�901�>Free Falling </song>

�� </tracks>

� </album>

</music>

�� This is called a Nested Loops Join, and is used to denormalize your data.� XML is much better at handling denormalized data than relational tables since there is no repetition of values (one album for each set of songs).

What to do:

Choose two or three relational tables in your project that have a simple join condition and perform a nested loop operation similar to that of the above example.� There should be one SP for each table and one JSP file total.� SPs can take parameters so it would be wise for your inner SP to take such a parameter.� In the example above, for instance, you would great a GetAllTracks SP that took as a parameter the album id number.

In general, follow the same guidelines as were in Part A.� Use of �FOR XML [option]� is again not permitted.

What to �submit� (via the course management software):

Create a file that includes the following information:

- The names and netids of all group members

- Each SP saved to a file with the name:�� <StoredProcName>.sp

- One JSP for Part A and one for part B

- A documentation file including a brief description of what each SP does, what each JSP does, what tables they affect/use, and what parameters each requires

- The contents of your table (don�t include more than 10-20 sample records!)

- Sample Output for each of your stored procedure (if it�s long, just submit a sample)

If your tables contain a lot of data, you can shorten the output you supply in your document.� For example, if your relational table contains 10,000 records, do not include more than 10-20 sample records in your document.� If your XML output is very long, submit a sample of your XML that is still valid (fully adheres to the requirements of true XML).� Do not turn in excessive samples (we do NOT want 10 pages of output, just a sample J).

Example File for Calling a Stored Procedure in JDBC:

<%@page import="java.sql.*"%>

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

Try {

� �� // Open database connection

�� Connection con = DriverManager.getConnection

�� ("jdbc:microsoft:sqlserver://egret:1433;databasename=

�� dbname","user","pass");

�� // Set Stored Procedure Call Information

�� String sp =� "{call Your_SP_Name}";

�� CallableStatement cs = con.prepareCall(sp);

��

�� // Execute Stored Procedure

�� ResultSet rs = cs.executeQuery();

�� // Retrieve output as result set

�� while(rs.next())

�� out.println(...);

/*�� If the "for xml auto" command is used in the SP,�� */

/*�� it will retrieve all the results as one string using� */

/*�� the command:�� */

/*�� */

/* �� out.println(rs.getString(1));� �� */

�� // We always closer our DB instances

�� rs.close();

�� cs.close();

�� con.close();

�� } catch(Exception e){

�� out.println(e.toString());

}

You can actually specify input values, output values and return value for stored procedure in JDBC.� A quick google search reveals the following intro: Calling Stored Procedures