Link to home
Start Free TrialLog in
Avatar of talal
talal

asked on

GroupWise Adress book and MS-SQL

Is there a way to update (Add/Delete/Update) GroupWise address beek from MS-SQL e.g. Stored Procedure or DTS?
Avatar of talal
talal

ASKER

of course... beek = book :P
Avatar of PsiCop
What's DTS?

What VERSION of GroupWise?

When you say "Address Book" do you mean an individual user's Personal Address Book, or are you referring to the GroupWise System Address Book?

Are you looking for real-time updates, or updated being performed in batch at a regular time?

What other tools do you have in your environment? For example, are you using Novell's Identity Manager to unify disparate systems (like eDirectory and AD)?
BTW, this Question is probably best-asked in the GroupWise TA --> https://www.experts-exchange.com/Applications/Groupwise/
Avatar of talal

ASKER

DTS is a Data Transform Service in MS-SQL server.
GroupWise 6.5

I have multiple User Defined Addressbooks that needs to be updated when the corresponding record in the database updates. I will take care of TRIGGERS to the table. But i need a way to insert/Update/Delete from GroupWise' addressbook from MS-SQL.

This question can be asked in MS-SQL section OR/AND GroupWise section. Is there a way to MOVE it or copy it there?
You'd have to open two separate Questions in order to get them into two separate TAs. However, you can open a "pointer" Question that just contains a link to your "real" Question.

You can post a (free) Question in the Community Support TA (https://www.experts-exchange.com/Community_Support/) and ask for this Question to be moved to the GroupWise TA.
ASKER CERTIFIED SOLUTION
Avatar of PsiCop
PsiCop
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Have you checked into the GroupWise programming tools available from Developernet?

http://developer.novell.com/groupwise/   is the "Develop to GroupWise" page.

http://developer.novell.com/ndk/gwsdk.htm  is the page for the GroupWise SDK

Since you're using GroupWise 6.5 and you want to update personal address books, you'll probably want to use C++ for the program so you can make it a trusted app.

You could also check into the Advansys Formativ products at http://www.advansyscorp.com   I believe they have an ADO method, and I know Formativ can be used to do all sorts of address book manipulation.  Since it's really made to enhance the GroupWise client, it would probably require the GroupWise client installed on your MSSQL server box.

If you want a 2-way sync between your database and your personal address books (if one is updated, so is the other) then your answer to PsiCop's query regarding Novell Identity Manager would be helpful.  XML can do lots of things, and IM is made for 2-way sync between disparate databases, as PsiCop mentioned.
Avatar of talal

ASKER

Hello ShineOn;
Although http://www.advansyscorp.com seem to be interesting, however i am not familiar with how it works so i am a bit hesitant to install on client env.

I asked the client about Novell Identity Manager and i will post as soon as i get a reply.

I am, mostly, a database developer so my ideal solution would be to "LINK" or have an update mechanism to GroupWise' address book. XML, ADO, etc or anything that enables me to update GroupWise' addressbook records. The requirement is one way update.

Thank you
Identity Manager is the tool that you want, it is a multi-database connector that can synchronize the GW Address Book with a myriad of other sources, including SQL, via XML.

http://www.novell.com/products/nsureidentitymanager/index.html?sourceidint=productscatmenu_nsureidentitymanager

-Mojo
If you do not have Novell Identity Manager, you can implement LDAP Address Book (you will need help from your sys adminso on this). Once LDAP address book has been implemented you can perform those updates from SQL server rather easily.
Avatar of talal

ASKER

I stayed up all night but finally i got it to work.
I ended up using a DTS package - > ActiveX VB.

It is working like a charm. I am testing it now .. when done i will post the solution here so others can use it too.

The reason i chose this solution is because:
1. Nothing new to buy/install for the client
2. COMPLETE fluxibility for mapping fields or processing logic.

It solved my problem because i can easily schedule it and it will not be a bother to te user

Thanks for everyone for your help however PsiCop put alot of time and efford on this matter. So i will give the points to him.

PsiCop just give me a day or so to make sure everything works :P i might need your help still :)

Thank you,
'Scuse me, but if you solved it yourself, after you post the solution, you *should* ask a moderator to close it with a refund.  If you *really* want to give away points, you should do it for a *good* reason.  

