Error inserting an image into Oracle using PHP PDO

Question

I’m making a system in PDO PHP that records some images in the database, that same system was migrated from a database MySQL and it was working perfectly, but at the time I’m going to record the image in a column like BLOB in the Oracle returns me the following exception:

[Thu Mar 02 13:11:37.290759 2017] [:error] [pid 5740:tid 2192] [client ::1:54413] PHP Fatal error: Uncaught Exception 'Pdoexception' with message 'SQLSTATE[HY000]: General error: 972 Ocistmtprepare: ORA-00972: very long identifier n (ext pdo_oci oci_driver.c:339)' in C: xampp htdocs Manutencaooracle admin.php:1581 nStack trace: n#0 C: xampp htdocs Manutencaooracle admin.php(1581): PDO->exec('Insert into equ...') n#1 {main} n thrown in C: xampp htdocs Manutencaooracle admin.php on line 1581, referer: http://localhost:81/Manutencaooracle/admin.php

Follow the Insert in the database :

$query = $con->exec("insert into equipamentos (id,grupo,quantidade,descricao,marca_modelo,patrimonio,serie,tipo_ponto,foto_acabamento,foto_maquina) values (".$maior_id.",'".$_POST['labelGrupo']."',".$_POST['labelQuantidades'].",'".$_POST['labelDescricao']."','".$_POST['labelMarcaModelo']."',".$_POST['labelPatrimonio'].",'".$_POST['labelSerie']."','".$foto_ponto_certa."','".$foto_acabamentos_certa."','".$foto_maquina_certa."')");

Table create:

CREATE TABLE equipamentos (id INTEGER NOT NULL,grupo VARCHAR2(50),quantidade INTEGER,descricao VARCHAR2(50),marca_modelo VARCHAR2(50),patrimonio INTEGER,serie VARCHAR2(50), tipo_ponto BLOB, foto_acabamento BLOB, foto_maquina BLOB,PRIMARY KEY (id))

Processing images with php before entry into database:

// ARMAZENA A PRIMEIRA FOTO NOS ARRAYS
$foto_ponto = $_FILES['fotoPonto']['tmp_name'];
$foto_ponto_tamanho = $_FILES['fotoPonto']['size'];
$foto_ponto_tipo = $_FILES['fotoPonto']['type'];
$foto_ponto_nome = $_FILES['fotoPonto']['name'];


// TRATA A PRIMEIRA FOTO PARA FAZER A INSERÇÃO DELA NO BANCO DE DADOS
$fp = @fopen($foto_ponto, "rb");
$conteudo = @fread($fp,$foto_ponto_tamanho);
$foto_ponto_certa = @addslashes($conteudo);
@fclose($fp);

Remarks:

• I put only treatment of the first photo so it doesn’t get too extensive, the treatment of all photos are the same.
• that same code works perfectly in a database MySQL.

Answer 1

Hello, I suggest you refactor a little more, since you switched from Mysql to Oracle and are inserting record with LOB field. Follow a teaching suggestion:

$conn = ocilogon('usuario','senha','nome_da_base');

$arquivo  = "Open Office.zip";
$handle   = fopen($arquivo, "rb");
$conteudo = fread($handle, filesize($arquivo));

$stmt = oci_parse($conn, "INSERT INTO TABELA_BLOB VALUES (EMPTY_BLOB()) RETURNING ANEXO INTO :ANEXO");
$blob_aux = oci_new_descriptor($conn, OCI_D_LOB);
oci_bind_by_name($stmt, ":ANEXO", &$blob_aux, -1, OCI_B_BLOB);
oci_execute($stmt, OCI_DEFAULT);
$blob_aux->save($conteudo);

if (ocicommit($conn)) {
   echo "Registro inserido OK.";
} else {
   echo "ERRO, registro não inserido.";
}

Answer 2

Try it like this:

<?php

   include '../conexao_oracle.php';

   $db->beginTransaction(); // VERY IMPORTANT !
   $stmt = $db->prepare(
       "INSERT INTO equipamentos (foto_ponto) ".
       "VALUES (EMPTY_BLOB()) ".
       "RETURNING foto_pontoINTO :foto_ponto");
   $stmt->bindParam(':foto_ponto', $blob, PDO::PARAM_LOB);
   $blob = fopen($foto_ponto, "rb");
   $stmt->execute();
   $db->commit();