Posted at

【PostgreSQL】WSH/VBScriptでpsqlを使用する


はじめに

Windowsサーバーリプレース作業にて、データベースを Oracle 11g から PostgreSQL 9.6 に移行作業をしています。

OracleのSQLPlusと違って、psqlだとパスワードを指定するオプションがないので不便ですね。


パスワード認証を省略

2通りの方法があります。


pgpass.conf

パスワード入力なしでpsqlを実行するには、下記フォルダの「pgpass.conf」にパスワードを設定しておく。

%APPDATA%\postgresql\pgpass.conf

※pgpass.confは、postgresのdataフォルダに置くものではありません。

※pgpass.confは、UTF-8(BOM無し)で保存・作成してください。


pgpass.conf

#hostname:port:database:username:password

localhost:5432:*:postgres:(パスワード)


環境変数(PGPASSWORD)

セキュリティ上は非推奨であるがバッチ内に環境変数(PGPASSWORD)を設定した上で実行する。

SET PGPASSWORD=(パスワード)

psql -U (ユーザー) -f xxxxx.sql


WSH/VBScriptで実行

pgpass.conf のユーザーであればパスワードを省略できるので、SET PGPASSWORDは不要ですが、指定以外のユーザーではパスワードを要求されます。

VBS上でSET変数を入れて実行するには、"CMD /C SET "を使用するのですが、PGPASSWORDに関しては簡単に行かなかった。

試行錯誤してコマンドプロンプトにてワンライナーで下記のようにすると実行できるようになりました。

CMD /C SET "PGPASSWORD=fuga" ^& "C:\PostgreSQL\9.6\bin\psql.exe" -h localhost -U test -d postgres -c "SELECT now()"

これならVBSで実行できるのではないかと組んだわけですが、結果は何も出力されない。

Option Explicit

Dim objShell, cmd

Set objShell = CreateObject("WScript.Shell")

cmd = "CMD /C SET ""PGPASSWORD=fuga"" ^& ""C:\PostgreSQL\9.6\bin\psql.exe"""
cmd = cmd & " -h localhost"
cmd = cmd & " -U test"
cmd = cmd & " -d postgres"
cmd = cmd & " -c ""SELECT now()"""

WScript.Echo objShell.Exec(cmd).StdOut.ReadAll
Set objShell = Nothing

無理矢理、なんとかしてみようと考えました。

バッチファイルを一時的に生成して実行することで、結果が表示されるようになります。

Option Explicit

Dim objShell, fso, cmd, objFile

const BAT_NAME = "tmp.bat"

Set objShell = CreateObject("WScript.Shell")
Set fso = WScript.CreateObject("Scripting.FileSystemObject")

cmd = "SET PGPASSWORD=fuga" & vbCrlf
cmd = cmd & """C:\PostgreSQL\9.6\bin\psql.exe"""
cmd = cmd & " -h localhost"
cmd = cmd & " -U test"
cmd = cmd & " -d postgres"
cmd = cmd & " -c ""SELECT now()"""
Set objFile = fso.OpenTextFile(BATNAME, 2, True)
objFile.WriteLine cmd
objFile.Close

WScript.Echo objShell.Exec(BATNAME).StdOut.ReadAll
fso.DeleteFile BATNAME, True

' オブジェクトを開放する
Set fso = Nothing
Set objFile = Nothing
Set objShell = Nothing


SQLの実行方法

psqlのcオプションで指定する。

cmd = cmd & " -c ""SELECT now()"""

psqlのfオプションで外部スクリーンを指定する。

cmd = cmd & " -f test.sql"

StdIn.Writeでsqlを指定する。

sql = "SELECT now()"

Set objShell = CreateObject("WScript.Shell")
With objShell.Exec(cmd)
.StdIn.Write sql
.StdIn.Close
WScript.Echo .StdOut.ReadAll
End With


psqlを使わない

VBScriptを使うならADOが使えます。これなら普通にパスワードを指定できるよね。

ODBCデータソースアドミニストレーターの設定で"PostgreSQL35W"をしておく。

PostgreSQLの接続確認

'初期化処理

Option Explicit
On
Error Resume Next
Dim Conn, RS

'DB接続
Set Conn = WScript.CreateObject("ADODB.Connection")
Conn.Open "DSN=PostgreSQL35W;UID=test;PWD=fuga"
Set RS = Conn.Execute("SELECT 'Connect OK'")

If RS.Eof Then
WScript.Echo 1 '接続エラ-
Else
WScript.Echo 0 '接続OK
End If

RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing


最後に

監視用にOracleのSQLPlusを使用していたプログラムの移植ってことでpsqlの使用を考えていたのですが、ものによってはADOを使用しても問題ないわけです。

ここらへんは柔軟に変更していくことも考えてみる。