Foros Club Delphi - Inserción masiva condicionada

Foros Club Delphi (https://www.clubdelphi.com/foros/index.php)

- Firebird e Interbase (https://www.clubdelphi.com/foros/forumdisplay.php?f=19)

- - Inserción masiva condicionada (https://www.clubdelphi.com/foros/showthread.php?t=96253)

Inserción masiva condicionada

Hola a tod@s.

En un hilo anterior sobre este tema, siguiendo los consejos de Casimiro, logré solucionar mis limitaciones.

Por refrescar el tema, os diré que trabajo con Delphi 2010, Firebird 4 y SQL Manager Lite.

Tengo que poner un espacio entre : y Campo, porque me salen caras

En Firebird tengo la siguiente tabla:

Código SQL [-]  CREATE TABLE CHARTS (
  ID INTEGER DEFAULT 0 NOT NULL,
  BREACK CHAR(1) DEFAULT 'G' NOT NULL,
  CHARTTYPE SMALLINT DEFAULT 0 NOT NULL,
  XUP SMALLINT DEFAULT 0 NOT NULL,
  PUP SMALLINT DEFAULT 0 NOT NULL,
  XXUP SMALLINT DEFAULT 0 NOT NULL,
  PPUP SMALLINT DEFAULT 0 NOT NULL,
  XDOWN SMALLINT DEFAULT 0 NOT NULL,
  PDOWN SMALLINT DEFAULT 0 NOT NULL,
  XXDOWN SMALLINT DEFAULT 0 NOT NULL,
  PPDOWN SMALLINT DEFAULT 0 NOT NULL,
  STEPS INTEGER DEFAULT 0 NOT NULL);


ALTER TABLE CHARTS ADD PRIMARY KEY (ID);


CREATE INDEX CHARTSKEY ON CHARTS(BREACK,CHARTTYPE,XUP,PUP,XXUP,PPUP,XDOWN,PDOWN,XXDOWN,PPDOWN);


SET TERM ^ ;

CREATE TRIGGER BI_CHARTS_ID FOR CHARTS
ACTIVE BEFORE 
  INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
      NEW.ID = GEN_ID(CHARTS_ID_GEN, 1);
END^

SET TERM ; ^

Y el siguiente Procedimiento almacenado:

Código SQL [-]SET TERM ^ ;

CREATE PROCEDURE INSERT_NEW_CHART(
  BREACK CHAR(1),
  CHARTTYPE SMALLINT,
  XUP SMALLINT,
  PUP SMALLINT,
  XXUP SMALLINT,
  PPUP SMALLINT,
  XDOWN SMALLINT,
  PDOWN SMALLINT,
  XXDOWN SMALLINT,
  PPDOWN SMALLINT)
AS
DECLARE VARIABLE EXISTINGID INTEGER;
BEGIN
  /* Comprobar si existe el registro */
  SELECT Id
  FROM CHARTS
  WHERE Breack = : Breack
    AND ChartType = : ChartType
    AND xUp = : xUp
    AND pUp = : pUp
    AND xxUp = : xxUp
    AND ppUp = : ppUp
    AND xDown = : xDown
    AND pDown = : pDown
    AND xxDown = : xxDown
    AND ppDown = : ppDown
  INTO :ExistingId;

  IF (ExistingId IS NULL) THEN
    BEGIN
      /* No existe, lo insertamos */
      INSERT INTO CHARTS (Breack, ChartType, xUp, pUp, xxUp, ppUp, xDown, pDown, xxDown, ppDown)
      VALUES (: Breack, : ChartType, : xUp, : pUp, : xxUp, : ppUp, : xDown, : pDown, : xxDown, : ppDown);
    END
  ELSE
    BEGIN
      /* Ya existe, incrementamos Steps */
      UPDATE CHARTS
          SET Steps = Steps + 1
          WHERE Id = :ExistingId;
    END
END^

SET TERM ; ^

Y en Delphi tengo el siguiente procedimiento:

Código Delphi [-]Procedure SaveTemplate;
Begin
If TemplatesInserteds = 0 Then
    Begin
    // TemplatesInserteds = Variable global,inicialmente = 0
    // Preparamos Para inserción múltiple
    If fTesting.IBTransaction.InTransaction Then
        fTesting.IBTransaction.Rollback;
    fTesting.IBTransaction.StartTransaction;
    fTesting.IBQuery.Close;
    fTesting.IBQuery.SQL.Text := 'EXECUTE PROCEDURE INSERT_NEW_CHART(: Breack, : ChartType, : xUp, : pUp, : xxUp, : ppUp, : xDown, : pDown, : xxDown, : ppDown)';
    fTesting.IBQuery.Prepare;
    End
Else If TemplatesInserteds >= MaxTemplatesInserteds Then
    Begin
    // MaxTemplatesInserteds = Constante global = 10000
    TemplatesInserteds := 0;
    fTesting.IBQuery.Transaction.CommitRetaining;
    End;
If fTesting.IBQuery.Transaction.InTransaction Then
    Begin
    Inc(TemplatesInserteds);
    fTesting.IBQuery.Params.ParamByName('Breack').AsString := Template.Breack;
    fTesting.IBQuery.Params.ParamByName('ChartType').AsSmallInt := Template.ChartType;

    fTesting.IBQuery.Params.ParamByName('xUp').AsSmallInt := Template.xUp;
    fTesting.IBQuery.Params.ParamByName('pUp').AsSmallInt := Template.pUp;
    fTesting.IBQuery.Params.ParamByName('xxUp').AsSmallInt := Template.xxUp;
    fTesting.IBQuery.Params.ParamByName('ppUp').AsSmallInt := Template.ppUp;

    fTesting.IBQuery.Params.ParamByName('xDown').AsSmallInt := Template.xDown;
    fTesting.IBQuery.Params.ParamByName('pDown').AsSmallInt := Template.pDown;
    fTesting.IBQuery.Params.ParamByName('xxDown').AsSmallInt := Template.xxDown;
    fTesting.IBQuery.Params.ParamByName('ppDown').AsSmallInt := Template.ppDown;
    fTesting.IBQuery.ExecSQL;
    End;
End;

La primera inserción la hace bien, pero en la segunda me da el error:
'Attempt to execute an unprepared dynamic SQL statement'

Agradeceré vuestra ayuda.

Perdonad, pero no he puesto qué es lo que pretendo.

Intento insertar registros en la tabla Charts, pero si existe el registro (todos los campos menos Id y Steps) que en lugar de una inerción se incremente el campo Steps del registro existente.

Gracias.

Creo que el orden de comprobaciones es incorrecto.
Primero miras si hay que hacer commit.
Si has hecho commit, el contador se voverá 0 y harás como si fuera la primera inserción.

En tu código, perparabas la sentencia y luego si se trataba de la MaxTenokatesInserteds hacías commit, con lo que ya no está preparada.

Código Delphi [-]Procedure SaveTemplate;
Begin
  // Forzamos commit cada MaxTemplatesInserteds inserciones
  If TemplatesInserteds >= MaxTemplatesInserteds Then
  Begin
    // MaxTemplatesInserteds = Constante global = 10000
    TemplatesInserteds := 0;
    fTesting.IBQuery.Transaction.CommitRetaining;
  End;
  
  // Si es la primera insercion preparo sentencia SQL
  If TemplatesInserteds = 0 Then
  Begin
    // TemplatesInserteds = Variable global,inicialmente = 0
    // Preparamos Para inserción múltiple
    
    // Si la transaccion esta activa, revertimos
    If fTesting.IBTransaction.InTransaction Then
      fTesting.IBTransaction.Rollback;

    // Si la transaccion esta inactiva, la iniciamos
    If not fTesting.IBTransaction.InTransaction Then
      fTesting.IBTransaction.StartTransaction;
      
    fTesting.IBQuery.Close;
    fTesting.IBQuery.SQL.Text := 'EXECUTE PROCEDURE INSERT_NEW_CHART(: Breack, : ChartType, : xUp, : pUp, : xxUp, : ppUp, : xDown, : pDown, : xxDown, : ppDown)';
    fTesting.IBQuery.Prepare;
  End;
  
  If fTesting.IBQuery.Transaction.InTransaction Then
  Begin
    Inc(TemplatesInserteds);
    fTesting.IBQuery.Params.ParamByName('Breack').AsString := Template.Breack;
    fTesting.IBQuery.Params.ParamByName('ChartType').AsSmallInt := Template.ChartType;

    fTesting.IBQuery.Params.ParamByName('xUp').AsSmallInt := Template.xUp;
    fTesting.IBQuery.Params.ParamByName('pUp').AsSmallInt := Template.pUp;
    fTesting.IBQuery.Params.ParamByName('xxUp').AsSmallInt := Template.xxUp;
    fTesting.IBQuery.Params.ParamByName('ppUp').AsSmallInt := Template.ppUp;

    fTesting.IBQuery.Params.ParamByName('xDown').AsSmallInt := Template.xDown;
    fTesting.IBQuery.Params.ParamByName('pDown').AsSmallInt := Template.pDown;
    fTesting.IBQuery.Params.ParamByName('xxDown').AsSmallInt := Template.xxDown;
    fTesting.IBQuery.Params.ParamByName('ppDown').AsSmallInt := Template.ppDown;
    fTesting.IBQuery.ExecSQL;
  End;
End;

De todos modos, creo que lo mejor sería hacer Commit y no CommitRetaining.

Luego, si es la "primera", no tentiendo porqué haces un Rollback.

Yo lo haría así, siempre y cuando la transacción no sea compartida por fTexting y otros DataSets.

Código Delphi [-]Procedure SaveTemplate;
Begin
  // Forzamos commit cada MaxTemplatesInserteds inserciones
  If TemplatesInserteds >= MaxTemplatesInserteds Then
  Begin
    // MaxTemplatesInserteds = Constante global = 10000
    TemplatesInserteds := 0;
    fTesting.IBQuery.Transaction.Commit;
  End;
  
  // Si es la primera insercion preparo sentencia SQL
  If TemplatesInserteds = 0 Then
  Begin
    // TemplatesInserteds = Variable global,inicialmente = 0
    // Preparamos Para inserción múltiple
    
    // Si la transaccion esta inactiva, la iniciamos
    If not fTesting.IBTransaction.InTransaction Then
      fTesting.IBTransaction.StartTransaction;
      
    fTesting.IBQuery.Close;
    fTesting.IBQuery.SQL.Text := 'EXECUTE PROCEDURE INSERT_NEW_CHART(: Breack, : ChartType, : xUp, : pUp, : xxUp, : ppUp, : xDown, : pDown, : xxDown, : ppDown)';
    fTesting.IBQuery.Prepare;
  End;
  
  If fTesting.IBQuery.Transaction.InTransaction Then
  Begin
    Inc(TemplatesInserteds);
    fTesting.IBQuery.Params.ParamByName('Breack').AsString := Template.Breack;
    fTesting.IBQuery.Params.ParamByName('ChartType').AsSmallInt := Template.ChartType;

    fTesting.IBQuery.Params.ParamByName('xUp').AsSmallInt := Template.xUp;
    fTesting.IBQuery.Params.ParamByName('pUp').AsSmallInt := Template.pUp;
    fTesting.IBQuery.Params.ParamByName('xxUp').AsSmallInt := Template.xxUp;
    fTesting.IBQuery.Params.ParamByName('ppUp').AsSmallInt := Template.ppUp;

    fTesting.IBQuery.Params.ParamByName('xDown').AsSmallInt := Template.xDown;
    fTesting.IBQuery.Params.ParamByName('pDown').AsSmallInt := Template.pDown;
    fTesting.IBQuery.Params.ParamByName('xxDown').AsSmallInt := Template.xxDown;
    fTesting.IBQuery.Params.ParamByName('ppDown').AsSmallInt := Template.ppDown;
    fTesting.IBQuery.ExecSQL;
  End;
End;

Aparte de lo indicado por duilioisola, también puedes simplificar el procedure usando "update or insert".
https://firebirdsql.org/refdocs/lang...or-insert.html

Código SQL [-]update or insert into Cows (Name, Number, Location)   
values ('Suzy Creamcheese', 3278823, 'Green Pastures')   
matching (Number)   
returning rec_id into :id;

Cita:

Empezado por Casimiro Notevi (Mensaje 551659)

Código SQL [-]update or insert into Cows (Name, Number, Location)   
values ('Suzy Creamcheese', 3278823, 'Green Pastures')   
matching (Number)   
returning rec_id into :id;

No conocía esta posibilidad. Me viene una duda: yo, por ejemplo, tengo una tabla que tiene como primary key dos campos (tipo y registro) ; en esa estructura ¿dónde validaría esos campos?

Se supone que siempre comprobará la clave primaria, sea de un campo o esté compuesta por más de un campo.
Eso dice la lógica.

Cita:

Empezado por Casimiro Notevi (Mensaje 554302)

Se supone que siempre comprobará la clave primaria, sea de un campo o esté compuesta por más de un campo.
Eso dice la lógica.

Gracias. Como no conocía la estructura estaba un poco perplejo. Otra cosa: esa instrucción MATCHING, ¿qué hace exactamente? Porque es evidente que lo último (returning) lo que hace es devolver un valor.

MATCHING se refiere a encontrar el registro según los campos que le pasas.

Código SQL [-]update or insert into Cows (Name, Number, Location)   
values ('Suzy Creamcheese', 3278823, 'Green Pastures')   
matching (Number)   
returning rec_id into :id;

En el ejemplo, busca primero si existe un registro cuyo campo Number es 3278823.
Si lo encuentra hace un update sobre ese registro modificando los otros campos que le pasas Name y Location.
De lo contrario hace un insert con los tres campos que le pasas.

Cita:

Empezado por duilioisola (Mensaje 554316)

MATCHING se refiere a encontrar el registro según los campos que le pasas.

Código SQL [-]update or insert into Cows (Name, Number, Location)   
values ('Suzy Creamcheese', 3278823, 'Green Pastures')   
matching (Number)   
returning rec_id into :id;

Gracias. No encontraba en ningún lado la explicación a ese comando.

Aquí el manual, ya que tiene algo más de juego... por ejemplo, puedes retornar valores OLD.campo o NEW.campo

En firebird 5 tiene más opciones todavía. ()

Cita:

Empezado por duilioisola (Mensaje 554324)

Aquí el manual, ya que tiene algo más de juego... por ejemplo, puedes retornar valores OLD.campo o NEW.campo

En firebird 5 tiene más opciones todavía. ()

Muchas gracias.

Cita:

Empezado por duilioisola (Mensaje 554316)

Código SQL [-]update or insert into Cows (Name, Number, Location)   
values ('Suzy Creamcheese', 3278823, 'Green Pastures')   
matching (Number)   
returning rec_id into :id;

Me vais a mandar a hacer gárgaras, pero hoy debo estar muy espeso porque no soy capaz ni de entender muchas cosa ni de encontrarlas. En una aplicación hecha con BCB ¿Cómo uso esa claúsula returning?
Tengo, por ejemplo, este procedimiento:

Código:

CREATE OR ALTER PROCEDURE Act_Libro(Registro SMALLINT, Titulo VARCHAR(60), Autor VARCHAR(60), Genero VARCHAR(25),

Situacion VARCHAR(6))

AS

DECLARE VARIABLE NewRec SMALLINT;

BEGIN

  UPDATE OR INSERT INTO Titulos (Tipo, Registro, Titulo, Autor, Genero, Situacion) VALUES (3, :Registro, :Titulo, :Autor, :Genero, :Situacion)

  RETURNING Registro INTO :NewRec;

END;

Que en la aplicación ejecuto así:

Código:

Query->Close();

Query->SQL->Text = "EXECUTE PROCEDURE Act_Libro (:Registro, :Titulo, :Autor, :Genero, :Situacion)";

Query->ParamByName("Titulo")->AsString       = Titulo_libro->Text;

Query->ParamByName("Autor")->AsString        = Autor->Text;

Query->ParamByName("Genero")->AsString       = Genero_libro->Text;

Query->ParamByName("Situacion")->AsString    = Situacion_libro->Text;

Query->ParamByName("Registro")->AsInteger = this->Tag;

Query->ExecSQL();

Query->Transaction->Commit();

¿Cómo sé el valor que devuelve en esa variable?

Es que estás ejecutando la sentencia dentro de un stored procedure, y en éste no estás devolviendo nada.

Código SQL [-]create or alter procedure act_libro (
    registro smallint,
    titulo varchar(60),
    autor varchar(60),
    genero varchar(25),
    situacion varchar(6))
returns (
    newrec smallint)
as
/*
Este dato se devuelve, por lo que se declara en el apartado RETURNS (...)
declare variable newrec smallint;
*/
begin
    update or insert into titulos (
    tipo, registro, titulo, autor, genero, situacion)
    values (
    3, :registro, :titulo, :autor, :genero, :situacion)
    returning registro
    into :newrec;
end;

Gracias a los dos; es lo que pasa cuando se tienen errores de base. Pero sigo teniendo la misma duda: ¿cómo trabajo con esa variable en BCB?

Igual que en un SELECT...

No conozco BCB pero supongo que hay algo así como FieldByName().

Código:

Query->Close();

Query->SQL->Text = "EXECUTE PROCEDURE Act_Libro (:Registro, :Titulo, :Autor, :Genero, :Situacion)";

Query->ParamByName("Titulo")->AsString       = Titulo_libro->Text;

Query->ParamByName("Autor")->AsString        = Autor->Text;

Query->ParamByName("Genero")->AsString       = Genero_libro->Text;

Query->ParamByName("Situacion")->AsString    = Situacion_libro->Text;

Query->ParamByName("Registro")->AsInteger = this->Tag;

Query->ExecSQL();



RegistroCreado = Query->FieldByName("newrec")->AsInteger;



Query->Transaction->Commit();

Creo que algo así:

Código Delphi [-]int newrec = Query->Params->ParamByName("newrec")->AsSmallInt;

EDITO: No había visto el mensaje de [duilioisola].

Gracias a los dos. Lo probaré y os digo como va.

Pues no: tanto con FieldByName como con ParamByName da error. Con FieldByName me dice Query; Field 'NewRec, not found, y con ParamByName en vez de Field dice que lo no encontrado es el parámetro. He probado a ejecutarlo desde SQL Manager y se ejecuta bien y devuelve un valor coherente, pero no veo como recuperarlo para la aplicación. :confused: