【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(BAT_NAME, 2, True)
objFile.WriteLine cmd
objFile.Close

WScript.Echo objShell.Exec(BAT_NAME).StdOut.ReadAll
fso.DeleteFile BAT_NAME, 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を使用しても問題ないわけです。
ここらへんは柔軟に変更していくことも考えてみる。