OLE Database access

52 views
Skip to first unread message

Julian Ford

unread,
Sep 10, 2019, 9:58:02 AM9/10/19
to VA Smalltalk
Hello...

As part of the services I offer to my clients, I migrate their existing data, when they switch to my
software.

I have just run into a system that uses Visual FoxPro.
The ODBC drivers do not work on Windows 7 or later, but I was able to read the
database using the ODBC driver, on a Windows XP machine.

Apparently there is an OLE driver available for VFP, which is supported on newer
OSs.

Has anyone tried connecting to a database using an OLE driver?
I have only ever used ODBC.

Thanks in advance for any responses...

Regards,
Julian Ford

Joachim Tuchel

unread,
Sep 10, 2019, 10:24:30 AM9/10/19
to VA Smalltalk
Julian,

it may sound silly, but did you try to use the old driver to first move the data as is to another database that is still supported on Win 7 (and 8 and on), like Access, SQL Server or maybe eben sqlite? You could then do your massaging for the actual migration using odbc as always...

This is probably easier than trying to nail some db driver functionality onto VAST...?

just a thought

Joachim

Julian Ford

unread,
Sep 11, 2019, 10:19:09 PM9/11/19
to VA Smalltalk
Hi, Joachim....thanks for the thought.

The problem is that the database is file-based....1 file per table.
And there are over 100 files.  So, yes, what you suggest would work,
but I would have to convert the 100+ files now, as I am writing the migration
routines, and then again when we actually switch the data to go live.....
A BIT time-consuming.... lol

So, I am going to do the development on XP.  It just means I also
have to use an older version of VAST, with an older object model.
So, once I have finished migrating into MY database, I then have to
run a migration on a current version of VAST, to bring it up to date.

I was just hoping there might be a way to skip all of this, and connect
directly from a current VAST image.

Thanks again...

Julian

Norbert Schlemmer

unread,
Sep 12, 2019, 2:17:51 AM9/12/19
to VA Smalltalk
Hi Julian
as Joachim mentioned and I'm sure you will get the "best solution" going this way:
.) export from VFP to (maybe) CSV files @Windows XP
.) import into a database running on Windows 10 / Windows Server 2019 like Oracle 18c XE , PostgreSQL, ... (using NeoCSVCoreAp)

Running the export and import functions / scripts will take a few minutes and can be done once a day...
Using VA 9.x on Windows 10 is great...

A RDMS like Oracle, PostgreSQL, ... supports constraints, views, ... and you are able to improve the database model

What's the size of the FVP database ?

Regards
Norbert

Joachim Tuchel

unread,
Sep 12, 2019, 2:52:57 AM9/12/19
to VA Smalltalk
Julian,

while I think your approach will work as well as the one I suggest, your approach sounds much mor painful to me...

The way I understand your initial post, there is an ODBC driver on XP that you can use to acess the VFP files via SQL. If so, why not write (in Smalltalk or anything else) a 1:1 table copy job from VFP to some Database server that you cann access from today's VAST, like Oracle or DB" or whatever. This 1:1 copy job should be a no brainer.

Another option: I know DB2 can load data from ASCII (think CSV, but of course a bit different) files, and I guess none of the other doesn't ship with tools to do that.

This way you'd only need a way to copy data out of VFP (no massaging of data at all) to a current database and the rest is just your usual data migration. Am I wrong?

Joachim

Joachim Tuchel

unread,
Sep 12, 2019, 3:00:26 AM9/12/19
to VA Smalltalk
Julian,

as always, my brain starts thinking the moment I clicked "send"...

You wrote there are OLE DB drivers for VFP that run on the latest windows versions. So maybe you can even do the copy step using some scriptable* Database Clients (squirrel SQL** comes to mind, but there are others) to do the data copy on top of that OLE DB stuff from VFP to a current database (really, just row by row, table by table, no transformations) and then do the migration in VA Smalltalk in this new (but intermediate) database.

Does this sound reasonable?

Joachim

* i say scriptable, because that way you can automate the copy job and replay (and refine) it at any time
** not sure this particular tool supports OLE DB, but it does support JDBMS, maybe that is an alternative available for VFP?

Peter Ode

unread,
Sep 12, 2019, 4:02:27 AM9/12/19
to va-sma...@googlegroups.com
Here's a tool that might help you quickly convert your FoxPro database to something more current:


They have FoxPro support and the ability to do: 
(a) one-time conversions, ro 
(b) ongoing updates from one database to another.


...
Peter Odehnal
778 338-4800



--
You received this message because you are subscribed to the Google Groups "VA Smalltalk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to va-smalltalk...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/va-smalltalk/781bad76-2ca3-48e2-a22c-451b2a0c92da%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages