Developing VFP front-end to SQL backend

[aharrisreid]

I am about to embark on a project using VFP as front-end and SQL-server as backend. I know very little about using SQL-server, so I will be developing using local views of dbfs extracted from SQL-server tables, and will switch-over to remote views when the application goes live.

My question is as follows. There are many fields in the SQL tables with names having more than 10 characters. How can I extract my local tables so that the field names are not truncated? I guess they will have to be part of a database container, but I'm not sure where to go from here. For example, would I have to create a separate .dbc containing the .dbf definitions as a remote view, in which case how would I get the data into a .dbf (which has to be in a .dbc itself)?

Any help would be appreciated.

Alan

 

[Mike Lewis]

Hi Alan,

Since your local (and remote) views will have to be in a database (DBC), the truncating of field names will not be an issue. DBC-hosted tables and views can have field names up to 128 characters.

I would suggest that you use two separate DBCs: one for the views, and one for the local DBFs that you will use during development. Once you switch to SQL Server, you can discard the DBFs, and convert the local views to remote views.

However, I would suggest that you think about an alternative way of going about this project. Instead of starting out with DBFs and local views, I think you should bite the bullet and go straight to remote views and SQL Server.

The main reason for this is that switching from local views to remote views is not nearly as simple as you might have been led to believe. There are many differences in syntax and usage between VFP and SQL Server, and you would have to spend a lot of time making many minor changes when you make the switch.

I would recommend you get hold of a developer's copy of SQL Server (or even a free 120-day trial copy, which is not hard to come by). You can run these versions on the same PC that you use for development. Spend a little time learning your way round -- you won't find it that difficult. Then plunge into the application.

A lot of the folk here have experience with SQL Server, so if you run into problems, you'll know where to ask.

Good luck,

Mike
Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[aharrisreid]

Mike, thanks for the reply.

>>Since your local (and remote) views will have to be in a database (DBC), the truncating of field names will not be an issue. DBC-hosted tables and views can have field names up to 128 characters.<<

Maybe I didn't explain myself clearly enough. What I really wanted to know is how can I create the local .dbf files from the SQL tables in the first place so that the field names are not truncated?

If I create a remote view as SELECT * FROM mySQLTable..., that will create a view/cursor with the same field names, but how can I convert the view to a .dbf and not lose the long field names? COPY TO .... or SELECT...INTO TABLE... will create a free table with truncated fields, but I need the .dbf to be part of a database container.

Any ideas how this might be possible?


>>However, I would suggest that you think about an alternative way of going about this project. Instead of starting out with DBFs and local views, I think you should bite the bullet and go straight to remote views and SQL Server.<<

That's an interesting suggestion, but rather than purchase/install/learn SQL Server, would it be possible to create a connection (ODBC?) to the SQL tables and use remote views to extract the data, which I can then view using a VFP front-end? Do I already have the necessary software to create the connection? Is it supplied with VFP or the OS (XP pro or W2K)?

It's not that I'm against purchasing or learning SQL Server, but time is of the essence with this project and I'd like to get something up-and-running asap.


Regards,

Alan

 

[fluteplr]

Franky what you want to do is a bad idea.

If you can't use sql server as your development back end use MSDE, it is included with VFP7 and probably 8.

If you insist however. If you create your dbfs as belonging to a dbc you can have long field names.

The command you want is:

copy to tablename database dbcname.

You really need to learn how to handle the data remotely lots of things what work well with local tables do not work well in a client server setup.

 

[aharrisreid]

fluteplr, thanks for the reply.

>>Frankly what you want to do is a bad idea.
If you can't use sql server as your development back end use MSDE, it is included with VFP7 and probably 8<.

Unfortunately my client will be using VFP 6 - will this version work ok with MSDE, or can I safely develop in 7 and recompile in 6 prior to delivery?

I think I have heard/read that there are some limitations using MSDE, do you know what they are? (Limited table size, perhaps?)

If I just want to test accessing SQL tables via. remote views or SQL pass-through. Do I need SQL-server or MSDE installed in order to do this, or does the ODBC driver take care of this?

I would like to get hold of some simple SQL-server tables to practice setting-up remote views. Do I have to install MSDE for this, or is there an easier way? Alternatively, what is the easiest way to convert VFP tables to SQL format? From what I have read, the SQL upsizing wizard does a lot more than this and expects SQL-server to be installed also. For instance, I can only see how to upsize from a database, not an individual table.


>>You really need to learn how to handle the data remotely lots of things what work well with local tables do not work well in a client server setup<<

Can you give me any examples of this? They may be useful if I have to persuade my client that I should develop using SQL-server tables straight away.

Many thanks,
Alan

 

[fluteplr]

VFP6 will work just fine with MSDE. I was just pointing out that the product MSDE is on the VFP 7 disk.

The limitations of MSDE is that the prerformance drops when you have more than a few users (5 I think). Other than that it is code compatable with SQL Server.

You need the server for the remote data to be somewhere so either SQL Server or MSDE needs to be installed so the remote views and ODBC have something to pull data from.

I would sugget to go to

http://www.hentzenwerke.com

and buy a copy of their client server development book.

Read it and get some idea of what you are doing before you dive in.

 

[aharrisreid]

fluteplr, thanks for the reply.

>>You need the server for the remote data to be somewhere so either SQL Server or MSDE needs to be installed so the remote views and ODBC have something to pull data from.<<

That's a shame - I was hoping there may be some clever way of being able to read the SQL tables without having to install the full software.

>>I would suggest to go to

http://www.hentzenwerke.com

and buy a copy of their client server development book.<<

I'll check it out.

Regards,
Alan

 

[Mike Lewis]

Alan,

Sorry if I misunderstood your question.

To download the data to local tables, first set up the tables in the normal way, but put them in a DBC. Then use APPEND FROM to populate the tables from the server data.

On the wider issue, if you have access to a network on which SQL Server is installed, then you can indeed access it with your existing tools, even if you are in VFP 6.0.

You will need to set up an ODBC connection -- you will already have the driver. Once you've done that, you can use remote views or SQL Pass-Through to do just about anything within VFP that you could do in SQL Server itself (although not necessarily as easily).

My main point is that it is not a good idea to develop against local DBFs, in the hope that you can &quot;press a button&quot; and switch to using SQL Server tables in the future. In theory, that would work, but in practice it wouldn't be the best way to exploit client-server.

Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk