Walkthrough: Using DB2 Stored Procedures with PHP

As a Web developer, I want my DB2 database to do more than just store pretty things. I want to be able to show them off for all the world to see. PHP gives me a fun way to do that. But how do the two work together? And how can I use PHP to make the most of one of DB2's most powerful features: stored procedures?

In this walkthrough, you'll build a simple DB2 database from scratch, then populate it with some sample data. After that, you'll create a fairly complex SQL procedure, starting it with the wizard then tweaking it manually to add the finishing touches. And finally, you'll write a PHP script that uses DB2 PECL extensions to connect to the database and execute your stored procedure. This script will accept simple search criteria and display the results of three different SELECT statements, as returned by the procedure

In case you haven't discovered this already, you'll find DB2's implementation of stored procedures to be uncommonly powerful. You can create procedures in just about any language, implement a wide variety of logic and control, and, in fact, use them to move significant portions of your app code to the database server. See "Introduction to DB2 Stored Procedures" for more info.

To learn the basic process for creating a stored procedure in DB2, read "Walkthrough: Creating Stored Procedures in DB2." I'll review some of that here and then take you to the next step of integrating your stored procedure into a functioning PHP script.

Note that while this walkthrough was created on Windows, DB2 and PHP are both deployed on so many platforms that the basic techniques should work no matter what system you develop on.

In this walkthrough, you'll build a PHPDEMO database with three tables: ACTORS, MOVIES, and QUOTES. Then you'll create a PHP page that allows the user to search actor names, movie titles, and famous quotes, all at the same time. This is, of course, intended only to show you how stored procedures and PHP code work together. Your own project would be much more robust, with more complex table relationships and more detailed data. But it would most likely be based on the same principles you'll learn here.

As your first step, you'll create the database and its tables. I used Toad to do this ("Get the Jump on Toad for DB2") but you can also use the Control Center.

1. Using the DB2 Control Center, create a new database PHPDEMO. If you're using the wizard, you don't need to schedule a maintenance window. Accept all other defaults. Note that you may need to start up the DB2 engine by typing "db2start" at the DB2 => prompt in the DB2 Command Line Processor.

2. Create the following four tables. (You can also use the SQL script here.)

MOVIES
Field Name	DataType	Length	Nullable
MOVIEID	BIGINT	8	No
MOVIETITLE	VARCHAR	255	No
PEOPLE
Field Name	DataType	Length	Nullable
PEOPLEID	BIGINT	8	No
LASTNAME	VARCHAR	255	No
FIRSTNAME	VARCHAR	255	Yes
QUOTES
Field Name	DataType	Length	Nullable
QUOTEID	BIGINT	8	No
QUOTETEXT	VARCHAR	32762	No
PEOPLEID	BIGINT	8	No

Bonus Points: Create a fourth table, many-to-many.

MOVIEACTORS
Field Name	DataType	Length	Nullable
MOVIEID	BIGINT	8	No
PEOPLEID	BIGINT	8	No

3. To enter the data, your best bet will be running the SQL code in Listing 1. (Download the sample data.)

4. At this point you should have a fully populated test database. As a simple check, go to the command line. If you haven't already connected to the database, do so now by typing:

db2 => connect to phpdemo

Then type:

db2 => select * from people where lcase(firstname) like '%juli%'

Executing SQL Queries in DB2 Instead of creating the database objects and populating them all individually, you might find it useful to run SQL scripts. The easiest way to do this is through the Control Center. Right-click on the database name and choose "Query..." This will bring up a "Command Editor." You can also select Tools -> Command Editor. Copy and paste your SQL script into this window and click Execute (the green arrow on the far left).

You should see a couple of names: Julia Childs and Julia Roberts. Remember this query—you'll see something like it again shortly.

