locked
Pass null values to 'open' method of 'ADODB.Recordset'? RRS feed

  • Question

  •   I am trying to utilise a method I used in VBScript in PowerShell in which I open a recordset asynchronously. In VBScript, I used:

    oRS.Open sQuery, , , , 16

      When I try a similar approach in PowerShell, it advises me that, "Missing expression after ','."

    $RS.Open($Query, , , , 16)

      I am able to specify the parameters and get the query to execute asynchronously, but it negates my page size setting that allows retrieving more than 1,000 records from Active Directory. I need to somehow pass only the ExecuteOptionEnum without specifying the ActiveConnection.


    • Edited by jwhitlow Friday, December 15, 2017 9:02 PM
    Friday, December 15, 2017 9:00 PM

Answers

  • You record set is not async.  Async requires a callback.

    The code I posted is more efficient. 

    AD will always return from a read when the page has been filled.  If you want faster response use a smaller page size.  you have to balance network turn around and buffering requirements on both ends.

    A recordset is available for reading as soon as the first page is returned which is when the initial call returns.  All reading in the loop is handled internally and will block when records are not available.  VBScript has no method for asynchronous processing of recordsets.

    To skip parameters in COM you must use "[type]::Missing".


    \_(ツ)_/


    • Edited by jrv Monday, December 18, 2017 11:15 PM
    • Marked as answer by jwhitlow Tuesday, December 19, 2017 3:07 PM
    Monday, December 18, 2017 11:12 PM

All replies

  • Are you referring to a disconnected recordset? Maybe a short VBScript example would help.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, December 15, 2017 9:19 PM
  • Don't use RS opn method and don't use ADODB I  PowerShell.  Use ADO.Net connections.

    Look in Gallery for examples.

    Also ADSI is a far better way to search AD. It is easier to use and faster.

    http://www.rlmueller.net/PowerShell/FindUser1.txt


    \_(ツ)_/

    Friday, December 15, 2017 9:32 PM
  • Here is how easy this is with ADO.Net (OleDb)

    $path = 'LDAP://' + ([adsi]'').DistinguishedName
    $conn = new-Object System.Data.OleDb.OleDbConnection('Provider=ADSDSOObject;Page Size=500')
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = "Select Name,samaccountname,title From '$path' Where objectCategory = 'Person'"
    $rdr = $cmd.ExecuteReader()
    $dt = New-Object System.Data.DataTable
    $dt.Load($rdr)
    $rdr.Close()
    $dt
    


    \_(ツ)_/

    Friday, December 15, 2017 9:44 PM
  • Richard,

      In the PowerShell script that I am writing, I am querying data from several databases, including a few different Active Directory queries. My goal is to submit all of the queries asynchronously & then wait for them to return, rather than doing them one after the other.

      As requested, here is a VBScript example:

    Set oADO = CreateObject("ADODB.Connection")
    Set oRS  = CreateObject("ADODB.Recordset")
    
    oADO.Open "Provider = ADsDSOObject"
    oRS.ActiveConnection = oADO
    oRS.Properties("Page Size") = 999
    oRS.CursorLocation = 3
    
    sQuery = "<LDAP://DC=domain,DC=com>;" _
           & "(&(objectCategory=Person)(objectClass=user)); " _
           & "cn,samAccountName;" _
           & "subtree"
    
    oRS.Open sQuery , , , , 16
    
    WScript.StdOut.Write "Waiting"
    
    Do  Until oRS.State = 1
     WScript.StdOut.Write "."
     WScript.Sleep 1000
    Loop
    
    WScript.Echo vbLf & "Record Count: " & oRS.RecordCount

      I have done this many times in VBScript, but PowerShell apparently does not like the empty parameters in '$RS.Open($Query, , , , 16)'.

      I can issue the command as '$RS.Open($Query, 'Provider = ADsDSOObject', 3, 1, 16)', but then I run into the page size issue when I get only 960 records back.


    Monday, December 18, 2017 9:11 PM
  • You record set is not async.  Async requires a callback.

    The code I posted is more efficient. 

    AD will always return from a read when the page has been filled.  If you want faster response use a smaller page size.  you have to balance network turn around and buffering requirements on both ends.

    A recordset is available for reading as soon as the first page is returned which is when the initial call returns.  All reading in the loop is handled internally and will block when records are not available.  VBScript has no method for asynchronous processing of recordsets.

    To skip parameters in COM you must use "[type]::Missing".


    \_(ツ)_/


    • Edited by jrv Monday, December 18, 2017 11:15 PM
    • Marked as answer by jwhitlow Tuesday, December 19, 2017 3:07 PM
    Monday, December 18, 2017 11:12 PM
  • To skip parameters in COM you must use "[type]::Missing".

      Thanks for this! It is exactly what I was looking for.

      This command now works for me:

    $RS.Open($Query, [type]::Missing, [type]::Missing, [type]::Missing, 16)

      I executed a query for a recordset for over 20,000 records with a client-side cursor using the above and PowerShell immediately returned control to me. I waited for the recordset state to become '1' and then checked the RecordCount and all was there as expected.

      Thanks to your help, I can now execute all of my recordsets asynchronously using the 'adAsyncExecute' option!

      I suspect I will make use of '[type]::Missing' with other COM objects as well. Very useful!

    Tuesday, December 19, 2017 3:07 PM
  • "adAsyncExecute" is the default for this type of server as it allows the recordsets to be returned in chunks.  Since you are waiting on the completion you ae just turming this back into a sync call so the async makes little difference.  For efficiency in processing don't wait.  Just process the records.  The reads will wait if your processing runs out of records and the read will return EOF when the last record is processed.

    ADO.Net is more efficient at managing this under PowerShell.

    The fastest way to get a count only is to use ADSI with a search. 


    \_(ツ)_/


    • Edited by jrv Tuesday, December 19, 2017 9:15 PM
    Tuesday, December 19, 2017 9:13 PM
  • The following would be faster:

    $path = 'LDAP://' + ([adsi]'').DistinguishedName
    $conn = new-Object System.Data.OleDb.OleDbConnection('Provider=ADSDSOObject;Page Size=500')
    $conn.Open()
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = "Select Name,samaccountname,title From '$path' Where objectCategory = 'Person'"
    $rdr = $cmd.ExecuteReader()
    $dt = New-Object System.Data.DataTable
    $dt.Load($rdr)
    $rdr.Close()
    $dt.Rows.Count

    Adjust "Page Size" for optimal speed.  500 works well in most networks and with most clients.

    Using ADO.Net with PowerShell is much more efficient and more flexible.  DataTables are et objects and can be used in PS like all other Net objects.


    \_(ツ)_/


    • Edited by jrv Tuesday, December 19, 2017 9:19 PM
    Tuesday, December 19, 2017 9:18 PM