Ver Mensaje Individual
  #1  
Antiguo 06-06-2023
ElDuc ElDuc is offline
Miembro
 
Registrado: jul 2004
Posts: 197
Reputación: 21
ElDuc Va por buen camino
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.
Responder Con Cita