• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1143
  • Last Modified:

DBExpress - Auto Trim Char Fields (Using SQL Server)

There was an option to auto trim chars fields using dbexpress connection?

The parameter "Trim Char" or "TrimFixedChar" isn't avaiable while using SQL Server drivers.

Do you guys knows if there is any way to do that?

If not possible on the connection, is there any way to do that directly on the clientdataset? Inheriting a method? (I already use a custom ClientDataSet, but, I haven't found a method that I can perform a trim while reading records)

I suppose you guys will tell me to change char fields to varchar, but, at this moment, It's impossible to change all my database datatypes to varchar (It has almost 13 years and has more than 150gb).
0
hidrau
Asked:
hidrau
  • 3
1 Solution
 
Geert GruwezOracle dbaCommented:
sorry ... bad database column type choice
converting to varchar is the way to go

besides .... you'll probably only have 30Gb in the end
the rested is wasted space

the other way ... create all calculated fields and trim the data
... but this will slllooooooowwww down your app very much
and you will be posting all the changes back to the db
>> the changes will be because of the trim
and this will be overruled in the db again by the char type
>>> back to square 1
0
 
Geert GruwezOracle dbaCommented:
oops i miss/crossposted 2 questions ... hasn't happened much before
comment in other Q should have gone here:

to make it a little more clearer
>> if you trim the data in your delphi app, the dataset will think every row is changed

any edit you still do and hit save, will post back the complete table to the database
>> overwriting any simultaneous (if possible) changes by others

i'm sorry to have to indicate that a mistake in database design costs a lot of money and time to correct
you'll have to get the bull by the horns and dig into this
>> any workarounds will probably be futile anyway
0
 
Sinisa VukCommented:
Try to create custom table view or procedure in your sql db. There you can trim using sql functions and use it in app.
0
 
Geert GruwezOracle dbaCommented:
sinisav,
didn't you forget to mention that all those views will have to made updatable using a instead of trigger on each of those views

or all the table names in the app will have to be changed to view names


my guess is that it's easiest to write a conversion program
make a backup ... just in case
>> create all tables as new_tablename
>> cast all columns of type char to type varchar
>> copy all the data over to the new tables with trim on char columns
>> script all the constraints
>> drop all the constraints
>> drop all the oldtablenames
>> rename the new_tablename to oldtablename
>> recreate all the constraints from the script

done

this can be done overnight
0
 
hidrauAuthor Commented:
thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now