crear archivo texto desde un store procedure

[mRoman]

Hola amigos.

Estoy trabajando con FB 2.0 y Delphi6

Estoy tratando de crear, desde un procedimiento almacenado, un archivo de texto con una cantidad de registros que no han sido encontrados en una consulta. Este archivo de texto deberá contener todos los registros que no fueron encontrados en la consulta SQL...

Se puede generar un archivo de texto con estos registros?
Como se hace? (Ejemplos)

He encontrado esto:

https://firebird21.wordpress.com/tag/archivo-de-texto/
https://www.google.com/search?q=crea...t=gws-wiz-serp

Pero sin éxito, he hecho lo del primer link, pero creo que mi FB 2.0 no lo soporta, al momento de compilar el SP marca este error:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 14, column 5.
CREATE.

Este es el STORE PROCEDURE que trato de crear:

Código SQL [-]
CREATE PROCEDURE SP_VALIDAR_COMBINACION (
    I_MES INTEGER,
    I_ANIO INTEGER)
AS
DECLARE VARIABLE V_RUTA INTEGER;
DECLARE VARIABLE V_FECHA_VISITA DATE;
DECLARE VARIABLE V_CONSEC_MAX INTEGER;
DECLARE VARIABLE V_CONSEC INTEGER;
DECLARE VARIABLE V_LEC_SEDE NUMERIC(10,0);
DECLARE VARIABLE V_LECHERIA NUMERIC(10,0);
DECLARE VARIABLE V_DATO NUMERIC(1,0);
DECLARE VARIABLE V_LECHERIA2 NUMERIC(10,0);
begin
    CREATE TABLE ERR_COMBINACIONES EXTERNAL 'C:\ATENCION\ERR_COMBINACION.TXT' (
                               ERR_LEC_ORIGEN CHAR(10),
                               ERR_LEC_DESTINO CHAR(10)
                               ERR_RUTA CHAR(3)
                               ERR_FECHA_VISITA CHAR(10));
    FOR SELECT DISTINCT(a.clave_ruta),
                        a.fecha_visita,
                   MAX( a.consec )
        FROM atencion_lecheria a
        WHERE a.clave_ruta in (SELECT DISTINCT(A.clave_ruta)
                               FROM atencion_lecheria A
                               WHERE A.mes_periodo=:i_MES
                                 AND A.anio_periodo=:i_ANIO)
          AND a.mes_periodo=:i_mes
          AND a.anio_periodo=:i_anio
        GROUP BY a.clave_ruta,
                 a.fecha_visita
        INTO :v_ruta, :v_fecha_visita, :v_consec_max DO
        BEGIN
             v_consec=1;
             While ( v_consec <= v_consec_max ) do
             begin
                  select b.clave_lec
                  from atencion_lecheria b
                  where b.fecha_visita=:v_fecha_visita
                    and b.clave_ruta=:v_ruta
                    and b.consec=:v_consec
                  into :v_lecheria;

                  /*DISTANCIA DE LA SEDE A LA PRIMER LECHERIA*/
                  if (v_consec=1) then
                  begin
                      /*Obtener la lecheria sede de la ruta*/
                      select r.clave_lec_sede
                      from ruta r
                      where r.clave_ruta=:v_ruta
                      into :v_lec_sede;

                      /*Buscar si existe la combinacion de la SEDE a la primer lecheria de la visita*/
                      select 1
                      from lecheria_a_lecheria c
                      where c.clave_lec_origen=:v_lec_sede
                        and c.clave_lec_destino=:v_lecheria
                      into :v_dato;

                      if (v_dato is null) then
                      begin
                          select 1
                          from lecheria_a_lecheria c
                          where c.clave_lec_origen=:v_lecheria
                            and c.clave_lec_destino=:v_lec_sede
                          into :v_dato;
                          if (v_dato is null) then
                          begin
                              INSERT INTO ERR_COMBINACIONES (ERR_LEC_ORIGEN,
                                                             ERR_LEC_DESTINO,
                                                             ERR_RUTA,
                                                             ERR_FECHA_VISITA)
                                                     values (:v_lecheria,
                                                             :v_lec_sede,
                                                             :v_ruta,
                                                             :v_fecha_visita);
                          end
                      end
                      v_lecheria2=v_lecheria;
                  end Else
                  begin
                      /*Buscar si existe combinacion de la primer lecheria con la segunda*/
                      select 1
                      from lecheria_a_lecheria c
                      where c.clave_lec_origen=:v_lecheria2
                        and c.clave_lec_destino=:v_lecheria
                      into :v_dato;

                      if (v_dato is null) then
                      begin
                          select 1
                          from lecheria_a_lecheria c
                          where c.clave_lec_origen=:v_lecheria
                            and c.clave_lec_destino=:v_lecheria2
                          into :v_dato;
                          if (v_dato is null) then
                          begin
                              INSERT INTO ERR_COMBINACIONES (ERR_LEC_ORIGEN,
                                                             ERR_LEC_DESTINO,
                                                             ERR_RUTA,
                                                             ERR_FECHA_VISITA)
                                                     values (:v_lecheria,
                                                             :v_lec_sede,
                                                             :v_ruta,
                                                             :v_fecha_visita);
                          end
                      end
                      v_lecheria2=:v_lecheria;
                  end;
                  v_consec:=:v_consec+1;
             end
        END
