Inserción masiva condicionada

ElDuc · #1 06-06-2023

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.

ElDuc · #2 06-06-2023

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.

[duilioisola] · #3 06-06-2023

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;

[duilioisola] · #4 06-06-2023

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;

Casimiro Notevi · #5 06-06-2023

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;

Angel.Matilla · #6 02-02-2024

Cita:

Empezado por Casimiro Notevi

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?

Casimiro Notevi · #7 02-02-2024

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.

Angel.Matilla · #8 02-02-2024

Cita:

Empezado por Casimiro Notevi

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.

[duilioisola] · #9 05-02-2024

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.

Angel.Matilla · #10 05-02-2024

Cita:

Empezado por duilioisola

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.

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

[duilioisola] · #11 05-02-2024

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. ()

Angel.Matilla · #12 05-02-2024

Cita:

Empezado por duilioisola

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.

Angel.Matilla · #13 05-02-2024

Cita:

Empezado por duilioisola

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?

Casimiro Notevi · #14 05-02-2024

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

[duilioisola] · #15 05-02-2024

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;

Angel.Matilla · #16 05-02-2024

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?

[duilioisola] · #17 05-02-2024

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();

Casimiro Notevi · #18 05-02-2024

Creo que algo así:

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

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

Angel.Matilla · #19 06-02-2024

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

Angel.Matilla · #20 06-02-2024

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.

Temas Similares
Tema	Autor	Foro	Respuestas	Último mensaje
Inserción masiva en Firebird	ElDuc	Conexión con bases de datos	40	30-04-2023 11:38:06
insercion masiva	Alfredo	Firebird e Interbase	3	12-11-2008 21:34:46
Insercion masiva de una BD a otra BD	oscjae	Firebird e Interbase	5	15-12-2006 21:25:49
Insercion masiva de registros MUY LENTA	Balda	Firebird e Interbase	10	24-03-2004 22:40:21
Inserción masiva en MySQL	Morfo	MySQL	3	09-01-2004 19:05:33