PowerShellでDB接続

• MySQL

(8.0.31)
https://ameblo.jp/ho1man/entry-11190167744.html?frm=theme

https://buralog.jp/powershell-mysql-select-execute/

接続元OS: Windows Server 2022
接続先OS: CentOS 7

前提: 
「 Connector/NET 8.0.31 」インストール済
リモート接続許可済

notepad a.ps1
---------------------
Add-Type -Path 'C:\Program Files (x86)\MySQL\MySQL Connector NET 8.0.31\Assemblies\v4.8\MySQL.Data.dll'

$server       = '10.1.1.66'
$port         = '3306'
$user         = 'root'
$pass         = 'password'
$db           = 'test'
$connstring = "server='$server';port='$port';uid='$user';pwd=$pass;database=$db"

$sql = "select * from tab1"

$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connstring)
$conn.Open()

$command = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $conn)

$result = $command.ExecuteReader()

$dataTable = New-Object "System.Data.Datatable"
$dataTable.Load($result)
$dataTable | Format-Table

$conn.Close()

---------------------
.\a.ps1

• Oracle

(19c)
https://maywork.net/computer/powershell-oracle-db-atach/

https://codezine.jp/article/detail/3966

接続元OS: Windows Server 2022
接続先OS: CentOS 7

前提: 
リモート接続許可済

notepad a.ps1
---------------------

Add-Type -AssemblyName System.Data.OracleClient

$server       = '10.1.1.65:1521/pdb1.example.com'
$user         = 'test'
$pass         = 'test'
$connstring = "data source=$server;uid=$user;pwd=$pass"

$sql = "select * from tab1"

$conn = new-object system.data.oracleclient.oracleconnection($connstring)
$conn.open()

$command = new-object system.data.oracleclient.oraclecommand($sql, $conn)

$result = $command.executereader()

$datatable = new-object "system.data.datatable"
$datatable.load($result)
$datatable | format-table

$conn.close()

---------------------
.\a.ps1

• PostgreSQL

(15)
https://ameblo.jp/ho1man/entry-11187045315.html

https://qiita.com/kimisyo/items/446b3b5b294d629acf1a

https://symfoware.blog.fc2.com/blog-entry-2386.html

https://github.com/npgsql/npgsql/releases

接続元OS: Windows Server 2022
接続先OS: Rocky Linux 8

前提: 
「 Npgsql-4.0.13 」インストール済 ※インストールタイプは「Npgsql GAC Installation」も選択必要
リモート接続許可済

notepad a.ps1
---------------------

Add-Type -Path 'C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Npgsql\v4.0_4.0.13.0__5d8b90d52f46fda7\Npgsql.dll'

$server       = '10.1.1.55'
$port         = '5432'
$user         = 'postgres'
$pass         = 'postgres'
$db           = 'test'
$connstring = "server='$server';port='$port';uid='$user';pwd=$pass;database=$db"

$sql = "select * from tab1"

$conn = New-Object NpgSql.NpgsqlConnection($connstring)
$conn.Open()

$command = New-Object NpgSql.NpgsqlCommand($sql, $conn)

$result = $command.ExecuteReader()

$dataTable = New-Object "System.Data.Datatable"
$dataTable.Load($result)
$dataTable | Format-Table

$conn.Close()

---------------------
.\a.ps1

• SQL Server

(2019)

https://qiita.com/ReplyToCC/items/7ba0efa11bab44fe5af1

https://tex2e.github.io/blog/powershell/invoke-sql-command

https://learn.microsoft.com/ja-jp/sql/powershell/download-sql-server-ps-module?view=sql-server-ver16

https://trend-desk.com/archives/1067

https://bayashita.com/p/entry/show/107

接続元OS: Windows Server 2022
接続先OS: Windows Server 2022

前提: 
「 SQL Server PowerShell モジュール 」インストール済 ※ Install-Module -Name SqlServer -AllowClobber
リモート接続許可済

notepad a.ps1
---------------------

$connstring = new-object -typename system.data.sqlclient.sqlconnectionstringbuilder
$connstring['data source'] = "mmm061,1433"
$connstring['initial catalog'] = "test"
$connstring['uid'] = "sa"
$connstring['pwd'] = 'password'

$sql = "select * from tab1"

$conn = new-object system.data.sqlclient.sqlconnection($connstring)
$conn.open()

$command = new-object system.data.sqlclient.sqlcommand($sql, $conn)

$result = $command.ExecuteReader()

$dataTable = New-Object "System.Data.Datatable"
$dataTable.Load($result)
$dataTable | Format-Table

$conn.Close()

---------------------
.\a.ps1