Description of problem: OpenOffice.org is unable to retrieve Evolution LDAP Address Book entries Version-Release number of selected component (if applicable): openoffice.org-core-2.0.2-5.17.2 How reproducible: Every time Steps to Reproduce: 1. Create an Evolution LDAP address data source using the wizard 2. Try to open the data source by clicking on the Tables --> Directory you defined Actual results: OpenOffice attempts to open the database, then fails with "The data content could not be loaded. Use SQL query with 'where clause' or 'criteria', to get the results." Expected results: As I am able to obtain all entries in Evolution without fail, I expect OpenOffice, using the Evolution LDAP address book could do the same. Additional info: See attached screenshot which lists the contents of the dialogue boxes that appear.
Created attachment 133641 [details] The contents of the dialogue boxes that appear
The first level problem is that the LDAP driver doesn't allow you to retrieve *all* LDAP information in one go, which is what that button does, probably because it assumes it will be huge and too large to process, you have to use a specific query instead. Though that area might also need a bit of work, I need to investigate it a bit.
So for FC-6 we need to fix http://www.openoffice.org/issues/show_bug.cgi?id=68383 Then (and should already be ok for FC-5) it's not possible to just list all contents of a LDAP database, a specific query must be used, e.g. use create->query in design view and click on the elements you want to see, in my case I choose full_name and email_1 and office, and then in the column that appeared I entered "Software Engineer" as the Office under Criterion, unticking the "design view" icon to see the SQL that this generates I see ... SELECT "full_name", "email_1", "office" FROM "RedHat" WHERE ( ( "office" = 'Software Engineer' ) ) clicking the icon beside it for "run query" and I get the list of all Software Engineers inside RH and their email and full names. I need to follow up with the evo guys about some specific issue regarding what can be searched for and what cannot, but in general it looks in reasonable shape.
Ok, I can do everything outlined in Comment #3, however, I am really trying to get postal address data from LDAP for mail merges, envelopes, etc. It seems that none of those attributes are searched for. To find out, I tried to use a filed like "city" in openldap which should map to "l" for locality in LDAP, but the ldap log showed that I was searching for "(objectclass="*")". I tried this with every single address field with the same result, which is telling me that those openoffice fields are not mapped to LDAP attributes. If I use the Evolution Local addressbook source, those same fields ARE mapped as expected.
Yes, that's pretty much the problem as it appears to me as well. I sent a few mails to some evo people with some examples of things which can't be searched for properly, e.g. "email_1". Was your city search done using "work_city" ? It really looks like that this is more of an e-d-s bug than an OOo one. Only certain (which ones?) elements can be used as search keys, though much more can be returned as search results.
i couldn't get search results for any of the postal address-related attributes, whether or not i used them as search keys. for example, if i used "full_name" in column 1 with LIKE '*' for the criteria and column 2 was work_city and column 3 was email_1, i got the full_name and email_1 results, but not the work_city. substitute any other postal address related attribute in place of work_city and get the same result. this is frustrating and it's the ONLY thing that keeps me using ms office! i must ask, if oo is using mozilla for ldap, etc. why must we go through evolution? couldn't we just go through mozilla's ldap addressbook instead, like thunderbird's address book? so have Thunderbird LDAP data source instead of Evolution LDAP data source?
It's be great wouldn't it, but that brings up a set of frustrating problems. a) you can't link OOo to the current system mozilla/thunderbird libraries because a.1) it doesn't build against contemporary mozilla a.2) if it did, you can't use the current in-use profile, so if mozilla/firefox is running, you couldn't access the LDAP stuff. There's an upstream moz bug about this. alternatively, do the upstream Sun-build thing and build against an older pre-built copy of mozilla which has the profile issue patched out, but then that gives us the headache of... b) you're linking against an older set of libs, so duplicated stuff for which a newer copy exists, though I could ignore that little bit of bloat c) I'm nervous about using these older libs as any known security flaws won't be fixed, and any new ones would have to backported to OOo every time a flaw in the re-used libs from moz becomes known. Upstream doesn't really care about that. d) the prebuilt stuff only exists for i386, not for ppc and x86_64. So we'd have to create our own ones for those platforms. The pain for d is pretty daunting, maintain a little mozilla fork to get the mozilla ldap from OOo stuff working. Or use the pre-built moz i386 stuff for i386 only, forget about the other platforms and hope for the best re any old moz bugs. It's a head-scratcher
Also, using Wireshark (formerly ethereal), I am able to determine that the ldap server is responding with postal address realted attributes, but for some reason, oo is not mapping them into anything.
since mozilla can't have duplicate profiles open at once and oo currently uses the mozilla profile, would it be possible to bring back directo oo-->ldap functionality by copying in memory the on-disk mozilla profile and opening that?
looking into this now again... Can you give me a sample output, modify it manually afterwards if you want to hide anything in particular, of a sample entry you are working with, e.g. something like ldapsearch -x -b 'dc=redhat,dc=com' '(mail=caolanm)'
Here's a sample entry. I'd like to retrieve any and all information I want from LDAP to put into any field in OO. I don't think that's too unreasonable as it's the whole point of a database :) Thanks for working on this again. This functionality, aside from printing envelopes with barcodes, is the only thing that keeps me using MS Word; and I'd love to stop using it. I use mostly the mozilla attributes, hoping that Mozilla will one day define an official schema compatible with all their products. I also use the Evo schema 'cause some of my people like that too. dn: uid=testuser,ou=People,dc=example,dc=com uid: testuser mail: testuser objectClass: organizationalPerson objectClass: posixAccount objectClass: top objectClass: shadowAccount objectClass: sambaSamAccount objectClass: evolutionPerson objectClass: mozillaAbPersonAlpha shadowMin: -1 shadowMax: 99999 shadowWarning: -1 loginShell: /bin/bash uidNumber: 500 gidNumber: 500 st: IL l: CHICAGO homePhone: +1 123 456 7890 mobile: +1 123 456 7890 sambaPwdCanChange: 1069166889 sambaPwdMustChange: 2147483647 sambaPwdLastSet: 1069166889 sambaAcctFlags: [U ] anniversary: 2000-01-07 telephoneNumber: +1 123 456 7890 birthDate: 1977-01-01 homeDirectory: /home/testuser postalCode: 60665 labeledURI: http://example.com/~testuser/ sambaSID: S-1-5-21-3903618366-1268580823-4026379918-2000 calendarURI: http://example.com/calendars/testuser.ics pager: +1 123 456 7890 shadowLastChange: 13302 sambaPrimaryGroupSID: S-1-5-21-3903618366-1268580823-4026379918-513 cn: Test User displayName: Test User gecos: Test User givenName: Test initials: A o: Super Company ou: Super Dept sn: User spouseName: Wife of Test User title: Title of Test User mozillaHomeCountryName: US mozillaHomeLocalityName: CHICAGO mozillaHomePostalCode: 60665 mozillaHomeState: IL mozillaHomeStreet: 123 4th ST mozillaHomeStreet2: APT 1 c: US mozillaHomeUrl: http://example.com/~testuser/ mozillaSecondEmail: testuser mozillaWorkStreet2: Box 123 mozillaWorkUrl: http://www.example2.org street: 123 5th ST homePostalAddress: 123 4th ST$APT 1$CHICAGO IL 60665$US postalAddress: 123 5th ST, BOX 123 description: Voicemail Access: +1 123 456 7890 mozillaCustom1: 2200
Created attachment 151754 [details] potential patch to add extra mappings to e-d-s ldap backend
Yeah, that works. So no changes needed in OOo itself, if the above patch is applied to evolution-data-server the the evolution address fields get filled in from the various common ldap entries for address book fields. Actually quite similar to what I see the groupwise backend in e-d-s does. i.e. when applied the following OOo query... SELECT "full_name", "work_addr_line1", "work_addr_line2", "work_city", "work_state", "work_zip", "work_country", "home_addr_line1", "home_addr_line2", "home_addr_City", "home_state", "home_zip", "home_country" FROM "localhost" WHERE ( ( "email" LIKE '@' ) ) gives address related results from a ldap entry such as the above, and you can see the fields in evo's own address book view for ldap entries. caolanm->mbarnes: I've upstreamed the e-d-s patch as http://bugzilla.gnome.org/show_bug.cgi?id=426893 With it in place then these common LDAP address entries get mapped into the evo address entry and can be displayed in OOo from a query.
The upstream patch was committed and the bug closed, so I'm closing this.