PHP: create 2 PDOs for two different databases, one block for the second
I have 2 DBs: 1 in MySQL and the other in SQLite3.
I need to insert the same data into both. To achieve this with a form, I am creating a PHP script that has some problems. Below is some code explaining what's going on:
// MySQL
try {
$sql = new PDO($pdo_servername, $username, $password, $pdo_options);
$sql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$ret = $sql->exec($query);
if(!$ret){
echo $sql->lastErrorMsg();
} else {
echo "New record created successfully on MySQL DB";
}
} catch (PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}
$sql->close();
// SQLite
try {
$sqlite = new PDO($pdo_servername_sqlite3);
$sqlite->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$retlite = $sqlite->exec($query);
if(!$retlite){
echo $sqlite->lastErrorMsg();
} else {
echo "New record created successfully on SQLite3 DB";
}
} catch (PDOException $e) {
echo $sqlite . "<br>" . $e->getMessage();
}
$sqlite->close();
MySQL works fine and SQLite3 won't even start. By inverting blocks, thus first SQLite3, then MySQL, the problem is inverted: SQLite3 works fine and MySQL does not start. I didn't get any errors
I also tried to avoid trying - finally and I just wrote the code so simply and I have the same situation. Is it forbidden to open 2 PDO connections to two different DBs? Where is my mistake?
source to share
Try this way, this is the only breakpoint where you really need try...catch
:
// MySQL
try {
$sql = new PDO($pdo_servername, $username, $password, $pdo_options);
} catch (PDOException $e) {
echo 'MySQL connection failed: ' . "<br>" . $e->getMessage();
$sql = false;
}
// SQLite
try {
$sqlite = new PDO($pdo_servername_sqlite3);
} catch (PDOException $e) {
echo 'SQLite connection failed: '. "<br>" . $e->getMessage();
$sqlite = false;
}
if ($sql != false) {
$ret = $sql->exec($query);
if(!$ret){
echo $sql->lastErrorMsg();
} else {
echo "New record created successfully on MySQL DB";
}
$sql->close();
}
if ($sqlite != false) {
$retlite = $sqlite->exec($query);
if(!$retlite){
echo $sqlite->lastErrorMsg();
} else {
echo "New record created successfully on SQLite3 DB";
}
$sqlite->close();
}
source to share
First of all, I want to thank all the contributors here :) I would like to post the final working code because some line needs to be changed as well, follow the code above. Indeed, the PDO method lastErrorMsg();
does not seem to exist, and the same is for PDO method close();
. Instead lastErrorMsg();
, use errorInfo()
this as an array. To close the DB connection: I read somewhere here on Stackoverflow that when the script execution finishes, PDO closes automatically, or you need to destroy the object by assigning null
.
Because finally the code as suggested by @Alex, with these small changes, worked, I was able to get errors from PHP highlighting the above details. Please, below the below working code, hoping it might be helpful for anyone else, has my same problem:
/**
* MySQL - try to open it. If it fails,
* it returns which error and continues the execution of the script
*/
try {
$sql = new PDO($pdo_servername, $username, $password, $pdo_options);
} catch (PDOException $e) {
echo 'MySQL connection failed: ' . "<br>" . $e->getMessage();
$sql = false;
}
/**
* SQLite - try to open it. If it fails,
* it returns which error and continues the execution of the script
*/
try {
$sqlite = new PDO($pdo_servername_sqlite3);
} catch (PDOException $e) {
echo 'SQLite connection failed: '. "<br>" . $e->getMessage();
$sqlite = false;
}
/**
* If the connection is made, it executes the Query
* If anything wrong with the Query insertion, an error is returned.
* The script continues
*/
if ($sql != false) {
$ret = $sql->exec($query);
if(!$ret){
print_r($sql->errorInfo()); // THIS is the valid method for PDO Exec and returns an array
} else {
echo "New record created successfully on MySQL DB";
}
}
if ($sqlite != false) {
$retlite = $sqlite->exec($query);
if(!$retlite){
print_r($sqlite->errorInfo()); // THIS is the valid method for PDO Exec and returns an array
} else {
echo "New record created successfully on SQLite3 DB";
}
}
/**
* Closes the DB Connections
*/
$sql = null;
$sqlite = null;
Thank you all for your real help. I really appreciated it :)
source to share