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 >>  SQL SERVER: THINGS TO CONSIDER PRIOR TO STARTING YOUR APPLICATION DEVELOPMENT PROCESS

  Pete Sass
  Where is Pete Sass?
 Marathon, Ontario
 Canada
 Pete Sass



1. First off decide upon the connection method. This could be ODBC, Cursor Adapters, or using a connection string like:

cConn = SQLStringConnect("Driver=SQL Server;Server=HPHOME\MAXIMOSQL;UID=PULPSALES;PWD=PULPS;Database=Pulp_Sales")

Nothing wrong with any of the three methods, but each method has it's pros and cons. I will leave this up to you to decide which connection method you wish to use, but please read up and research each method and make a choice that is right for you prior to starting your project.

2. If you are the person designing the SQL-Server backend ensure in all tables you create in the SQL-Server backend has a unique self-incrementing fields that auto-popultates a new unique ID for each new record. This is paramount in backend design concepts. If you want to update, or delete any record; you must have an absolute unique ID to target your update, or deletion process against. If you see a SQL-Server table that does not have a unique key, you could be setting yourself up for mega problems. NOTE: Your backend design will make, or break your project.

3. I use extensively the SQLStringConnect() function to achive my initial conection to the SQL-Server backend. My choice, but may not be others who may opt to other methods. Others may opt to use an ODBC connection, with an ODBC connection configured on each workstation.

4. I try to work with all my SQL-Server backend tables as local cursors. What I mean by this is I perform a select statement against the SQL-Server backend to create a local VFP cursor that I use to bring the SQL-Server backend table into a VFP cursor that I can now manipulate. I do need to code generic insert, update, and deletion functions; so what I do in my VFP local cursor is in sync with the SQL-Server bakend tables.

5. I also have to error trap for scenaios like I brought up an record to edit, and leave for my coffee break and another user edits the record while I am gone to coffee. Now I come back and complete my changes. I want the application to advise me: "The record has been changed by another user, do you wish to cancel, or overwrite the other user's changes!' In this case a messagebox() pops up and provides me the options "Yes", or "No".

6. What about another user adds a customer into the customer table while you are gone for coffee. You bring up a picklist of customers on an Order entry screen. The select for all customers must take place within the command button of the select customers, not in creating the local cursor in the INIT event of the Orders form. I hope you see what I am trying to explain, you want your multi-user system to behave in real-time and reflect the new customer added in your picklist.

All of the above are part and parcel of multi-user development. In using SQL-Server, or for that matter Oracle; you are one step removed from a VFP backend and a lot of logic considerations have to be looked at. Keep in mind with SQL-Server and Oracle you do not have record locking.

I can tell you and I am 100% sure experts like Andy and Ken will agree hand down, you must diligently spend a lot of up front to work on "suto code", as what the final logic flow will be prior to laying fingers on the keyboard.

I personally spend more time with my junior developers in various scenarios, than what I spend in hardcare programming. One thing that I have always followed is a commaent made by my one of my teachers in Univisity; "A coding issue is easy to fix, a logigistics issues is not always visible, beware of logistitics issues."

Another revelation from my prof. many years ago is as follows: "The best software in the world has little value if nobody wants to use it."

FEEDBACK

Subhankar Pandey @ 1/21/2009 9:04:47 AM
I use SPT connection with SqlStringConnect and my connection string is store in .H file inorder to maintain the programe incase of SQLSERVER change or manipulate.

But the problem is whenever SQLSERVER change in IP or u/n or password then I hv to recomplie all my source code in VFP with modified .H file.

Can it be possible without recomplie issue.?

Willianto Hendriawan @ 7/10/2011 8:52:49 PM
@subhankar: easy. simply exclude the .H file in your exe.



Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: