Script to Read MDB wildcard issue

This topic contains 9 replies, has 3 voices, and was last updated by  Pj 4 weeks, 1 day ago.

  • Author
    Posts
  • #101082

    Pj
    Participant

    I have a working script for getting a single entity of data from my target mdb but I cannot seem to get the select statement to wildcard and get more than one at a time. All that needs to change is the Eng.SerialPortMgr.PrinterPort needs numbers 1 through 4 at the end, any thoughts?

    $path = "C:\temp\Config.mdb"
    $adOpenStatic = 3
    $adLockOptimistic = 3
    $cn = new-object -comobject ADODB.Connection
    $rs = new-object -comobject ADODB.Recordset
    
    $cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $path")
    $Port1 = $rs.open("Select * from Options where OptionName='Eng.SerialPortMgr.PrinterPort1'", $cn,$adOpenStatic,$adLockOptimistic)
    $rs.MoveFirst()
    do {
    $rs.Fields.Item("OptionName").value + " = " + $rs.Fields.Item("OptionData").value 
    $rs.MoveNext()} 
    until($rs.EOF -eq $True)
    $rs.Close()
    $cn.Close()
    
    $Port1
    
  • #101083

    Don Jones
    Keymaster

    Troubleshooting SQL statements that you build like that is hard. I suggest putting the query into a variable, and then outputting that to the screen prior to executing it. It'll be a lot easier to help you find the problem if I can see the final query statement.

  • #101106

    Pj
    Participant

    Looking at actual SQL scripts I used before in working tools the following is what I came up with for a corrected script string. It runs without error but does not return anything when ran in the entire script.

    $Script = "Select OptionName, OptionData from Options where left (OptionName, 29)='Eng.SerialPortMgr.PrinterPort'"

    $path = "C:\temp\Config.mdb"
    $adOpenStatic = 3
    $adLockOptimistic = 3
    $cn = new-object -comobject ADODB.Connection
    $rs = new-object -comobject ADODB.Recordset
    $Script = "Select OptionName, OptionData from Options where left (OptionName, 29)='Eng.SerialPortMgr.PrinterPort'"
    
    Write-Host $Script
    
    $cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $path")
    $Port1 = $rs.open($Script, $cn,$adOpenStatic,$adLockOptimistic)
    
    $rs.Close()
    $cn.Close()
    
    $Port1
    
  • #101109

    Fredrik Kacsmarck
    Participant

    Well first, does the query work as espected when doing it in e.g. Management Studio?

    Also in SQL the '=' is explicit.
    Meaning it is looking for Eng.SerialPortMgr.PrinterPort explicitly, not Eng.SerialPortMgr.PrinterPort1 or Eng.SerialPortMgr.PrinterPort2 and so on (as far as I remember at least).

    You use LIKE if you want to use wildcards.

    But as mentioned earler, try it in Management Studio, if the query fails there it won't work in your script either.

  • #101118

    Pj
    Participant

    My understanding of that statement is I'm saying the left 29 characters are explicitly Eng.SerialPortMgr.PrinterPort the number would be ignored. This is on a Access database can you use SQL Management Studio on that? Never tried I guess

  • #101124

    Fredrik Kacsmarck
    Participant

    Ahh, not sure if Access do it differently.

    Haven't really used Access but I'm sure that there are some tools for Access that let you try the query directly?

    I doubt though that it will work that way, since what the statement would do is take the optionname and do a Left 29 on it.
    Then explicitly test that against 'Eng.SerialPortMgr.PrinterPort'.

    What do you get if you do:

    "Select OptionName, OptionData from Options where OptionName LIKE 'Eng.SerialPortMgr.PrinterPort*'"

  • #101125

    Pj
    Participant

    executes without error but returns no results again

  • #101128

    Don Jones
    Keymaster

    So, a couple of things:

    Access uses % as a wildcard, not *.

    We've also kind of moved out of the realm of this being a “PowerShell” question :). You might find better assistance on a site like StackOverflow, where you've got developers working with the database technology every day. Just a suggestion – you're more than welcome to continue here.

    However, I can tell you that nearly any experienced developer is going to suggest taking your query into some kind of query tool, like SSMS would be for SQL Server. In the case of Access, that might mean Access itself. Troubleshooting “the query doesn't return anything” is nearly impossible straight from PowerShell.

  • #101136

    Pj
    Participant

    Well if it helps, I have the script sorted and for sure working if queried directly in Access which is the following...

    SELECT Options.OptionName, Options.OptionData
    FROM Options
    WHERE OptionName LIKE 'Eng.SerialPortMgr.PrinterPort*'

    which returns the correct data in Access so I added it to my script variable but no return in PowerShell

    $path = "C:\temp\Config.mdb"
    $adOpenStatic = 3
    $adLockOptimistic = 3
    $cn = new-object -comobject ADODB.Connection
    $rs = new-object -comobject ADODB.Recordset
    $Script = "SELECT Options.OptionName, Options.OptionData FROM Options WHERE OptionName LIKE 'Eng.SerialPortMgr.PrinterPort*'"
    
    $cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $path")
    $Port = $rs.open($Script,$cn,$adOpenStatic,$adLockOptimistic)
    
    $rs.Close()
    $cn.Close()
    
    $Port
    
  • #101140

    Pj
    Participant

    got it working

    $path = "C:\temp\Config.mdb"
    $adOpenStatic = 3
    $adLockOptimistic = 3
    $cn = new-object -comobject ADODB.Connection
    $rs = new-object -comobject ADODB.Recordset
    
    $cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $path")
    $SawConfigPorts = $rs.open("SELECT Options.OptionName, Options.OptionData FROM Options WHERE OptionName LIKE 'Eng.SerialPortMgr.PrinterPort%'",$cn,$adOpenStatic,$adLockOptimistic)
    $rs.MoveFirst()
    
    do {
        $rs.Fields.Item("OptionName").value + " = " + $rs.Fields.Item("OptionData").value; 
            $rs.MoveNext() } 
            until($rs.EOF -eq $True)
    
    $rs.Close()
    $cn.Close()
    
    $SawConfigPorts
    

You must be logged in to reply to this topic.