At 02:17 PM 16-03-01 +1300, you wrote:

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

What is "AuditEntry.RecordId"? it seems it's a RECORD structure into which you are reading some values from some external file - is that correct? If so, then I think your problem of "strange data" comes from that source. I suspect the fields in that structure have not been initialized. That's where I'd be looking - at the place where you read this structure in. You need to explicitly call Clear on the fields of the RECORD structure before reloading them from the next external record you are reading in, at least during your debugging phase, until you are certain that your ext. file is always supplying values for the fields.

And when you go to get a field value from the RECORD structure, validate it before you apply it to a parameter; and give your routine some strategy for dealing with invalid field entries.

>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: the server defaults are applied in one and only one condition: where no value at all is passed for the defaulted column, i.e. the column is ABSENT from your insert statement.

If you want to force a default on receiving NULL or some other unwanted value, do it with a trigger.

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

If you want the server defaults to be applied, omit those columns from your insert statement. If you are letting IBO decide what the insert statement is to be, through keylinks and dataset columns, then omit them from the select statement also.

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

If you have columns present AND you want to pass NULL AND you have taken care of any NOT NULL constraints, then use the TIB_Column's .Clear method to ensure that NULL will be passed; then either store NULL (if that is what you want) or handle the defaulting via a trigger.

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

It is neither necessary nor desirable to make allowances for the length and type bytes on varchars. Just define the string as "the string you want". There is nothing in what you have described that supposes the server default was getting used at all; and it should not have gotten used. Your assumption about the use of the DEFAULT constraint was incorrect.

Cheers,
Helen
(and hello Auckland from an expat!)

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________