Defaults won't appear

[user]

Hello,

Here's an example of what I'm trying to fix:

/* SQL */

CREATE TABLE JOBS (
ID INTEGER NOT NULL,
NAME VARCHAR(25)
);

/* The first record of JOBS table, ID=0, has a NAME value of '(None)' */

ALTER TABLE JOBS
ADD CONSTRAINT PK_JOBS PRIMARY KEY (ID);

CREATE GENERATOR EMPLOYEE_ID;

CREATE TABLE EMPLOYEES (
ID INTEGER NOT NULL,
NAME VARCHAR(25),
JOB_ID INTEGER DEFAULT 0 NOT NULL
);

ALTER TABLE EMPLOYEES
ADD CONSTRAINT PK_EMPLOYEES PRIMARY KEY (ID);

ALTER TABLE NEW_TABLE
ADD CONSTRAINT NEW_TABLE_JOBS FOREIGN KEY (JOB_ID)
REFERENCES JOBS (ID) ON DELETE SET DEFAULT ON UPDATE CASCADE;

SET TERM ^ ;

CREATE TRIGGER EMPLOYEES_BI0 FOR EMPLOYEES
ACTIVE BEFORE INSERT POSITION 0
AS
begin
if (NEW.ID is NULL) then
NEW.ID = GEN_ID(EMPLOYEE_ID, 1);
if (NEW.JOB_ID is NULL) then
NEW.JOB_ID = 0;
end
^

/* DFMs */

object DMod: TDMod
OldCreateOrder = False
Left = 201
Top = 379
Height = 257
Width = 433
object DB: TIBDatabase
Connected = True
DatabaseName = 'c:\data\test.gdb'
Params.Strings = (
'user_name=sysdba'
'password=masterkey'
'lc_ctype=ASCII')
LoginPrompt = False
DefaultTransaction = Trans
IdleTimer = 0
SQLDialect = 1
TraceFlags = []
Left = 20
Top = 20
end
object Employees: TIBDataSet
Database = DB
Transaction = Trans
AfterPost = EmployeesAfterPost
BufferChunks = 10
CachedUpdates = False
DeleteSQL.Strings = (
'delete from EMPLOYEES'
'where'
' ID = :OLD_ID')
InsertSQL.Strings = (
'insert into EMPLOYEES'
' (ID, NAME, JOB_ID)'
'values'
' (:ID, :NAME, :JOB_ID)')
RefreshSQL.Strings = (
'Select '
' ID,'
' NAME,'
' JOB_ID'
'from EMPLOYEES'
'where'
' ID = :ID')
SelectSQL.Strings = (
'select *'
'from EMPLOYEES'
'where ID > 0'
'order by ID')
ModifySQL.Strings = (
'update EMPLOYEES'
'set'
' ID = :ID,'
' NAME = :NAME,'
' JOB_ID = :JOB_ID'
'where'
' ID = :OLD_ID')
Active = True
Left = 20
Top = 70
object EmployeesID: TIntegerField
FieldName = 'ID'
Origin = 'EMPLOYEES.ID'
end
object EmployeesNAME: TIBStringField
DisplayWidth = 15
FieldName = 'NAME'
Origin = 'EMPLOYEES.NAME'
Size = 25
end
object EmployeesJOB_ID: TIntegerField
AutoGenerateValue = arDefault
FieldName = 'JOB_ID'
Origin = 'EMPLOYEES.JOB_ID'
end
object EmployeesJOB_L: TIBStringField
FieldKind = fkLookup
FieldName = 'JOB_L'
LookupDataSet = Jobs
LookupKeyFields = 'ID'
LookupResultField = 'ID_NAME_C'
KeyFields = 'JOB_ID'
Size = 25
Lookup = True
end
end
object Trans: TIBTransaction
Active = True
DefaultDatabase = DB
DefaultAction = TACommitRetaining
Params.Strings = (
'read_committed'
'rec_version'
'nowait')
AutoStopAction = saCommitRetaining
Left = 70
Top = 20
end
object EmpSrc: TDataSource
DataSet = Employees
Left = 20
Top = 120
end
object JobsSrc: TDataSource
DataSet = Jobs
Left = 70
Top = 120
end
object Jobs: TIBQuery
Database = DB
Transaction = Trans
OnCalcFields = JobsCalcFields
Active = True
BufferChunks = 10
CachedUpdates = False
SQL.Strings = (
'select *'
'from JOBS'
'order by NAME')
Left = 70
Top = 75
object JobsID: TIntegerField
FieldName = 'ID'
Origin = 'JOBS.ID'
Required = True
end
object JobsNAME: TIBStringField
FieldName = 'NAME'
Origin = 'JOBS.NAME'
Size = 25
end
object JobsID_NAME_C: TIBStringField
FieldKind = fkCalculated
FieldName = 'ID_NAME_C'
Size = 35
Calculated = True
end
end
end

