Need a solution and I am clueless

[billfaith]

Hi all

I am trying to use odbc to pull information from a progress table to UPS worldship. My problem is that my fields are all named with dashes ie.(ship-to-name) and UPS will not let you map fields with dashes. What I tried was to pull the information into access and rename the fields. This works but I cant figure out a way to make it live. I would have to re-query the progress table everytime I added a shipping record and that defeats my purpose. I tried linking the table but I access wont let you rename fields on linked tables. Renaming my fields in the progress database is not an option either. I need some middle tier with different field names. If anyone has any ideas please shoot them at me. Thanks.

 

[jongpau]

One way of doing it would be to create one or more interface tables in your Progress database that do not have any dashes in table and/or field names. Then create write and delete triggers in the original table(s) that automatically populate the interface table when records are written or deleted. That way you can use your ODBC to access the interface table(s) instead of the original with the dashes and the whole thing should work automagically (all changes in the original tables would be reflected in the interface tables).

The backside is of course that the trigger code has to be executed when you write data in or delete data from the original table, which means some extra db access etc...

 

[Progress_Guy]

Hi there,

I had a similar problem.

I know this solution may not be massively popular with most Progress guys, but, these days you have got to use whatever tools are at your disposal

I was using a gateway product (via ODBC) to synch. up to our Progress databases and drag data away which was used to populate an application held on Pocket PC hand held devices.

I hit the same problem and devised a work around which was to create VIEWS on the tables (SQL syntax). Can be slightly difficult to administer, but, it does the job.

In the Progress tram lines - best to save these in a command file which can be modified in the future :

DROP VIEW BusinessAreaDbases. --> errora if already there
CREATE VIEW BusinessAreaDbases (Business_Area,DBase_Name)
AS SELECT
Business-Area ,
Dbase-Name
from Business-Area-Dbases

We now don't use -'s in any names in new databases - lol

View are much more powerful then just this. Fields can be completely renamed to make more sense to users in the context which they are working, a where clause can be placed on the SQL to, say, restrict the user to only viewing the record(s) they are entitled to see and multiple views can exist on the same table, so, on a sales table you could have a view for each salesman/woman and only return their own figures. You may need to put pub (public) in front of the sql, e.g. from pub.Business-Area-Dbases.

I suggest you go on to Progress web site and read up on access rights/securities and views.

Let me know if you need further help or any further examples.

Cheers,
Dave.

 

[StefGay]

Hi,

our db schema is as yours (with dashes). You can create a view like this on all the tables you want to deliver to UPS:

create view NoDash (Fld1NoDash,Fld2NoDash) as select "Fld1-No-Dash","Fld2-No-Dash" from "No-Dash"

Take care that if you change something on the table No-Dash you have to drop/recreate your view.

Hope it helps.

Stéphane.

 

[StefGay]

Oooops too late, congratulations Dave you're the winner. Same solution but faster writing.

Regards.

Stéphane.

 

[Progress_Guy]

lol. Just.

Is a neat way around an awkward problem.

 

[billfaith]

Thanks

Thank you progress_guy, stefgay and jongpau!

I will read up on views and see if I can figure it out. As far as your solution jongpau, its probably above my head. I am not a progress programmer. We use an application built on progress but our vendor writes/controls it. It would be nice if I could do some small things myself someday though. Thanks again to u all tho..

 

[Progress_Guy]

No probs.

If you want to send me details of the table(s) that are causing you problems I'll create some trigger code for you and email it back with step by step instructions.

Take care.

 

[legionar]

Dashes ...

Try: example\-with\-dashes