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.
And the second is:
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.
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.
You should also able to replace below lines
$BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($id.password) $password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR)
$password = $id.GetNetworkCredential().Password
after doing that I am now getting the errors –
Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader: CommandText property has not been initialized"
Exception calling "Load" with "1" argument(s): "Value cannot be null.
You must be logged in to reply to this topic.