6 Replies Latest reply on Feb 6, 2009 6:34 AM by mauro.luna

    error in result of storeproc

    mauro.luna
      I have a problem with cfstoredproc and Informix database, when I invoke a stored procedure by cfstoredproc, it returns me several columns with the same name (EXPRESSION) and repeat the value of first column.

      I am running CFMX 8.0.1 Enterprise, with Informix 9.40 and built-in driver, wich works great, but when i try to use CFMX 8.0.1 with JDBC 3.50 for IBM Informix cfstoredproc error occurs. I need this version of JDBC because I will upgrade to informix 11.50 and CFMX 8.0.1.

      This is the stored procedure...

      CREATE PROCEDURE admgcn.stpbacklog (unidad1 char(1)) returning char(10), char(12), char(12), char(30), char(7), char(1), char(40), char(30), char(8), int, varchar(255,0), varchar(255,0), varchar(255,0), char(1), char(40), char(12), char(8);

      define r_fecemision char(10);
      define r_numaviso like r3avisos.numaviso;
      define r_numorden like r3avisos.numorden;
      define r_ubicacion like r3avisos.ubicacion;
      define r_inop like r3otinopcd.noinop;
      define r_prior like r3avisos.prior;
      define r_descaviso like r3avisos.descaviso;
      define r_resp like r3avisosop.responsable;
      define r_numsem like r3avisosop.numsem;
      define r_porc like r3activ.porcentaje;
      define r_coment like r3avisosop.comentop;
      define r_solpeds like r3avisosop.solpeds;
      define r_comentab like r3avisosop.comentab;
      define r_repercusion like r3avisos.repercusion;
      define r_status like r3avisos.status;
      define r_pn like r3avisos.numaviso;
      define r_ptotrbres like r3avisos.ptotrbres;
      define valor int;


      let valor = 1;
      if (weekday(today) = 1) then let valor = 3;
      end if;

      begin
      set isolation to dirty read;
      foreach
      SELECT
      a.fecemision, a.numaviso, a.numorden, a.ubicacion, ao.inop, a.prior prioridad, a.descaviso descripcion, ao.responsable, ao.numsem, 0 porcentaje, nvl(trim(ao.comentop),'') comentop,

      nvl(trim(ao.solpeds),'') solpeds, nvl(trim(ao.comentab[1,234]), '') comentab, a.repercusion, a.status,trim((select a.numaviso from r3avisos avs where unaviso =
      unidad1 and status[1,4] <> 'MECE' and edodisp = '2' and prior is not null and a.numaviso = avs.numaviso)) PN, ptotrbres
      INTO
      r_fecemision, r_numaviso, r_numorden, r_ubicacion, r_inop, r_prior, r_descaviso, r_resp, r_numsem, r_porc, r_coment, r_solpeds, r_comentab, r_repercusion, r_status, r_pn, r_ptotrbres
      FROM
      r3avisos a, outer r3avisosop ao
      WHERE
      a.status[1,4] <> ('MECE') and
      a.prior in ('1', '2', '3', '4', '5') and
      (a.repercusion <> 'A' or a.repercusion is null) and
      a.claseaviso = 'AN' and
      ao.numaviso = a.numaviso and
      a.unaviso = unidad1 and
      a.numorden is null {and
      (DATE(TO_DATE(creado,'%d/%m/%Y')) >= today - valor and
      DATE(TO_DATE(creado,'%d/%m/%Y')) <= today)}

      UNION

      SELECT
      a.fecemision, a.numaviso, a.numorden, a.ubicacion, '', r3o.prioridad, r3o.descripcion, ao.responsable, r3o.pep numsem, r3a.porcentaje porcentaje, nvl(trim(ao.comentop),'') comentop,
      nvl(trim(ao.solpeds),'') solpeds, nvl(trim(ao.comentab[1,234]), '') comentab, a.repercusion, r3o.statussist,trim((select a.numaviso from r3avisos avs where unaviso = unidad1 and status[1,4] <> 'MECE' and edodisp = '2' and prior is not null and a.numaviso = avs.numaviso)) PN, r3o.ptotrbres
      FROM
      r3avisos a, outer r3avisosop ao, r3orden r3o, r3activ r3a
      WHERE
      r3o.statusus[1,4] <> 'CANC' and r3o.statussist[1,4] in ('LIBD','LIBE', 'ABIE') and
      r3o.prioridad in ('1', '2', '3', '4', '5') and
      (a.repercusion <> 'A' or a.repercusion is null) and
      r3o.claseorden = 'LV01' and
      r3o.claseact = 'N00' and
      a.unaviso = unidad1 and
      ao.numaviso = a.numaviso and
      a.numorden = r3o.numorden and
      r3a.numorden = a.numorden and
      r3a.porcentaje < 100 and
      r3a.numope is not null

      UNION

      SELECT
      a.fecemision, a.numaviso, a.numorden, a.ubicacion, '', r3o.prioridad, r3o.descripcion, ao.responsable, r3o.pep numsem,0 porcentaje, nvl(trim(ao.comentop),'') comentop,

      nvl(trim(ao.solpeds),'') solpeds, nvl(trim(ao.comentab[1,234]), '') comentab, a.repercusion, r3o.statussist,trim((select a.numaviso from r3avisos avs where unaviso = unidad1 and status[1,4] <> 'MECE' and edodisp = '2' and prior is not null and a.numaviso = avs.numaviso)) PN, r3o.ptotrbres
      FROM
      r3avisos a, outer r3avisosop ao, r3orden r3o
      WHERE
      r3o.statusus[1,4] <> 'CANC' and r3o.statussist[1,4] in ('LIBD','LIBE', 'ABIE') and
      r3o.prioridad in ('1', '2', '3', '4', '5') and
      (a.repercusion <> 'A' or a.repercusion is null) and
      r3o.claseorden = 'LV01' and
      r3o.claseact = 'N00' and
      a.unaviso = unidad1 and
      ao.numaviso = a.numaviso and
      a.numorden = r3o.numorden and
      r3o.numorden not in (select numorden from r3activ where numorden is not null and numorden <> 'N/A')
      ORDER BY
      ptotrbres, prioridad, numaviso
      return

      r_fecemision, r_numaviso, r_numorden, r_ubicacion,
      r_inop, r_prior, r_descaviso, r_resp, r_numsem, r_porc, r_coment,
      r_solpeds, r_comentab, r_repercusion, r_status, r_pn, r_ptotrbres with resume;

      end foreach;
      end;
      end procedure




      as you can see I return several columns with different names


      This is the way how I invoke the stored

      <cfstoredproc datasource="prueba" procedure="stpbacklog">
      <cfprocparam cfsqltype="CF_SQL_CHAR" value="1">
      <cfprocresult name="RS">
      </cfstoredproc>

      <cfdump var="#RS#"/>


      This is the result

      query
      (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION) (EXPRESSION)
      1 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009
      2 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009 07/01/2009
      3 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009 06/01/2009
      4 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008
      5 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008 23/06/2008
      6 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008 23/12/2008
      7 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008 28/12/2008
      8 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008 24/12/2008
      9 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008 20/12/2008
      10 18/10/2008 18/10/2008 18/10/2008 18/10/2008 18/10/2008 18/10/2008 18/10/2008 18/10/2008


      You see how repeat first column? This is the problem I have.

      I hope you can support me.

      In advance,

      Thanks a lot.