vfp to ms sql

Posted on 2012-09-21
Last Modified: 2012-09-28
I have an ms sql database. I have created a DSN ok.

Ho can I get access to my sql tables from VFP and the DSN?
Question by:robrodp
    LVL 12

    Expert Comment

    You have a number of ways to access your MySQL tables from VFP.

    Remote View
    Within a VFP Database, create a Connection which will utilize your DSN
    Then create a Remote View (updateable or not) to your MySQL table(s) using the Connection
    Then have your VFP application use that Remote View

    SQL Pass-thru
    Open the previously described VFP Database
    Execute a SQLConnection() to acquire a ConnectionHandle utilizing the Connection defined within the VFP Database
    Build a SQL Command which is consistent with the MySQL database language
    Use the ConnectionHandle in a SQLExec() to execute your SQL command

    Use a fully defined Connection string (look at: )
    Acquire a ConnectionHandle using SQLSTRINGCONNECT()
    Build a SQL Command which is consistent with the MySQL database language
    Use the ConnectionHandle in a SQLExec() to execute your SQL command

    There are a lot of detailed examples available through a Google search for:
        vfp sqlexec()
        vfp sqlconnection
        vfp connection
        vfp dsn-less connection

    Or other similar search strings

    Good Luck

    Author Comment

    The connection is a ms sql connection. I really need a more dummies approach
    LVL 27

    Expert Comment

    To Connect
    nSQL = SQLConn()

    cSQL = "DRIVER=SQL Server"
    cSQL = cSQL + ";SERVER="
    cSQL = cSQL + ";DATABASE=MyMSSQLDatabase"
    cSQL = cSQL + ";UID=username"
    cSQL = cSQL + ";PWD=password"
    ? "Connecting ..."
    IF nSQL > 0
    	? "Connected"
    	? "Not Connected"

    Open in new window

    LVL 27

    Accepted Solution

    To get the full structure in VFP cursor

    IF nSQL > 0
    	IF SQLTABLES(nSQL, "'TABLE'", "tmptables") > 0
    		DELETE FILE structure.dbf
    		CREATE TABLE structure (table_name C(50), field_name C(50), field_type C(10), field_len N(20), field_dec N(20))
    		SELECT table_name;
    			FROM tmptables;
    			INTO CURSOR tmptables
    		SCAN ALL
    			WAIT WINDOW "Downloading structure " + STR(RECNO('tmptables')/RECCOUNT('tmptables')*100,6,2) + " %" NOWAIT
    			IF SQLCOLUMNS(nSQL, ALLTRIM(tmptables.table_name), "FOXPRO", "tmpstructure") > 0
    				SELECT structure
    				APPEND FROM DBF('tmpstructure')
    				REPLACE FOR EMPTY(table_name) table_name WITH ALLTRIM(tmptables.table_name)
    				USE IN tmpstructure
    				? "Table " + ALLTRIM(tmptables.table_name) + "'s done."
    		USE IN tmptables
    		SELECT structure
    	= CloseConnection(nSQL)

    Open in new window

    LVL 27

    Expert Comment

    To get records from a table

    = SQLEXEC(nSQL,"SELECT * FROM Customers WHERE state='CA'" ,'customersincalifornia')

    Open in new window

    LVL 41

    Expert Comment

    Above short samples should be completed by more comprehensive information...

    A list of SQL Pass-Through functions:

    If you decide to use remote views:

    Another way is to define Cursoradapter:

    And appropriate pages are on FoxPro Wiki, as well:

    And finally you may study some books from Hentzenwerke which are perfect resource of knowledge for FoxPro community. The SQL data access is discussed in and even when it is written for SQL 7 the info is still valid.

    BTW, I am using SQL Pass-Through as the cleanest way of SQL data access for programmers. Everything other (remote views and Cursoradapters) brings one or more levels of internal complexity to the data retrieval process which is always more difficult to maintain.

    You could also use ADO to access SQL data:
    but if I remember it well Microsoft will stop OLE DB support in next MS SQL Server version.
    LVL 29

    Expert Comment

    by:Olaf Doschke
    If you don't want to browse and read through above mentioned articles and help topics: It's a three liner. You can use your DSN via SQLConnect() and then need SQLExec and SQLDisconnect.

    The other approaches, especially Cursoradapters, have their advantages over SQLExec, but need more learning first. It pays.

    But for a quick start just these three lines will give you data in a foxpro cursor:
    SQLExec(lnH,"Select * From Table","curResult")

    This isn't limited to MSSQL, you can use any DSN to any database offering an ODBC driver. You use that databases SQL dialect, so here T-SQL.

    The disadvantage of SQLExec incomparson with other techniques is, it needs more lines to prepare a result cursor to enable foxpro to write back changes to the remote database server, which is easier using a CA builder or it's properties, but needs more preparation.
    The three liner above gives you a feel of what you get from SQL Server and how fast or slow that may be in comparison to USE some.dbf.

    You will need to redo most of your programming, it's not just simply done by wrapping sqls into SQLExec calls. You work disconnected, you fetch some data and then do stuff client side and then need to store back changes.

    Bye, Olaf.
    LVL 12

    Expert Comment

    If you really need a detailed instruction 'manual' you might want to look at:
        Client-Server Applications with Visual FoxPro and SQL Server 7.0

    Some of the information is a little dated due to version changes since publication time, but in general, it gives you the details of how to get your VFP application working with a SQL Server 'backend'.

    Good Luck

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now