Adam

>> UPDATE table SET field = '1234567890'
>>
>> will fail if field is a VARCHAR with a length less than 10
>
>Shouldn't it fail? (ie - the value given is larger than the size of the
>varchar field)?

It doesn't in DBISAM, or using a table component, they crop the value so it fits. ie with a VARCHAR(5) it would insert '12345'

>> will leave the trailing blanks in place.
>
>As I would expect too...

Why?

>But shouldn't Update table SET field = '12345' work on a varchar(10)?

yes it does

>> They're ignored in an SQL comparison (ie SELECT * FROM table WHERE field = '12345' will work) but will cause a fail in a Delphi comparison!
>
>Not sure I follow you by what you mean with a 'Delphi Comparison'?

In code if tablefield.asstring = '12345' will fail you need if tablefield.asstring = '12345  '

So someone's working from a printed report and types what they see into the computer .....

In earlier incarnations of ElevateDB its SQL would also have failed the test. Trailing blanks in a VARCHAR are just stupid (they're stupid in a CHAR as well but I can understand the logic of leaving them in there).

So for VARCHARS we now have the situation of the database stores the trailing blanks but ignores then in comparisons but the programming language used in the UI part of an app takes notice of them - YUCK!!!!!!

Roy Lambert [Team Elevate]

Hi Roy,

>>> will fail if field is a VARCHAR with a length less than 10
>>
>> Shouldn't it fail? (ie - the value given is larger than the size of the
>> varchar field)?
>
> It doesn't in DBISAM, or using a table component, they crop the value so it fits. ie with a VARCHAR(5) it would insert '12345'
>
>>> will leave the trailing blanks in place.
>>
>> As I would expect too...
>
> Why?

Well - I guess that auto-trimming would be OK, but I'm happy for an
exception to be raised if the value won't fit in the field. (So the user
knows that they have lost data).

Admitidally I would prefer it more like DBISam (as you do) becuase
that's what I'm used to - but I can live with it either way.

>>> They're ignored in an SQL comparison (ie SELECT * FROM table WHERE field = '12345' will work) but will cause a fail in a Delphi comparison!
>>
>> Not sure I follow you by what you mean with a 'Delphi Comparison'?
>
> In code if tablefield.asstring = '12345' will fail you need if tablefield.asstring = '12345  '

You almost gave me a heart attack Roy!

I just tried that in Delphi, and it's working fine. (On VARCHAR fields).

ie: I have the value "MIC" in a VarChar(10) field, and typed in

if MyTable1Field1.value = 'MIC' then
 beep;

Which works OK.

Alternatively, if I type in
if MyTable1Field1.value = 'MIC       ' then
 beep;

Then beep does NOT occur (which I would expect).

Is this possibly something that happened in earlier versions of EDB that
has now been corrected?

Cheers

Adam.

Adam

>Well - I guess that auto-trimming would be OK, but I'm happy for an
>exception to be raised if the value won't fit in the field. (So the user
>knows that they have lost data).

I'd agree for some things BUT where you're catenating stuff together and you don't care if a bit falls off the end its a pain.

>Admitidally I would prefer it more like DBISam (as you do) becuase
>that's what I'm used to - but I can live with it either way.


As can I, especially since with my modded components it does it the DBISAM way

>You almost gave me a heart attack Roy!

I'll test again in case things have changed, but you have what I said the wrong way round.

Data in field = "MIC " ie trailing space here

Test in Delphi field.asstring = 'MIC' no trailing space in the data entered to test against

Roy Lambert

Hey Roy,

>> Well - I guess that auto-trimming would be OK, but I'm happy for an
>> exception to be raised if the value won't fit in the field. (So the user
>> knows that they have lost data).
>
> I'd agree for some things BUT where you're catenating stuff together and you don't care if a bit falls off the end its a pain.

Indeed...

>> Admitidally I would prefer it more like DBISam (as you do) becuase
>> that's what I'm used to - but I can live with it either way.
>
> As can I, especially since with my modded components it does it the DBISAM way

It's great we can do those things, ey.

>
>> You almost gave me a heart attack Roy!
>
> I'll test again in case things have changed, but you have what I said the wrong way round.
>
> Data in field = "MIC " ie trailing space here
>
> Test in Delphi field.asstring = 'MIC' no trailing space in the data entered to test against

Sorry - I'm really confused now. Are you saying rather that if you want
the value stored as "MIC   " that it trims it to "MIC" - coz that's the
one thing I didn't try.

Cheers mate

Adam.

Adam


>Sorry - I'm really confused now. Are you saying rather that if you want
>the value stored as "MIC " that it trims it to "MIC" - coz that's the
>one thing I didn't try.

This is where it gets confusing I may be wrong but my understanding is that if you use a TDataset derived component it will be trimmed, if you use SQL (eg unbound controls) then it won't trim it.

field.asstring := 'MIC    ' will be trimmed

UPDATE table SET field = 'MIC    ' will not be trimmed

I'm sure Tim will correct me if I'm wrong.

Roy Lambert

Roy,

<< This is where it gets confusing I may be wrong but my understanding
is that if you use a TDataset derived component it will be trimmed, if you
use SQL (eg unbound controls) then it won't trim it. >>

You're mixing up "trimming" with "truncating".  If you put a value into a
TStringField in Delphi/C++Builder that is longer than the defined length of
the field, then the VCL layer will truncate the value before it ever reaches
the EDB engine.  Therefore, you won't see a truncate error.  However, if you
try to do the same with SQL (or .NET/ODBC/PHP), EDB will raise a truncation
error message.

None of the layers in EDB automatically right-trim CHAR/VARCHAR columns.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>You're mixing up "trimming" with "truncating".

I was actually mixing up languages - English and Delphi so I was using trimming in the sense of "trimming off the fat" as opposed to Delphi's "remove training blanks"

>If you put a value into a
>TStringField in Delphi/C++Builder that is longer than the defined length of
>the field, then the VCL layer will truncate the value before it ever reaches
>the EDB engine. Therefore, you won't see a truncate error. However, if you
>try to do the same with SQL (or .NET/ODBC/PHP), EDB will raise a truncation
>error message.
>
>None of the layers in EDB automatically right-trim CHAR/VARCHAR columns.

Thank you. That explains it a lot better than I could.

Roy Lambert