Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
SQL SERVER >>  MOVING TO MYSQL, SQL, ORACLE, ETC. - SOME BASICS

  Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy



If you have decided to move to MySQL, SQL, Oracle or one of the other back ends, you need to begin by takung a look at the following commands: SQLCONNECT(), SQLSTRINGCONNECT() and SQLDISCONNECT() in the help file. You may also need to download and install the ODBC drivers for your backend.

SQLSTRINGCONNECT() allows you to use a "connection string" to create your connection. You can find sample connection strings at www.connectionstrings.com.

The primary disadvantage of the connection string is that if your SysAdmin moves the database, you need to change the database location in your connection string. If you have hard coded the database location, it is going to be a LOT of work. If you have used a .H file to hold your connection string using a #DEFINE (or a series of #DEFINEs) you still need to recompile and re-distribute.

SQLCONNECT() allows you to use a DSN (Data Source Name) when connecting to MySQL, SQL, etc. Talk to your SysAdmin about DSN's or go to the Windows help file. You can create a DSN on your own machine for testing purposes. Start->Control Panel->Administrative Tools->Data Sources (ODBC)->Add and then follow the wizard.

Your SysAdmin will probably want you to use SQLCONNECT(). The SysAdmin can move the database and then change the properties of the DSN without having to touch your app.

SQLDISCONNECT(), as the name suggests, lets you disconnect from your database.

SQLCONNECT() and SQLSTRINGCONNECT() both return an integer that you use as the connection handle. If the integer is positive, you have a good connection. If the integer is negative, you have a connection error, so use AERROR() to find out what that error is. For example,

lnConnHndl = SQLCONNECT([MyDsn])
IF lnConnHndl < 0
   AERROR([laError])
   MESSAGEBOX(laError(2,1))
ENDIF


You really only want to create one connection at the beginning of the app and disconnect when the user leaves the app. Creating the connection takes a fair amount of time and with some servers, the number of connections you can create is dependent on the number of seat licenses you have. Using multiple connections simply slows down your app and could cost you more in licensing.

Once you have the connection made, you have three choices on how to work with the data. You can use Remote Views, CA's or SPT (SQL Pass Through.)

Remote views are probably the easiest to learn and use. Unfortunately, views are also the most limited method of working with the data. A remote view is simply a stored procedure containing a SELECT command, followed by a series of commands that set the properties of the cursor created by that SELECT. It gives you a VFP cursor to work with, so it is very simple to use. On the other hand, try parameterizing a remote view on the fly.

CA's are far more flexible than remote views and I would use a CA in place of a view EVERY time. Again, you get an updatable VFP cursor, so it is quite simple to use. A CA also uses a SELECT statement, but it is object based - not procedural like the RV. For more information on CA's go directly to Bernard's excellent series of articles titled "A Very Simple CA Tutorial" in the Foxite Articles page.

Finally, you can use SPT. This gives you the most control and power, but SPT probably has the most difficult learning curve. Start by reading the help file on SQLEXEC() The idea here is that you Pass an SQL command like SELECT FROM, INSERT INTO, DELETE FROM Through to the server as a string. The server then processes the command and returns the results back to the client (if any.)

If you decide that you want to use SPT, I recommend that you go to Andy Kramek's web site and read his article on "Class Based Data Management here:

http://www.tightlinecomputers.com/Downloads.htm

What I use is largely based on that article. As Andy puts it, "You can use these data classes to do most of the heavy lifting." If you find this article a little complex, take the time to work through it. Remember, you only have to build your data classes once. You can then use them in all of your apps.

In VFP you can normally USE MyTable and you have access to the entire table. You can controlsource directly to a field in that table and work with all of the records. In a client server situation, you won't do this. You use SELECT ... FROM ... WHERE ... commands to access the data you wish to work with you use and you use an UPDATE or INSERT INTO command to save changes back to the database. In other words, you only work with local cursors that contain only those few records and fields that are specifically required for the functionality you are currently working on. This will be a bit of a change in thinking for you. For example, if you are editing the details of an invoice, in VFP you might:

