Querying ADSI's LDAP through ADO using VB6.0 and the "SQL Dialect".

By ServerWatch Staff (Send Email)
Posted Jan 17, 1999


Bmichely

Introduction

After many intranet and internet projects using every tool within reach, I became fond of writing Visual Basic Custom Business Objects for my web projects. About 90 percent of those projects have been database-driven using primarily Internet Information Server and SQL Server 6.5. As the projects became more complex, I needed client-side recordsets more and more often reducing the effectiveness of Active Server Pages. Researching a solution, I was introduced to the power of Microsoft's ActiveX Data Objects(ADO), and began shifting my program design to include Visual Basic dll's to make database calls through the business objects. I reached a point where everything was component driven, which meant that code changes only had to occur in one place, instead of several. Life was good.

After many intranet and internet projects using every tool within reach, I became fond of writing Visual Basic Custom Business Objects for my web projects. About 90 percent of those projects have been database-driven using primarily Internet Information Server and SQL Server 6.5. As the projects became more complex, I needed client-side recordsets more and more often reducing the effectiveness of Active Server Pages. Researching a solution, I was introduced to the power of Microsoft's ActiveX Data Objects(ADO), and began shifting my program design to include Visual Basic dll's to make database calls through the business objects. I reached a point where everything was component driven, which meant that code changes only had to occur in one place, instead of several. Life was good.

I then started using Microsoft Site Server 3.0. Getting familiar with this huge product, I realized it's potential with components supporting personalization, knowledge management and site management. At the same time. I began to learn ADSI and LDAP. Initially they threw me for a loop at first because of my comfort with making straight SQL Server calls. This led me to wonder, "How can I still achieve the benefits of the business object architecture without changing the way I do everything now that I have to go through LDAP?"

Solution: Querying ADSI's LDAP through ADO using VB6.0 and the "SQL Dialect".

At first this was puzzling since I was not that familiar with Site Server, ADSI or LDAP.

Using the Site Server Rule Builder, I could only achieve minimal results. To create the functionality I needed I wanted to run my own queries against LDAP. I had found some samples on using the "LDAP Dialect" and decided that I still wanted to use the "SQL Dialect".

Examples of each are shown below.

LDAP Dialect:

strADO = "<LDAP:// Test.testinglabs.com:1002/o=Microsoft/ou=PortalURL>;(&(objectClass=*));cn,adspath,sn,givenName;subtree"

SQL Dialect:

strADO = "Select Branch, Department, VendorURL, URLTitle, contentType, Abstract, CreateDate FROM 'LDAP:// Test.testinglabs.com:1002/o=Microsoft/ou=PortalURL ' WHERE objectClass = 'PortalURL' AND " & asSortOption & " = '*' Order By " & asSortOption & " " & strSortDirection

After much trial and error, I created a DLL with a few functions that I used to retrieve data as client-side recordsets. I can then loop through these recordsets and populate the areas of my page that I need to populate. I also took the same code and tested it by creating ASP include pages. Below is a sample function that retrieves promotional URL's that vendor's have posted to a Site Server Intranet web site.

 

 

 

NOTE that there is a problem with the "Ads Provider" that ADO uses. This problem occurs when you try to sort the ADO Recordset using "ORDER BY". Apparently the provider does not yet support sorting options. Supposedly Microsoft is still developing this provider, so eventually it will support sorting. IN THE MEANTIME I had to create a Bubble Sort function that resides in my DLL. This function takes arguments such as a sort parameter, a server string, and an ADODB recordset. This function will take the records in the recordset and re-sort them based upon the sort options you pass to it. The Bubble Sort function will then pass back the sorted recordset to the main DLL function that was originally called. This function will then pass back the recordset to the web page that called the DLL.

 

 

 

This function is called by the web page in the Site Server web project:

Public Function GetPortalURL(strContentType As Variant, asSortOption As Variant) As ADOR.Recordset

Dim objADOconn As New ADODB.Connection ' ADO connection object

Dim objRS As New ADODB.Recordset ' ADO recordset object

Dim strServer As String ' ADO connection string

Dim strADO As String ' ADO query string

Dim strSortDirection As String

'establish error handler

On Error GoTo GetPortalURLError

