- Storing the MyInformIT Data
- From Recordset to Browser
- Finally, the HTML
- Deleting Unwanted Entries
From Recordset to Browser
Though I'm sure it will be very straightforward for all of you, I'll go through the process that pulls the recordset containing the MyInformIT saved content and plunks it into your browser. We'll start in the ASP layer in the function myinformit/index.asp. Let's walk through an example that will display the user's list of saved books:
1 <% 2 GenericMyInformit_Display _ 3 "Books", _ 4 "", "Books", _ 5 5, 20, false, false, false, false 6 %>
This digs into an include file, includes/buckets/GenericMyInformit.asp. The top of the function is displayed below, if you want to play "match up the arguments." The salient argument is "Books", which matches l_major_desc, and, more importantly, l_product_group_name.
1 <% 2 Function GenericMyInformit_Display 3 ( l_major_desc, _ 4 l_product_group_id, _ 5 l_product_group_name, _ 6 l_number_of_results_to_display, _ 7 l_number_of_more_results, _ 8 l_show_product_type, _ 9 l_show_published_date, _ 10 l_show_authors, _ 11 l_debug )
I'll skip over the setup of variables in this function and get right down to its brass tacks.
1 Set objMyInformIT =
Server.CreateObject("informit.myinformit")
2
3 objMyInformIT.setUserID( get_user_id() )
4 ' get_user_id() is from session
5
6 objMyInformIT.setProductGroupID( l_product_group_id )
7 objMyInformIT.setProductGroup( l_product_group_name )
It looks worse than it is—this particular developer is fond of long variable names. (No, it isn't me. I cut my teeth on FORTRAN and prefer ultra-descriptive variable names like i and x).
On line 1, we create a reference to the informit.myinformit Java object. On line 3, we pull your user_id from your session_id. Most likely, we retrieved your session_id from the query string. Check it now. It should be the first argument after the question mark. Lines 6 and 7 set the product_group_id and product_group_name for the object. For the purposes of our example, the product_group_name is "Books". The product_group_id is a uniqueidentifier that ties to the group_name.
As an aside, let me explain the concept of product groups as InformIT uses it. We provide access to a number of different types of products: books, e-books (or Free Library books), articles, white papers, Q&A articles, discussions, et al. It would be tedious in the extreme to determine how to display each one of these product categories individually, so we group them by type. Thus an article is grouped as product_group_name Articles (a stretch, I know). So are sample chapters, Q&As, and white papers. Each product_id has an associated product_group_id that ties back to the product_group_name.
Continuing with the code snippet, lines 8–11 set the number of results to display on a page. This was passed to the function in the call to GenericMyInformit_Display.
8 If Not l_number_of_results_to_display = "" Then
9 objMyInformIT.setPreferredResultCount
l_number_of_results_to_display, false
10 objMyInformIT.setMaxRecordsetSize
( pc_start + l_number_of_results_to_display )
11 End If
12
13 objMyInformIT.getBucketResults()
14
15 Set dsMyInformIT = objMyInformIT.items()
16
17 GenericMyInformit_Display_HTML dsMyInformIT, _
18 l_major_desc, _
19 l_product_group_id, _
20 l_product_group_name, _
21 objMyInformIT.getResultCount(), _
22 objMyInformIT.getTotalAvailableCount(), _
23 l_number_of_more_results, _
24 l_show_product_type, _
25 l_show_published_date, _
26 l_show_authors, _
27 l_debug
On lines 13 and 15 we see the now-familiar calls that retrieve a pointer to our MyInformIT book list recordset. We pass that recordset data, on lines 17–27, to another function that takes care of enumerating the list into HTML. Before we examine that final step, let's peek at the Java and stored procedures behind the informit.myinformit.getBucketResults() method on line 13.
1 BucketDB BucketDB = new BucketDB();
2 RS = BucketDB.getMyInformitByProductGroupName
( getUserID(),
getProductGroup(),
getMaxRecordsetSize(),
l_total_available_count );
We call yet another method, passing in our user_id, product_group info, and two variables relating to the number of records returned. Remember that the user_id and product_group_name were set as public variables in the myinformit object in the GenericMyInformit_Display function.
Within the BucketDB() Java, we find this:
1 public Recordset getMyInformitByProductGroupName
( String user_id,
String product_group_name,
int max_recordset_size,
int_wrapper count )
2 {
3
4 StoredProcedure StoredProcedure =
new StoredProcedure();
5
6 StoredProcedure.appendParam(
7 "@user_id",
8 AdoEnums.DataType.GUID,
9 AdoEnums.ParameterDirection.INPUT,
10 38,
11 user_id );
12
13 StoredProcedure.appendParam(
14 "@product_group_name",
15 AdoEnums.DataType.VARCHAR,
16 AdoEnums.ParameterDirection.INPUT,
17 50,
18 product_group_name );
19
20 StoredProcedure.appendParam(
21 "@max_recordset_size",
22 AdoEnums.DataType.INTEGER,
23 AdoEnums.ParameterDirection.INPUT,
24 32,
25 new Integer(max_recordset_size) );
26
27 StoredProcedure.appendParam(
28 "@count",
29 AdoEnums.DataType.INTEGER,
30 AdoEnums.ParameterDirection.OUTPUT,
31 32,
32 null );
33
34 StoredProcedure.openStandardClient(Database.dsn(),
35 "sp_buckets_get_myinformit_by_product_group_name");
36
37 count.int_value =
38 StoredProcedure.Parameters.getItem("@count").getInt();
39
40 return ( (Recordset) StoredProcedure );
41 }
If you've been paying attention (shame on you if you haven't!), you'll recognize our homegrown procedures for communicating with the Java database access objects. Above we create a StoredProcedure object and feed it our four parameters. Note that the last, @count, is an output variable. We then call the stored procedure (take a deep breath), sp_buckets_get_myinformit_by_product_group_name. When that returns, we have a recordset of the book list and a count of the records fetched.
sp_buckets_get_myinformit_by_product_group_name looks like this:
1 CREATE PROCEDURE
[dbo].[sp_buckets_get_myinformit_by_product_group_name]
2
3 (
4 @user_id [uniqueidentifier],
5 @product_group_name varchar(50),
6 @max_recordset_size int,
7 @count int OUTPUT
8 )
9
10 AS
11
12 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
13
14 SELECT
15 @count = count(*)
16
17 FROM
18 products P,
19 product_groups PG,
20 product_types PT,
21 linked_products LP
22
23 WHERE
24 LP.user_id = @user_id
25 AND PG.product_group_name = @product_group_name
26 AND LP.product_id = P.product_id
27 AND P.product_type_id = PT.product_type_id
28 AND PT.product_group_id = PG.product_group_id
29
30 SET ROWCOUNT @max_recordset_size
On lines 14–28, we do a four-table join to determine the total number of records retrieved from the linked_products table for this product_group. The products, linked_products, and product_groups tables will be familiar. We add the product_types table to link back from the product_group to pull any products of various types that exist in that group. We send the total number back in the variable @count.
31 SELECT 32 P.product_id, 33 P.product_name, 34 P.second_product_name, 35 P.published_date, 36 PG.product_group_name, 37 PT.product_type, 38 LP.element_id, 39 CE.element_name, 40 P.free_IT_library_flag 41 42 FROM 43 products P, 44 product_groups PG, 45 product_types PT, 46 linked_products LP, 47 content_elements CE 48 49 WHERE 50 LP.user_id = @user_id 51 AND PG.product_group_name = @product_group_name 52 AND LP.product_id = P.product_id 53 AND P.product_type_id = PT.product_type_id 54 AND PT.product_group_id = PG.product_group_id 55 AND LP.element_id *= CE.element_id 56 57 ORDER BY 58 LP.date_added desc 59
We perform essentially the same query on the lines above, but we add the content_elements table in case an element_id is part of the MyInformIT record. My last article explained that the content_elements table stores the actual content for a product that has multiple sections, like products of type Articles. Our Books query shouldn't have any associated element_ids, so this won't come into play. Because we still want to return records from the linked_products table, even when the element_id is NULL, we use the *= notation on line 55. This is the "old" way of writing a LEFT OUTER JOIN. It simply instructs the query to return a null in that column if there are no matches between the left side of the line (LP.element_id) and the right (CE.element_id). Without the *, the query wouldn't return a record for any linked_products entries without an element_id.
When we return from the stored procedure, the @count variable is set, and we pass back the recordset containing the nine fields specified on lines 32–40.