如何使用PowerShell或VBScript将数据插入Postgres表？

I need to periodically query the event logs on a handful of servers and insert specific events into a Postgres table.

我需要定期查询少数几台服务器上的事件日志,并将特定事件插入Postgres表中。

I am having trouble figuring out how I can insert data into a table via ODBC using PowerShell and/or VBScript. I'm reasonably familiar with both VBScript and PowerShell generally, and I can craft a SQL UPDATE statement that works, I'm just trying to tie the two together, which I've never done before.

我无法弄清楚如何使用PowerShell和/或VBScript通过ODBC将数据插入表中。我一般都熟悉VBScript和PowerShell,我可以制作一个有效的SQL UPDATE语句,我只想把两者绑在一起,这是我以前从未做过的。

I have the Postgres ODBC driver installed, I've configured a data source which tests OK.

我安装了Postgres ODBC驱动程序,我已经配置了一个测试好的数据源。

Google isn't helping me thus far, can someone provide some pointers?

谷歌到目前为止没有帮助我,有人可以提供一些指示吗?

2 个解决方案

#1

What part are you having trouble with? How far have you got? Do you have a connection open? Do you know the syntax of the connection string?

你有什么问题?你有多远?你有连接吗?你知道连接字符串的语法吗?

Prepare a connection:

准备连接:

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open dsn, dbuser, dbpass

insert:

insert = "insert into table (col1, col2) values (12, 'Example Record')"
conn.Execute insert
If conn.errors.Count > 0 Then
    Dim counter
    WScript.echo "Error during insert"
    For counter = 0 To conn.errors.Count
        WScript.echo "Error #" & DataConn.errors(counter).Number
        WScript.echo "  Description(" & DataConn.errors(counter).Description & ")"
    Next
Else
    WScript.echo "insert: ok"
End If

for completeness, query:

为了完整性,查询:

query = "select * from table where col1 = 7"
Set recordSet = conn.execute(query)
' result is an object of type ADODB.RecordSet

If you want powershell, try this post.
If you need to know the connection string, try connectionstrings.com.

如果你想要PowerShell,请尝试这篇文章。如果您需要知道连接字符串,请尝试connectionstrings.com。

#2

Thanks for the response.

谢谢你的回复。

I used more Googling and eventually grokked enough ADO.NET to get it working, although it may not necessarily be "correct".

我使用了更多的谷歌搜索,并最终使用足够的ADO.NET来使其工作,尽管它可能不一定是“正确的”。

$DBConnectionString = "Driver={PostgreSQL UNICODE};Server=$DBIP;Port=$DBPort;Database=$DBName;Uid=$DBUser;Pwd=$DBPass;"

$DBConn = New-Object System.Data.Odbc.OdbcConnection
$DBConn.ConnectionString = $DBConnectionString

$DBCmd = $DBConn.CreateCommand()

[void]$DBCmd.Parameters.Add("@TimeStamp", [System.Data.Odbc.OdbcType]::varchar, 26)
[void]$DBCmd.Parameters.Add("@ErrorText", [System.Data.Odbc.OdbcType]::varchar, 4000)

$DBCmd.CommandText = "INSERT INTO errorinfo (errortime,xml) VALUES(?,?)"

$DBCmd.Connection.Open()

$DBCmd.Parameters["@TimeStamp"].Value = $TimeStamp.ToString("yyyy-MM-dd HH:mm:ss")
$DBCmd.Parameters["@ErrorText"].Value = $ErrorText

[void]$DBCmd.ExecuteNonQuery()

#1