Usar variables en un strored procedure

[Angel.Matilla]

Tengo un procedimiento en una BB.DD. en FB 2.5 definido así:

Código SQL [-]
CREATE PROCEDURE GEN_AFILIACION
RETURNS(
  CODIGO INTEGER)
AS
BEGIN
  Codigo = GEN_ID(ID_Afiliacion, 1);
END

El problema es que me acabo de dar cuenta que en la tabla sobre la que actúa la clave primaria tiene dos campos:

Código SQL [-]
CREATE TABLE Afiliacion (CodPrv SMALLINT NOT NULL, Codigo SMALLINT NOT NULL,
Nombre VARCHAR(30) NOT NULL, Ant_Codigo SMALLINT,
CONSTRAINT PK_Afiliacion PRIMARY KEY (CodPrv, Codigo))

De los dos campos de la clave, el primero de ellos (CodPrv) es fijo y va asociado a la instalación (para los que seáis de España el valor es: 13 Ciudad Real, 28 Madrid, 45 Toledo, etc.); está pensado para si en un futuro se integran las bases de datos que hay en diversas provincias en una única base de datos central. Lo que no se me ocurre es como asignar ese valor fijo CodPrv, que se guarda en una variable desde el momento que se lanza la aplicación, a la hora de ejecutar ese procedimiento. Me he vuelto loco buscando documentación.

[Casimiro Notevi]

Pásale el parámetro que necesites:

Código SQL [-]
CREATE PROCEDURE name 
 ( input_parameter_name < datatype>, ... ) 
RETURNS 
 ( output_parameter_name < datatype>, ... )
AS 
DECLARE VARIABLE variable_name < datatype>; 
BEGIN
  /* write your code here */ 
END^

Código SQL [-]
CREATE PROCEDURE GEN_AFILIACION ( iProvincia INTEGER )
RETURNS(
  CODIGO INTEGER)
AS
BEGIN
  Codigo = iProvincia || GEN_ID(ID_Afiliacion, 1);
END

[Angel.Matilla]

Garcias por la respuesta Casimiro. Un modelo de código similar lo había visto pero no entiendo como asignar el valor a la variable de entrada; es decir: ¿cómo le digo al procedimiento, que además vendrá lanzado desde un trigger, que por ejemplo iProvincia vale 45? En este tema de tirggers y procedires estoy muy verde y os juro que me pierdo hasta con mapa

[Angel.Matilla]

Me explico mejor. Entiendo que la llamada al procdure sería algo así:

Código PHP:

EXCUTE PROCEDURE GEN_AFILIACION(45); 


Ahora tengo definido un trigger de esta manera:

Código PHP:

CREATE TRIGGER Afiliacion_New_Reg FOR Afiliacion
ACTIVE BEFORE INSERT
  POSITION 0
AS
BEGIN
  IF (NEW.Codigo IS NULL) THEN 
  NEW.Codigo = GEN_ID(ID_Afiliacion, 1);
END; 


Y definiendo el procedure como me has indicado en tu código, ¿el trigger debería quedar así?

Código PHP:

CREATE TRIGGER Afiliacion_New_Reg FOR Afiliacion
ACTIVE BEFORE INSERT
  POSITION 0
AS
BEGIN
  IF (NEW.Codigo IS NULL) THEN 
  NEW.Codigo = GEN_AFILIACION(45);
END; 


Pero por lo que llevo leído en los triggers no pueden usarse variables y aquí es donde me pierdo del todo.

[Casimiro Notevi]

Creo que te estás liando, o seguramente no te estoy entendiendo
Tienes una tabla con 2 campos, el código de la provincia y un código incremental, supongo.
Tú das de alta un registro con esos 2 campos, tan sólo que el código incremental se genera automáticamente mediante un trigger en el before insert de la tabla.
No necesitas crear ningún stored procedure para eso.

El inconveniente que veo es que por ejemplo: provincia madrid 28 y código 1= 281. Sin embargo, la provincia Albacete es 2 y el código 81 formarían: 281 también.
Debes hacer un "cast" a ambos valores si no quieres tener problemas, bueno, bastaría con hacerle el cast a la provincia para que tenga siempre 2 caracteres,
Para ello, el campo provincia debe ser varchar(2) y rellenar con cero por la izquierda: 01, 02, etc.
Algo así: cast(new.provincia as varchar(2))

Tal vez te interese tener una clave ID autoincremental y un campo provincia+código indexado, de tipo varchar.

No sé he entendido bien.

[Angel.Matilla]

Gracias por la respuesta. Creo que no me he explicado bien y me has dado una pista para solucionarlo. Vamos por partes.