end

O puedo crear una tabla con los campos para almacenar los registros con error y luego crear el archivo texto desde DELPHI....pero quisiera agotar la opción de crearlo desde el STORE PROCEDURE.

Gracias por su tiempo.

[marco3k]

Haz intentado usar ese código "CREATE TABLE ERR_COMBINACIONES EXTERNAL" en un procedimiento almacenado de prueba y luego agregarle los registros de prueba directamente para ver si tu versión de firebird 2.0 lo soporta?

[mRoman]

Empezado por marco3k

Haz intentado usar ese código "CREATE TABLE ERR_COMBINACIONES EXTERNAL" en un procedimiento almacenado de prueba y luego agregarle los registros de prueba directamente para ver si tu versión de firebird 2.0 lo soporta?

Hola Marco...

Gracias por contestar.

Pues hice SP q publiqué, pero no anda, marca error. Sería el mismo resultado si lo hago en SP de prueba como comentas.

Le quité esa parte, lo compilé y funcionó, la otra alternativa es crear la tabla dentro de la B.D. firebird y llenara de registros y luego desde delphi lo ligo a un DataSet para luego exportarlo a un archivo plano, no quería hacer esto poque es modificar el EXE del sistema, pero si no hay de otra, tendré que resolverlo asi de esa manera.

Saludos.

[Casimiro Noteví]

Yo he usado en varias ocasiones tablas externas y sin ningún problema.
Aunque con firebird 2.5 en adelante.

[[duilioisola]]

El problema es que no se pueden crear y borrar tablas externas desde un procedimiento.

Primero tienes que crear la tabla y luego utilizarla como si fuera una tabla más de la base de datos.
Además, veo que faltan algunas comas...

El script de creación sería algo así;
Recuerda inicializar V_DATO en cada iteración del bucle.
Si un select no encuentra registros, V_DATO no se toca y queda con el valor anterior y podría dar falsos positivos.

Código SQL [-]
set term ^;

CREATE TABLE ERR_COMBINACIONES EXTERNAL 'C:\ATENCION\ERR_COMBINACION.TXT' (
                           ERR_LEC_ORIGEN CHAR(10),
                           ERR_LEC_DESTINO CHAR(10),
                           ERR_RUTA CHAR(3),
                           ERR_FECHA_VISITA CHAR(10))
^
commit work^

CREATE PROCEDURE SP_VALIDAR_COMBINACION (
    I_MES INTEGER,
    I_ANIO INTEGER)
