To read the article online, visit http://www.4GuysFromRolla.com/webtech/092599-1.3.shtml

Data Shaping, Part 3


In Part 2 we looked at the syntax for data shaping. Great, but how do we obtain a data shape through ADO? For starters, we need to set the Provider option of the Connection object to MSDataShape. So, before you open your Connection object, make sure you set the provider. Creating and opening a connection object might look like:

   Dim objConn
   Set objConn = Server.CreateObject("ADODB.Connection")

   objConn.Provider = "MSDataShape"
   objConn.ConnectionString = "DSN=University"
   objConn.Open

The rest of the code doesn't change much. We simply use a recordset to obtain our data shape. We can create a recordset object and use the Open method, just like normal. Here is some syntax:

   strShapeSQL = "SHAPE {SELECT ProfessorID, FirstName, LastName, " & _
			"PhoneNumber FROM Professor}" & _
		" APPEND({SELECT ClassName, RefNumber, ProfessorID " & _
			"FROM Class} AS Class " & _
		" RELATE ProfessorID TO ProfessorID)"

   Dim objProfessorRS
   Set objProfessorRS = Server.CreateObject("ADODB.Recordset")
   objProfessorRS.Open strShapeSQL, objConn

The above code uses the data shaping syntax that obtains the list of professors and the classes they teach. This syntax was shown in Part 2. Now, we can simply iterate through the recordset of professors! Remember, we have a class recordset inside our professor recordset. We'll iterate through that for each professor. Here is the syntax to iterate through each professor and class recordset:

'Our class recordset Dim objClassRS Do While Not objProfessorRS.EOF Response.Write "<BR>" Response.Write objProfessorRS("FirstName") & " " Response.Write objProfessorRS("LastName") & _ " (Phone: " & objProfessorRS("PhoneNumber") & ")<BR>" Set objClassRS = objProfessorRS("Class").Value Do While Not objClassRS.EOF Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;" & _ objClassRS("ClassName") & _ " (" & objClassRS("RefNumber") & ")<BR>" objClassRS.MoveNext Loop objProfessorRS.MoveNext Loop 'Clean up... objProfessorRS.Close Set objProfessorRS = Nothing objConn.Close Set objConn = Nothing

Notice how the outter Do ... Loop steps through the professor recordset. Inside that loop, we set objClassRS to the Class recordset in the professor recordset! We then iterate through the class recordset. The output for this script is:

Dr. Smith (Phone: 123-4567)
    Analysis of Algorithms (CS355)
    Artificial Intelligence (CS341)

Dr. Johnson (Phone: 123-8901)
    Distributed Systems (CS333)

Dr. Mitchell (Phone: 123-1234)
    Formal Languages & Automata Theory (CS330)
    Study of Film (ART85)
    Software Engineering (CS301)

If you used the syntax where the Class table was the parent SQL statement and the Professor table the child, a slight change in the code above could yield a listing of classes and the professors that taught them. However, I will leave this as an exercise to the reader! :)

Well, that's data shaping in a nutshell. There are a bunch of other neat things that data shaping can do, such as related grandchilden, multiple related children... it's really quite amazing. Those topics are covered in Advanced Data Shaping!

Happy Programming!

(Read Part 1)
(Read Part 2)
(Read Advanced Data Shaping)


Attachments:

  • Some data shaping code in text format


  • Article Information
    Article Title: Data Shaping
    Article Author: Scott Mitchell
    Published Date: Saturday, September 25, 1999
    Article URL: http://www.4GuysFromRolla.com/webtech/092599-1.3.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers