need example code for IBCquery
Posted: Tue 10 Feb 2009 21:03
Hi,
being new on IBDAC, can someone provide me a small sample how to work with IBCQuery where the sql is defined by stored procedures?
I placed a IBCConnection, IBCQuery, and IBCDatasource and a DBGrid on a new form, connected them to a test database that has defined a table testtable and SIUD procedures (see below).
Then on IBCQuery I opened IBCQuery editor and on Stored Proc Call Generator i defined the calls for select, update, insert and delete
All other properties of IBCQuery, IBCConnection,DBGrid are left on default.
running the application I entered something for column name and pressed down key to get a new row : this always results in an error 'Update failed. 0 records found'.
But when restarting the appplication I can see that the new record was created. Now I tried to modify the name for an existing record. Also fter changing the row I get the same error.
So what am I doing wrong? (latest IBDAC on Delphi 2007)
(I know that I could use IBCQuerys key field, key generator properties but I want to do it purely with stored procedures and triggers as I need a consistent way also for inserting data from other stored procedures.
best regards,
Ulrich
being new on IBDAC, can someone provide me a small sample how to work with IBCQuery where the sql is defined by stored procedures?
I placed a IBCConnection, IBCQuery, and IBCDatasource and a DBGrid on a new form, connected them to a test database that has defined a table testtable and SIUD procedures (see below).
Then on IBCQuery I opened IBCQuery editor and on Stored Proc Call Generator i defined the calls for select, update, insert and delete
All other properties of IBCQuery, IBCConnection,DBGrid are left on default.
running the application I entered something for column name and pressed down key to get a new row : this always results in an error 'Update failed. 0 records found'.
But when restarting the appplication I can see that the new record was created. Now I tried to modify the name for an existing record. Also fter changing the row I get the same error.
So what am I doing wrong? (latest IBDAC on Delphi 2007)
(I know that I could use IBCQuerys key field, key generator properties but I want to do it purely with stored procedures and triggers as I need a consistent way also for inserting data from other stored procedures.
best regards,
Ulrich
Code: Select all
/******************************************************************************/
/**** Generated by IBExpert 2009.01.16 10.02.2009 21:42:15 ****/
/******************************************************************************/
/******************************************************************************/
/**** Generators ****/
/******************************************************************************/
CREATE SEQUENCE SEQ_UID;
SET TERM ^ ;
/******************************************************************************/
/**** Stored Procedures ****/
/******************************************************************************/
CREATE PROCEDURE TESTTABLE_DEL (
ID BIGINT)
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE TESTTABLE_INS (
ID BIGINT,
NAME VARCHAR(255))
AS
BEGIN
EXIT;
END^
CREATE PROCEDURE TESTTABLE_SEL
RETURNS (
ID BIGINT,
NAME VARCHAR(255))
AS
BEGIN
SUSPEND;
END^
CREATE PROCEDURE TESTTABLE_UPD (
ID BIGINT,
NAME VARCHAR(255))
AS
BEGIN
EXIT;
END^
SET TERM ; ^
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE TABLE TESTTABLE (
ID BIGINT NOT NULL,
NAME VARCHAR(255)
);
/******************************************************************************/
/**** Primary Keys ****/
/******************************************************************************/
ALTER TABLE TESTTABLE ADD CONSTRAINT PK_TESTTABLE PRIMARY KEY (ID);
/******************************************************************************/
/**** Triggers ****/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/**** Triggers for tables ****/
/******************************************************************************/
/* Trigger: TESTTABLE_BI0 */
CREATE TRIGGER TESTTABLE_BI0 FOR TESTTABLE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
NEW.ID = next value for seq_uid;
end
^
SET TERM ; ^
/******************************************************************************/
/**** Stored Procedures ****/
/******************************************************************************/
SET TERM ^ ;
ALTER PROCEDURE TESTTABLE_DEL (
ID BIGINT)
AS
begin
delete from testtable
where (id = :id);
end^
ALTER PROCEDURE TESTTABLE_INS (
ID BIGINT,
NAME VARCHAR(255))
AS
begin
insert into testtable (name)
values (:name);
end^
ALTER PROCEDURE TESTTABLE_SEL
RETURNS (
ID BIGINT,
NAME VARCHAR(255))
AS
begin
for select id,
name
from testtable
into :id,
:name
do
begin
suspend;
end
end^
ALTER PROCEDURE TESTTABLE_UPD (
ID BIGINT,
NAME VARCHAR(255))
AS
begin
update testtable
set name = :name
where (id = :id);
end^
SET TERM ; ^