1 Reply Latest reply on Jun 2, 2006 1:39 AM by kabbi~thkek

    LDAP Query timeout update sql database table

    kabbi~thkek
      Hello,

      I try to update a SQL database table with some data from a LDAP Query. What I wanted to do is to update all data in the SQL database with al data From LDAP.
      However I keep getting this error: "The request has exceeded the allowable time limit Tag: cfldap "
      I tried again by setting the timeout on the maximum timeout, but no result!
      Is there a possibilty to update the table step by step for instance?

      Here is my code:

      <cfquery name="ap_info" datasource="">
      SELECT dev_serie FROM dev
      </cfquery>
      <cfloop query="ap_info">

      <!--- Connectie ! --->
      <cfldap action="QUERY"
      name="qtest"
      attributes="cn, ou, wMNAMEUser, wMNAMECPU, wMNAMEOS, wMNetworkAddress,
      zENINVOSType, zENINVComputerModel, zENINVComputerType,
      zENINVProcessorType, zENINVDiskInfo, wMUserHistory, zENINVMemorySize, zENINVMACAddress, zENINVSerialNumber, ZENINVlastscandate, wMLastRegisteredTime, zeninvsubnetaddress, wMNAMEDNS, lastLoginTime"
      start="ou=,o=,c="
      scope="SUBTREE"
      timeout="60000"
      filter="(&(objectClass=Workstation)(zENINVSerialNumber=#ap_info.dev_serie#))"
      server="l"
      >

      <!--- lijst met historie gebruikers bewerken --->

      <!--- NDS/ZEN --->
      <cfquery name="nov" datasource="">
      SELECT N_serienr
      FROM novell WHERE N_serienr = '#qtest.zENINVSerialNumber#'
      </cfquery>



      <!--- gebruikers bewerken naar juiste pnummers // ALLEEN ALS BESTAAT!!--->
      <!--- als waarde grootte is 30 tekens // nog doen--->
      <cfif IsDefined("qtest.wMNAMEUser") AND #qtest.wMNAMEUser# NEQ "">
      <cfset pnr = left(qtest.wMNAMEUser, 10)>
      <cfset pnr = right(pnr, 7)>
      <!--- gebruikersnr zonder p --->
      <cfset gnr = left(qtest.wMNAMEUser, 10)>
      <cfset gnr = right(pnr, 6)>
      </cfif>
      <!--- last registered date bewerken--->
      <cfif IsDefined("qtest.wMLastRegisteredTime") AND #qtest.wMLastRegisteredTime# NEQ "">
      <cfset jaar= left(qtest.wMLastRegisteredTime, 4)>
      <cfset maand= Mid(qtest.wMLastRegisteredTime,5,2)>
      <cfset dag= Mid(qtest.wMLastRegisteredTime,7,2)>
      <cfset datum = #DateFormat(CreateDate(jaar, maand, dag), "yyyy-mm-dd")#>
      <cfelse>
      <cfset datum = "niet bekend">
      </cfif>
      <!--- Einde conectie --->



      <!--- Insert de data in de Novell tabel! --->
      <cfif Isdefined("nov.N_serienr") AND #nov.N_serienr# NEQ "">
      <!--- UPDATE NOVELL TABEL --->
      <cfelse>
      <cfquery datasource="">
      INSERT INTO NOVELL (N_serienr, N_mac, N_subnet, N_ip, N_fabrikant, N_model, N_Proctype, N_DiskInfo, N_UserP, N_UserNR, N_ram, N_LRT, N_UserHist, UserHistNR) VALUES (
      <cfif IsDefined("qtest.zENINVSerialNumber") AND #qtest.zENINVSerialNumber# NEQ "">
      '#qtest.zENINVSerialNumber#'
      <cfelse>
      NULL
      </cfif>
      ,
      <cfif IsDefined("qtest.zENINVMACAddress") AND #qtest.zENINVMACAddress# NEQ "">
      '#qtest.zENINVMACAddress#'
      <cfelse>
      NULL
      </cfif>
      ,
      <cfif IsDefined("qtest.zeninvsubnetaddress") AND #qtest.zeninvsubnetaddress# NEQ "">
      '#qtest.zeninvsubnetaddress#'
      <cfelse>
      NULL
      </cfif>
      ,
      <cfif IsDefined("qtest.wMNetworkAddress") AND #qtest.wMNetworkAddress# NEQ "">
      '#qtest.wMNetworkAddress#'
      <cfelse>
      NULL
      </cfif>
      ,
      <cfif IsDefined("qtest.zENINVComputerType") AND #qtest.zENINVComputerType# NEQ "">
      '#qtest.zENINVComputerType#'
      <cfelse>
      NULL
      </cfif>
      ,
      <cfif IsDefined("qtest.zENINVComputerModel") AND #qtest.zENINVComputerModel# NEQ "">
      '#qtest.zENINVComputerModel#'
      <cfelse>
      NULL
      </cfif>
      ,
      <cfif IsDefined("qtest.zENINVProcessorType") AND #qtest.zENINVProcessorType# NEQ "">
      '#qtest.zENINVProcessorType#'
      <cfelse>
      NULL
      </cfif>
      ,
      <cfif IsDefined("qtest.zENINVDiskInfo") AND #qtest.zENINVDiskInfo# NEQ "">
      '#qtest.zENINVDiskInfo#'
      <cfelse>
      NULL
      </cfif>
      ,
      <cfif IsDefined("pnr") AND #pnr# NEQ "">
      '#pnr#'
      <cfelse>
      NULL
      </cfif>
      ,
      <cfif IsDefined("gnr") AND #gnr# NEQ "">
      '#gnr#'
      <cfelse>
      NULL
      </cfif>
      ,
      <cfif IsDefined("qtest.zENINVMemorySize") AND #qtest.zENINVMemorySize# NEQ "">
      '#qtest.zENINVMemorySize#'
      <cfelse>
      NULL
      </cfif>
      ,
      <cfif IsDefined("datum") AND #datum# NEQ "">
      '#datum#'
      <cfelse>
      NULL
      </cfif>
      ,
      <cfif IsDefined("qtest.wMUserHistory") AND #qtest.wMUserHistory# NEQ "">
      '#qtest.wMUserHistory#'
      <cfelse>
      NULL
      </cfif>
      ,
      <cfif IsDefined("qtest.wMUserHistory") AND #qtest.wMUserHistory# NEQ "">
      '#qtest.wMUserHistory#'
      <cfelse>
      NULL
      </cfif>
      )

      </cfquery></cfif>
      </cfloop>

      Is there someone with an Idea?

      Thank you in advance.

      Regards,

      Kabbi
        • 1. Re: LDAP Query timeout update sql database table
          kabbi~thkek Level 1
          Hi,
          Does really nobody has an Idea how to avoid this?
          I have to update a SQL database table with some LDAP data.

          Here is how I planned to do it:

          <cfquery name="devZen" datasource="">
          SELECT dev_id, dev_serie, dev_typenr FROM dev WHERE dev_typenr = '1'
          </cfquery>


          <cfloop query= "devZen">

          <!--- Connectie ! --->
          <cfldap action="QUERY"
          name="qtest"
          attributes="cn, ou, wMNAMEUser, wMNAMECPU, wMNAMEOS, wMNetworkAddress,
          zENINVOSType, zENINVComputerModel, zENINVComputerType,
          zENINVProcessorType, zENINVDiskInfo, wMUserHistory, zENINVMemorySize, zENINVMACAddress, zENINVSerialNumber, ZENINVlastscandate, wMLastRegisteredTime, zeninvsubnetaddress, wMNAMEDNS, lastLoginTime"
          start="ou=,o=,c="
          scope="SUBTREE"
          filter="(&(objectClass=Workstation)(zENINVSerialNumber=#devZen.dev_serie#))"
          server="server"
          >

          "HERE THE UPDATE QUERY"

          </cfloop>

          However I keep getting this error:

          The request has exceeded the allowable time limit Tag: cfldap


          The error occurred in C:\xampp\htdocs\bar\zen\act\updatezen.cfm: line 30

          28 : scope="SUBTREE"
          29 : filter="(&(objectClass=Workstation)(zENINVSerialNumber=#devZen.dev_serie#))"
          30 : server="server"
          31 : >

          Does anybody have another suggestion?

          Thank you in advance,

          Regards,
          Kabbi