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