ODBC or dBase III standard for 3rd party users?

[SitesMasstec]

Hello colleagues!

I have a free VFP9 table with client records (CLIENTS.DBF) (containing names, their addresses, etc).

1) Can I merge these data in other software which uses ODBC to link with my table? How to do this?

OR

2) What about if I convert the VFP9 free table (CLIENTS.DBF) into another dBase III standard table (CLIENTS2.DBF)?
I will put in my VFP application the command:

USE CLIENTS
COPY TO CLIENTS2 FIELDS EXCEPT COBS2 FOX2X
* COBS2 is a Memo field

I think this will be a clear approach for users (There will be a button "Transform clients records into standard dBaseIII file")

What do you think about these 2 options?

Thank you,
SitesMasstec

 

[Mike Lewis]

Both those options are possible.

I'm not sure what you mean by "merge these data in other software which uses ODBC". But, in general, ODBC lets you retrieve data from remote databases in the form of a VFP cursor. At its simplest, you use SQLCONNECT() and SQLEXEC() to fetch the data into a cursor, which then behaves more or less like a (temporary) VFP table. You can then copy the data to a permanent VFP table, or you can use it in queries, reports, etc.

That said, there is no point in using ODBC if you only want to share data with dBASE III (is that really what you want to do?).

VFP can read DBF files from all versions of dBASE, FoxBase and FoxPro without any special programming. It can also write to those files, using COPY TO ... TYPE FOX2X, as you indicated.

It's been years (decades?) since I tried to use VFP data in dBASE, and I can't honestly remember how well it works. But it is certainly possible to share it with Foxbase and Foxpro 2.x.

Does that answer your question?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Other clients than FoxPro can access FoxPro data via the VFP ODBC driver up to version 6 DBFs and OLEDB Provider for any newer DBFs.

The connection strings vary for a DBC or a directory of free DBFs, for that matter, you don't need to go back to dbase or fox2x dbfs, you just limit yourself to field data types, that only have the field types VFP6 already supported to enable using the DBFs from other clients. That includes the most typical ones, it's not a big restriction, You can use character, integer, float, date, datetime, memo, currency and some more, but likely that already is sufficient. No varchar, varbinary, blob, but for binary data you have memo with nocptran option.

If you don't already have the ODBC driver download, MS has stopped providing the download. The OLEDB provider is the better option anyway, but often, eg in the Office products, OLEDB usage aka ADO has not the same options. Eg you can have linked tables in Access only via ODBC, you need VBA code using ADO to make use of an OLEDB Provider, but it's totally possible and VFP OLEDB Provider comes with sample data and sample code. It's not much of your concern, but sadly most usage is not even by developers but Excel users dipping into the data world with sheets having a data source and that's all ODBC based.

There is a better way, the "royal" way to provide data for others, that is export it to a database any client can connect to and that's MySQL Server or MS SQL Server and some others, that can also become your basis for shared data your FoxPro application also uses instead of DBFs, Yes, that needs some more changes to existing code, but that opens up much more possibilities eg putting the data into the web or even a cloud - GCP, Azure, AWS, IBM or others.

And then, last not least, you keep control on the data access not only via limiting file visibility or granting database connections and read permissions but put up another layer, a web service or REST API that provides data in form of XML and JSON. That is the norms also for web clients and besides control over access at all, you can also limit the number of requests, and data volume fetches etc. by providing that level of API access.

OK, that was getting a bit off topic, I don't know whether you just want to enable someone internal non-developer to get at some data or if you plan to sell your data to customers or provide an devleoper interface or whatever is the overall goal.

But I can end up at a round trip with XML and JSON, you can't just send these formats as HTTP response bodies, you can of course also save XML or JSON files and also the most simplest and oldest format for data exchange, CSV files, if you just ask for data export formats other developers and applications can process most easily.

If you really want shared data acces without the need to move data back and forth between your own application VFP9 DBFs and another database (server) fox2x or dbase II might look tempting, as you can also bind VFP controls to it, reccylce code, but you put restrictions on yourself and you only get a partial acccess to your data from others, eg only within your LAN. Opening up your network to the public is not a good solution once that needs more public access. Growing your business today means going online and that means putting your data into an online database server with an APi put in front for controlled access. It all works more complicated than DBF files, but it also has more options and control.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[SitesMasstec]

Hello colleagues!

Sorry, I was not clear with my question. Let me picture it:

- I have a VFP9 application which uses customer data (name, adrress, etc) The table is a VFP9 with many fields.

- The application is used by people who are not programmers, just "members of the public".

(this example is just to simply illustrate the problem, as the application already prints labels from inside the VFP application
- These users may use a program (please see picture above) to print special labels with some fields from customers'records (CLIENTES.DBF).

But in the example above, the 3rd party program (Avery LabelPro) does NOT read VFP tables, so my application will convert CLIENTES.DBF to ETIQUETA.DBF, created with:

USE CLIENTS
COPY TO CLIENTS2 FIELDS EXCEPT COBS2 FOX2X
* COBS2 is a Memo field

I just want to provide the user with a dBase III standard file, for use in other programs.

Is my approach correct?


Thank you,
SitesMasstec

 

[Olaf Doschke]

FOX2X means foxpro 2.x format. That isn't dabse III. If it works anyway, it works anway, you can just try.
But there should be other ways to feed data into Avery LabelPro, don't just get fixed on the dbf file ending, it's much simpler to create csv or feed any other database Avery LabelPro can read from.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Yes, I agree. A quick glance at the specs suggests that Avery Label Pro can read several different types of "database" (as per the "Link List to Design" strep in the wizard). Choose a file format sucn as comma-delimited (CSV) or Excel, both of which can easily be created with VFP's COPY TO command.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[SitesMasstec]

Yes Mike, I will create the CSV type also, as you suggested.

Bellow are the available types :

Thank you,
SitesMasstec