Implementing Interoperable LDAP Applications

--
-- -----------------------------------------------------------------------------
-- Project  : Implement Generic ClLDAP support
-- Purpose  : To manage and maintain user/accounts in a directory server
-- Author   : Eduardo Sobrino
-- Remarks  : The 'clldaplib.dll' will write a log file where all errors that
--          : where found will be detailed. You should find it in the system32
--          : directory of your OS directory. The name of the file is
--          : 'clldaplib.log'...
-- Date     : Aug/2000
--

-- Move to the database where all ClLDAP stuff should be...

USE AbacBill
GO

--
-- -----------------------------------------------------------------------------
-- PROCEDURE : ClLDAP_UpLoadAccounts
-- Purpose   : Upload accounts from another database / table into the directory.
-- Date      : Aug/2000  (ESob)
--

IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE ID = object_id(N'[dbo].[ClLDAP_UpLoadAccounts]')
   AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE [dbo].[ClLDAP_UpLoadAccounts]
GO

CREATE PROCEDURE ClLDAP_UpLoadAccounts
AS
BEGIN
   DECLARE @fAcctno INTEGER,
           @fUserId VARCHAR(20),
           @fEMail  VARCHAR(64),
           @fPasswd VARCHAR(20),
           @iCustId VARCHAR(20),
           @cindex AS INTEGER

   -- prepare cursor by extracting from appropiate tables the account and user
   -- information that will go into the directory...

   DECLARE acct_cursor CURSOR FOR 
      SELECT CustomerId,EMail,CustPass FROM Customers

   OPEN acct_cursor
  
   -- fetch first record
   FETCH NEXT FROM acct_cursor
   INTO @fAcctno,@fEMail,@fPasswd

   -- connect into the directory server...
   EXEC master..xp_ClLdapConnect 'celpagedc1','Administrator','admin'
   EXEC master..xp_ClLDAPSetOrganizationalUnitDN 'ou=subscribers,dc=corporate,dc=pr,dc=celpage,dc=net'

   -- insert record into the directory until there no more accounts...

   WHILE @@FETCH_STATUS = 0
   BEGIN

      -- prepare the customer id in string format

      IF @fEmail IS NOT NULL
      BEGIN
         SET @cindex = CHARINDEX('@',@fEmail)-1
         IF @cindex <= 0
            SET @cindex = LEN(@fEmail)
         SET @fUserId = substring(@fEmail,1,@cindex)
      END
      ELSE
         SET @fUserId = ''

      SET @iCustId = CAST(@fAcctno AS CHAR)

      -- add the account/user to the directory...
      EXEC master..xp_ClLDAPAddUser @iCustId,@fUserId,@fPasswd

      -- insert a new record
      INSERT INTO ClLDAP_Accounts
         (Acctno,UserId,Passwd,LastUpdt) VALUES
         (@fAcctno,@fUserId,@fPasswd,getdate())

      FETCH NEXT FROM acct_cursor
      INTO @fAcctno,@fEMail,@fPasswd
   END

   -- release allocated resources...

   CLOSE acct_cursor
   DEALLOCATE acct_cursor

   -- finally disconnect from the server...

   EXEC master..xp_ClLDAPDisconnect

END  -- end of updload a all users / accounts into the direcotry...
GO