Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy
 To: Allan Cerrudo
  Where is Allan Cerrudo?
 Las Pinas City
 Philippines
 Allan Cerrudo
 Tags
Subject: RE: Converting from DBF to Client Server
Thread ID: 143505 Message ID: 143525 # Views: 4 # Ratings: 4
Version: Visual FoxPro 9 Category: Projects and Design
Date: Wednesday, August 29, 2007 3:08:02 PM         
   


> I have been using DBF for a long time. I wanted to try using SQL files. What are the things I must know and what are the codes I will add & change in my program.
>
> I also wondering if I will change the programs file structure. For example I have 4 dbf tables like employee.dbf, period.dbf, month.dbf, history.dbf where those file are related by emp_no. If I will use a MySQL or an Oracle database, Will I still use 4 files in SQL or just 1 files.
>
> Thank you in advance for any help.

Allan,

I will answer your second question first. If your VFP database structure is normalized, then your SQL database structure would be quite similar and possibly identical. SQL has features that do not exist in the VFP database and you may want to use some of them. You can use your existing table structures and create identical SQL table structures, but this may not be the most efficient or most effective way to do things in SQL. If your table structures are not normalized, you may wish to normalize the data as you bring it into the SQL database.

If you are going to attempt to use a SQL type back end, then you need a bit of a primer. The first thing you need to do is to connect to that database. First there is the network connection to deal with. In a LAN situation that is easy - your network is always there and it is all behind the firewall. If you are talking about using the Internet as a wide area connection, you have to make this secure. You do not want to float your data around the internet un-encrypted. You can use a VPN connection (virtual private network) to create a secure "tunnel" through the internet. Talk to your SysAdmin type about VPN. There are some options open to you with VPN (using VPN routers, vs RAS, etc.) and your SysAdmin is probably the person who has the authority to make such decisions. This however, does not take place in your VFP code.

In your code, you then have to make a connection to the SQL backend. You do this with a SQLSTRINGCONNECT() or SQLCONNECT() function. SQLCONNECT() will make a connection to a DNS and SQLSTRINGCONNECT() uses a "connection string." Again, talk to your sysadmin about DSN's and you can go to www.connectionstrings.com for a connection string example. Both of these functions will return a positive integer on success and you use this positive integer as a connection handle:

lnConnHndl = SQLCONNECT([MyDSN])
IF lnConnHndl < 0
   *Could not connect
   AERROR(laError) 
   MESSAGEBOX(laError(2,1))
   RETURN .f.
ENDIF

Now that you have a connection, you will need to be able to work with the data - you get three choices:

1 - Remote views
Remote views are probably the easiest to learn and work with - you get a VFP cursor that is readwrite and you can basically use it the same way you would a table. Remote views are also the least flexible and they are procedural in nature. While I suggest that you learn how to use them, I do not recommend the use of remote views in today's applications. If you were to look at the SQL behind a remote view, you would see that it is basically a SELECT statement followed by a series of commands that set the properties of the cursor that it creates.

2 - Cursor Adapters
A CA is an OOP based version of a view. It is far more flexable than the view and is class based. It still gives you a VFP readwrite cursor and is therefore quite simple to use. For more information on CA's go to Bernard's excellent series of articles titled "A very simple CA tutorial" in the Foxite Articles page.

3 - SPT (SQL Pass Through)
SPT is probably the hardest to learn, but it gives you the most flexablity and control over what you do. Begin by looking up SQLCONNECT(), SQLSTRINGCONNECT(), SQLDISCONNECT(), SQLEXEC() in the help file. If you are going to learn SPT, you need to start with a good understanding of what these functions do. An example of SPT code might be:
TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 15
   SELECT * FROM MyTable 
      WHERE nSomeField = << lnSomeValue >> 
      ORDER BY SomeField, SomeOtherField
ENDTEXT
IF SQLEXEC(lnConnHndl,lcSQL,[MyResultsCursor]) < 0
   *** Your command failed
   AERROR(laError)
   MESSAGEBOX(laError(2,1))
   RETURN .f.
ENDIF

As the name suggests, with SPT you "pass" a command (like this SELECT) "through" to the SQL server. The server then processes the command and sends the results back to the client and SPT stores these results in a cursor called MyResultsCursor. If an error occurs, the SQLEXEC() returns a negative value so you can use AERROR() to find out what that error was.

While SPT may be harder to use and learn, in my mind, it is probably the best method to use when creating n-tier client server applications. Should you wish to attempt SPT, I recommend that you read Andy's excellent article "Class Based Data Management" at http://www.tightlinecomputers.com/Downloads.htm What I use is largely based on that article. Data classes may be difficult for you to grasp at first, but it is well worth the effort to learn. These classes will simplify your code and as Andy says "do the heavy lifting for you."

Hope this helps.

Ken
You shall know the truth - and the truth shall set you free. (John 8:33)

ENTIRE THREAD

Converting from DBF to Client Server Posted by Allan Cerrudo @ 8/29/2007 12:54:51 PM
RE: Converting from DBF to Client Server Posted by Ken Murphy @ 8/29/2007 3:08:02 PM
RE: Converting from DBF to Client Server Posted by Benny Thomas @ 8/29/2007 3:53:56 PM
RE: Converting from DBF to Client Server Posted by Ken Murphy @ 8/29/2007 4:06:40 PM
RE: Converting from DBF to Client Server Posted by Benny Thomas @ 8/30/2007 9:16:28 AM
RE: Converting from DBF to Client Server Posted by Allan Cerrudo @ 8/31/2007 6:26:36 AM
RE: Converting from DBF to Client Server Posted by Ken Murphy @ 8/31/2007 2:52:57 PM
RE: Converting from DBF to Client Server Posted by tushar @ 8/29/2007 3:25:07 PM
RE: Converting from DBF to Client Server Posted by Allan Cerrudo @ 8/31/2007 6:23:38 AM