If you're not seeing the result you want, first check that you're using the correct Schema name. In my case, I created the table under the "NULLID" schema (because I'm lazy) so I specify "select * from nullid.people..." Also, if you just want a quick check, leave off the where clause.

In "Walkthrough: Creating Stored Procedures in DB2." you can see how to create a Java-based external stored procedure. For this demo, just for variety, you'll create an internal SQL procedure instead, and a fairly complex one at that. With the versatility of SQL, and the even greater versatility of using other languages for DB2 stored procedures, you can pull dissimilar data from multiple tables in a variety of ways, such as carefully formed UNIONs or temporary tables. But to demonstrate some cool PHP commands, you're going to generate multiple result sets, which the script will then iterate through.

5. Open the Developer Center. Assuming you haven't created a project for this database yet, do so now by choosing Create Project and following the wizard.

6. Go to Project View and right-click on the Stored Procedure folder. Choose New -> Stored Procedure Using Wizard. Select the following options:

• Select SQL stored procedure
• Name your procedure SEARCHALL
• On the Definition page, for Result Set choose "Multiple". Next to Statement, where it says "Single SQL Statement", click the ellipses and select "Generate multiple SQL statements". On this page, you'll see a SQL Assist button. Click it. See what happens.
• Using SQL Assist you can quickly build basic queries, complete with WHERE clauses. Go ahead and build three independent queries (click Add to add a new query to the mix). Don't worry if they're not perfect, you'll review the SQL code in a moment.
  • For the MOVIES table, add a WHERE clause for the MOVIETITLE field. As a placeholder, I specified "LIKE '%juli%'".
  • For the PEOPLE table, add a WHERE ... OR clause for FIRSTNAME or LASTNAME.
  • For the QUOTES table, try something a little trickier. Using only SQL Assist, do an inner join on QUOTES and PEOPLE where PEOPLEID matches. In addition (AND), specify the same WHERE as for the PEOPLE table, above—matching the criteria on either FIRSTNAME or LASTNAME (use parentheses). The end result should look something like "...WHERE x AND (y OR z). For now, you're not going to search the text of the quotes table, though you can easily change the query to do just that.
• After all three select statements are created, click OK to get back to the Definition page, then Next to move on.
• On the Parameters page, add one parameter: an IN parameter called sSearch (varchar: 255). Click Next.
• Options are optional, so skip it this time around. Click Next.
• If the Summary looks good, click Finish.

7. Once the procedure is generated, you'll want to make a few tweaks. But first, test it. In either your Command Editor or the command line processor (CLP), type:

call NULLID.SEARCHALL ('Juli')

Again, replace "NULLID" with whatever schema you are using. If you run into problems, here are a few things to watch out for:

• In the Command Editor, be sure to Add the PHPDEMO database as your target.
• If you use all lower-case, as in "juli", then your result sets may be empty right now because you didn't yet specify LCASE in the WHERE clauses. You'll do that in just a moment.
• You might also just need to rebuild your stored procedure. In testing, mine returned only 0 record result sets. But after editing the procedure and rebuilding it, I got all the results I was looking for.

8. On that note, go ahead and open up the procedure now. You need to make some modifications anyway. You could have done these along the way but why would you want to miss out on another way to tweak your code?

• In the Development Center, right-click the new procedure and choose "Edit". If your Editor View is not already open, select it from the View menu.
• Replace the contents of your procedure with the following SQL code:

  CREATE PROCEDURE NULLID.SEARCHALL ( IN sSearch VARCHAR(255) )
  DYNAMIC RESULT SETS 3
  ---------------------------------------------------------------------
  -- SQL Stored Procedure
  -- sSearch
  ---------------------------------------------------------------------
  P1: BEGIN
  -- Declare cursors
  DECLARE cursor1 CURSOR WITH RETURN FOR
  SELECT MOVIES.MOVIEID, MOVIES.MOVIETITLE
  FROM NULLID.MOVIES AS MOVIES
  WHERE LCASE(MOVIES.MOVIETITLE)
  LIKE '%' || LCASE(sSearch) || '%';
  DECLARE cursor2 CURSOR WITH RETURN FOR
  SELECT PEOPLE.PEOPLEID, PEOPLE.LASTNAME, PEOPLE.FIRSTNAME
  FROM NULLID.PEOPLE AS PEOPLE
  WHERE LCASE(PEOPLE.FIRSTNAME)
  LIKE '%' || LCASE(sSearch) || '%'
  OR LCASE(PEOPLE.LASTNAME)
  LIKE '%' || LCASE(sSearch) || '%';
  DECLARE cursor3 CURSOR WITH RETURN FOR
  SELECT QUOTES.QUOTEID, QUOTES.QUOTETEXT, QUOTES.PEOPLEID,
  PEOPLE.LASTNAME, PEOPLE.FIRSTNAME
  FROM NULLID.QUOTES AS QUOTES, NULLID.PEOPLE AS PEOPLE
  WHERE QUOTES.PEOPLEID = PEOPLE.PEOPLEID
  AND (LCASE(PEOPLE.LASTNAME)
  LIKE '%' || LCASE(sSearch) || '%'
  OR LCASE(PEOPLE.FIRSTNAME)
  LIKE '%' || LCASE(sSearch) || '%');
  -- Cursor left open for client application
  OPEN cursor1;
  -- Cursor left open for client application
  OPEN cursor2;
  -- Cursor left open for client application
  OPEN cursor3;
  END P1

• Take a close look at this code and you'll see some minor but important changes.
  • First off, delete or replace the NULLID if you're using something else.
  • Note the use of LCASE to normalize the fields and search parameter.
  • More importantly, note how the sSearch parameter is concatenated with the wildcards using "||".
• Using the toolbar, Save, Check, Build, and Run the procedure, in that order. Test it with our friend "juli"—you should see three separate result sets filled with data. Also try "steve" - the first result set should be empty, with one row in each of the other two. You may need to click on the Results tab and page through to see all three result sets.
• Also try going back to your Command Editor and re-running the "call" statement. You should get the same results.

For this step, rather than walking you through each line of code, I'll just give you the finished product. Copy and paste from Listing 2 or download from here.

Granting Execute Authority Before this will run, you may need to grant the correct permissions, primarily EXECUTE permissions for "IUSR_CHANNEL". To do this, follow these steps: Go to the Control Center and right-click on the database itself. Choose "Authorities..." and go to the User tab. Click the "Add User..." button. Select IUSR_CHANNEL and click Ok. In the User tab, select IUSR_CHANNEL. Click "Grant All". Click Ok.

A few particulars you'll want to make note of:

• You need to have the DB2 PHP PECL Extension installed to access the database. Read "Installation of Extensions on Windows" for more information or download the extension from here.
• I haven't included any validation or heavy security here. A more robust page will have some scripting to clean up the form entry, stripping any tags or potentially malicious syntax, as well as catching errors.

Let's cut to the chase. How do you hook DB2 up to PHP? When I'm exploring a new database / language relationship, the first thing I like to learn are those database calls. In this sample script, you'll want to focus on the following three functions:

• db2_connect
• db2_prepare
• db2_execute

The SQL statement here is really nothing more than the one you were testing with, spiced up a bit to add the search criteria.

To iterate through the records, the DB2 team has provided some crazy powerful functions to play with. As you can see here, the db2_fetch_array function, my favorite, sucks everything into an easy-to-use array.

Figure 1. PHP Demo Form

But the function you should especially look at is db2_next_result. This lets you iterate through the result sets themselves, giving you a way to take advantage of the multi-select statement you created in DB2.

For more information about PHP commands for DB2, read "Making the Most of PHP with DB2."

Try it out. Navigate to your new php page and see what happens. It should start out with a blank form, as in

In the search box, enter "juli" and click Search. The page should call the procedure, receive the results, then iterate through those results to display the following:

Figure 2. Search Results

Just for kicks, enter a new search term, perhaps "Swayze".

So far, this search tool is pretty gimped in some ways. You can make it a lot better. To practice your newfound skills, try adding some of these features:

• Search on the text of the quotation as well as the speaker.
• If the search turns up a name, get any movies that person has been in (use the MOVIEACTORS table if you added it in Step One). Or conversely, if the search turns up a movie, display its actors.
• For extra-extra credit, try coding the Kevin Bacon game. Create a form where you enter an actor's name and a number indicating how deep you want to go, such as 3 levels. Then write a script and a procedure that pulls the actors that person has worked with, the actors they've worked with, and so on as deep as the levels indicated.

You've seen what you can do by combining DB2 stored prcoedures with PHP. Now just imagine what you can do with PHP's SimpleXML functions in a SOA environment based on DB2 Viper. The upcoming version of DB2 includes some amazing native XML support. Using SQL and XQuery to view, update, or otherwise change data in a secured environment gives you even more options for even more clever applications. Stay tuned for more!

Email Article Print Article Download PDF Digg del.icio.us Newvine furl StumbleUpon BlinkList Newsvine Magnolia Facebook Tailrank Slashdot Technorati Google Bookmarks Yahoo Favorites Windows Live Ask