enumeration headache

[stanl]

All started when I wanted to see if I could use a compiled WB script on work PC for processing with ACE Provider. Ran a Powershell script on that machine to list providers and ACE wasn't there. But I noticed when I examined an Excel data link to Access I had set up, the Ace.12.0 Provider was referenced. So I went through creating a WB exe and copying it over and can use ACE there now.


Curious, wanted to see if I could replicate the PS script with the CLR, as when I ran the PS script on my home PC where WB is installed, it showed all Providers including ACE and Jet.


Best I can do is display the Column Names, but not the values. Appreciate if someone can show the error of my ways. BTW: a plan B would be to enumerate via WMI or the registry but I want to stick with CLR

Code (WINBATCH) Select Expand



; Winbatch 2020A - List OLEDB Providers
; Stan Littlefield - January 11, 2020
;====================================================================================
ObjectClrOption("useany", "System.Data")
oProv = ObjectClrNew('System.Data.OleDb.OleDbEnumerator')
list = oProv.GetElements()
cList=""
Foreach r in list.Rows
   Foreach c in list.Columns
      cList = cList:c.ToString():","
   Next
   cList=cList:@LF
Next
oProv=0
Message("Providers",cList)
Exit


[JTaylor]

Maybe something along the lines of ....

      cList = cList:c.ColumnName:r.Item(0):",":r.Item(1):",":",":r.Item(2):",":",":r.Item(3):","

This probably won't format like you want and isn't complete but might put you on the right track .

Jim

[stanl]

Thanks Jim;


I originally looked at r.Item[0] instead of Item(0) and got a little discouraged there. Took out the Foreach columns. Still don't understand why ACE shows up on my personal PC but not my work one, although ACE can be called there.

Code (WINBATCH) Select Expand



; Winbatch 2020A - List OLEDB Providers
; Stan Littlefield - January 11, 2020
;====================================================================================
ObjectClrOption("useany", "System.Data")
oProv = ObjectClrNew('System.Data.OleDb.OleDbEnumerator')
list = oProv.GetElements()
cList=""
n=list.Columns.Count
Foreach c in list.Columns
  cList = cList:c.ToString():","
Next
cList=cList:@CRLF
Foreach r in list.Rows
   For i=0 To n-1
      cList = cList:r.Item(i):","
   Next
   cList=cList:@CRLF
Next
oProv=0
Message("Providers",cList)
Exit

[td]

You have likely already thought of this but could your "provider missing" problem be a bitness issue?  You would get a different list executing your script as 32-bit as opposed to 64-bit.  The Jet provider is only available as 32-bit.

[stanl]

You have likely already thought of this but could your "provider missing" problem be a bitness issue?  You would get a different list executing your script as 32-bit as opposed to 64-bit.  The Jet provider is only available as 32-bit.

Correct sir! I used 64 bit PS and either it or .NET on a 64-bit system only enumerate Providers that support 64-bit. Running the 32-bit PS on a 64-bit system showed all providers.


So, here is neat trick for 64-bit:  open notepad, immediately save as test.udl (should create a UDL icon ). double click to open the udl and the Providers tab will show 64-bit providers.  Open notepad again and paste in C:\Windows\syswow64\rundll32.exe "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile <path to test.udl> and save as test.bat. Execute the batch file and see larger provider list