Script to Read MDB wildcard issue

Welcome Forums General PowerShell Q&A Script to Read MDB wildcard issue

This topic contains 9 replies, has 3 voices, and was last updated by

Pj
 
Participant
6 months, 3 weeks ago.

  • Author
    Posts
  • #101082
    Pj

    Participant
    Points: 0
    Rank: Member

    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

    Keymaster
    Points: 1,704
    Helping HandTeam Member
    Rank: Community Hero

    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
    Points: 0
    Rank: Member

    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

    Participant
    Points: 190
    Helping Hand
    Rank: 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
    Points: 0
    Rank: Member

    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

    Participant
    Points: 190
    Helping Hand
    Rank: 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
    Points: 0
    Rank: Member

    executes without error but returns no results again

  • #101128

    Keymaster
    Points: 1,704
    Helping HandTeam Member
    Rank: Community Hero

    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
    Points: 0
    Rank: Member

    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
    Points: 0
    Rank: Member

    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
    

The topic ‘Script to Read MDB wildcard issue’ is closed to new replies.