object fMain: TfMain
Left = 202
Top = 109
BorderStyle = bsSingle
Caption = 'Test'
ClientHeight = 147
ClientWidth = 270
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Label1: TLabel
Left = 15
Top = 60
Width = 11
Height = 13
Caption = 'ID'
end
object Label2: TLabel
Left = 15
Top = 90
Width = 28
Height = 13
Caption = 'Name'
end
object Label3: TLabel
Left = 15
Top = 120
Width = 17
Height = 13
Caption = 'Job'
end
object DBEdit1: TDBEdit
Left = 55
Top = 55
Width = 36
Height = 21
DataField = 'ID'
DataSource = DMod.EmpSrc
TabOrder = 0
end
object DBEdit2: TDBEdit
Left = 55
Top = 85
Width = 121
Height = 21
DataField = 'NAME'
DataSource = DMod.EmpSrc
TabOrder = 1
end
object DBLookupComboBox1: TDBLookupComboBox
Left = 55
Top = 115
Width = 121
Height = 21
DataField = 'JOB_L'
DataSource = DMod.EmpSrc
TabOrder = 2
end
object DBNavigator1: TDBNavigator
Left = 20
Top = 10
Width = 240
Height = 25
DataSource = DMod.EmpSrc
TabOrder = 3
end
end

/* C++ */

void __fastcall TDMod::JobsCalcFields(TDataSet *DataSet)
{
JobsID_NAME_C->AsString = "[" + JobsID->AsString + "] " +
JobsNAME->AsString;
}
//--------------------------------------------------------------------------
-

void __fastcall TDMod::EmployeesAfterPost(TDataSet *DataSet)
{
Trans->CommitRetaining();
}
//--------------------------------------------------------------------------
-

==================================

So, the problem is that I want to be able for the user to be completely
ignorant of the Employees.ID field. It has a generator. I'd like to
eliminate the field from the form entirely. I also want the default value
for Employees.JOB_ID to be looked up for the TDBLookupComboBox if the user
doesn't provide a value.

What happens is, when you click the "+" button on the navigator, you get the
expected blank record to fill in. If only the NAME field is entered, then
the Post button is clicked, the ID and JOB fields remain blank, instead of
showing their generated and default values. How do I get those values to
appear on POST?

Many thanks to all,

Terry

[Wayne Niddery [TeamB]]

Post by user
So, the problem is that I want to be able for the user to be
completely ignorant of the Employees.ID field. It has a generator.
I'd like to eliminate the field from the form entirely. I also want
the default value for Employees.JOB_ID to be looked up for the
TDBLookupComboBox if the user doesn't provide a value.
What happens is, when you click the "+" button on the navigator, you
get the expected blank record to fill in. If only the NAME field is
entered, then the Post button is clicked, the ID and JOB fields
remain blank, instead of showing their generated and default values.
How do I get those values to appear on POST?

The normal way. in Interbase, for applications to get a generated key value
is to manually ask for it *first* - execute a query:
select Gen_ID(employee_id, 1) from rdb$database
(the result will be in Fields[0].AsInteger).

Assign that to your insert statement as a parameter.

For Job_ID, it appears you are trying to set up the combo box to handle
this, is that not working?

If handling Job_ID manually, then note that as long as you are including
job_id on the insert statement, whether the parameter is 0 or null, the
default value defined in the table will never take - that default value gets
assigned if the field is *not included* in the insert statement. Your
trigger should take care of the null however You could simply assign it to
zero on your insert, but if you don;t want the application to "assume" this
value, then insert it with null and then select the new record back again
(you'll have the primary key if you have manually selected from the
generator as above).