'Set sort direction depending on Sort Types

If asSortOption = "CreateDate" Then

strSortDirection = "DESC"

ElseIf asSortOption = "Branch" Then

strSortDirection = "ASC"

ElseIf asSortOption = "Description" Then

strSortDirection = "ASC"

End If

strServer = "Test.testinglabs.com:1002/o=Microsoft/ou=PortalURL"

Set objADOconn = CreateObject("ADODB.Connection")

'Create connection object

objADOconn.Mode = adModeUnknown

objADOconn.Provider = "ADsDSOObject"

objADOconn.Open "ADs Provider"

'check connection state

If objADOconn.State = adStateOpen Then

'Debug.Print "Connection is open." & "<br>"

End If

'create SQL Dialect select statement

strADO = "Select Branch, Department, VendorURL, URLTitle, contentType, Abstract, CreateDate FROM 'LDAP://" & strServer & "' WHERE objectClass = 'PortalURL' AND " & asSortOption & " = '*'" 'Order By " & asSortOption & " " & strSortDirection

'Debug.Print strADO

Set objRS = objADOconn.Execute(strADO)

'before setting the function's return value (recordset) , let's go sort it in the bubble sort.

Set GetPortalURL = BubbleSort(strServer, asSortOption, objRS)

Set objRS = Nothing

Set objCmd = Nothing

Set objADOconn = Nothing

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

GetPortalURLExit:

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

'clear error handler

On Error GoTo 0

Exit Function

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

GetPortalURLError:

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

'pass along the error...

Err.Raise Err.Number, Err.Source, Err.Description

Resume GetPortalURLExit

End Function

 

This next function is the Bubble Sort function:

Public Function BubbleSort(strServer As Variant, asSortOption As Variant, objRS As ADODB.Recordset) As ADODB.Recordset

Dim rsTemp As String

Dim i As Integer

Dim j As Integer

Dim tempIndex As Integer

Dim RECS As Integer

'establish error handler

On Error GoTo BubbleSortError

RECS = objRS.RecordCount

'redimension the arrays

ReDim aMain(RECS) As String

ReDim aLogical(RECS) As Integer

Randomize Timer

objRS.MoveFirst

For i = 0 To RECS - 1

ReDim Preserve aMain(i)

rsTemp = objRS.Fields(asSortOption).Value(0)

aMain(i) = rsTemp

objRS.MoveNext

Next i

For i = 0 To RECS - 1

ReDim Preserve aLogical(i)

aLogical(i) = i

Next i

objRS.MoveFirst

For i = 0 To RECS - 1

ReDim Preserve aMain(i)

ReDim Preserve aLogical(i)

aMain(i) = objRS.Fields(asSortOption).Value(0)

'Debug.Print objRS.Fields(asSortOption).Value(0)

aLogical(i) = i

objRS.MoveNext

Next

objRS.MoveFirst

'sort recordset

For i = 2 To RECS

objRS.MoveFirst

For j = 0 To RECS - i

If aMain(aLogical(j)) > aMain(aLogical(j + 1)) Then

tempIndex = aLogical(j)

aLogical(j) = aLogical(j + 1)

aLogical(j + 1) = tempIndex

End If

objRS.Fields(asSortOption).Value(0) = aMain(aLogical(j))

objRS.MoveNext

Next j

Next i

Set BubbleSort = objRS

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

BubbleSortExit:

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

'clear error handler

On Error GoTo 0

Exit Function

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

BubbleSortError:

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

'pass along the error...

Err.Raise Err.Number, Err.Source, Err.Description

Resume BubbleSortExit

End Function

 

 

 

 

 

Conclusion:

I can now access ADSI's LDAP the same way I have in the past with SQL Server 6.5. The DLL will create a recordset that was created from LDAP, sort it and pass it back to the web page or function that called it originally.

This functionality is fully modular, easy to change, and pretty powerful. I now have a Visual Basic DLL that I can continually add functions to, and that I can now just register the DLL on the server and it is fully functional at that point.

This article should help anyone who is now, or will be encountering the tasks of retrieving data from LDAP.

Page 1 of 1


Comment and Contribute

Your name/nickname

Your email

(Maximum characters: 1200). You have characters left.