Query Database From PowerShell

PowerShell Help
1

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

2

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
3

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: (:slight_smile: , 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: (:slight_smile: , MethodInvocationException
    • FullyQualifiedErrorId : ArgumentNullException