Query Database From PowerShell

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Keith Keith 3 months, 2 weeks ago.

  • Author
    Posts
  • #63517
    Profile photo of Keith
    Keith
    Participant

    Hello,

    I am *attempting* to convert some Perl script into PowerShell. I am still pretty new to PowerShell and think I may have the right idea, but am a little lost so I am hoping for some help.

    The company I work for uses a program called BMC Remedy to track tickets for work and whatnot. I am trying to get the script to query for data in the database. With the Perl Script it connects through the ODBC connection and queries a bunch of data; I am only attempting to query at least 1 ticket right now. I think I have the connection right for the database but am a little unsure how to query for data in PS.

    Here is the PS Code:

    $id = Get-Credential "ONE\$($env:USERNAME)"
    $userId = $id.UserName.split("\")[1].tolower()
    $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($id.password)
    $password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR)  
        write-host "Running Query"
        ##### Start the database connection and set up environment
        $DbString="DSN=AR SYSTEM ODBC Driver;UID=$userID;PWD=$password"
        add-type -path 'C:\Users\\Documents\ARAPI764.NET\BMC.ARSystem.dll'
        $DBConnection=New-Object System.Data.Odbc.OdbcConnection
        $DBCommand=New-Object System.Data.Odbc.OdbcCommand
        $DBConnection.ConnectionString=$DbString
        $DBConnection.Open()
        $DBCommand.Connection=$DBConnection
    
        $SelectStatement="SELECT * FROM HPD_HelpDesk_QueryList_Service WHERE (Assignee ='Username Here')"
        $DBCommand.CommandText=$SelectStatement
        $DBResult=$DBCommand.ExecuteReader()
        $UserTable=New-Object system.data.datatable
        $UserTable.load($DBResult) 
        $UserTable
        sleep 3
    

    I have it query for my credentials which should hopefully log me in pull data. When I run it I get a quite a few errors however.
    The first is:
    "Exception calling "Open" with "0" argument(s): "ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an
    architecture mismatch between the Driver and Application"
    At C:\Users\\Desktop\\DatabaseODBC.ps1:13 char:5
    + $DBConnection.Open()""

    And the second is:
    "Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The
    connection's current state is closed."
    At C:\Users\\Desktop\\DatabaseODBC.ps1:18 char:5
    + $DBResult=$DBCommand.ExecuteReader()"

    I really have no idea how to fix these. I think the 2nd error is because I have my query wrong but the first one I am not sure how to fix. I don't have much experience attempting to query data from a database with powershell so it is all new to me.

    any help would be great.

    Thank you

  • #63528
    Profile photo of Daniel Krebs
    Daniel Krebs
    Moderator

    I believe the AR Remedy ODBC driver is only available as 32-bit version. Try to run your script in the 32-bit PowerShell console or PowerShell ISE.

    c:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe
    c:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell_ise.exe

    You should also able to replace below lines

    $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($id.password)
    $password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR) 
    

    with

    $password = $id.GetNetworkCredential().Password
    
    • #63532
      Profile photo of Keith
      Keith
      Participant

      Thank you,

      after doing that I am now getting the errors –

      Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader: CommandText property has not been initialized"
      At C:\Users\\Desktop\\DatabaseODBC.ps1:16 char:5
      + $DBResult=$DBCommand.ExecuteReader()
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : InvalidOperationException

      Exception calling "Load" with "1" argument(s): "Value cannot be null.
      Parameter name: dataReader"
      At C:\Users\\Desktop\\DatabaseODBC.ps1:18 char:5
      + $UserTable.load($DBResult)
      + ~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : ArgumentNullException

You must be logged in to reply to this topic.