AS
DECLARE VARIABLE V_RUTA INTEGER;
DECLARE VARIABLE V_FECHA_VISITA DATE;
DECLARE VARIABLE V_CONSEC_MAX INTEGER;
DECLARE VARIABLE V_CONSEC INTEGER;
DECLARE VARIABLE V_LEC_SEDE NUMERIC(10,0);
DECLARE VARIABLE V_LECHERIA NUMERIC(10,0);
DECLARE VARIABLE V_DATO NUMERIC(1,0);
DECLARE VARIABLE V_LECHERIA2 NUMERIC(10,0);
begin
    /* La tabla ya existe
    CREATE TABLE ERR_COMBINACIONES EXTERNAL 'C:\ATENCION\ERR_COMBINACION.TXT' (
                               ERR_LEC_ORIGEN CHAR(10),
                               ERR_LEC_DESTINO CHAR(10)
                               ERR_RUTA CHAR(3)
                               ERR_FECHA_VISITA CHAR(10));
    */

    FOR SELECT DISTINCT(a.clave_ruta),
                        a.fecha_visita,
                   MAX( a.consec )
        FROM atencion_lecheria a
        WHERE a.clave_ruta in (SELECT DISTINCT(A.clave_ruta)
                               FROM atencion_lecheria A
                               WHERE A.mes_periodo=:i_MES
                                 AND A.anio_periodo=:i_ANIO)
          AND a.mes_periodo=:i_mes
          AND a.anio_periodo=:i_anio
        GROUP BY a.clave_ruta,
                 a.fecha_visita
        INTO :v_ruta, :v_fecha_visita, :v_consec_max DO
        BEGIN
             v_consec=1;
             While ( v_consec <= v_consec_max ) do
             begin
                  select b.clave_lec
                  from atencion_lecheria b
                  where b.fecha_visita=:v_fecha_visita
                    and b.clave_ruta=:v_ruta
                    and b.consec=:v_consec
                  into :v_lecheria;

                  /********************************/
                  /*INICIALIZO V_DATO EN CADA BUCLE*/
                  /********************************/
                  v_dato = NULL;

                  /*DISTANCIA DE LA SEDE A LA PRIMER LECHERIA*/
                  if (v_consec=1) then
                  begin
                      /*Obtener la lecheria sede de la ruta*/
                      select r.clave_lec_sede
                      from ruta r
                      where r.clave_ruta=:v_ruta
                      into :v_lec_sede;

                      /*Buscar si existe la combinacion de la SEDE a la primer lecheria de la visita*/
                      select 1
                      from lecheria_a_lecheria c
                      where c.clave_lec_origen=:v_lec_sede
                        and c.clave_lec_destino=:v_lecheria
                      into :v_dato;

                      if (v_dato is null) then
                      begin
                          select 1
                          from lecheria_a_lecheria c
                          where c.clave_lec_origen=:v_lecheria
                            and c.clave_lec_destino=:v_lec_sede
                          into :v_dato;
                          if (v_dato is null) then
                          begin
                              INSERT INTO ERR_COMBINACIONES (ERR_LEC_ORIGEN,
                                                             ERR_LEC_DESTINO,
                                                             ERR_RUTA,
                                                             ERR_FECHA_VISITA)
                                                     values (:v_lecheria,
                                                             :v_lec_sede,
                                                             :v_ruta,
                                                             :v_fecha_visita);
                          end
                      end
                      v_lecheria2=v_lecheria;
                  end Else
                  begin
                      /*Buscar si existe combinacion de la primer lecheria con la segunda*/
                      select 1
                      from lecheria_a_lecheria c
                      where c.clave_lec_origen=:v_lecheria2
                        and c.clave_lec_destino=:v_lecheria
                      into :v_dato;

                      if (v_dato is null) then
                      begin
                          select 1
                          from lecheria_a_lecheria c
                          where c.clave_lec_origen=:v_lecheria
                            and c.clave_lec_destino=:v_lecheria2
                          into :v_dato;
                          if (v_dato is null) then
                          begin
                              INSERT INTO ERR_COMBINACIONES (ERR_LEC_ORIGEN,
                                                             ERR_LEC_DESTINO,
                                                             ERR_RUTA,
                                                             ERR_FECHA_VISITA)
                                                     values (:v_lecheria,
                                                             :v_lec_sede,
                                                             :v_ruta,
                                                             :v_fecha_visita);
                          end
                      end
                      v_lecheria2=:v_lecheria;
                  end
                  v_consec=:v_consec+1;
             end
        END
end
^
commit work^

[[duilioisola]]

Para evitar errores con la inicialización de variables como V_DATO, yo prefiero utilizar IF EXISTS(...).

• Te ahorras la variable
• Te ahorras tener que inicializarla
• Te ahorras tener que pensar si tiene valores basura de otros usos.
• Es una instrucción optimizada que corta el SELECT en cuanto encuentra algo y no tiene que ir creando la respuesta con los registros encontrados.

Mira si te deja compilar así:

Código SQL [-]
                      /*Buscar si existe la combinacion de la SEDE a la primer lecheria de la visita*/
                      if (not exists(select *
                                     from lecheria_a_lecheria c
                                     where c.clave_lec_origen=:v_lec_sede
                                       and c.clave_lec_destino=:v_lecheria)) then
                      begin
                          if (not exists(select *
                                         from lecheria_a_lecheria c
                                         where c.clave_lec_origen=:v_lecheria
                                           and c.clave_lec_destino=:v_lec_sede)) then
                          begin
                              INSERT INTO ERR_COMBINACIONES (ERR_LEC_ORIGEN,
                                                             ERR_LEC_DESTINO,
                                                             ERR_RUTA,
                                                             ERR_FECHA_VISITA)
                                                     values (:v_lecheria,
                                                             :v_lec_sede,
                                                             :v_ruta,
                                                             :v_fecha_visita);
                          end
                      end

