Trimming spaces from strings returned from a query?

[cosine]

Hello All,

I am working with base/writer in a simple mail-merge application. OOo 3.1.0 running on Windows Vista SP2.

The database defined in base fetch data (mostly strings from a CHAR table) from a FoxPro free table directory (via ODBC) and writer then use the data returned from the query to populate a table.

The query works fine and I have no problem using the data in writer but I cannot trim the strings so the writer document contains a lot of trailing spaces.

The query I use (in base) looks something like this:

select name,addr from contacts

I have tried various variants of trim:

select trim(name), trim(addr) from contacts
select ltrim(rtrim(name)), ltrim(rtrim(addr)) from contacts
-- trimall is FoxPro specific
select trimall(name), trimall(addr) from contacts

but the strings are still returned to writer "untrimmed". The data comes from a CHAR (not VARCHAR) table and I cannot alter the FoxPro schema. I'm using the latest FoxPro ODBC drivers (from MSDN).

Has anyone else here had any problems trimming strings in base?
Is there an obvious mistake I'm making in the query above that I'm not seeing?
Apart from the SQL trim/ltrim/rtrim functions is there an other way to manipulate strings in base before passing the data on to another OOo application?

Regards,

Toby

[squenson]

Cosine,

Cross-posting is strongly discouraged on this forum.

[cosine]

Cosine,

Cross-posting is strongly discouraged on this forum.

Yes, I can imagine. You have no doubt noticed that I asked a similar question in the writer forum.
However, no consensus was reached there and I was asked to try the base forum instead.

[DrewJensen]

By any chance does trim(both name) ... make a difference -or is that rejected w/foxpro odbc?

[cosine]

By any chance does trim(both name) ... make a difference -or is that rejected w/foxpro odbc?

This is rejected. The ODBC driver reports "too many arguments".

Thank you for your suggestion though.

[DrewJensen]

Well - I don't have a foxpro driver...hmm...suppose I do...but assuming it is not working..then

Only thing I can think of is a script that runs against your newly created file(s) and trims the strings in the document tablem as a post processing step to the mail merge..

That's aufully convoluted...maybe someone will pop in with a foxPro tip...else maybe t needs an Issue opened, but that doesn't get you any relief now does it.

[cosine]

That's aufully convoluted...maybe someone will pop in with a foxPro tip...else maybe t needs an Issue opened, but that doesn't get you any relief now does it.

Well, it would work. I suppose a could write up a regexp and use search/replace in a macro to clean things up before printing.

I guess, for now, I'll just assume it's a ODBC driver issue.
I'll wait some more just in case anyone here has more experience in working with base+FoxPro/ODBC.

[DrewJensen]

might not to write all that - look in the shard basic libraries for "Tools" in the string module - if all you want to do is trim leading and trailing spaces there is already decent trim function - your code would be mostly Open - grab table - walk cols/rows

[acknak]

if all you want to do is trim leading and trailing spaces there is already decent trim function - your code would be mostly Open - grab table - walk cols/rows

If I follow you correctly, this would happen in the Writer document, after merging the data, right? If so, will the standard functions work on the database fields; i.e. these won't be plain text at that point.

It would be nice if Writer had a built-in way to convert fields to plain text by replacing the fields with their content.

I sent cosine over here because it seems like the database is the right level to deal with the problem. Since TRIM is such a standard function, I though it should be available, but I don't know what functions are provided by the various layers. Maybe you only get TRIM if you're connecting through a native driver? Can the ODBC driver provide such functions? I thought ODBC was just a simple connection API, but I really don't know anything about it. If ODBC can provide the function, maybe it would be possible to fix the bug--or add the function--and improve the FoxPro support.

[DrewJensen]

It seems that TRIM is simply broken on this connector.

Hadn't thought about the fact that it would be a field int he Writer file and not just text....still I think that can be overcome. If that is where he wants to go with this.

I'll be a week without access to any MS OS so can't help troubleshoot the foxPre driver - but will have a linux NetBook with me..so could still play with the Writer side.

[cosine]

Hello All,

Well. I have found a "fix" (for vague definitions of the word 'fix').
It may not be suitable for everyone.

In the workplace (apart from the large collection of FoxPro table-files) we have an installation of MS SQL Server 2005.
MSSQL lets you define "linked servers" which functions as a sort of "communications layer" between different data-collections but are managed from a single SQL Server.

This is what I did:

* Make a "linked server" i MSSQL 2005 pointing to the collection of FoxPro-files (using FoxPro OLE DB, not ODBC).
* Set up "global read-rights" on the linked server (in MSSQL) for a special user.
* Create a System DSN on the client (MS SQL ODBC) pointing to the SQL Server without specifying a "default database".

In "base" I can now query the FoxPro tables via MSSQL/ODBC (via the linked server) using the special "..." operator:

select rstrip(name) as name, rstrip(addr)as addr from LINKEDSRVNAME...foxpro_table_name

Since the results from the FoxPro tables gets stored in a temporary table in MSSQL the rstrip function gets executed by MSSQL and not the FoxPro ODBC/OLEDB driver. I now get "stripped" strings in my "writer" document.

DrewJensen mentioned the word "convoluted". Well, here it is: the Rube Goldberg device of FoxPro connectivity
Documenting this won't be easy...

This doesn't fix the FoxPro ODBC driver-issues of course (which I still think is the *real* problem).
Also. If speed is important in collecting the data this is *not* a good solution. When a query grows beyond the simplest kind of "select" it slows down by a factor of... a lot.

Thank you DrewJensen and acknak for your help and suggestions.

If you have any questions post them here and I will try and answer them to the best of my ability.
I'll experiment some more with my old setup to see if I can confirm that it is the FoxPro ODBC driver and not just some silly mistake on my part.

I'll wait a while and then mark this as "solved".

/Toby

[DrewJensen]

One question - Have you tried connecting to the FoxPro files using OLEdb from Base directly?