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("\").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
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
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.