Visual foxpro with some other database ODBC less

[Drsasa]

Hi all,

I would like to know is it possibile to connect from vfp to some other database but without odbc or oledb drivers.
Basicly to be portable, without additional installs of some drivers except vfp runtime.

I have complete DataWarehous solution but after some time my app bumped in wall with 2gb limit and some
fact tables just need more space. I tried with spliting in more tables but its not ellegant and consume much
more time for etl's and reports.

Thanks in advance.

 

[Olaf Doschke]

So the short answer is no. What is so difficult about a parallel installation of an ODBC driver or OLEDB provider?

Bye, Olaf.

 

[Drsasa]

Thanks for answer.

Its not problem install but more like visit over 2000 clients who use software for intalls additional
software(odbc/oledb driver). At least what you think i should choose for database from view of
speed of drivers and large tables ?

Thanks in advance.

 

[Mike Lewis]

Drsasa,

Welcome to the forum.

You don't have to physically visit each of your clients to install an ODBC driver. It's possible to create a Setup program to install it for you. You can do that with Inno Setup, InstallShield, or something similar. Just send the Setup program to the user and have them follow the prompts.

A bigger issue is actually installing the database. Depending on what database you choose, it's likely that the user will have to install it themselves, and also have to deal with purchasing the product or obtaining licences. Even if you find a way of avoiding that, you would also have to deal with the transfer of data from your DBF files to the new database.

Also, be aware that converting an application from native DBF files to a back-end database involves far more work than setting up a few remote views. You'll have to deal with differences in data types, indexing, SQL Syntax, and quite a lot more. That's true whether you use ODBC or some other method of access.

I'm not saying you shouldn't do it. I'm just trying to point out that having to install an ODBC driver is likely to be the least of your problems.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

In regard of what database to install.

I only have detailed experience with Access DBs (which even fail before DBFs), DBC/DBF of course, MSSQL Server and MySQL Server. I have medium experience with SapDB/MaxDB. I worked a few days on Ingres/Oracle PL/SQL, it's not hard, mostly the field types differ a bit and sql dialects are differing. So any RDBMS having storage limitations far beyond what you need is viable.

The question is, what your customers' budget ist. MSSQL only is free in Express versions, which have an overall limitation lower than what you can do with DBF, MySQL also isn't free to use commercially.

In regard to installations, for example you can only bundle the MYSQL ODBC Connector for free, if you put your software under the GPL, too. But of course you can make use of a preinstalled MySQL ODBC driver. That's going into legal greyzones.

MSSQL offers a dedicated way of storing data in OLAP Cubes, which you can also update almost live, once you have defined the needed SSIS packages.

It depends, if it's worth the price and if you are willing to learn to do datawarehousing with T-SQL and SSIS. Both your need for large DBFs and faster native access then will vansih, because you would only need to transport the original data to MSSQL, not the blown up fact tables. The transformation from OLTP database to OLAP cubes will then be done within SQL Server.

There is a MySQL spinoff compataible to MySQL from the original MySQL developers, called MariaDB, but that would also be GPLed. If you want royalty free data storage, there is not much more available than DBFs. You can only go to less good formats like txt, xml.

Bye, Olaf.

 

[Drsasa]

Thanks all for answers, will try some of appointed ways and see what will suit most.

 

[Olaf Doschke]

In regard to data transfer, of course I forgot to mention one very common way: Textfiles, CVS or other formats, are of course easy to create with VFP and fast to import. That would also circumvent the need to transfer fact tables via some driver.

Anyway, the strategy to fill in the source data and transform it to fact tables within the destination database system would be the fastest in any case.

Bye, Olaf.

 

[Mike Lewis]

Olaf's mention of Microsoft Access reminded me of an alternative to ODBC. Microsoft Access - and possibly some other databases - can be accessed via COM. I did that a long time ago with Access, but can't remember any of the details (or why I did it). But I do know that it's do-able (but probably slow).

You can also access SQL Server via COM - they call it SQLDMO - but I believe it's now deprecated, so probably best not ro rely on it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[craigber]

You're fooling yourself if you think other databases can't match the speed of VFP. SQL Server for example, is similar or faster.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Mike Lewis]

You're fooling yourself if you think other databases can't match the speed of VFP.

I'm not sure who that remark is addressed to, Craig. I don't see any mention of speed in this thread - except my point about COM access being slow; but that's because it's COM, and has got nothing to do with the choice of database.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Drsasa]

Maybe Craig but since other DB will mainly be storage for bulk inserts and primary selects from it and nothing more,
so far i didnt found exact or similar results.

For example bulk inserts in one table 500000 rows of 5 colums in fox is ~0.5 sec, via ODBC
in some other DB best i got is ~8sec. Again from VFox not some other language.

Ofc, if you know better way i would like to hear.

Thanks again to all.

 

[Olaf Doschke]

Drsasa therefore already is considering another RDBMS for the data warehouse storage and of course also it's final usage. The problem is just to fill the fact tables from outside. So the speed of the external database isn't argued, is it?

If the problem is the large volume of the data, after it's transferred into data warehouse form, that transfer can be minimized by transferring the original source data instead (which presumably is within DBFs), then gnerating the data warehouse within the external RDBMS. But that would be a major rewrite of the data warehouse generation code.

Bye, Olaf.

 

[craigber]

@Mike: See Drsasa's second post.

@Drsasa: When doing bulk inserts with SQL Server, do you use the BULK INSERT command?

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Drsasa]

@Craig Didnt try that but will give it a try.

@Olaf ahh didnt think of that but that could be even better (staging area to sql server and do rest transforms by server). Yea will be lot work but for long run think is best solution. Think will start working on that.

Thanks again all.