CODEWITHSUNDEEP
postgresqlpowershell
Doncarlito87
Doncarlito87

Reputation: 459

How can I execute SQL scripts via Powershell?

I managed to connect to a PostgreSQL database via PowerShell after ages of trying.

But now I have 1 more question:

My goal is to create a Powershell script that executes my SQL scripts. How can I run my sql scripts in Powershell that are stored in c:\scripts for example?

This is my current source code:

...

$DBConnectionString = "Driver={PostgreSQL ODBC Driver(UNICODE)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;"
$DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;
$DBConn.Open();
$DBCmd = $DBConn.CreateCommand();
$DBCmd.CommandText = "CREATE TABLE accounts (
    user_id serial PRIMARY KEY,
    username VARCHAR ( 50 ) UNIQUE NOT NULL,
    password VARCHAR ( 50 ) NOT NULL,
    email VARCHAR ( 255 ) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
        last_login TIMESTAMP 
);";
$DBCmd.ExecuteReader();
$DBConn.Close();

UPDATE 1:

...

$DBConnectionString = "Driver={PostgreSQL ODBC Driver(UNICODE)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;"
$DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;
$DBConn.Open();
$DBCmd = $DBConn.CreateCommand();

/* 
Here I want to go to a folder where all my SQL files are and I want to run an SQL file
*/

);";
$DBCmd.ExecuteReader();
$DBConn.Close();

Upvotes: 2

Views: 5300

Answers (1)

mklement0
mklement0

Reputation: 439767

Perhaps you're looking for something like:

# ...
Get-ChildItem C:\Scripts -Filter *.psql | ForEach-Object {
  # Execute the content of each file as its own command.
  $DBCmd.CommandText = $_ | Get-Content -Raw
  $DBCmd.ExecuteReader();
}
# ...

If you want to merge the contents of all *.psql files and submit it as a single command:

# ...
# Execute the merged content of all script files as a single command.
$DBCmd.CommandText = (Get-Content -Raw C:\Scripts\*.psql) -join "`n"
$DBCmd.ExecuteReader();
# ...

Upvotes: 1

Related Questions