USE InvDetail IN 0 SHARED 
USE InvHeader IN 0 SHARED TAG InvNo
SELECT InvDetail
SET RELATION TO InvNo INTO InvHeader
LOCATE FOR InvHeader.InvNo = lnTheInvoiceIWantToEdit


In a client/server backend, you would
SELECT * FROM InvDetail 
   INNER JOIN InvHeader 
      ON InvDetail.InvNo = InvDetail.InvNo
   WHERE InvHeader.InvNo = lnTheInvoiceIWantToEdit


In a traditional VFP app, you have two tables open and all of the records in those two tables. In the client server situation, you will only have one cursor and it will contain only the records for that one invoice.

One other thing you will need to think about. When you move to a client/server back end, you will no longer have a "delete" mark in the table. If you issue a DELETE FROM command, that record is GONE. There is no PACK command in SQL. Should you wish to be able to recover deleted records, you will need to store these records in an archive somewhere before you delete them. I use a RecordStatus field that serves the same purpose as the delete mark. I use a "RecordStatus" field for this purpose. For example, if the recordstatus = 1, it is a current record and if the recordstatus = 0, that record has been "retired."

Hope this helps.

FEEDBACK

Binod Binani @ 9/9/2007 7:10:45 PM
In an Entry Form there is one transation Table ie: tab_1 and one is Master table ie: mst_1 is SQL.

Earlier with VFP Native Data file I used :
If SEEK(Tab_1.MST_code,"MST_1","MST_CODE")
** Replace certain fields of TAB_1.dbf with Certian Fields of MST_1
Else
Message....
Endif
At this Point I had to open the both the Tables.

What would be the scene for SQL Tables.

Allan Cerrudo @ 9/10/2007 3:44:46 AM
Your post is very much...

Thanks a lot

Allan Cerrudo
Cetorn Computer System

SRINIVAS SARMA @ 9/10/2007 9:11:23 AM
Dear Mr. Ken

Very useful. Many thanks.

Regards

Srinivas

Benny Thomas @ 9/10/2007 9:14:19 AM
This is very helpful and thanks Ken for such a post. This is on the VFP side of the story. How about another article to show how to start in SQL/MySQL etc. for people like me who never seen SQL before. For instance, I have downloaded MySQL and installed (I do not know if the installation is correct) and that is all I could do. Don't know how to go ahed, could not find any help file or manual with it.

Benny

Daniel Hofford @ 9/11/2007 12:30:11 AM
Does the DNS get created on just the server? On just the workstations? On both? Is it the same name if its on both? Thanks for this.

Ken Murphy @ 9/11/2007 7:10:11 AM
Thanks all for the comments.

Binod, I suggest that you post that question in the forum - it is a bit more indepth than I would want to handle here.

Allan, Srinivas and Benny, thany you for your kind words.

Benny. I would need to write more than an article or FAQ. The MySQL side of things is the subject for a book. Indeed it is a book - check out this link:
http://www.hentzenwerke.com/catalog/mysqlvfp.htm

Daniel, The DNS gets created on the server. You can create a DNS on your computer, but just use that for testing purposes. The DNS on the server will be the one you use to access production data.

KASSEM NASSER @ 9/12/2007 11:27:27 PM
As usual.... Very Helpful. Thanks a lot Ken

Fred Cunanan @ 12/9/2007 8:15:01 PM
It's pretty helpful on beginners. I'll check this out. Thanks and God bless!

Ken Murphy @ 12/13/2007 12:24:28 AM
Kassem, Fred

Thank you for your kind words.

Prasant @ 10/23/2008 3:22:22 PM
Ken, Thanks a lot. I know you are not with us. But you are still helping us.

SHAHERYAR RASHED @ 8/19/2011 7:37:52 AM
Excellent Post Very Helpful

Suresh Ramtekkar @ 11/25/2011 5:42:12 AM
Important Information for all....



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: