Getting data from PostgreSQL using Powershell
I was struggling with database connection to PostgreSQL from Powershell. Finally, I can connect and insert into the database. Now I cannot figure out how to fetch data from a DB selection into a variable.
I am not including my insert for the sake of clarity, but I will refer to it later because I know it is difficult to find and might be useful to someone.
so here is my code:
# use existing 64 bit ODBC System DSN that we set up manually
$DBconn = New-Object -comobject ADODB.Connection
$DBconn.Open("PostgreSQL35W")
$theQuery = "select * from test1"
$theObject = $DBconn.Execute($theQuery) # $theObject is a System.__ComObject
$numRecords = $theObject.RecordCount
write-host "found $numRecords records" # getting -1
$theObject.MoveFirst() # throws no error
# $theValue = $theObject.DataMember # throws no error, but gives no result
$theValue = $theObject.Index[1] # throws "Cannot index into a null array"
write-host($theValue)
+3
source to share
1 answer
I ended up figuring it out - this is what I did
$conn = New-Object -comobject ADODB.Connection
# use existing 64 bit ODBC System DSN that we set up manually
$conn.Open("PostgreSQL35W")
$recordset = $conn.Execute("SELECT * FROM JobHistory")
while ($recordset.EOF -ne $True)
{
foreach ($field in $recordset.Fields)
{
'{0,30} = {1,-30}' -f # this line sets up a nice pretty field format, but you don't really need it
$field.name, $field.value
}
'' # this line adds a line between records
$recordset.MoveNext()
}
$conn.Close();
Exit
+1
source to share