[mRoman]

Empezado por duilioisola

El problema es que no se pueden crear y borrar tablas externas desde un procedimiento.

Primero tienes que crear la tabla y luego utilizarla como si fuera una tabla más de la base de datos.
Además, veo que faltan algunas comas...

El script de creación sería algo así;
Recuerda inicializar V_DATO en cada iteración del bucle.
Si un select no encuentra registros, V_DATO no se toca y queda con el valor anterior y podría dar falsos positivos.

Código SQL [-]
set term ^;

CREATE TABLE ERR_COMBINACIONES EXTERNAL 'C:\ATENCION\ERR_COMBINACION.TXT' (
                           ERR_LEC_ORIGEN CHAR(10),
                           ERR_LEC_DESTINO CHAR(10),
                           ERR_RUTA CHAR(3),
                           ERR_FECHA_VISITA CHAR(10))
^
commit work^

CREATE PROCEDURE SP_VALIDAR_COMBINACION (
    I_MES INTEGER,
    I_ANIO INTEGER)
AS
DECLARE VARIABLE V_RUTA INTEGER;
DECLARE VARIABLE V_FECHA_VISITA DATE;
DECLARE VARIABLE V_CONSEC_MAX INTEGER;
DECLARE VARIABLE V_CONSEC INTEGER;
DECLARE VARIABLE V_LEC_SEDE NUMERIC(10,0);
DECLARE VARIABLE V_LECHERIA NUMERIC(10,0);
DECLARE VARIABLE V_DATO NUMERIC(1,0);
DECLARE VARIABLE V_LECHERIA2 NUMERIC(10,0);
begin
    /* La tabla ya existe
    CREATE TABLE ERR_COMBINACIONES EXTERNAL 'C:\ATENCION\ERR_COMBINACION.TXT' (
                               ERR_LEC_ORIGEN CHAR(10),
                               ERR_LEC_DESTINO CHAR(10)
                               ERR_RUTA CHAR(3)
                               ERR_FECHA_VISITA CHAR(10));
    */

    FOR SELECT DISTINCT(a.clave_ruta),
                        a.fecha_visita,
                   MAX( a.consec )
        FROM atencion_lecheria a
        WHERE a.clave_ruta in (SELECT DISTINCT(A.clave_ruta)
                               FROM atencion_lecheria A
                               WHERE A.mes_periodo=:i_MES
                                 AND A.anio_periodo=:i_ANIO)
          AND a.mes_periodo=:i_mes
          AND a.anio_periodo=:i_anio
        GROUP BY a.clave_ruta,
                 a.fecha_visita
        INTO :v_ruta, :v_fecha_visita, :v_consec_max DO
        BEGIN
             v_consec=1;
             While ( v_consec <= v_consec_max ) do
             begin
                  select b.clave_lec
                  from atencion_lecheria b
                  where b.fecha_visita=:v_fecha_visita
                    and b.clave_ruta=:v_ruta
                    and b.consec=:v_consec
                  into :v_lecheria;

                  /********************************/
                  /*INICIALIZO V_DATO EN CADA BUCLE*/
                  /********************************/
                  v_dato = NULL;

                  /*DISTANCIA DE LA SEDE A LA PRIMER LECHERIA*/
                  if (v_consec=1) then
                  begin
                      /*Obtener la lecheria sede de la ruta*/
                      select r.clave_lec_sede
                      from ruta r
                      where r.clave_ruta=:v_ruta
                      into :v_lec_sede;

                      /*Buscar si existe la combinacion de la SEDE a la primer lecheria de la visita*/
                      select 1
                      from lecheria_a_lecheria c
                      where c.clave_lec_origen=:v_lec_sede
                        and c.clave_lec_destino=:v_lecheria
                      into :v_dato;

                      if (v_dato is null) then
                      begin
                          select 1
                          from lecheria_a_lecheria c
                          where c.clave_lec_origen=:v_lecheria
                            and c.clave_lec_destino=:v_lec_sede
                          into :v_dato;
                          if (v_dato is null) then
                          begin
                              INSERT INTO ERR_COMBINACIONES (ERR_LEC_ORIGEN,
                                                             ERR_LEC_DESTINO,
                                                             ERR_RUTA,
                                                             ERR_FECHA_VISITA)
                                                     values (:v_lecheria,
                                                             :v_lec_sede,
                                                             :v_ruta,
                                                             :v_fecha_visita);
                          end
                      end
                      v_lecheria2=v_lecheria;
                  end Else
                  begin
                      /*Buscar si existe combinacion de la primer lecheria con la segunda*/
                      select 1
                      from lecheria_a_lecheria c
                      where c.clave_lec_origen=:v_lecheria2
                        and c.clave_lec_destino=:v_lecheria
                      into :v_dato;

                      if (v_dato is null) then
                      begin
                          select 1
                          from lecheria_a_lecheria c
                          where c.clave_lec_origen=:v_lecheria
                            and c.clave_lec_destino=:v_lecheria2
                          into :v_dato;
                          if (v_dato is null) then
                          begin
                              INSERT INTO ERR_COMBINACIONES (ERR_LEC_ORIGEN,
                                                             ERR_LEC_DESTINO,
                                                             ERR_RUTA,
                                                             ERR_FECHA_VISITA)
                                                     values (:v_lecheria,
                                                             :v_lec_sede,
                                                             :v_ruta,
                                                             :v_fecha_visita);
                          end
                      end
                      v_lecheria2=:v_lecheria;
                  end
                  v_consec=:v_consec+1;
             end
        END
