Published: Saturday, September 25, 1999
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:
<![CDATA[
'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 " " & _
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
