Esto se traduce en SQL como: SELECT, CURSOR, INSERT, UPDATE y DELETE.
En este artículo vamos a "construir" cuatro de ellos; dejaremos la rutina de lista para explicar en un artículo aparte.
Este tipo de rutinas serán llamadas desde otros programas, ya sean BATCH u ONLINE. Tendrán definidas en la LINKAGE SECTION los campos a través de los cuales intercambiarán información con el programa llamante. Estos campos pueden venir definidos dentro de COPYs.
Consulta: SELECT.
Se trata del caso más sencillo en el que sólo queremos recuperar 1 registro de la tabla.
Vamos a ver un ejemplo:
LINKAGE SECTION
01 ENTRADA.
05 CLAVE-SELECT PIC X(10).
01 SALIDA.
05 SAL-CAMPO1 PIC X(10).
05 SAL-CAMPO2 PIC X.
05 SAL-PRI-CAMPO3 PIC XX.
05 SAL-SEC-CAMPO3 PIC XX.
(...)
Los campos de entrada de la linkage contendrán la información enviada por el programa llamante. En este caso la clave por la que queremos acceder a nuestra SELECT.
En el inicio del programa podemos poner una validación del campo clave, para comprobar que trae datos, y en caso de no ser así finalizar la ejecución.
INICIO
IF CLAVE-SELECT EQUAL SPACES OR LOW-VALUES
DISPLAY 'LA CLAVE VIENE VACIA'
PERFORM 90000-FINAL
END-IF
Realizaremos la SELECT:
EXEC SQL
SELECT CAMPO1,
CAMPO2,
PRI_CAMPO3,
SEC_CAMPO3
INTO :TABLA1-CAMPO1,
:TABLA1-CAMPO2,
:TABLA1-PRI-CAMPO3,
:TABLA1-SEC-CAMPO3
FROM TABLA1
WHERE CAMPO1 = :CLAVE-SELECT
ORDER BY CAMPO1
END-EXEC
Los campos que van en el apartado de SELECT son los campos de la tabla. Se escribirán del mismo modo en el que estén en el CREATE de la tabla. Se utilizará siempre el guión bajo.
Ocurre lo mismo en el apartado de ORDER BY.
Los campos que van en el apartado de INTO son variables HOST definidas en la WORKING del programa. Podemos nombrarlos como queramos. Siempre irán precedidos por los dos puntos ":". Se utilizará siempre el guión alto.
Ocurre lo mismo para los campos utilizados en el apartado de WHERE.
En caso de que tengamos campos numéricos, siempre irán definidos como signados y comprimidos (S9(X) COMP-3).
La variable HOST utilizada en el WHERE no deberá tener niveles inferiores. En caso de que así sea, deberemos utilizar un REDEFINES o moverla a otra variable:
01 CLAVE-SELECT.
05 PARTE1 PIC X(5).
05 PARTE2 PIC X(5).
01 CLAVE-ENTERA REDEFINES CLAVE-SELECT PIC X(10).
O bien:
01 CLAVE-SELECT.
05 PARTE1 PIC X(5).
05 PARTE2 PIC X(5).
01 CLAVE-ENTERA PIC X(10).
(...)
MOVE CLAVE-SELECT TO CLAVE-ENTERA
Puede darse el caso de que el campo del WHERE no sea una clave única de la tabla, y que la SELECT nos recupere más de un registro.
Si sólo queremos saber que existe "alguno" que cumple la condición, podemos solventar este problema añadiendo a la SELECT la sentencia FETCH FIRST ROW ONLY:
EXEC SQL
SELECT CAMPO1,
CAMPO2,
PRI_CAMPO3,
SEC_CAMPO3
INTO :TABLA1-CAMPO1,
:TABLA1-CAMPO2,
:TABLA1-PRI-CAMPO3,
:TABLA1-SEC-CAMPO3
FROM TABLA1
WHERE CAMPO1 = :CLAVE-SELECT
ORDER BY CAMPO1
FETCH FIRST ROW ONLY
END-EXEC
De este modo aunque recupere más de un registro no dará error, y nos devolverá el primero que encuentre.
Si no lo hiciésemos la consulta devolvería un SQLCODE = -811.
Una vez codificada la SELECT validaremos el código SQL que nos devuelve para comprobar que todo ha ido bien:
EVALUATE TRUE
WHEN SQLCODE EQUAL ZEROES
DISPLAY 'TODO VA BIEN'
WHEN SQLCODE EQUAL +100
DISPLAY 'NO ENCONTRE NADA'
WHEN OTHER
DISPLAY 'ALGO HA IDO MAL. EL SQLCODE ES: 'SQLCODE
PERFORM 90000-FINAL
END-EVALUATE
Si la SELECT nos devuelve un SQLCODE distinto de cero y de 100, lo normal es finalizar la ejecución del programa.
Una vez comprobado que todo ha ido bien, informaremos los campos de SALIDA con la información recuperada de la SELECT:
MOVE TABLA1-CAMPO1 TO SAL-CAMPO1
MOVE TABLA1-CAMPO2 TO SAL-CAMPO2
MOVE TABLA1-PRI-CAMPO3 TO SAL-PRI-CAMPO3
MOVE TABLA1-SEC-CAMPO3 TO SAL-SEC-CAMPO3
En el párrafo de final devolveremos el control al programa llamante:
FINAL
GOBACK.
Y se terminó nuestra rutina de consulta : )
Alta: INSERT.
Cuando queremos dar de alta/insertar un registro en una tabla, tendremos que informar un mínimo de campos de la tabla(se deben informar al menos aquellos que pertenezcan a la clave única).
Estos campos vendrán informados desde el programa llamante y los recibiremos en la linkage.
Veamos un ejemplo:
LINKAGE SECTION.
01 ENTRADA.
05 ENT-CAMPO1 PIC X(10).
05 ENT-CAMPO2 PIC X.
05 ENT-PRI-CAMPO3 PIC XX.
05 ENT-SEC-CAMPO3 PIC XX.
En el inicio del programa podemos poner una validación de los campos obligatorios, para comprobar que vienen informados, y en caso de no ser así finalizar la ejecución.
Informaremos las variables HOST con las que realizaremos el INSERT:
MOVE ENT-CAMPO1 TO TABLA1-CAMPO1
MOVE ENT-CAMPO2 TO TABLA1-CAMPO2
MOVE ENT-PRI-CAMPO3 TO TABLA1-PRI-CAMPO3
MOVE ENT-SEC-CAMPO3 TO TABLA1-SEC-CAMPO3
Realizamos el INSERT:
EXEC SQL
INSERT INTO TABLA1
(CAMPO1,
CAMPO2,
PRI_CAMPO3,
SEC_CAMPO3)
VALUES(:TABLA1-CAMPO1,
:TABLA1-CAMPO2,
:TABLA1-PRI-CAMPO3,
:TABLA1-SEC-CAMPO3)
END-EXEC
Es importante colocar los campos definidos en VALUES en el orden correcto.
Ahora validaremos el SQLCODE devuelto para comprobar que todo ha ido bien:
EVALUATE TRUE
WHEN SQLCODE EQUAL ZEROES
DISPLAY 'TODO HA IDO BIEN'
WHEN SQLCODE EQUAL -803
DISPLAY 'EL REGISTRO YA EXISTE EN LA TABLA'
WHEN OTHER
DISPLAY 'LA HEMOS LIAO PARDA. EL SQLCODE ES: 'SQLCODE
PERFORM 90000-FINAL
END-EVALUATE
Una vez comprobado que todo ha ido bien, devolveremos el control al programa llamante:
FINAL
GOBACK.
Modificación: UPDATE.
Puede ocurrir que queramos modificar un solo campo o todos. Si no sabemos qué campos van a venir modificados lo normal es actualizarlos todos. Para ello debemos haber recuperado previamente toda la información del registro, o podríamos estar actualizando algunos campos con ceros o espacios.
En una rutina de modificación ONLINE se debe controlar siempre el TIMESTAMP de modificación del registro, para asegurarnos que no ha sido modificado mientras nosotros lo estábamos visualizando.
Veamos el ejemplo:
LINKAGE SECTION.
01 ENTRADA.
05 ENT-CAMPO1 PIC X.
05 ENT-CAMPO2 PIC X.
05 ENT-CAMPO3 PIC XX.
05 ENT-CAMPO4 PIC XX.
05 ENT-TIMESTAMP PIC X(26).
La información de la linkage la habrá obtenido el programa llamante realizando una consulta del registro y con la información de los campos modificados.
Antes de realizar el UPDATE realizaremos una nueva consulta del registro:
EXEC SQL
SELECT CAMPO1,
TIMESTAMP
INTO :TABLA2-CAMPO1,
:TABLA2-TIMESTAMP
FROM TABLA2
WHERE CAMPO1 = :ENT-CAMPO1
ORDER BY CAMPO1
END-EXEC
Validaremos el SQLCODE devuelto.
Si todo ha ido bien:
Compararemos el TIMESTAMP obtenido en TABLA2-TIMESTAMP con el recibido por linkage.
Recordad que un campo timestamp tiene este aspecto: 'AAAA-MM-DD-HH.MM.SS.MMMMMM'
Donde:
AAAA: año
MM: mes
DD: día
HH: hora
MM: minutos
SS: segundos
MMMMMM: milésimas de segundo
2010-12-01-22.15.01.012345
En total 26 posiciones.
IF ENT-TIMESTAMP NOT EQUAL TABLA2-TIMESTAMP
DISPLAY 'ERROR: REGISTRO MODIFICADO POR OTRO USUARIO'
PERFORM 90000-FINAL
END-IF
Esto significa que mientras nosotros visualizábamos el registro, otra persona lo modificó.
En caso de que los TIMESTAMP coincidan, informaremos las variables HOST del update con la información recibida por linkage.
MOVE ENT-CAMPO1 TO TABLA2-CAMPO1
MOVE ENT-CAMPO2 TO TABLA2-CAMPO2
MOVE ENT-CAMPO3 TO TABLA2-CAMPO3
MOVE ENT-CAMPO4 TO TABLA2-CAMPO4
Procederemos a hacer el UPDATE:
EXEC SQL
UPDATE TABLA2
SET (CAMPO2 = :TABLA2-CAMPO2,
CAMPO3 = :TABLA2-CAMPO3,
CAMPO4 = :TABLA2-CAMPO4,
TIMESTAMP = CURRENT TIMESTAMP)
WHERE CAMPO1 = :TABLA2-CAMPO1
END-EXEC
Tened en cuenta que no se deben modificar los campos de la tabla que pertenezcan a la clave única.
Validaremos el SQLCODE devuelto.
EVALUATE TRUE
WHEN SQLCODE EQUAL ZEROES
DISPLAY 'TODO HA IDO BIEN'
WHEN SQLCODE EQUAL +100
DISPLAY 'EL REGISTRO QUE SE QUIERE MODIFICAR NO EXISTE'
WHEN OTHER
DISPLAY 'YA LA HEMOS VUELTO A LIAR. EL SQLCODE ES: 'SQLCODE
PERFORM 90000-FINAL
END-EVALUATE
Y devolveremos el control al programa llamante.
Puede darse el caso de que queramos modificar algún campo de la clave del registro. Normalmente para que pueda hacerse debe existir un campo clave o ISN a mayores que será numérico y autoincrementable y que identificará inequívocamente cada registro de la tabla.
Por ejemplo:
Donde CAMPO1 será el índice único y CLAVE la clave autoincrementable.
Ahora podríamos modificar el campo CAMPO1 accediendo a la tabla por el campo CLAVE.
Por ejemplo, si queremos modificar el campo CAMPO1 del registro con CLAVE = 14:
MOVE '5' TO CAMPO1
MOVE 'X' TO CAMPO2
MOVE 'YY' TO CAMPO3
MOVE 'ZZ' TO CAMPO4
EXEC SQL
UPDATE TABLA2
SET (CAMPO1 = :TABLA2-CAMPO1,
CAMPO2 = :TABLA2-CAMPO2,
CAMPO3 = :TABLA2-CAMPO3,
CAMPO4 = :TABLA2-CAMPO4,
TIMESTAMP = CURRENT TIMESTAMP)
WHERE CLAVE = :TABLA2-CLAVE
END-EXEC
y el registro con CLAVE =14 quedaría:
Baja: DELETE.
Este tipo de baja también se denomina "baja física", en contraste con la "baja lógica", donde el registro no se borra, sino que se actualiza un campo "estado" indicando que el registro en cuestión "está de baja" ^^.
Para realizar un DELETE sólo necesitaremos el valor del campo clave del registro que queremos eliminar.
LINKAGE SECTION
01 ENTRADA.
05 CAMPO-CLAVE PIC X.
(...)
EXEC SQL
DELETE FROM TABLA3
WHERE CAMPO1 = :CAMPO-CLAVE
END-EXEC
Validaremos el SQLCODE devuelto y devolveremos el control al programa llamante.
Os dejo los enlaces a los ejemplos completos de SELECT, INSERT, UPDATE y DELETE.
15 comentarios:
Pregunta:¿Qué query hay que hacer para que devuelva el valor 2 de la C3 correspondiente al registro con clave A de la tabla 1 para aquellos B de la tabla 2 que cumplan C2 = S , teniendo en cuenta que puedo relacionar las dos tablas por el indice C1 y no queremos duplicados?
Tabla1 con indice C1
C1 B A . . .
C2 A K . . .
C3 1 2 . . .
Tabla2 con indice C1,C3
C1 B B . . .
C2 S S . . .
C3 1 2 . . .
Buenos días Anónimo,
una respuesta podría ser:
EXEC SQL
SELECT C3
INTO :CAMPO-C3
FROM TABLA1
WHERE C1 = 'A'
END-EXEC
Y otra:
EXEC SQL
SELECT DISTINCT(2)
INTO :CAMPO-C3
FROM TABLA1
END-EXEC
:D
jajaja
Ahora te contestan los listos^^
Saludos,
La verdad, he pasado la noche dándole vueltas a tu pregunta. Me resultó desconcertante. Quizás por recordarme a mis pasados años universitarios y a los ejercicios abstractos con tablas1 y columnas C1.
Es por ello, que para poder contestar a tu pregunta correctamente decidí trasladar tus tablas al mundo real y ponerle nombres y columnas manejables :)... Veamos:
TABLA1 = Tabla de facturas (con clave C1=Código de factura)
C1 = Código de factura
C2 = Número de cliente
C3 = Importe
TABLA2 - Tabla de lineas de factura (con clave C1=Código de factura y C3=Línea de factura)
C1 = Código de factura
C2 = Código de producto
C3 = Línea de factura
Por lo tanto, la pregunta que planteas sería la siguiente:
¿Qué query hay que hacer para se devuelva el importe = 2 correspondiente a la factura A para aquellas líneas de factura B que cumplan que el código de producto es S y sin duplicados?
Lo cual, ciertamente me confunde por el hecho de querer establecer una relación entre las facturas A y las líneas de factura B...
Si es un problema real, quizás sería mejor que lo expusieses tal cual, sin renombrar las tablas como tabla1, tabla2... Si es un problema de algún libro de ejercicios, la respuesta podría ser tan evidente como la que ha puesto Tallian, o tan compleja como la que está planteando Pepegan...
Hola
Por lo que hemos llegado a entender estas buscando como obtener el regsitro de la tabla1 donde tabla1.c3 = 2 uniendo las tablas tabla2 y tabla1 con la condicion tabla2.c2 = 'S'
Las tablas estarian definidas de la siguiente forma
Tabla 1
Columnas
C1
C2
C3
Indices
C1
Datos de la tabla
C1 C2 C3
------------
B A 1
A K 2
Tabla 2
Columnas
C1
C2
C3
Indices
C1 + C3
Datos de la tabla
C1 C2 C3
------------
B S 1
B S 2
Haciendo la consulta
SELECT DISTINCT a.*
FROM Tabla1 a
,Tabla2 b
where a.C1 = b.C1
and b.c2 = 'S'
Obtenemos los siguientes datos
C1 C2 C3
------------
B A 1
De donde vemos que poniendo la condicion tabla2.c2 = 'S' obtenemos el registro tabla1.c3 = 1, pero no es lo que queremos
A este punto solo podemos seguir de la siguiente forma, sacar todos aquellos registros que no sea 1, que en este caso al tener solo dos registros la tabla1 obtendremos tabla1.c3 = 2 c.q.d.
Nuestra consulta tendra las siguiente forma:
SELECT A.C3
FROM TABLA1 A
LEFT OUTER JOIN (
SELECT DISTINCT A.*
FROM TABLA1 A
,TABLA2 B
WHERE A.C1 = B.C1
AND B.C2 = 'S') B
ON A.C1 = B.C1
WHERE B.C1 IS NULL
Este tema de consulta especiales, inner join, left outer join, union, .. estamos preparando una serie de articulos, que intentaremos publicar en breve.
Espero que esto te haya servido de ayuda .. si necesitas algo mas no dudes en preguntarnoslo.
Tambien nos gustaria que te añadieras como seguidor, para nosotros es como una recompensa de que todo este trabajo que estamos haciendo es util a la comunidad y nos da animos a seguir trabajando.
Gracias por las respuestas, no esperaba tanta celeridad. Ya os diré si funciona para mi propósito. En mi caso se hace necesario realizar una query de este tipo para descargar datos de forma masiva para realizar buenos test de prueba.En el curro no tengo tiempo de andar mirando manuales de SQL y menos empezar a hacer pruebas a fin si funciona esto o aquello ... y en casa como que no me apetece indagar mucho (a pesar de haber entrado en este bloq).
Por otro lado animaros a continuar con la labor que hacéis que es encomiable ya que es un "coñazo" tener que leer los manuales en inglés.
La próxima vez que comente algo lo haré con el nombte de chuckfedor.
Hola, veo que el post tiene algun tiempo pero es lo que mejor se aproxima a mi duda,, espero no revivir un tema ya muerto.
La duda es la siguiente: en un archivo de texto tengo un conjunto de queries, uno por cada linea. La idea es ir leyendo linea por linea el archivo de entrada y pasar el query leido por el exec sql para su ejecucion y finalmente validar el exito o error.
El problema de esto es que no he sido capaz de pasar unicamente la variable que contiene el query, ya que el precompilador me devuelve un error al no encontrar palabras reservadas de SQL dentro del bloque exec sql.
¿saben de alguna manera para hacer lo que necesito?
Desde ya muchas gracias.
Hola
Nunca es tarde si la dicha es buena.
Sin embargo leyendo tu comentario nos surgen una serie de dudas. Por lo que he leido, tienes un conjunto de consultas una por linea. ¿Seria algo como ... ?
SELECT CAMPO1 FORM TABLA1;
SELECT CAMPO2 FROM TABLA2;
Nos podrias proporcionar un ejemplo del fichero que tiene en mano.
Por otro laso comentas que quieres pasar la consulta por el exec sql. Y despues nos comentas sobre un error en el precompilador.
En esta punto nos surgen mas duda, ¿quieres pasar cada una de la sentencias anterior, bajo un programa COBOL con condigo EXEC SQL?
EXEC SQL
SELECT CAMPO1 FROM ....
END-EXEC.
¿Es correcto, lo que hemos entendido? Si nos proporcionas un poco mas de información te intentaremos solucionar el problema.
Hola. Muchas gracias por responder.
En efecto, en un fichero tengo una serie de consultas (insert´s y update´s en realidad), una por linea, concretamente el fichero se ve de esta forma:
INSERT INTO TABLA VALUES (...)
INSERT INTO TABLA2 VALUES (...)
UPDATE TABLA SET CAMPO2 = '' WHERE campo1='a'
.
.
.
Necesito ir leyendo este fichero linea por linea en un pequeño programa batch y ejecutar la sentencia que encuentre, posteriormente validar si la respuesta es un ok o algun error y escribirla en un archivo de salida sin hacer mas con ella.
Para ejecutar la sentencia estoy tratando de usar un bloque exec sql con sentencias "prepare, execute" o "execute immediate", algo del tipo:
exec sql execute immediate :var-host end-exec.
la variable host ya fue previamente preparada para este fin, sin embargo desde el momento de la compilacion recibo mensajes como este: "syntax error at execute, missing {end_exec select include... etc" (mensajes que yo conozco como errores del precompilador de SQL mas que del compilador cobol, tal vez esta forma de nombrarlos le haya causado algun conflicto puesto que no se si sean correctas) por lo que ya no sé si es un error de mi parte o si es que la instalación que tengo no soporta este tipo de sentencias.
Espero no haberme enrollado mucho y haberme explicado claramente.
De antemano muchas gracias.
Hola
El problema creo que es el compilador COBOL, mas bien el precompilador DB2 no admite SQL dinámicas. Sin embargo si quieres hacer un proceso para la carga de datos en tablas a través de una serie de consulta como las que describes, te aconsejo que crees un procedimiento hecho en REXX, y ejecutado bajo un JCL.
El REXX es un lenguaje que admite cualquier mainframe, y que si puede hacer lo que estas pidiendo.
En un articulo de este consultorio tenemos hecho uno que ejecuta una SELECT creada dinámicamente a través de un entrada de datos. Si haces una adaptación del mismo podrás crear tu procedimiento batch sin ningún problema
Espera que esto te sirve de ayuda a lo que estas intentando hacer.
Buenas.
He colgado los ejemplos completos. Tenéis los enlaces al final del artículo.
Un saludo.
Creo que...
MOVE '5' TO CAMPO1
MOVE 'X' TO CAMPO2
MOVE 'YY' TO CAMPO3
MOVE 'ZZ' TO CAMPO4
debe ser
MOVE '5' TO TABLA2-CAMPO1
MOVE 'X' TO TABLA2-CAMPO2
MOVE 'YY' TO TABLA2-CAMPO3
MOVE 'ZZ' TO TABLA2-CAMPO4
Previo al UPDATE.
hola buenas tardes a todos,
necesito de su ayuda para poder migrar una aplicacion en cobol, que es en si una migración del proceso BAtch que se encarga de hacer las operaciones y movimientos contables, tengo el programa principal, el cual llama a otros modulos que en si son como los procedimientos que llenan unas tablas, si me ayudan con esto seria fantastico, de antemano gracias
Buen día, me puedes indicar donde consultar un ejemplo de como conectar una base de datos?, gracias
Este comentario ha sido eliminado por el autor.
Buenas,
como debería hacer si necesito realizar un update de un registro modificando una fecha, y luego insertar ese mismo registro con otra fecha distinta. La idea es mantener un historico. Si hago el update, le doy commit y luego quiero hacer el insert me arroja error de duplicado.
Gracias