I don't like to be picky, but I feel like it today.  I won't complain to the mods if you do whatever you want, but I'm feeling a bit grumpy today so I will speak my mind here.

I actually pointed you toward several programming options.  You obviously chose a programming option.   PsiCop essentially asked a bunch of questions, some of which you answered.  I don't see how that rates giving all the points to PsiCop.

Like I said, do whatever you want.
Avatar of talal

ASKER

:-S The question i sked was help on Stored Proc or DTS and that was the direction i ended up going.

I appreciate your pointers specially http://www.advansyscorp.com. I may use them in future projects.

Anyways. I will wait a day or so to make sure that my solution WORKED for the client first then i will decide on this question.

Thank you,
"The question i sked was help on Stored Proc or DTS and that was the direction i ended up going."

Don't see any advice in either direction from any of us.

Still grumpy :P  Nothing personal.
Avatar of talal

ASKER

Here is the code i ended up using (of course this is reduced to show the point only, i removed the bigger pivture of the application.. But will demo the addition/deletion of GroupWise from MS-SQL DTS:


      Set objApplication = CreateObject("NovellGroupWareSession")
      Set GWRootAccount = objApplication.MultiLogin(UserName, "/ntservice", Password, 1)

      ' **** 1. DELETE ****'
      set GWAddressBook = GWRootAccount.AddressBooks(trim(rsTemp("BookName")))
      Dim oEntry
            For each oEntry in GWAddressBook.AddressBookEntries
               oEntry.Delete
            Next
     
      ' **** ADD THE RECORDS ****'
      sSqlTemp="SELECT * FROM vw_GroupWise order by BookName, DisplayName"
      set rsTemp = CreateObject("ADODB.Recordset")
      rsTemp.Open sSqlTemp, conn, 3, 1

      While not rsTemp.EOF
         'Loop through the Address books
         If sActiveBook <> rsTemp("BookName") & "" Then
            sActiveBook = rsTemp("BookName") & ""
            set GWAddressBook = Nothing
            set GWAddressBook = GWRootAccount.AddressBooks(trim(sActiveBook))
         End If

         sDisplayName = trim(rsTemp("DisplayName") & "")
         sEmailAddress = trim(rsTemp("EmailAddress") & "")
         sFirstName = trim(rsTemp("FirstName") & "")
         sLastName = trim(rsTemp("LastName") & "")
         sAddress = trim(rsTemp("Address") & "")
         sCity = trim(rsTemp("City") & "")
         sState = trim(rsTemp("State") & "")
         sOfficePhone = trim(rsTemp("OfficePhone") & "")
         sFaxNumber = trim(rsTemp("FaxNumber") & "")
         sOrganization = trim(rsTemp("Organization") & "")
         sDepartment = trim(rsTemp("Department") & "")
         sTitle = trim(rsTemp("Title") & "")
         
         Set objAddrEntry = GWAddressBook.AddressBookEntries.Add(sDisplayName, sEmailAddress, "MAPI", 1)

         With objAddrEntry.Fields
            .Add "First Name", 1, sFirstName
            .Add "Last Name", 1, sLastName
            If sAddress <> "" Then
               .Add "Address", 1, sAddress
            End If
            If sCity <> "" Then
               .Add "City", 1, sCity
            End If
            If sState <> "" Then
               .Add "State", 1, sState
            End If
            If sZip <> "" Then
               .Add "ZIP Code", 1, sZip
            End If
            If sOfficePhone <> "" Then
               .Add "Office Phone Number", 1, sOfficePhone
            End If
            If sFaxNumber <> "" Then
               .Add "Fax Number", 1, sFaxNumber
            End If
            If sOrganization <> "" Then
               If blnUseBuiltInOrganizationField = 1 Then
                  .Add "Organization", 1, sOrganization
               Else
                  '.Add sGWOrgField, 1, sOrganization
                  'SET THIS FIELD REGARDLESS (See below)'
               End If
               '.Add sGWOrgField, 1, sOrganization ' Talal: removed to speed things up
            End If

            If sDepartment <> "" Then
               .Add "Department", 1, sDepartment
            End If
            If sTitle <> "" Then
               .Add "Title", 1, sTitle
            End If
         End With

         rsTemp.MoveNext
      Wend


Hope this helps someone someday