使用Powershell从PostgreSQL检索数据

问题描述

我一直在努力从Powershell到PostgreSQL进行数据库连接。我终于能够连接并插入数据库。现在我不知道如何从数据库选择中提取数据到变量中。

I have been wrestling with database connection to PostgreSQL from Powershell. I finally am able to connect to and insert into the database. Now I can't figure out how to extract data from a DB select into a variable.

为了清楚起见，我不包括插入内容，但会加以处理后来我知道它很难找到并且可能对某人有所帮助。

I'm not including my insert for the sake of clarity but will tack it onto this thread later as I know it was super hard to find and may be helpful to someone.

所以这是我的代码：

# 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)

推荐答案

我结束了弄清楚-这就是我所做的

I ended up figuring it out - here's 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

这篇关于使用Powershell从PostgreSQL检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！