La tabla a la que me refiero en el ejemplo es esta:

Código SQL [-]
CREATE TABLE Afiliacion (CodPrv SMALLINT NOT NULL, Codigo SMALLINT NOT NULL, 
Nombre VARCHAR(30) NOT NULL, Ant_Codigo SMALLINT, 
CONSTRAINT PK_Afiliacion PRIMARY KEY (CodPrv, Codigo))

Como ves los dos primeros campos son los que están en la clave primaria. No se me había ocurrido ese posible problema que indicas con la clave; es evidente que poniendo el código provincial como VARCHAR(2) se elimina dicho problema.

El campo Codigo es (o debería serlo) autoincremental. Y es aquí donde me surgen las dudas: Si no estuviera el campo de la provincia no tendría problema; pero al estar ese campo en la calve primaria ¿cómo haría para crear dicho valor? Es decir, y poniendo el código provincial como VARCAHR (tienes razón: es mejor), para que fuera 011, 012, 013, etc.

Con un trigger, por lo que llevo leído y entendido, no se podría ya que en los mismos no se pueden usar variables y si se pueden usar no veo como llamar a una que está definida en el programa. Es decir: en un query podría hacer esto:

Código PHP:

Query->SQL->Text = "SELECT * FROM Afiliacion WHERE CodPrv = :Provincia";
Query->ParamByName("Provincia")->AsString = "45"; 


¿se puede hacer algo similar en un trigger?

[Casimiro Notevi]

La clave primaria puede estar compuesta por varios campos, no hay ningún problema en ello.
Tendrás que hacer la búsqueda por ambos campos:

Código SQL [-]
select * from tabla where provincia=? and codigo=?

La verdad es que todavía no entiendo qué problema tienes. No entiendo lo que preguntas del trigger.

[Angel.Matilla]

Tienes razón. Me acabo de dar cuenta que me estaba liando de mala manera en las preguntas y todo el lío que estoy montando es absurdo: Dado que las BB.DD. están separadas, cada una en su provincia y no están conectadas entre sí, me da lo mismo que el contador tenga en cuenta o no el código provincial.

[[fjcg02]]

Hola,
independientemente de que lo necesites o no, el tema que has propuesto es fácil de solucionar.

Igual que pones el id mediante un generador, puedes poner la provincia. En éste último caso, iría a piñón. Para que puedas utilizar el mismo código para todas las sucursales, sólo te haría falta una tabla de parámetros o similar para coger el valor.

Algo así

Código SQL [-]
CREATE TRIGGER Afiliacion_New_Reg FOR Afiliacion
ACTIVE BEFORE INSERT
  POSITION 0
AS
BEGIN
  IF (NEW.Codigo IS NULL) THEN 
  NEW.Codigo = GEN_ID(ID_Afiliacion, 1);
  IF (NEW.CodPrv  IS NULL) THEN 
     ** coger el código de provincia de otra tabla y darle valor **
   NEW.CodPrv = VALOR CALCULADO;

END;

yo te sugiero una tabla de parámetros. En cada provincia pones un valor diferente, y el código del trigger es para todas igual.

Un saludo

[Angel.Matilla]

Empezado por fjcg02

Hola,
independientemente de que lo necesites o no, el tema que has propuesto es fácil de solucionar.

Igual que pones el id mediante un generador, puedes poner la provincia. En éste último caso, iría a piñón. Para que puedas utilizar el mismo código para todas las sucursales, sólo te haría falta una tabla de parámetros o similar para coger el valor.

Algo así

Código SQL [-]CREATE TRIGGER Afiliacion_New_Reg FOR Afiliacion ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.Codigo IS NULL) THEN NEW.Codigo = GEN_ID(ID_Afiliacion, 1); IF (NEW.CodPrv IS NULL) THEN ** coger el código de provincia de otra tabla y darle valor ** NEW.CodPrv = VALOR CALCULADO; END;


yo te sugiero una tabla de parámetros. En cada provincia pones un valor diferente, y el código del trigger es para todas igual.

Un saludo

Gracias por la rspuesta. Lo cierto es que esa tabla existe pero no se me había ocurrido utilizarla para esto. Sin embargo, sigo con la misma duda: ¿Cómo le indico para qué provincia estoy calculando el código? Al arrancar la aplicación una de las primeras cosas que se hace es guardar en una variable ese valor pero sigo sin entender como pasárselo al trigger ya que por lo que he leído estos elementos no aceptan variables de entrada.

[Casimiro Notevi]

