Hi Geoff (and others),

I have appended the IB Monitor output and table definition at the bottom of
this message as requested...

I also have a query about your reply...

>> Server defaults are only used if the field is not explicitly provided
>> in an insert statement. eg.
>> create tablea(
>> fielda varchar(20) default 'a',
>> fieldb varchar(20) default 'b'
>> );

>> insert into tablea (fielda) values ('c');
>> will result with fieldb set to its default. Whereas...

>> insert into tablea (fielda,fieldb) values( 'c', null );
>> will result with fieldb set to NULL!!!

Also from your reply doing a select * will insert all fields and set the non
specified fields to Null... so what happens if the table definition defines
Not Null for some fields? I was assuming that the table defaults would
spring into action... when I have defaults on a field, no error is raised
about trying to insert a null into a not null field when posting or
committing so I assumed this is what was happening.

Note that the IB Monitor output below contains output from the cursor that
had PreparedEdits set to True, this showed the actual data in IB Monitor
that was ending up in the DB. (It did the same set to False, it just didn't
show in IB Monitor)

I am using Delphi 5 and downloaded the latest partial source version of IBO
the other day before posting to see if that made a difference... it didn't.

Also, re-looking at the IB Monitor output... it seems as though the strange
data is getting added for an update and not an insert.

I shall just explain a bit what the program does (it is not tuned for
efficiency!).
What I do is read through an audit file. Each line in the file tells me to
either insert a record or a update one particular field of the table in
question. So that explains why there is to updates in the IB Monitor output,
I set the dataset to Edit, update the fieldByName, then post for each field.
(I don't read ahead in the file and group updates to the same record in one
edit, update, post set... like I said it is not tuned for efficiency.) But
there is still strange data getting in there and I don't know how.

Regards
Colin

*******Table Def *********
CREATE TABLE CT_CONTACT (
CONTACT_ID INTEGER NOT NULL,
STAFF_CONTACT BOOLEAN,
DEBTOR_CONTACT BOOLEAN,
PARENT_CONTACT_ID INTEGER DEFAULT 0 NOT NULL,
NAME VARCHAR(40) NOT NULL,
QUICK_CODE VARCHAR(10) DEFAULT '' NOT NULL,
TITLE VARCHAR(10) DEFAULT '' NOT NULL,
SALUTATION VARCHAR(25) DEFAULT '' NOT NULL,
COMPANY_TITLE VARCHAR(25) DEFAULT '' NOT NULL,
PHONE VARCHAR(25) DEFAULT '' NOT NULL,
FAX VARCHAR(25) DEFAULT '' NOT NULL,
MOBILE VARCHAR(25) DEFAULT '' NOT NULL,
EMAIL VARCHAR(40) DEFAULT '' NOT NULL,
POSTAL_ADDRESS1 VARCHAR(30) DEFAULT '' NOT NULL,
POSTAL_ADDRESS2 VARCHAR(30) DEFAULT '' NOT NULL,
POSTAL_TOWNCITY VARCHAR(30) DEFAULT '' NOT NULL,
POSTAL_COUNTRY VARCHAR(30) DEFAULT '' NOT NULL,
DELIVERY_ADDRESS1 VARCHAR(30) DEFAULT '' NOT NULL,
DELIVERY_ADDRESS2 VARCHAR(30) DEFAULT '' NOT NULL,
DELIVERY_TOWNCITY VARCHAR(30) DEFAULT '' NOT NULL,
DELIVERY_COUNTRY VARCHAR(30) DEFAULT '' NOT NULL,
POST_CODE INTEGER NOT NULL,
TIME_STAMP DATE,
UC_NAME VARCHAR(40),
INITIALS VARCHAR(10) DEFAULT '' NOT NULL,
HOLD_CREDIT BOOLEAN DEFAULT 'F' NOT NULL,
TEMNOTES VARCHAR(255),
GENERAL_NOTES VARCHAR(1023),
IN_USE CHAR(1) DEFAULT 'F'
)


************IB Monitor Output starts here*********
/*---
CONNECT DATABASE Server_1:R:/Resource/Interbase/Inventory_DB.gdb
DB_HANDLE = 12930176

SECONDS = 1.362
----*/
/*---
DATABASE INFO
DB_HANDLE = 12930176
----*/
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 12927724

SECONDS = 0.220
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 12927724
STMT_HANDLE = 12927532

Select Max(Audit_Id) from Audit


PLAN (AUDIT NATURAL)

FIELDS = [ Version 1 SQLd 1 SQLn 1
"MAX" = <NIL> ]

SECONDS = 0.120
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 12927724
STMT_HANDLE = 12927532
PARAMS = [ ]

SECONDS = 0.010
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 12927724
STMT_HANDLE = 12927368

SELECT *
FROM
ct_Contact
WHERE
Contact_Id = ? /* Contact_Id */
FOR UPDATE

PLAN (CT_CONTACT INDEX (RDB$PRIMARY2))

FIELDS = [ Version 1 SQLd 29 SQLn 1
"MAX" = <NIL> ]

SECONDS = 0.101
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 12927724
STMT_HANDLE = 12927368
PARAMS = [ Version 1 SQLd 1 SQLn 1
[CONTACT_ID] = 34790 ]
----*/
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 12934344

SECONDS = 0.020
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 12934344
STMT_HANDLE = 12934416

SELECT R.RDB$FIELD_NAME
, R.RDB$RELATION_NAME
FROM RDB$RELATION_FIELDS R, RDB$FIELDS F
WHERE R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME
AND F.RDB$COMPUTED_SOURCE IS NOT NULL
AND NOT R.RDB$RELATION_NAME STARTING WITH 'RDB$'
ORDER BY 1 ASC

PLAN SORT (JOIN (F NATURAL,R INDEX (RDB$INDEX_3)))

FIELDS = [ Version 1 SQLd 2 SQLn 2
RDB$RELATION_FIELDS.RDB$FIELD_NAME = <NIL>
RDB$RELATION_FIELDS.RDB$RELATION_NAME = <NIL> ]

SECONDS = 0.050
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 12934344
STMT_HANDLE = 12934416
PARAMS = [ ]

SECONDS = 0.020
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 12927724
STMT_HANDLE = 12934416

INSERT INTO CT_CONTACT
( CT_CONTACT.CONTACT_ID
, CT_CONTACT.STAFF_CONTACT
, CT_CONTACT.DEBTOR_CONTACT
, CT_CONTACT.PARENT_CONTACT_ID
, CT_CONTACT.NAME
, CT_CONTACT.QUICK_CODE
, CT_CONTACT.TITLE
, CT_CONTACT.SALUTATION
, CT_CONTACT.COMPANY_TITLE
, CT_CONTACT.PHONE
, CT_CONTACT.FAX
, CT_CONTACT.MOBILE
, CT_CONTACT.EMAIL
, CT_CONTACT.POSTAL_ADDRESS1
, CT_CONTACT.POSTAL_ADDRESS2
, CT_CONTACT.POSTAL_TOWNCITY
, CT_CONTACT.POSTAL_COUNTRY
, CT_CONTACT.DELIVERY_ADDRESS1
, CT_CONTACT.DELIVERY_ADDRESS2
, CT_CONTACT.DELIVERY_TOWNCITY
, CT_CONTACT.DELIVERY_COUNTRY
, CT_CONTACT.POST_CODE
, CT_CONTACT.TIME_STAMP
, CT_CONTACT.UC_NAME
, CT_CONTACT.INITIALS
, CT_CONTACT.HOLD_CREDIT
, CT_CONTACT.TEMNOTES
, CT_CONTACT.GENERAL_NOTES
, CT_CONTACT.IN_USE)
VALUES
( ? /* CT_CONTACT.CONTACT_ID */
, ? /* CT_CONTACT.STAFF_CONTACT */
, ? /* CT_CONTACT.DEBTOR_CONTACT */
, ? /* CT_CONTACT.PARENT_CONTACT_ID */
, ? /* CT_CONTACT.NAME */
, ? /* CT_CONTACT.QUICK_CODE */
, ? /* CT_CONTACT.TITLE */
, ? /* CT_CONTACT.SALUTATION */
, ? /* CT_CONTACT.COMPANY_TITLE */
, ? /* CT_CONTACT.PHONE */
, ? /* CT_CONTACT.FAX */
, ? /* CT_CONTACT.MOBILE */
, ? /* CT_CONTACT.EMAIL */
, ? /* CT_CONTACT.POSTAL_ADDRESS1 */
, ? /* CT_CONTACT.POSTAL_ADDRESS2 */
, ? /* CT_CONTACT.POSTAL_TOWNCITY */
, ? /* CT_CONTACT.POSTAL_COUNTRY */
, ? /* CT_CONTACT.DELIVERY_ADDRESS1 */
, ? /* CT_CONTACT.DELIVERY_ADDRESS2 */
, ? /* CT_CONTACT.DELIVERY_TOWNCITY */
, ? /* CT_CONTACT.DELIVERY_COUNTRY */
, ? /* CT_CONTACT.POST_CODE */
, ? /* CT_CONTACT.TIME_STAMP */
, ? /* CT_CONTACT.UC_NAME */
, ? /* CT_CONTACT.INITIALS */
, ? /* CT_CONTACT.HOLD_CREDIT */
, ? /* CT_CONTACT.TEMNOTES */
, ? /* CT_CONTACT.GENERAL_NOTES */
, ? /* CT_CONTACT.IN_USE */ )

FIELDS = [ Version 1 SQLd 0 SQLn 0 ]

SECONDS = 0.040
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 12927724
STMT_HANDLE = 12934416
PARAMS = [ Version 1 SQLd 29 SQLn 29
CT_CONTACT.CONTACT_ID = 34790
CT_CONTACT.STAFF_CONTACT = <NULL>
CT_CONTACT.DEBTOR_CONTACT = <NULL>
CT_CONTACT.PARENT_CONTACT_ID = <NULL>
CT_CONTACT.NAME = ''
CT_CONTACT.QUICK_CODE = <NULL>
CT_CONTACT.TITLE = <NULL>
CT_CONTACT.SALUTATION = <NULL>
CT_CONTACT.COMPANY_TITLE = <NULL>
CT_CONTACT.PHONE = <NULL>
CT_CONTACT.FAX = <NULL>
CT_CONTACT.MOBILE = <NULL>
CT_CONTACT.EMAIL = <NULL>
CT_CONTACT.POSTAL_ADDRESS1 = <NULL>
CT_CONTACT.POSTAL_ADDRESS2 = <NULL>
CT_CONTACT.POSTAL_TOWNCITY = <NULL>
CT_CONTACT.POSTAL_COUNTRY = <NULL>
CT_CONTACT.DELIVERY_ADDRESS1 = <NULL>
CT_CONTACT.DELIVERY_ADDRESS2 = <NULL>
CT_CONTACT.DELIVERY_TOWNCITY = <NULL>
CT_CONTACT.DELIVERY_COUNTRY = <NULL>
CT_CONTACT.POST_CODE = <NULL>
CT_CONTACT.TIME_STAMP = <NULL>
CT_CONTACT.UC_NAME = <NULL>
CT_CONTACT.INITIALS = <NULL>
CT_CONTACT.HOLD_CREDIT = <NULL>
CT_CONTACT.TEMNOTES = <NULL>
CT_CONTACT.GENERAL_NOTES = <NULL>
CT_CONTACT.IN_USE = 'T' ]

INSERT COUNT: 1

SECONDS = 0.110
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 12927724
STMT_HANDLE = 12927368
PARAMS = [ Version 1 SQLd 1 SQLn 1
[CONTACT_ID] = 34790 ]
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 12927724
STMT_HANDLE = 12934260

UPDATE CT_CONTACT
SET CONTACT_ID = ? /* CONTACT_ID */
, STAFF_CONTACT = ? /* STAFF_CONTACT */
, DEBTOR_CONTACT = ? /* DEBTOR_CONTACT */
, PARENT_CONTACT_ID = ? /* PARENT_CONTACT_ID */
, NAME = ? /* NAME */
, QUICK_CODE = ? /* QUICK_CODE */
, TITLE = ? /* TITLE */
, SALUTATION = ? /* SALUTATION */
, COMPANY_TITLE = ? /* COMPANY_TITLE */
, PHONE = ? /* PHONE */
, FAX = ? /* FAX */
, MOBILE = ? /* MOBILE */
, EMAIL = ? /* EMAIL */
, POSTAL_ADDRESS1 = ? /* POSTAL_ADDRESS1 */
, POSTAL_ADDRESS2 = ? /* POSTAL_ADDRESS2 */
, POSTAL_TOWNCITY = ? /* POSTAL_TOWNCITY */
, POSTAL_COUNTRY = ? /* POSTAL_COUNTRY */
, DELIVERY_ADDRESS1 = ? /* DELIVERY_ADDRESS1 */
, DELIVERY_ADDRESS2 = ? /* DELIVERY_ADDRESS2 */
, DELIVERY_TOWNCITY = ? /* DELIVERY_TOWNCITY */
, DELIVERY_COUNTRY = ? /* DELIVERY_COUNTRY */
, POST_CODE = ? /* POST_CODE */
, TIME_STAMP = ? /* TIME_STAMP */
, UC_NAME = ? /* UC_NAME */
, INITIALS = ? /* INITIALS */
, HOLD_CREDIT = ? /* HOLD_CREDIT */
, TEMNOTES = ? /* TEMNOTES */
, GENERAL_NOTES = ? /* GENERAL_NOTES */
, IN_USE = ? /* IN_USE */
WHERE CURRENT OF C1786823262936246

PLAN (CT_CONTACT INDEX ())

FIELDS = [ Version 1 SQLd 0 SQLn 0 ]

SECONDS = 0.050
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 12927724
STMT_HANDLE = 12934260
PARAMS = [ Version 1 SQLd 29 SQLn 29
CT_CONTACT.CONTACT_ID = 34790
CT_CONTACT.STAFF_CONTACT = 'F'
CT_CONTACT.DEBTOR_CONTACT = 'F'
CT_CONTACT.PARENT_CONTACT_ID = 0
CT_CONTACT.NAME = 'Smith'
CT_CONTACT.QUICK_CODE = ''
CT_CONTACT.TITLE = ''
CT_CONTACT.SALUTATION = ''
CT_CONTACT.COMPANY_TITLE = ''
CT_CONTACT.PHONE = ''
CT_CONTACT.FAX = ''
CT_CONTACT.MOBILE = ''
CT_CONTACT.EMAIL = ''
CT_CONTACT.POSTAL_ADDRESS1 = ''
CT_CONTACT.POSTAL_ADDRESS2 = ''
CT_CONTACT.POSTAL_TOWNCITY = ''
CT_CONTACT.POSTAL_COUNTRY = ''
CT_CONTACT.DELIVERY_ADDRESS1 = ''
CT_CONTACT.DELIVERY_ADDRESS2 = ''
CT_CONTACT.DELIVERY_TOWNCITY = ''
CT_CONTACT.DELIVERY_COUNTRY = ''
CT_CONTACT.POST_CODE = 808464432
CT_CONTACT.TIME_STAMP = '15 Mar 2001 16:25:32'
CT_CONTACT.UC_NAME = ''
CT_CONTACT.INITIALS = '
'
CT_CONTACT.HOLD_CREDIT = 'F'
CT_CONTACT.TEMNOTES = <NULL>
CT_CONTACT.GENERAL_NOTES = <NULL>
CT_CONTACT.IN_USE = 'T' ]

SELECT COUNT: 1
UPDATE COUNT: 1

SECONDS = 0.120
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 12927724
STMT_HANDLE = 12934260
PARAMS = [ Version 1 SQLd 29 SQLn 29
CT_CONTACT.CONTACT_ID = 34790
CT_CONTACT.STAFF_CONTACT = 'F'
CT_CONTACT.DEBTOR_CONTACT = 'F'
CT_CONTACT.PARENT_CONTACT_ID = 0
CT_CONTACT.NAME = 'Smith'
CT_CONTACT.QUICK_CODE = ''
CT_CONTACT.TITLE = ''
CT_CONTACT.SALUTATION = 'Wendy'
CT_CONTACT.COMPANY_TITLE = ''
CT_CONTACT.PHONE = ''
CT_CONTACT.FAX = ''
CT_CONTACT.MOBILE = ''
CT_CONTACT.EMAIL = ''
CT_CONTACT.POSTAL_ADDRESS1 = ''
CT_CONTACT.POSTAL_ADDRESS2 = ''
CT_CONTACT.POSTAL_TOWNCITY = ''
CT_CONTACT.POSTAL_COUNTRY = ''
CT_CONTACT.DELIVERY_ADDRESS1 = ''
CT_CONTACT.DELIVERY_ADDRESS2 = ''
CT_CONTACT.DELIVERY_TOWNCITY = ''
CT_CONTACT.DELIVERY_COUNTRY = ''
CT_CONTACT.POST_CODE = 808464432
CT_CONTACT.TIME_STAMP = '15 Mar 2001 16:25:32'
CT_CONTACT.UC_NAME = ''
CT_CONTACT.INITIALS = '
'
CT_CONTACT.HOLD_CREDIT = 'F'
CT_CONTACT.TEMNOTES = <NULL>
CT_CONTACT.GENERAL_NOTES = <NULL>
CT_CONTACT.IN_USE = 'T' ]

SELECT COUNT: 1
UPDATE COUNT: 1

SECONDS = 0.010
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 12927724
STMT_HANDLE = 12927532

Delete from Audit
where Audit_Id > 468099

PLAN (AUDIT INDEX (RDB$PRIMARY21))

FIELDS = [ Version 1 SQLd 0 SQLn 0 ]

SECONDS = 0.040
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 12927724
STMT_HANDLE = 12927532
PARAMS = [ ]

SELECT COUNT: 23
DELETE COUNT: 23

SECONDS = 0.010
----*/
/*---
COMMIT
TR_HANDLE = 12927724

SECONDS = 0.411
----*/
/*---
COMMIT
TR_HANDLE = 12926800
----*/
/*---
COMMIT
TR_HANDLE = 12934344

SECONDS = 0.020
----*/


######################################################################
Attention:
The information in this email and in any attachments is confidential.
If you are not the intended recipient then please do not distribute,
copy or use this information. Please notify us immediately by return
email and then delete the message from your computer.
Any views or opinions presented are solely those of the author.
######################################################################