Hi Geoff,

Firstly, thanks for your help!!

Here are a couple more details... I have made the program do an insert
only... Here is the code...

with datLabSys2.curContact do begin
Close;
ParamByName('Contact_Id').AsInteger := AuditEntry.RecordId;
Open;
if eof then begin
Insert;
FieldByName('Contact_Id').AsInteger := AuditEntry.RecordId;
FieldByName('Name').AsString := ''; //'AuditNew';
FieldByName('In_Use').AsString := 'T';
Post;
end;
end;

What this does is add a new record and set any non null fields that don't
have defaults on the Server.
The curContact contains a 'SELECT * FROM ct_Contact WHERE Contact_Id =
:Contact_Id'.

The curContact has CheckRequired set to False, if set to True it does report
an error (when set to False, no error is reported which is why I assumed
that the Server defaults were getting applied.) No other defaults are
specified on the client side. Also, I tried selecting a few of the fields
rather than select * and as you thought, only those fields which I had
selected in the SQL statement and that I didn't set in code ended up with
the strange character.

So, so far I have ascertained that the strange data does end up in the DB
from an Insert (i.e. it is still there when I don't do an edit...)

Also note that the code above has been ported over from using the BDE...
when using the BDE none of the strange characters appear...

Also... and this may give away more info to someone... (not to me, but I
recall you saying something about the first 2 characters!)... I tried making
the default on the database a longer string (e.g. '0123456789') and what
ended up in the database after an insert was missing the first 2 characters
(e.g. '23456789'). I am assuming that when the default is '' something is
trying to read the string a couple of characters later which is garbage.

If this does not give anything away, I could develop a small test app as
requested.

Regards
Colin

-----Original Message-----
From: Geoff Worboys [mailto:geoff@...]
Sent: Friday, 16 March 2001 11:42 am
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] Strange Data in my inserts


Hi Colin,

> 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?

If the dataset CheckRequired property is true (the default), then IBO
itself will detect the null fields and give you an error. If
CheckRequired is false, the null fields will get through to the server
and the error will come from there.

> I was assuming that the table defaults would spring into
> action... when I have defaults on a field,

If you have entries in the dataset DefaultValues stringlist property,
these will be applied to inserted records, but defaults defined at the
server will not be used unless the field itself is NOT part of the
insert.

> 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.

Which just proves that the data you are seeing in the monitor is
really there (so it is not a monitor bug :-)

> 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.

This leads to lots of questions :-)

How do you select the record for update? That is; Firstly where do
you get your key value from. Secondly I do not see the
select/retrieve of the row to be edited in the monitor output - you
can turn on the Row option of the monitor to see individual rows
returned from the server. This should demonstrate whether the rows
are being returned with these strange characters.

Perhaps you can see the curious logic here. If the problem is
happening on update, then the NOT NULL fields must have contained
something originally. So that data should be returned when the record
was selected for update. So either the row was updated by your
processing OR the row contained the strange data to begin with.

You say you are reading from file, is this an external table defined
to Interbase or is it truly just a file? (The "Select Max(Audit_Id)
from Audit" made me wonder.)

Have you checked the contents of the audit log? If it was written
from UDFs in interbase triggers, make sure the UDFs did not write the
2byte size prefix that accompanies all varchar fields.

Are you using variants at all? eg. Reading the value from the audit
log into a variant and then assigning to the column AsVariant. Should
work, but variants are interesting things :-)


Is it possible for you to reproduce this in a sample application and
database and send it to me privately? (geoff@...)

Geoff Worboys
Telesis Computing





Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/



######################################################################
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.
######################################################################