CODEWITHSUNDEEP
phpsqlpostgresqlpdo
mHouses
mHouses

Reputation: 925

Running SQL scripts to PostgreSQL using PHP/PDO

I need to run some SQL scripts to my database, to update the schema and data (some kind of migration).

Because there is some logic to check before running each script, I'm writting a small PHP tool to execute the scripts, but I have a simple problem: Can I load and execute a "simple" SQL script (including table manipulation, triggers & stored procedures updates) directly, or should I add markers to the script (to mark where each sentence ends), and run the script sentence by sentence?

For the database access I'm using the PDO.

Upvotes: 1

Views: 971

Answers (1)

openai_sucks
openai_sucks

Reputation: 1273

I had a similar situation today.

My solution is extremely simple, but is just smart enough to allow for comments and statements that span multiple lines.

// open script file

$scriptfile = fopen($script_path, "r");
if (!$scriptfile) { die("ERROR: Couldn't open {$scriptfile}.\n"); }

// grab each line of file, skipping comments and blank lines

$script = '';
while (($line = fgets($scriptfile)) !== false) {
    $line = trim($line);
    if(preg_match("/^#|^--|^$/", $line)){ continue; } 
    $script .= $line;
}

// explode script by semicolon and run each statement

$statements = explode(';', $script);
foreach($statements as $sql){
    if($sql === '') { continue; }
    $query = $pdo->prepare($sql);
    $query->execute();
    if($query->errorCode() !== '00000'){ die("ERROR: SQL error code: ".$query->errorCode()."\n"); }
}

Upvotes: 2

Related Questions