3

We are currently using Delphi with Borland database. We are planning to move from borland to Firebird libraries, (borland lib has mem leaks).

The problem that we are having now is, Firebird library returns a char with trailing spaces, which was somehow handled borland library. We have huge code written without any trimming.

So now if 'abc' is stored in char(10) field, it returns 'abc       '. and our code fails. I was wondering if there is some configuration that I can do on the DB or firebird library to automatically trim char fields.

Johan
  • 74,508
  • 24
  • 191
  • 319
Ali
  • 309
  • 1
  • 5
  • 20
  • Try setting the `TStringField.FixedChar` property to `False`. By default, a `CHAR` field is true, meaning it's a fixed width; I think (but don't have any `CHAR` columns to test against) if it's false it will trim the trailing spaces. – Ken White Dec 01 '11 at 15:31
  • @Ken - Last time I played with it, [it had no effect](http://qc.embarcadero.com/wc/qcmain.aspx?d=65550). But this might have changed... – Sertac Akyuz Dec 01 '11 at 15:33
  • @Sertac, thanks. That's why I posted as a comment and not an answer; I wasn't sure. :) An alternate method is to use the field's `OnGetText` event to do the trimming, but that requires changes if you haven't already been using persistent fields. – Ken White Dec 01 '11 at 15:35
  • 1
    Which components are you using? For example FIBPlus has char field trimming as an option property on a dataset. – Ondrej Kelle Dec 01 '11 at 15:41
  • Is VarChar(10) syntax allowed in FireBird ? – philnext Dec 01 '11 at 18:40

2 Answers2

9

This is per the requirements laid down in the SQL standards: CHAR fields should be returned padded with spaces to the maximum defined length. If you don't want that behaviour, you need to use VARCHAR instead of CHAR.

Update: Not sure if it is helpful, but according to this message you could create a workaround if you are using IBX.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Agreed. But is there a way around for this? – Ali Dec 01 '11 at 15:29
  • Your only options are the ones described by Andrei K., or you will need to trim the string yourself in your application. – Mark Rotteveel Dec 01 '11 at 15:32
  • 1
    @Ali - if you're using a ClientDataSet set 'DisableStringTrim' to false, or have a look at 'Trim Char' parameter if you're using a 'TSQLConnection'. But these will have a global effect. – Sertac Akyuz Dec 01 '11 at 15:36
  • How can I automatically change all the char fields to varchar? Some system table change or ..? – Ali Dec 01 '11 at 15:50
  • Using [ALTER TABLE](http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-ddl-table.html#langrefupd25-alter-table). You should never make direct changes to the system tables (unless there is no DDL to perform the change otherwise) – Mark Rotteveel Dec 01 '11 at 15:51
4
  1. Why don't you just change fields types in a database from CHAR to VARCHAR?
  2. You can use TRIM() function in SELECT statement.
  3. You can put a couple of changes into IBX source codes in order to right trim CHAR strings.
Andrej Kirejeŭ
  • 5,381
  • 2
  • 26
  • 31
  • My manager would not agree with the first two options, as we don't want to spend too much time in this change. What do you mean by 3rd option? – Ali Dec 01 '11 at 15:24
  • 3
    I'm very tempted to downvote this because of the third bullet. Changing the IBX source because you designed your database is absolutely the wrong way to go about it. – Ken White Dec 01 '11 at 15:25
  • I think it is a bit tongue in cheek, and it is the only real way if the TS doesn't want to go with the first two options. – Mark Rotteveel Dec 01 '11 at 15:30
  • @Ali Why wouldn't your manager agree to the first two options, those are the only sensible solutions to this problem. – Mark Rotteveel Dec 01 '11 at 15:33
  • 2
    @Mark: It's a bad joke if it's not clear that it is meant to be one, especially when posted to someone who may be a new developer and not know it's a joke. It was posted as if it were a serious third option (which I think it was intended to be, BTW). And modifying source that's part of the VCL is a bad idea; it breaks updates because the files no longer match, and you have to also apply the hack to any new releases of the VCL in the future. Using a proper fix is almost always a better solution. – Ken White Dec 01 '11 at 15:34