How to create a MS SQL Server connection

Although Visual FoxPro includes a fast database you can hardly compare it with robust databases like MS SQL Server, PostgreSQL, Oracel, Sybase etc. Why? No security, any user can open your data with an ODBC driver and Excel. Of course you can get third-party products to encrypt your data but that influences perfomance and there are more reasons like data corruption, tablesize limitations, scalability issues etc.

Visual FoxPro integrates very nicely with MS SQL Server so many VFP professionals have started using the MS SQL Server database a long time ago. Of course now that you are developing in Servoy it makes sense to at least experiment with PostgreSQL.

I have no doubt about the professional capabilities of PostgreSQL as a database but have encountered some strange behaviour and especially lack a good user interface like SQL Servers Enterprise Manager/SQL Server Management Studio. The best alternative I have found so far is NaviCat Premium.

Then again, if you are used to MS SQL Server and want to keep on using it, there is no reason not to (of course some may say that Microsoft is a good reason not to ;-) but I am not into MS bashing. MS SQL Server is a good product, it's fast and user-friendly. Some will say it is expensive but it's price is scalable as well, from zero to thousands of dollars/euros, depending on the needs of your client.

I have a subscription to Microsoft Action Pack which gives me all their server software including the Entrpise edition of SQL Server for something like € 350 euros. Enough already, how do we set it up? I assume in this walkthrough that you have a running version of MS SQL Server on your computer or a server in your network that you can connect to.

Locate the Database Servers node under Resources.
Right-click the Database Servers node and select Connect to existing database
From the popup-menu that opens select MS SQL (freetds)

You may have noticed that there are two MS SQL drivers but the MS SQL (freetds) driver is the one to use. I believe the MS SQL driver is a native Microsoft driver but I couldn't get it to work and the MS SQL (freetds) driver works fine and is blazingly fast so don't worry about it.

Fill the following popup with your server details:

If you want to connect to a SQL Server that is on the network instead of on your development machin then you shoud replace localhost with the servername or IP-address. Also replace 'big' with the name of your database. If you don't have a database yet then create one or install the Northwind sample database.

To test your connection save changes (Ctrl+S). If everything is ok nothing happens. If a connection could not be made an error-message will appear and you will have to check your settings again.

Note! To create tables you can do so from Servoy or from SQL Management Studio. If you make changes from SQL Management Studio then you will have to reload the table schema in Servoy and/or synchronize the DB info. To do so right-click your database node under Database Servers and select Reload tables/table structure from DB and/or Synchronize with DB server information. If you add records outside of Servoy then use the Update servoy sequences option.

Now you can use your connection just as you would with PostgreSQL or any other database. Note that it is far more easier to change databases with Servoy than it is in VFP. You can even change them on the fly without recompiling!

External resources:

vfp plugin

ServoyWorld 2012 pics

Official Servoy website

Contents:

How to create a MS SQL Server connection

External resources:

VisualFoxpro.com © 2010-2012 • All rights reserved • Contact: info@visualfoxpro.com