end
^
commit work^

Muchas gracias Duilio. Anoche estaba analizando la inicialización de la variable V_DATO....porque no insertaba registros y si hay!, pero ahora con tu sugerencia del IF NOT EXIST, mejor lo usaré. Al final mejor cree la TABLA como otra mas y la estaré llenando, para luego exportar los datos ahi registrados.


Gracias a todos por su tiempo y aportes.


Muchas gracias.

[mRoman]

Empezado por duilioisola

Para evitar errores con la inicialización de variables como V_DATO, yo prefiero utilizar IF EXISTS(...).

• Te ahorras la variable
• Te ahorras tener que inicializarla
• Te ahorras tener que pensar si tiene valores basura de otros usos.
• Es una instrucción optimizada que corta el SELECT en cuanto encuentra algo y no tiene que ir creando la respuesta con los registros encontrados.

Mira si te deja compilar así:

Código SQL [-]
                      /*Buscar si existe la combinacion de la SEDE a la primer lecheria de la visita*/
                      if (not exists(select *
                                     from lecheria_a_lecheria c
                                     where c.clave_lec_origen=:v_lec_sede
                                       and c.clave_lec_destino=:v_lecheria)) then
                      begin
                          if (not exists(select *
                                         from lecheria_a_lecheria c
                                         where c.clave_lec_origen=:v_lecheria
                                           and c.clave_lec_destino=:v_lec_sede)) then
                          begin
                              INSERT INTO ERR_COMBINACIONES (ERR_LEC_ORIGEN,
                                                             ERR_LEC_DESTINO,
                                                             ERR_RUTA,
                                                             ERR_FECHA_VISITA)
                                                     values (:v_lecheria,
                                                             :v_lec_sede,
                                                             :v_ruta,
                                                             :v_fecha_visita);
                          end
                      end

Listo, funcionó correcto e insertó registros....ahora a optimizar mas el SP. Gracias nuevamente.

[cloayza]

Estimado mRoman, otra opción es que su procedimiento almacenada le retorne las líneas que debe contener su archivo.

Luego podrá guardar usted estas en un archivo CSV... Es solo una sugerencia mas...

Código SQL [-]
CREATE PROCEDURE SP_VALIDAR_COMBINACION (
    I_MES INTEGER,
    I_ANIO INTEGER)
    returns(line varchar(1000))
AS
...
begin
    /* La tabla ya existe
    CREATE TABLE ERR_COMBINACIONES EXTERNAL 'C:\ATENCION\ERR_COMBINACION.TXT' (
                               ERR_LEC_ORIGEN CHAR(10),
                               ERR_LEC_DESTINO CHAR(10)
                               ERR_RUTA CHAR(3)
                               ERR_FECHA_VISITA CHAR(10));
    */
    --Campos del archivo CSV...
    line='ERR_LEC_ORIGEN;ERR_LEC_DESTINO;ERR_RUTA;ERR_FECHA_VISITA';
    suspend;
    ...
    ...

    --Yo reemplazaría los INSERT por
    line=ERR_LEC_ORIGEN||';'||
         ERR_LEC_DESTINO||';'||
         ERR_RUTA||';'||
         ERR_FECHA_VISITA;

    suspend
    ...
    ...
 end

Saludos cordiales