VFP which SQL to use

[ashnam10]

Hello All,

I have been using native VFP database in my application and now am planning to move to use SQL for data storage
My reason to change is better security, encryption , and scalability to other platforms

Please advice as to which SQL version I should go for.
My data size would not be much.
Also easier and automated installation on client side


Please guide me as to which database to use or point me in right direction

I am totally novice with SQL

Thanks very much in advance

 

[Mike Lewis]

The most popular choice among VFP developers is Microsoft SQL Server. It has many advantages - not least its VFP-like performance - but the full versions can be quite expensive. Fortunately, there is an "express" version for light users (the actual name varies with the version), and a low-cost developer's version, which is ideal for program development but cannot be used for production application.

Another good choice is MySQL. This is very ecomomical (and possibly free, depending on the version).

I've used both of the above. On balance I prefer the Microsoft product, but if I had to carry the entire cost myself, I would probably go for MySQL.

There are plenty of other options, as I'm sure you'll discover.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Just to add a few points: MySQL is cross-platform, whereas SQL Server is Windows-only (as far as I know). MySQL is essentially open source. The free version I have used is called "MySQL Community Server" and can be downloaded from

http://dev.mysql.com/downloads/mysql/.

The free version of SQL Server 16 is called SQL Server Express. It has virtually all the features of the paid-for versions (at least, all the features that you are ever likely to use), but is limited to a total database size of 10 GB.

I haven't used SQL Server 16 myself, but have used earlier versions. I have found the Express edition quite time-consuming to install, but once installed it works smoothly.

Both products (SQL Server and MySQL) have good support: many books, articles, forums, etc. to help you get started. I have always found the official MySQL documentation to be somewhat obscure, and usually rely on articles on web sites if I need information.

The above are just my personal opinions. I'm sure others here will have their own views.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

There are several aspects to think through.

One aspect is the number of examples and the help you find. Most of VFP developers work with MS SQL Server and some with MySQL/MariaDB, Hentzenwerke has books on both backends, that is no strict choice limitation, but those two are the typical and sufficient choices. Another aspect, of course, is the destination OS and what platforms you want to address in the future. Then finally the license costs.

You hint on wanting to install client side, I assume still a VFP Windows application having an SQL backend. Then the natural choice is MS SQL Server, but you could also use MySQL or MariaDB or any other open source database with an ODBC driver to use it from a VFP client frontend application, their usage is widespread as web application backend, so it seems they need a web server, but they also can be used on premise.

In regard to license costs MS makes it easy with the express versions, they are free to use, also in commercial products and already offer astounding many features. MySQL can be used in commercial products, but I still haven't fully wrapped my head around the consequences of the GPL especially with bundling MySQL. If you look at Oracles site and their idea of licensing, you need a commercial license when bundling even just the ODBC driver. The MariaDB site has a good FAQ section about this at [URL unfurl="true"]https://mariadb.com/kb/en/mariadb/licensing-faq/[/url]

Besides these aspects, what needs can you specify? What questions do you have?
Do you use a certain VFP Framework? Then the support for MSSQL might already be in the frameworks data access and business logic base classes.

The switch to a server/client model makes a major rewrite of data access an unavoidable requirement. You only have a good basis, if you already worked with three tiers beforehand, too, but the typical data binding is two tier, VFP help and tutorials and sample code binds forms and their controls to DBFs directly, that's not a good basis for changing to client/server development.

Bye, Olaf.

 

[vgulielmus]

You can also consider Postgres.
It's free and very powerful.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

http://praisachion.blogspot.com/

 

[ashnam10]

Hello,

Thank you all for your esteemed and experienced view.


I am planning to go with VFP 9, and will have to rewrite the entire data logic as currently the app is using free tables no database
as this is evolved app from FPD 2.6 to VFP 6, to VFP 9

Olaf :
Can you please elaborate
"you use a certain VFP Framework? Then the support for MSSQL might already be in the frameworks data access and business logic base classes."

Currently the application is in VFP 6 so i can move and upgrade to any other version where in i can get more flexibiility for SQL


So from your views :

From the above discussion :

MySQL ---> Cross Platform, Open Source, Free
> Installation : Not sure of GPL

SQL Express --> Windows Platform, Free, Preferred choice of programmers

