如何在Powershell中连接ODBC ?

Question

如何从powershell连接到odbc ?

我找到了这个函数:

function Get-ODBC-Data{
param(
[string]$query=$('select count(*) from [master].[sys].[table_name]'),
)
$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "DSN=AllSecure;"
$conn.open()
$cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)
$ds = New-Object system.Data.DataSet
(New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
$conn.close()
$ds.Tables[0]
}
$result = Get-ODBC-Data
Write-Host "Statistic: " $result[0];
Write-Host "Message: " $result[0] ;

但我仍然不知道如何使用它!

我应该在哪里提供用户名和密码?

你能给我提供在Powershell上运行脚本的命令吗?

或者有更好的连接方式吗?

谢谢!

Answer 1

我应该在哪里提供用户名和密码?

将它们作为UID=和PWD=包含在连接字符串中:

$connStr = @"
DSN=mssqlLocal64;
UID=scott;
PWD=tiger;
"@
$con = New-Object System.Data.Odbc.OdbcConnection $connStr
$con.Open()
$sql = "SELECT name, create_date FROM sys.tables ORDER BY name"
$cmd = New-Object System.Data.Odbc.OdbcCommand $sql, $con
$rdr = $cmd.ExecuteReader()
while ($rdr.Read())
{
Write ("[{0}] -> {1}" -f $rdr["name"], $rdr["create_date"])
}
$rdr.Close()
$con.Close()