Instead of multiple ORs, use the "IN" functionality:

SELECT ALL many, fields, selected, here FROM 'LDAP://path.to.server/'
WHERE objectClass='member' And siteid='mysite.com' And zip IN ('12345','12346','12347')

Also- do you really have your Zip filed as a CHAR or VARCHAR? Convert it to INT and your queries will speed up drastically. (INT searches are much faster than CHAR/VARCHAR searches.)

The zip_code field is stored as an INT. Not CHAR or VARCHAR. Could using the text comparison make it error out, or just cause it to work slower. Speed is not a problem so far.

ugh, sorry to double post

I tried using the In function, with no success.

One or more errors occurred during processing of command.

I didn't think Active Directory supported the In function.

My sql looked like this:

SELECT ALL ... FROM 'LDAP://...' WHERE objectClass='member' And siteid='...' And zip In (10001, 10002, 10003, 10005, 10006, 10007, 10009, 10010, 10011, 10012, 10013, 10014, 10016, 10017, 10018, 10020, 10038, 10041, 10045, 10048, 10095, 10098, 10110, 10118, 10120, 10121, 10122, 10123, 10158, 10165, 10166, 10167, 10168, 10169, 10170, 10171, 10172, 10173, 10174, 10175, 10176, 10177, 10178, 10199, 10270, 10271, 10279, 10280, 10281, 10282, 10285, 10286)

Probably slower. So change my examples to:

SELECT ALL many, fields, selected, here FROM 'LDAP://path.to.server/'
WHERE objectClass='member' And siteid='mysite.com' And zip IN (12345,12346,12347)

the In function is returning errors. even while using one zip as the value. i dont think you can use "IN" in active directory.

Ideas?

I tried splitting up the Or statements, such as:

(((zip = 12345) Or (zip = 12346) Or (zip = 12347)) Or ((zip = 12348) Or (zip = 12349) Or (zip = 12340)))

It works to a certain point, but if too much zips are added, it doesnt error out, it just doesn't return any records, even though it should.

Ideas?

P.S. The "In" statement doesn't work with ADsDSOObject.

If the IN function doesn't work, then I'd say your only other choice is to break up the queries into multiple queries (after the number of zip codes reaches a certain number) and then combine the results. How you actually do that depends on your programming language of choice.

One other thought- is there another driver you can use to access the LDAP data?

[edited by: LifeinAsia at 4:57 pm (utc) on Mar. 8, 2007]

I'm using ASP, and splitting up the queries into multiple recordsets is my last option, i know it would work, it would just put a lot of extra load time for the user, as well as putting load on the server.

i have looked into using a different driver, but nothing has worked out. here is the connection code:

Public Function RunQueryLdapWithPage(s_Sql, i_PageSize, i_PageStart)
On Error Resume Next
Dim o_LdapConP, o_LdapCmdP, o_LdapRsP
'create and open connection
Set o_LdapConP = Server.CreateObject("ADODB.Connection")
o_LdapConP.Provider = "ADsDSOObject"
o_LdapConP.CursorLocation = 3
o_LdapConP.Open "ADs Provider", m_sLdapUser, m_sLdapPw
Set o_LdapRsP = Server.CreateObject("ADODB.Recordset")
o_LdapRsP.CursorType = 3
o_LdapRsP.Open s_Sql, o_LdapConP
o_LdapRsP.PageSize = i_PageSize
If err.number <> 0 Then
If o_LdapRsP.State <> 0 Then o_LdapRsP.Close
If o_LdapConP.State <> 0 Then o_LdapConP.Close
Set o_LdapConP = Nothing
Set o_LdapRsP = Nothing
Exit Function
End If
'if there are no matching records, then exit
If Not(o_LdapRsP.EOF And o_LdapRsP.BOF) Then
RunQueryLdapWithPage = o_LdapRsP.GetRows(i_PageSize,((i_PageStart - 1) * i_PageSize) + 1)
PageTotal = o_LdapRsP.PageCount
End If
If o_LdapRsP.State <> 0 Then o_LdapRsP.Close
If o_LdapConP.State <> 0 Then o_LdapConP.Close
Set o_LdapConP = Nothing
Set o_LdapRsP = Nothing
End Function

If you have any ideas on how to change this driver, let me know.

Any ideas?

Sorry- I don't know ASP and I'm not familiar enough with the limitations of SQL for LDAP to offer any other suggestions.

anyone else have any knowledge of the limitations or workarounds for LDAP?

2 week later bump

I don't know if this works with LDAP for SQL.... but you could try using LDAP's builtin expressions.

Something like:

(¦(&(sn=Brahm)(givenname=Felipe))(!(zip=123456)))

This means:

(zip!= 23456) OR (sn == 'Brahm' AND givenname == 'Felipe')

So, for multiple "OR zip = .." you could use:

(¦(zip = 123)(zip = 456)(zip = 789)(zip = 159))

I used this for a LDAP search engine I coded for my university:
[cai.cl...]

There is a tool available for sql ldap - you can download the free ldap browser version from the following url
[ldapsoft.com...] -- This tool will allow you to run the select ldap sql statements.

For insert and update you need to download the trial version of Ldap admin tool from the following url
[ldapsoft.com...]