PostGres --- >I have to still review

I have few Queries :

1) How is redistribution / installation on client side in case of MySQL?
2) Can this installation be managed with installers on client machine without intervention ?
3) Does each network machine needs to install this setup or like vfp

 

[Mike Lewis]

Ahsnam,

A few points:

Regarding frameworks. The fact that you had to ask for elaboration re the use of frameworks suggests that you are almost certainly not using one, which means that you can probably ignore the issue.

You wrote that you "will have to rewrite the entire data logic as currently the app is using free tables no database". You do not have to rewrite any logic if you are moving from free tables to a database (assuming you mean a database in the VFP sense of the word, that is, a DBC). Apart from a few very minor considerations, your application will need no changes as a result of that move.

But moving to a back-end database such as SQL Server or MySQL will most definitely require a lot of re-coding. The overall logic of the application will probably not change, but much of the details of how you access your data will. That applies regardeless of which back-end you choose.

Regarding the licensing of MySQL. I am not sure about this, but my understanding is that if you embed MySQL in a commercial application, then either that application must itself be open source, or you must use a commercial version of MySQL, that is, you must pay for it.

You asked about client-side installation. With any back-end, the database itself usually sits on a server. That's the whole point of it. The client machines don't need to install the database software, but they will of course need your VFP application, which in turn needs an ODBC driver (this is the interface between the client software and database). But the ODBC driver is fairly small component that can easily be distributed as part of your application's Setup procedure.

Finally, don't underestimate the work involved in converting an application from VFP-based data to a SQL back-end. It is always more than people think. Consider carefully your reasons for making the move, and ask yourself if you can't achieve the same goals entirely within VFP.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ividoevski]

Postgresql is freeware for commercial use without any limitations.

I use this database with VFP9 and everything is OK.

Ilija

 

[Olaf Doschke]

In regard of VFP Frameworks. Well, there are lots of well-known application frameworks which simplify development by giving you basic application functionalities. As you don't seem to even know the term framework, you don't seem to use one. The most basic one is included in VFP via the FFC (Foxpro foundation classes) you use when using wizards. More to the point frameworks are ProMatrix VisualExtend, Mere Mortals...

http://fox.wikis.com/wc.dll?Wiki~FrameworkFeatureChart

Let me answer your questions in different order:
3. If you plan a multi-user application you surely will have one central database, not a database per client, so you do a central server installation. That's why it's a database server, it's not merely DBF files as in VFP, it's a server, a service on a server computer, even when installed on the workstation it remains a server, then it's just client/server on the same computer.
1. As said in regard to the answer 3, only if you want a per user database you could install a local MySQL instance per client workstation. A single server instance on a server can also hold many databases, though. And a company surely prefers data to be central for administration purposes and control.
2. Silent installations are possible in several ways, depends on which backend, see eg

https://dev.mysql.com/doc/refman/5.5/en/windows-installer-msi-quiet.html,

in general google "Database server" silent install...

In general I'd rather opt for a separate server and client installation. Server installation first, then client installations. If you have a corporation as a customer their IT operations department surely will know how to assign software, eg via SCCM. This is not a simple system, it's a multi-user application and even with VFP applications based on a DBC on a file server I never did a setup installing that, too. As this is a one time prerequisite step you can also do it personally once. Of course selling a noncustom product you'd offer setups. But take a look at the sql servers themselves, there is a part that is the server itself and there are client side tools. Express version come in bundles with both, as you typically install them for development, tests or single user, but aside of the database there are the drivers needed to connect to the server from clients, so that's minimum part of a sql server installation and typically also is separate from each other.

Bye, Olaf.

 

[Olaf Doschke]

In regard of the unsureness about what license you need for MySQL, the compatible MariaDB has more straight forward ideas about its free software state, especially with the MariaDB ODBC driver you are free to use that in commercial software packages as the faq says. Just read that, it's worth it. It also says a bundling of open source databases is clearly free, if your software can run on several of them. That makes each database an optional component and the GPL even in its stronger form is clear about that.

For a company already running an MSSQL Server, even perhaps an enterprise edition, an additional database in an instance might be smaller in costs, including operations and administration than a separate SQL database, even if it's free. Database servers are never as maintenance free as DBF files are.

Bye, Olaf.