El problema es que desconoces cómo funcionan los triggers, por eso te estás liando. Lo primero es entender el funcionamiento de los mismos, revisa el capítulo 23 de La cara oculta de Delphi, habla de "Stored procedure y triggers". Concretamente el apartado " Las variables new. y old. ".
Es sencillo, pero hay que tener los conceptos claros.

[[fjcg02]]

De memoria... con errores sintácticos y demás...

Código SQL [-]
CREATE TRIGGER Afiliacion_New_Reg FOR Afiliacion
ACTIVE BEFORE INSERT
  POSITION 0
AS
DECLARE WCODPRV AS INTEGER;
BEGIN
  IF (NEW.Codigo IS NULL) THEN 
  NEW.Codigo = GEN_ID(ID_Afiliacion, 1);
  IF (NEW.CodPrv  IS NULL) THEN 
  BEGIN
     SELECT FIRST CODPRV FROM TABLAPARAMETROS 
     WHERE PARAMETRO='PROVINCIA' 
     INTO :WCODPRV
     NEW.CodPrv = WCODPRV;
  END
END;

Ahora, te hace falta leer mucho, tal y como te ha remoendado Casimiro.

Saludos

[Angel.Matilla]

Gracias a los dos. Partiendo de la base de que efectivamente estoy aprendiendo a usar triggers y strored procedures; leí el capítulo que me indicáis de la Cara oculta de Builder y mucha información que he encontrado, especialmente en Teoría y Práctica sobre Firebird. Pero en todo lo que he leído no me aclara la duda que tengo.

A ver si lo he entendido bien, y perdonad si soy un pesado: En el código que pone de ejemplo fjcg02 se busca un valor en la tabla de parámetgros (SELECT FIRST ...) pero es que no es el caso que yo planteo. Yo ya sé cual es el valor de ese campo (pongamos por ejemplo, ya que resido en Toledo, el 45); por lo tanto no tengo que buscar cuál es su valor, y esa es mi pregunta: ¿Cómo le digo al trigger o al procedimiento que el código provincial es 45?

El otro día Casimiro me sugería un código:

Empezado por Casimiro Notevi

Código PHP:

CREATE PROCEDURE GEN_AFILIACION ( iProvincia INTEGER )
RETURNS (CODIGO INTEGER) 
AS
BEGIN
   Codigo = iProvincia || GEN_ID(ID_Afiliacion, 1); 
END 


Pero sigo sin entender, y por más que he leído no lo entiendo, como decirle a ese procedimiento que iProvincia vale 45. Y reitero mis dusculpas por ser tan bruto.

[Casimiro Notevi]

Es que hay muchas formas de hacerlo y no sabemos cómo lo estás haciendo tú.
Supongamos que grabas el registro desde delphi/builder.

Código SQL [-]
insert into afiliacion (codproveedor, nombre) values (45,'Alguien')

El trigger before insert sabe qué código de proveedor estás grabando, el 45, no tienes que hacer nada.
Ummm... sigo sin entender el problema

[Angel.Matilla]

Empezado por Casimiro Notevi

El trigger before insert sabe qué código de proveedor estás grabando, el 45, no tienes que hacer nada.
Ummm... sigo sin entender el problema

Gracias. Ahora sí me has resuelto la duda.

[Casimiro Notevi]

Ten en cuenta que los triggers se "disparan" automáticamente. Una vez programados no hay que preocuparse por ellos, hacen el trabajo repetitivo y pesado, no se interactua con ellos desde delphi/builder, son independientes.
Por ejemplo, en el ejemplo anterior que he puesto (el insert), el trigger tiene acceso al registro que se está creando. Si necesitas hacer algo con el codprov, su valor está en new.codprov

Código SQL [-]
CREATE TRIGGER Afiliacion_New_Reg FOR Afiliacion
ACTIVE BEFORE INSERT
  POSITION 0
AS
  DECLARE WCODPRV AS INTEGER;
BEGIN
  IF (new.Codigo IS NULL) THEN new.Codigo = GEN_ID(ID_Afiliacion, 1);
  IF (new.CodPrv = 45) THEN 
  BEGIN
     /* hacer aquí lo que sea cuando sea un proveedor de la provincia 45 */
  END
END;

En los triggers Before/After update tienes disponible las variables new. y old.
Ejemplo, automáticamente vamos a mudar a los proveedores de Madrid hacia Toledo:
trigger before update ...

Código SQL [-]
if old.codigo=28 then new.codigo=45

Depende si es un trigger insert/update/delete tienes a disposición solamente new. / old. y new. / solamente old.

[[fjcg02]]

