Problem with trailing spaces on varchar field

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for InterBase & Firebird in Delphi and C++Builder
Post Reply
natanieljr
Posts: 13
Joined: Fri 03 Sep 2010 14:33

Problem with trailing spaces on varchar field

Post by natanieljr » Mon 20 Sep 2010 15:55

I’m importing a text file to my database trough Delphi.

In my table there is a field:
DEENDERECO varchar(150)

I use a simple insert SQL to populate the table

insert into table (field1,…, DEENDERECO,…) values (…,’street 50 ‘,…)

Sometimes, this field is showed and printed as "street 50 " instead of "street 50" on my Delphi application.

Using a database viewer (IbExpert), the field is shown as "street 50", with no right blank characters.

When I do a copy/paste in this field and post the change, the problem disappears.

When I update another record, by SQL statement, the problem persists.

View the text file in binary mode, all blank characters are code 0x20.

I'm using both, Delphi 6 and 7, with dbExpIda 2.50.0.23

I have a database and a query sample.

AndreyZ

Post by AndreyZ » Tue 21 Sep 2010 12:50

Hello,

DbxIda is showing varchar strings exactly as they are in database. If you want to trim the strings, then you can do it on the server side.

natanieljr
Posts: 13
Joined: Fri 03 Sep 2010 14:33

Post by natanieljr » Mon 27 Sep 2010 11:54

The field is a VARCHAR, it does not store trailing spaces.

When I use IBExpert it shows correctly.

I made the following test:
the same database
the same source
changed the drivername from the connection to Interbase

Everything worked just fine.

AndreyZ

Post by AndreyZ » Tue 28 Sep 2010 08:36

The behavoiur of the DbxIda driver is correct. I have tried to do the same insert SQL with the IBX components and the trailing spaces weren't trimmed. It seems that IBExpert trims the trailing spaces.

natanieljr
Posts: 13
Joined: Fri 03 Sep 2010 14:33

Post by natanieljr » Tue 28 Sep 2010 12:59

Ok.

My doubt was because Borland's dbExpress driver seems to remove all the trailing spaces of the strings to.

But if this is the driver default behavior then I'll have to change the routine.

Thanks.

Smoorfs
Posts: 2
Joined: Tue 28 Feb 2012 12:16

Post by Smoorfs » Tue 28 Feb 2012 12:57

the trailing spaces are probably no spaces (hex 20), they just look like one.

Try a CHAR2HEXINT(col) to see the actual hex value, e.g. could be '80' for LATIN.

And then use "trim (trailing '80'xc from col)"

Post Reply