[Terry]

OK, I'm trying this for a BeforeInsert event handler on the EMPLOYEES table:

void __fastcall TDMod::EmployeesBeforeInsert(TDataSet *DataSet)
{
TIBQuery *Qry = new TIBQuery(this);

Qry->Database = DB;
Qry->SQL->Text = "select Gen_ID(employee_id, 1) from rdb$database;";
Qry->Open();

Employees->ParamByName("ID")->AsInteger =
Qry->Fields->Fields[0]->AsInteger;

// Since the param in the INSERT statement of the TIBDataSet is named
":ID"...

Qry->Close();

delete Qry;
}

...and it isn't working. I recieve a "Field "ID" not found" exception.

I'm assuming I have misunderstood something here.

Thanks,
Terry

Post by Wayne Niddery [TeamB]

Post by user
So, the problem is that I want to be able for the user to be
completely ignorant of the Employees.ID field. It has a generator.
I'd like to eliminate the field from the form entirely. I also want
the default value for Employees.JOB_ID to be looked up for the
TDBLookupComboBox if the user doesn't provide a value.
What happens is, when you click the "+" button on the navigator, you
get the expected blank record to fill in. If only the NAME field is
entered, then the Post button is clicked, the ID and JOB fields
remain blank, instead of showing their generated and default values.
How do I get those values to appear on POST?

The normal way. in Interbase, for applications to get a generated key value
select Gen_ID(employee_id, 1) from rdb$database
(the result will be in Fields[0].AsInteger).
Assign that to your insert statement as a parameter.
For Job_ID, it appears you are trying to set up the combo box to handle
this, is that not working?
If handling Job_ID manually, then note that as long as you are including
job_id on the insert statement, whether the parameter is 0 or null, the
default value defined in the table will never take - that default value gets
assigned if the field is *not included* in the insert statement. Your
trigger should take care of the null however You could simply assign it to
zero on your insert, but if you don;t want the application to "assume" this
value, then insert it with null and then select the new record back again
(you'll have the primary key if you have manually selected from the
generator as above).
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"True peace is not the absence of tension, but the presence of
justice." - Martin Luther King, Jr.

[Wayne Niddery [TeamB]]

Post by Terry
Employees->ParamByName("ID")->AsInteger =
Qry->Fields->Fields[0]->AsInteger;
// Since the param in the INSERT statement of the TIBDataSet is named
":ID"...
Qry->Close();
delete Qry;
}
...and it isn't working. I recieve a "Field "ID" not found"
exception.

Using the IBDataset, you use its Insert/Post methods, and therefore set its
fields, not parameters. It will format and execute the insert sql you've
provided it (forgive possible C++ typos):

Qry->Open();
IBDataset->Insert;
IBDataset->Fields->FieldByName("ID)->AsInteger =
Qry->Fields->Fields[0]->AsInteger;
IBDataset->Post;
Qry->Close();

That means the Select sql must include the ID field as well.

If you want to directly execute an insert statement, use TIBSQL instead,
then you would set its parameter values instead of fields and execute it.

[Jeff Overcash (TeamB)]

Post by user
So, the problem is that I want to be able for the user to be completely
ignorant of the Employees.ID field. It has a generator. I'd like to
eliminate the field from the form entirely. I also want the default value
for Employees.JOB_ID to be looked up for the TDBLookupComboBox if the user
doesn't provide a value.
What happens is, when you click the "+" button on the navigator, you get the
expected blank record to fill in. If only the NAME field is entered, then
the Post button is clicked, the ID and JOB fields remain blank, instead of
showing their generated and default values. How do I get those values to
appear on POST?

Use the GeneratorField property. You probably want to use either OnPost or
OnNewRecord as the event type since you want access to the value on the client side.

[Terry]

Post by Jeff Overcash (TeamB)
Use the GeneratorField property. You probably want to use
either OnPost or OnNewRecord as the event type since you want
access to the value on the client side.

Thanks! That was the magic. I should have played around with
it despite the fact that it isn't documented. Some of those
kinda things I presume are just holdovers from BDE inheritance.

(Oh, and many thanks for all the superb work on the TurboPower
components!)

-Terry