7 Replies Latest reply: Oct 28, 2014 8:26 AM by LarryLee RSS

    objectGUID in a ms sql databse in a varchar(100) column

    nikos101 Community Member
    nikos101 Jul 7, 2009 5:33 AM

      HI

       

      I'm doing

       

       

         returnAsBinary = "objectGUID"

       

      in a <cfldap> query.

       

      I was thinking of storing this value returned by objectGUID in a ms sql databse in a varchar(100) column as a primary key. Can you see any pitfalls with this method?

      • 1752 Views
      • Tags:
        • 1. Re: objectGUID in a ms sql databse in a varchar(100) column
          nikos101 Community Member
          nikos101 Jul 8, 2009 8:59 AM (in response to nikos101)

          Hmm after passing this value to flex I though it would be a simple piece of data that I could pass back to CF, it turns out to be an array in flex so that makes it not possible to return to coldfusion to check it is the same as a value in the ms sql database.

           

           

          • 2. Re: objectGUID in a ms sql databse in a varchar(100) column
            nikos101 Community Member
            nikos101 Jul 8, 2009 9:01 AM (in response to nikos101)

            Hmm after passing this value to flex I though it would be a simple piece of data that I could pass back to CF, it turns out to be an array in flex so that makes it not possible to return to coldfusion to check it is the same as a value in the ms sql database.

             

             

            • 3. Re: objectGUID in a ms sql databse in a varchar(100) column
              nikos101 Community Member
              nikos101 Jul 8, 2009 9:02 AM (in response to nikos101)

              Mabye this is the solution from another website:

               


              Can anyone please explain how to get the objectguid binary attribute from Active  Directory using cfldap and store it in the database? I need this so as to maintain a global unique id both in the AD and in my  database for the users.  Thanks



              You'll probably have to use JLDAP for this - CFLDAP has many shortcomings that  we've used JLDAP to work around. It's a very simple API and everything works from  within CF. I've blogged a few of things we've done, so that should get you  started:


              http://www.d-ross.org/index.cfm?objectid=9C65EEF0-508B-E116-6F30CA79F5BFDE07

              http://www.d-ross.org/index.cfm?objectid=9C65EF0F-508B-E116-6F851C474448E08B

              http://www.d-ross.org/index.cfm?objectid=9C65ED79-508B-E116-6F81AF8F75FB40AD

              Terry Ryan used a lot of this work to create a CFC that you can use:

              • 4. Re: objectGUID in a ms sql databse in a varchar(100) column
                nikos101 Community Member
                nikos101 Jul 10, 2009 5:25 AM (in response to nikos101)

                This is the magic code:

                 

                <cfset results.objectGUID = binaryencode(results.objectGUID,"HEX")>

                • 5. Re: objectGUID in a ms sql databse in a varchar(100) column
                  nikos101 Community Member
                  nikos101 Jul 10, 2009 5:27 AM (in response to nikos101)

                  Does anyone know how to do the reverse of

                   

                  <cfset results.objectGUID = binaryencode(results.objectGUID,"HEX")>

                   

                  so that you can use the objectGUID in the

                   

                  <cfldap filter

                   

                  ?

                  • 6. Re: objectGUID in a ms sql databse in a varchar(100) column
                    afceait Community Member
                    afceait Mar 28, 2014 7:26 AM (in response to nikos101)

                    <cfldap

                    server = "server ip/name"

                    username="adminuser"

                    password="adminpass"

                    action = "query"

                    name = "results"

                    start = "CN=Users,dc=domain,dc=org"

                    filter = ""

                    attributes = "objectGUID,cn,displayName,givenName,sn,DN,o,Description,company,department,physicalDeliv eryOfficeName,title,mail,telephonenumber,mobile,streetAddress,l,st,postalCode,c,info"

                    returnAsBinary="objectGUID"

                    sort = "sn ASC">

                     

                     

                    <cfoutput query = "results">

                     

                     

                      <cfset hexguid = BinaryEncode(objectGUID,"Hex")>

                      <cfset sthex = toString(hexguid)>

                      <cfset GuidStr = mid(#sthex#,7,2)>

                      <cfset GuidStr = GuidStr & mid(sthex,5,2)>

                      <cfset GuidStr = GuidStr & mid(sthex,3,2)>

                      <cfset GuidStr = GuidStr & mid(sthex,1,2)>

                      <cfset GuidStr = GuidStr & mid(sthex,11,2)>

                      <cfset GuidStr = GuidStr & mid(sthex,9,2)>

                      <cfset GuidStr = GuidStr & mid(sthex,15,2)>

                      <cfset GuidStr = GuidStr & mid(sthex,13,2)>

                      <cfset GuidStr = GuidStr & mid(sthex,17,18)>

                      <cfset TempGuid = left(GuidStr,8) & "-" & mid(GuidStr,9,4) & "-" & mid(GuidStr,13,4) & "-" & mid(GuidStr,17,4) & "-" & mid(GuidStr,21,18) >

                     

                     

                      #displayName#: ObjectGUID before: #sthex# | objectGUID Parsed: #TempGuid#<br>

                     

                     

                    </cfoutput>

                    • 7. Re: objectGUID in a ms sql databse in a varchar(100) column
                      LarryLee Community Member
                      LarryLee Oct 28, 2014 8:26 AM (in response to nikos101)

                      Using afceait's sample from March 28th above, you can filter on objectGUID by escaping the converted binary sthex substrings with '\' as shown here:

                      <cfset SrchStr = '\'& mid(#sthex#,1,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,3,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,5,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,7,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,9,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,11,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,13,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,15,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,17,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,19,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,21,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,23,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,25,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,27,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,29,2)>

                      <cfset SrchStr = SrchStr & '\'& mid(sthex,31,2)>

                      <cfdump var="#SrchStr#">

                       

                       

                      Then you can do
                      <cfldap .....

                      filter = "objectGUID=#SrchStr#"

                      ...

                      >