Aqui el tema es que si pones a piñón el valor de la provincia, tendrás que tener un trigger diferente para cada BBDD de cada provincia.

Si coges el codigo de una tabla de parámetros, el trigger siempre será igual, y sólo tendrás que poner una pantalla de configuración que posibilite cambiar el valor para cambiarlo. Incluso podrías tener una tabla de provincias para hacer la selección más fácil.

Creo que es más fácil hacerlo así de cara al futuro mantenimiento de la BBDD y/o aplicación que acordarse de que hay que cambiar el trigger y poner el código correspondiente. Creo que es un ejemplo de la típica decisión chunga que haces al principio de la que luego te arrepientes toda la vida... , generalmente después de cagarla alguna vez que otra.

Cada uno puede hacer lo que quiera, pero ya tengo una cana, y más sabe el zorro por viejo que por zorro.

Un saludo

[Casimiro Notevi]

Empezado por fjcg02

Aqui el tema es que...

¿Tu mensaje es para mí o para Ángel?
Si es para mí, obviamente lo que he puesto es solo un ejemplo para que Ángel entienda el funcionamiento de los triggers.
Jamás se me ocurriría escribir un código así en "la vida real"

[[fjcg02]]

Jajaja,
me has hecho reir Casimiro....

el mensaje no era para tí...

Bajo mi punto de vista es más fácil tener ciertos datos en tablas de configuración que desperdigados por el código.

Un saludo

[Angel.Matilla]

Empezado por fjcg02

Aqui el tema es que si pones a piñón el valor de la provincia, tendrás que tener un trigger diferente para cada BBDD de cada provincia.

Si coges el codigo de una tabla de parámetros, el trigger siempre será igual, y sólo tendrás que poner una pantalla de configuración que posibilite cambiar el valor para cambiarlo. Incluso podrías tener una tabla de provincias para hacer la selección más fácil.

Gracias a los dos por las respuestas, pero seguís sin contestarme a mi duda, y no os ofendáis; tal vez no os esté entendiendo.

Empezado por Casimiro Notevi

Es que hay muchas formas de hacerlo y no sabemos cómo lo estás haciendo tú.
Supongamos que grabas el registro desde delphi/builder.
Código SQL [-]insert into afiliacion (codproveedor, nombre) values (45,'Alguien')

El trigger before insert sabe qué código de proveedor estás grabando, el 45, no tienes que hacer nada.
Ummm... sigo sin entender el problema

La forma que me explicas en que operan los triggers la entendí perfectamente, y el ejemplo que me pones de un INSERT puro y duro es lo que hago yo ahora (olvidaros de los valores a la derecha del signo igual; en la aplicación se "cargan" bien).

Código PHP:

Query->Close();
Query->SQL->Text = "INSERT INTO Afiliacion (CodPrv, Codigo, Nombre) VALUES (:CodPrv, :Codigo, :Nombre)";
Query->ParamByName("CodPrv")->AsString  = ProvTit;
Query->ParamByName("Codigo")->AsInteger = Afiliacion->Tag;
Query->ParamByName("Nombre")->AsString  = Afiliacion->Text;
Query->ExevcSQL(); 


Lo que creo que no acabáis de "pillar" es que el valor ProvTit está guardado en una variable DESDE QUE ARRANCA LA APLICACIÓN; por lo tanto es un valor que no tengo que buscar, ya existe:

Código PHP:

Query->Close();
Query->SQL->Text = "SELECT Valor FROM Instalacion WHERE Etiqueta = 'Provincia'";
Query->Open();
AnsiString ProvTit = FormatFloat("00", Query->FieldByName("Valor")->AsInteger); 


En una tabla auxliar están guardados los datos básicos de la instalación: Propietario, domicilio, CIF, etc. y uno de ellos es la provincia que rescato con este código. Repito mi pregunta, que es válida tanto para el caso que ha abierto este hilo como para cualquier otro en que se usen procedimientos:

¿Cómo hago para meter en un stored procedure ese valor ProvTit que he sacado de una tabla y que no se modifica en toda la vida del programa? Esto es lo que no entiendo; meterlo "a capón" lo había comprendido desde el primer ejemplo que me puso Casimiro. Y los siento de veras por la paliza que os estoy dando y las molestias para explicármelo pero ya os dicho que soy bastante bruto.

Empezado por fjcg02

Cada uno puede hacer lo que quiera, pero ya tengo una cana, y más sabe el zorro por viejo que por zorro.

¡Digo la sartén al cazo...! De canas tampoco ando mal servido, pero nunca es tarde para aprender.