用php插入CLOB

Question

Hi everybody I am trying to insert a photo data in base64 into a CLOB in field in a Oracle DB, first I try with a simple INSERT statement but I find thats that don't work.

So I try this:

    $foto_data = $data->FOTOGRAFIA;

    $query = 'INSERT INTO "TMREPORTE" (
            "ID_REPORTE", 
            "NUMINT", 
            "NUMEXT", 
            "ENTRECALLE1", 
            "ENTRECALLE2", 
            "OBSERVACIONES", 
            "VISIBILIDAD", 
            "FOTOGRAFIA", 
            "LAT", 
            "LON", 
            "IDATENDIDO", 
            "FECHA_INGRESO", 
            "DIVICE_IDENTIFIER", 
            "ID_TIPO", 
            "ID_PRIORIDAD", 
            "ID_CPASENT", 
            "CALLE", 
            "FOLIO"
        ) VALUES (
            SEQ_TMREPORTE_IDREPORTE.NEXTVAL, 
            \''.xss_clean($data->NUMINT).'\', 
            \''.xss_clean($data->NUMEXT).'\', 
            \''.xss_clean($data->ENTRECALLE1).'\', 
            \''.xss_clean($data->ENTRECALLE2).'\', 
            \''.xss_clean($data->OBSERVACIONES).'\', 
            '.intval(xss_clean($data->VISIBILIDAD)).', 
            EMPTY_CLOB(),
            '.intval(xss_clean($data->LAT)).', 
            '.intval(xss_clean($data->LON)).', 
            0, 
            SYSDATE, 
            \''.xss_clean($data->DIVICE_IDENTIFIER).'\', 
            '.intval(xss_clean($data->ID_TIPO)).', 
            1, 
            '.intval(xss_clean($data->ID_CPASENT)).', 
            \''.xss_clean($data->CVE_VIALIDAD).'\', 
            \''.$rand_letter.'\'||\''.date( 'y', time() ).'\'||to_char(SEQ_TMREPORTE_IDREPORTE.NEXTVAL,\'FM0000\')
        ) RETURNING
            FOTOGRAFIA INTO :fotodata';

    $conn = $this->db->conn_id;

    $statement = oci_parse($conn, $query);
    $descriptor = oci_new_descriptor($conn, OCI_DTYPE_LOB);
    oci_bind_by_name($statement, ':fotodata', $descriptor, -1, OCI_B_CLOB);
    oci_execute($statement, 0);
    $descriptor->save($foto_data);
    oci_commit($conn);
    oci_free_descriptor($descriptor);

And this code seems to work because that don't send me any error, but in the db the FOTOGRAFIA field is always null.

So I try a diferent example code:

   $foto_data = $data->FOTOGRAFIA;

    $query = 'INSERT INTO "TMREPORTE" (
            "ID_REPORTE", 
            "NUMINT", 
            "NUMEXT", 
            "ENTRECALLE1", 
            "ENTRECALLE2", 
            "OBSERVACIONES", 
            "VISIBILIDAD", 
            "FOTOGRAFIA", 
            "LAT", 
            "LON", 
            "IDATENDIDO", 
            "FECHA_INGRESO", 
            "DIVICE_IDENTIFIER", 
            "ID_TIPO", 
            "ID_PRIORIDAD", 
            "ID_CPASENT", 
            "CALLE", 
            "FOLIO"
        ) VALUES (
            SEQ_TMREPORTE_IDREPORTE.NEXTVAL, 
            \''.xss_clean($data->NUMINT).'\', 
            \''.xss_clean($data->NUMEXT).'\', 
            \''.xss_clean($data->ENTRECALLE1).'\', 
            \''.xss_clean($data->ENTRECALLE2).'\', 
            \''.xss_clean($data->OBSERVACIONES).'\', 
            '.intval(xss_clean($data->VISIBILIDAD)).', 
            :fotodata,
            '.intval(xss_clean($data->LAT)).', 
            '.intval(xss_clean($data->LON)).', 
            0, 
            SYSDATE, 
            \''.xss_clean($data->DIVICE_IDENTIFIER).'\', 
            '.intval(xss_clean($data->ID_TIPO)).', 
            1, 
            '.intval(xss_clean($data->ID_CPASENT)).', 
            \''.xss_clean($data->CVE_VIALIDAD).'\', 
            \''.$rand_letter.'\'||\''.date( 'y', time() ).'\'||to_char(SEQ_TMREPORTE_IDREPORTE.NEXTVAL,\'FM0000\')
        )';

    $conn = $this->db->conn_id;

    $statement = oci_parse($conn, $query);
    oci_bind_by_name($statement, ':fotodata', $foto_data);
    oci_execute($statement);

But i get the same result, a null value in FOTOGRAFIA field.

I am a little lost with this insert, do you see any error?

This is my table create statement.

  CREATE TABLE "ADMATENCIONC"."TMREPORTE" 
   (    "ID_REPORTE" NUMBER(6,0) NOT NULL ENABLE, 
    "NUMINT" VARCHAR2(10 BYTE), 
    "NUMEXT" VARCHAR2(10 BYTE), 
    "ENTRECALLE1" VARCHAR2(150 BYTE), 
    "ENTRECALLE2" VARCHAR2(150 BYTE), 
    "OBSERVACIONES" VARCHAR2(300 BYTE), 
    "VISIBILIDAD" NUMBER(1,0), 
    "FOTOGRAFIA" CLOB, 
    "LAT" NUMBER(12,4), 
    "LON" NUMBER(12,4), 
    "IDATENDIDO" NUMBER(4,0), 
    "FECHA_INGRESO" DATE, 
    "DIVICE_IDENTIFIER" VARCHAR2(100 BYTE), 
    "ID_TIPO" NUMBER(3,0), 
    "ID_PRIORIDAD" NUMBER(2,0), 
    "ID_CPASENT" NUMBER(4,0), 
    "CALLE" VARCHAR2(500 BYTE), 
    "ESTADO" NUMBER(3,0) DEFAULT 0, 
    "FOLIO" VARCHAR2(7 BYTE), 
     PRIMARY KEY ("ID_REPORTE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TD_DATOS"  ENABLE, 
     FOREIGN KEY ("ID_TIPO")
      REFERENCES "ADMATENCIONC"."TCTIPO" ("ID_TIPO") ON DELETE SET NULL ENABLE, 
     FOREIGN KEY ("ID_PRIORIDAD")
      REFERENCES "ADMATENCIONC"."TCPRIORIDAD" ("ID_PRIORIDAD") ON DELETE SET NULL ENABLE, 
     FOREIGN KEY ("ID_CPASENT")
      REFERENCES "ADMATENCIONC"."TCCEPOMEX" ("ID_CP") ON DELETE SET NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TD_DATOS" 
 LOB ("FOTOGRAFIA") STORE AS BASICFILE (
  TABLESPACE "TD_DATOS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

Answer 1

I found my error, the POST request was not sending me the image data.