Subject | Newbie Insert SQL question. |
---|---|
Author | Walter Ogston |
Post date | 2005-02-25T21:05:49Z |
Dear friends,
Following Helen's example in the Great Book, I have defined a table with a
Before Insert trigger thus:
CREATE TABLE ORGANIZATION(
CLIENT_NUMBER INTEGER NOT NULL PRIMARY KEY,
NAME VARCHAR(64) NOT NULL,
/* other fields all nullable */
);
COMMIT;
CREATE GENERATOR GEN_PK_ORGANIZATION;
COMMIT;
SET TERM ^;
CREATE TRIGGER BI_ORGANIZATION FOR ORGANIZATION
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.CLIENT_NUMBER IS NULL) THEN
NEW.CLIENT_NUMBER = GEN_ID(GEN_PK_ORGANIZATION, 1);
END^
COMMIT^
SET TERM ;^
Now the following SQL works as expected when executed in IB_SQL:
insert into ORGANIZATION (NAME) values ('Test Organization')
- I get a new row in the table with the generated key.
Now in Delphi I want to access this table using TIBOQuery with RequestLive
= True, connected to a set of TEdit components (legacy code). To save
edits and inserts I use the following code (having previously started a
transaction when calling Insert or Edit on the query):
with iboQuery1 do begin
if State in [dsEdit, dsInsert] then
try
Post;
if IB_Transaction.InTransaction then
IB_Transaction.Commit;
except
if IB_Transaction.InTransaction then
IB_Transaction.Rollback;
end;
end; {with}
When I execute this code after inserting into the query, I get the
following exception at the Post call:
EDatabaseError with message 'Field 'CLIENT_NUMBER' must have a value'.
I have tried setting the InsertSQL property of the TIBOQuery to:
INSERT INTO ORGANIZATION(
NAME,
/* other fields snipped */)
VALUES (
:NAME,
/*other parameters snipped */ )
but I still get the error.
What is the correct way of inserting rows in the table in this
situation? Am I misunderstanding how InsertSQL is supposed to be used?
Thanks,
Walter
/*----------------
C. Walter Ogston
ogstoncw@... Kalamazoo, Michigan
*/
Following Helen's example in the Great Book, I have defined a table with a
Before Insert trigger thus:
CREATE TABLE ORGANIZATION(
CLIENT_NUMBER INTEGER NOT NULL PRIMARY KEY,
NAME VARCHAR(64) NOT NULL,
/* other fields all nullable */
);
COMMIT;
CREATE GENERATOR GEN_PK_ORGANIZATION;
COMMIT;
SET TERM ^;
CREATE TRIGGER BI_ORGANIZATION FOR ORGANIZATION
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.CLIENT_NUMBER IS NULL) THEN
NEW.CLIENT_NUMBER = GEN_ID(GEN_PK_ORGANIZATION, 1);
END^
COMMIT^
SET TERM ;^
Now the following SQL works as expected when executed in IB_SQL:
insert into ORGANIZATION (NAME) values ('Test Organization')
- I get a new row in the table with the generated key.
Now in Delphi I want to access this table using TIBOQuery with RequestLive
= True, connected to a set of TEdit components (legacy code). To save
edits and inserts I use the following code (having previously started a
transaction when calling Insert or Edit on the query):
with iboQuery1 do begin
if State in [dsEdit, dsInsert] then
try
Post;
if IB_Transaction.InTransaction then
IB_Transaction.Commit;
except
if IB_Transaction.InTransaction then
IB_Transaction.Rollback;
end;
end; {with}
When I execute this code after inserting into the query, I get the
following exception at the Post call:
EDatabaseError with message 'Field 'CLIENT_NUMBER' must have a value'.
I have tried setting the InsertSQL property of the TIBOQuery to:
INSERT INTO ORGANIZATION(
NAME,
/* other fields snipped */)
VALUES (
:NAME,
/*other parameters snipped */ )
but I still get the error.
What is the correct way of inserting rows in the table in this
situation? Am I misunderstanding how InsertSQL is supposed to be used?
Thanks,
Walter
/*----------------
C. Walter Ogston
ogstoncw@... Kalamazoo, Michigan
*/