IIUG Español

Problemas con Stored Procedures

Posted By: DAVID AYALA
Date: Wednesday, 18 June 2003, at 5:34 p.m.

Saludos,

Estoy trabajando con la version SE 7.23.UC1, y estoy tratando de stored procedure llamado upper (creo que la intencion es obvia), con el siguiente codigo:

DROP PROCEDURE "informix".upper;
CREATE PROCEDURE "informix".upper(low_name CHAR(255))
RETURNING CHAR(255);

DEFINE loop INTEGER;
DEFINE len INTEGER;
DEFINE retstr CHAR(255);

LET len = LENGTH(low_name);
LET retstr = NULL;

FOR loop = 1 to len
IF low_name[1,1] BETWEEN "a" AND "z" THEN
IF low_name[1,1] = 'a' THEN
LET retstr = retstr || 'A';
ELIF low_name[1,1] = 'b' THEN
LET retstr = retstr || 'B';
ELIF low_name[1,1] = 'c' THEN
LET retstr = retstr || 'C';
ELIF low_name[1,1] = 'd' THEN
LET retstr = retstr || 'D';
ELIF low_name[1,1] = 'e' THEN
LET retstr = retstr || 'E';
ELIF low_name[1,1] = 'f' THEN
LET retstr = retstr || 'F';
ELIF low_name[1,1] = 'g' THEN
LET retstr = retstr || 'G';
ELIF low_name[1,1] = 'h' THEN
LET retstr = retstr || 'H';
ELIF low_name[1,1] = 'i' THEN
LET retstr = retstr || 'I';
ELIF low_name[1,1] = 'j' THEN
LET retstr = retstr || 'J';
ELIF low_name[1,1] = 'k' THEN
LET retstr = retstr || 'K';
ELIF low_name[1,1] = 'l' THEN
LET retstr = retstr || 'L';
ELIF low_name[1,1] = 'm' THEN
LET retstr = retstr || 'M';
ELIF low_name[1,1] = 'n' THEN
LET retstr = retstr || 'N';
ELIF low_name[1,1] = 'o' THEN
LET retstr = retstr || 'O';
ELIF low_name[1,1] = 'p' THEN
LET retstr = retstr || 'P';
ELIF low_name[1,1] = 'q' THEN
LET retstr = retstr || 'Q';
ELIF low_name[1,1] = 'r' THEN
LET retstr = retstr || 'R';
ELIF low_name[1,1] = 's' THEN
LET retstr = retstr || 'S';
ELIF low_name[1,1] = 't' THEN
LET retstr = retstr || 'T';
ELIF low_name[1,1] = 'u' THEN
LET retstr = retstr || 'U';
ELIF low_name[1,1] = 'v' THEN
LET retstr = retstr || 'V';
ELIF low_name[1,1] = 'w' THEN
LET retstr = retstr || 'W';
ELIF low_name[1,1] = 'x' THEN
LET retstr = retstr || 'X';
ELIF low_name[1,1] = 'y' THEN
LET retstr = retstr || 'Y';
ELIF low_name[1,1] = 'z' THEN
LET retstr = retstr || 'Z';
END IF
ELSE
LET retstr = retstr || low_name[1,1];
END IF

LET low_name = low_name[2,255];
END FOR
RETURN retstr;
END PROCEDURE;

GRANT EXECUTE ON "informix".upper TO "public" AS "informix";

DROP VIEW "informix".dual;
CREATE VIEW "informix".dual (tabname) AS
SELECT x0.tabname FROM "informix".systables x0 WHERE (x0.tabid = 1 );

--DROP VIEW "informix".sequence;
--CREATE VIEW "informix".sequence (tabname,currval) AS
-- SELECT x1.tabname ,x0.ti_serialv FROM sysmaster:"informix".systabinfo
-- x0 ,sysmaster:"informix".systabnames x1 WHERE ((x0.ti_partnum = x1.partnum
-- ) AND (x1.dbsname = 'superm' ) ) ;

La parte comentada no funciona, no existen las tablas que utiliza. Al ejecutarlo tal como se muestra aqui, pese a que si crea el procedimiento, establece los permisos y crea las vistas, al ejecutar algo como esto:

SELECT upper(usuario)
FROM usuarios
WHERE usuario = "miusuario"

El query devuelve esto:

(expression)



Y nada mas. Alguien tiene idea de porque no puedo utilizar los procedmientos que he creado?

Gracias,

David Ayala.

IIUG Español is maintained by Administrator with WebBBS 5.12.