Subject Re: [IBO] Manual insertion of new record
Author Jack Cane
Post date 2014-06-03T21:29:26Z
Table AddlColors has three columns, primary key,  and foreign key, both of type integer, and a property column of type varchar (30).

In ibSql Relations | Data tab for that table, I select '+', and make entries into fgn key and property. Click 'Commit' and get an error, '(pk column name) is a required field'.

Tried this at last row as well as at other rows in middle of table, etc.

Following copied from creation script:

CREATE GENERATOR GENADDLCOLORSNO;
SET GENERATOR GENADDLCOLORSNO TO 0;


/* ---------------------------------------------------------------------- */
/* Add table "ADDLCOLORS"                                                 */
/* ---------------------------------------------------------------------- */

CREATE TABLE ADDLCOLORS (
    KADDLCOLOR INTEGER NOT NULL,
    OTHCOLORS VARCHAR(20) NOT NULL,
    FKIMAGE INTEGER NOT NULL,
    CONSTRAINT KCOLOR2 PRIMARY KEY (KADDLCOLOR)
);

UPDATE RDB$RELATIONS SET RDB$DESCRIPTION = 'Secondary color(s) in an image' WHERE RDB$RELATION_NAME='ADDLCOLORS';

UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'pk="autoincrement" label="##" type="hidden"' WHERE (RDB$RELATION_NAME = 'ADDLCOLORS') AND (RDB$FIELD_NAME = 'KADDLCOLOR');

UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Other Colors' WHERE (RDB$RELATION_NAME = 'ADDLCOLORS') AND (RDB$FIELD_NAME = 'OTHCOLORS');

UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'type="hidden"' WHERE (RDB$RELATION_NAME = 'ADDLCOLORS') AND (RDB$FIELD_NAME = 'FKIMAGE');

ALTER TABLE ADDLCOLORS ADD CONSTRAINT IMAGES_ADDLCOLORS 
    FOREIGN KEY (FKIMAGE) REFERENCES IMAGES (KIMAGE) ON DELETE CASCADE;

*/==========*/

From Triggers script:

// ADDLCOLORS
CREATE TRIGGER ADDLCOLORS_BI
FOR ADDLCOLORS
ACTIVE
BEFORE INSERT
AS
BEGIN
END^

CREATE TRIGGER ADDLCOLORS_UC
FOR ADDLCOLORS
ACTIVE
BEFORE UPDATE
AS
BEGIN
END^

CREATE TRIGGER ADDLCOLORS_PK
FOR ADDLCOLORS
ACTIVE
BEFORE INSERT
AS
BEGIN
  if (NEW.KADDLCOLOR IS NULL)
  then
    NEW.KADDLCOLOR = GEN_ID(GenADDLCOLORSNo, 1);
  if (new.KADDLCOLOR = -1)
  then
    NEW.KADDLCOLOR = GEN_ID(GenADDLCOLORSNo, 1);
END^


Best,
jwc

On Jun 3, 2014, at 11:40 AM, 'IBO Support List' supportlist@... [IBObjects] <IBObjects@yahoogroups.com> wrote:


There likely is something that can be set to allow for this.
Will you send me more detailed information about the table and where in IB_SQL you were doing the insert?
 
Ideally it should make use of the RETURNING clause so that whatever value the server provides comes back to the client.
 
Thanks,
Jason
 

From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] 
Sent: Monday, June 2, 2014 9:03 AM
To: IBObjects ListServer
Subject: [IBO] Manual insertion of new record

Although all of my tables contain triggers to manage primary key field values. today I noticed that, when using ibSQL to insert a new record manually, I was required to type in the PK value. This required navigating away from the insertion point because I'm entering into the new record, the FK value of some current record), finding the current maximum, adding 1 and making a correct entry.

Wondering if there is a setting to allow automatic entry of the new PK value, into ibSQL.

Best,
jwc