How can I execute SQL scripts via Powershell?

Question

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();

Unfortunately not. I want to execute SQL scripts via Powershell. I.e. establish connection --> execute sql scripts --> close connection again. — Doncarlito87
– Doncarlito87, Commented Jan 10, 2022 at 15:46
The code works but I want to replace the command block and run a sql file instead. I updated my Code. Maybe that will make it more understandable — Doncarlito87
– Doncarlito87, Commented Jan 10, 2022 at 15:51
I misread your question; it's definitely clear what you're looking for now. — mklement0
– mklement0, Commented Jan 10, 2022 at 16:18
I've updated the answer to show a solution that merges the content of all SQL script files to submit the merged content as a single command (assuming that is supported). — mklement0
– mklement0, Commented Jan 10, 2022 at 16:23

mklement0 · Accepted Answer · 2022-01-11 13:37:42Z

1

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();
# ...

edited Jan 11, 2022 at 13:37

answered Jan 10, 2022 at 16:06

mklement0

452k68 gold badges727 silver badges986 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I execute SQL scripts via Powershell?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related