CODEWITHSUNDEEP
phpmysqloracle-databasepdoblob
Orsu
Orsu

Reputation: 417

OCI-Lob could not be converted to string error when Importing an Oracle BLOB into a MySQL one, using PHP

For a work, i have to import tables from an Oracle Database into a MySQL one.

Here is my approach to this :

$db = "" //Oracle connection    
$connexion = oci_connect(user, login,$db)

$sql2 = "SELECT * FROM table" //SQL query

$requete2 = oci_parse($connexion, $sql); 
$result2 = oci_execute($requete); // result is useless i think

if (!$requete2) {

    $e = oci_error();   

}else {

    try {

        $conn2 = new PDO("mysql:host=localhost;dbname=db", 'login', 'pass');
        $conn2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $stmt2 = $conn2->prepare("INSERT IGNORE INTO table 
                                ('field1', 'field2', 'field3') 
                                VALUES (?, ?, ?)");
        $conn2->beginTransaction();

        while (($row2 = oci_fetch_array($requete2, OCI_NUM+OCI_RETURN_NULLS)) != false) {

               $stmt2->execute($row2);

        }

   }catch(PDOException $e){
       $conn2->rollBack();
       echo "Connection failed: " . $e->getMessage();   
   }
    $conn2->commit(); 
}

Here is an example of the var_dump of the $row2 before the execute statement:

array (size=3)
  0 => string '2541.pdf' (length=29)
  1 => 
    object(OCI-Lob)[5]
      public 'descriptor' => resource(7, oci8 descriptor)
  2 => string 'id' (length=6)

And here are the fields i'm trying to insert in :

field1 : VARCHAR field2 : BLOB field3 : VARCHAR

Running this code gives me :

Catchable fatal error: Object of class OCI-Lob could not be converted to string in line 95

With line 95 being $stmt2->execute($row2);

trying to bind each parameter, in this fashion :

$stmt2->bindParam(1, $row2[0]);
$stmt2->bindParam(2, $row2[1],PDO::PARAM_LOB);
$stmt2->bindParam(3, $row2[2]);
$stmt2->execute();

gives me the same error

Catchable fatal error: Object of class OCI-Lob could not be converted to string

And just to see, here is the var_dump of the $row2[1] (my blob):

object(OCI-Lob)[5]
  public 'descriptor' => resource(7, oci8 descriptor)

Thanks.

EDIT : To add some precision, thsi aprroach works like a charm if i only have Strings (i tried it). The problem here comes specifically from the BLOB

Upvotes: 0

Views: 1322

Answers (1)

Álvaro González
Álvaro González

Reputation: 146573

The PDO and OCI8 extensions cannot interact each other the way you intend. More specifically, PDOStatement::execute() expects a flat array of values that are either strings or can be cast to strings. The OCI-Lob class doesn't implement a __toString() magic method, thus the error.

I haven't worked with OCI8 for a while and I can't remember if there's a way to return LOBs as plain strings (which would be one option) but it's pretty straightforward to render a string yourself:

while (($row2 = oci_fetch_array($requete2, OCI_NUM+OCI_RETURN_NULLS)) != false) {
    $row2[1] = $row2[1]->load();
    $stmt2->execute($row2);
}

... or this (to your liking):

while (($row2 = oci_fetch_array($requete2, OCI_NUM+OCI_RETURN_NULLS)) != false) {
    $stmt2->execute(
        [
            $row2[0],
            $row2[1]->load(),
            $row2[2],
        ]
    );
}

Beware this is memory intensive since you're loading the complete LOB into RAM. If you need to manipulate large values, you'll have to research on PDO LOBs.

Also, this code will break when you add more columns to the oracle table or alter their position. I suggest you replace SELECT * with a proper column list.

Upvotes: 1

Related Questions