Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

vfp to ms sql

Posted on 2012-09-21
8
Medium Priority
?
1,008 Views
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?
0
Comment
Question by:robrodp
8 Comments
 
LVL 12

Expert Comment

by:jrbbldr
ID: 38423989
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

DSN-less
Use a fully defined Connection string (look at:  http://www.connectionstrings.com/mysql )
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
0
 

Author Comment

by:robrodp
ID: 38424000
The connection is a ms sql connection. I really need a more dummies approach
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 38424157
To Connect
nSQL = SQLConn()

FUNCTION SQLConn
cSQL = "DRIVER=SQL Server"
cSQL = cSQL + ";SERVER=1.2.3.4"
cSQL = cSQL + ";TRUSTED_CONNECTION=No"
cSQL = cSQL + ";DATABASE=MyMSSQLDatabase"
cSQL = cSQL + ";UID=username"
cSQL = cSQL + ";PWD=password"
? "Connecting ..."
nSQL = SQLSTRINGCONNECT(cSQL)
IF nSQL > 0
	? "Connected"
ELSE
	? "Not Connected"
ENDIF
RETURN nSQL

Open in new window

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 27

Accepted Solution

by:
CaptainCyril earned 1500 total points
ID: 38424159
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."
			ENDIF
		ENDSCAN
		WAIT CLEAR
		USE IN tmptables
		SELECT structure
		BROWSE NOWAIT
	ENDIF
	= CloseConnection(nSQL)
ENDIF

Open in new window

0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 38424162
To get records from a table

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

Open in new window

0
 
LVL 43

Expert Comment

by:pcelba
ID: 38424511
Above short samples should be completed by more comprehensive information...

A list of SQL Pass-Through functions:
http://msdn.microsoft.com/en-us/library/ad8d6efe(v=vs.80).aspx

If you decide to use remote views:
http://msdn.microsoft.com/en-us/library/hf52bkf7(v=vs.80).aspx

Another way is to define Cursoradapter:
http://www.redware.com/handbooks/foxprocursoradapter.html
http://www.code-magazine.com/Article.aspx?quickid=0301062

And appropriate pages are on FoxPro Wiki, as well:
http://fox.wikis.com/wc.dll?Wiki~SQLPass-Through
http://fox.wikis.com/wc.dll?Wiki~CursorAdapter
http://fox.wikis.com/wc.dll?Wiki~RemoteViews~Wiki
http://fox.wikis.com/wc.dll?Wiki~RemoteViewFAQ~VFP

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 http://www.hentzenwerke.com/catalog/csvfp.htm 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:
http://msdn.microsoft.com/en-us/library/ms917355.aspx
http://msdn.microsoft.com/en-us/library/aa905875(v=sql.80).aspx
but if I remember it well Microsoft will stop OLE DB support in next MS SQL Server version.
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 38426326
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:
lnH=SQLConnect("DSNName")
SQLExec(lnH,"Select * From Table","curResult")
SQLDisconnect(lnH)

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.
0
 
LVL 12

Expert Comment

by:jrbbldr
ID: 38428863
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
    http://www.hentzenwerke.com/catalog/csvfp.htm

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
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

810 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