postgresql 如何使用 PowerShell 或 VBScript 将数据插入 Postgres 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1058156/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I insert data into a Postgres table using PowerShell or VBScript?
提问by ThatGraemeGuy
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?
到目前为止，谷歌没有帮助我，有人可以提供一些指导吗？
采纳答案by ThatGraemeGuy
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()
回答by Cheeso
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。
