VFP9 and DSN/ODBC connectivity.

[Blueevo]

As I do not know a great deal about the VFP9 IDE, I was hoping someone could help me out. Can you use a DSN to query another database, using Fox Pro? (Is this possible, I do not want to know how to do it).

Q2. If so, is this using the SQL standards? I.e. SELECT * FROM TABLE. Then returns a dataset which you may loop through?

The current system uses the VFP Database, but looking to move away from the database but still use the VFP front end.

Many thanks, hopefully this post can be resolved and closed quickly.

Jim

 

[GriffMG]

Q1 Yes
Q2 Yes

Both subject to a suitable driver being available.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Blueevo]

Wonderful, just the job Griff.

Thanks.

 

[JRB-Bldr]

Q1: "Can you use a DSN to query another database"

That depends on your use of the terminology 'database'. OLD versions of Foxpro used the word to mean Data Table which may or may not be contained in a Database.

Newer terminology used with VFP has the word 'Database' referring to what we think of today as a Database (VFP, SQL Server, etc.) which may or may contain Data Tables.

Every DSN that I have set up has required me to specifically name the Database.
Yes, I can set up multiple DSN's to have access to multiple Databases (1 DSN per Database) within the same Database Server.

However once I utilize the specific DSN I can access any Data Table within the Database.

NOTE - there are some proprietary Databases which do not allow full access. These are typically associated with special application such as Accounting, Payroll, etc.

Q2: There are a number of ways to get a record set that is the result of a SQL Query utilizing a DSN connection.

Again, while there are a number of ways to do it, the basic SQL Pass Through method I use the most is something like:

* --- Open VFP Database which has Connection (SQL_Server) defined utilizing Windows DSN ---
OPEN DATABASE "C:\VFP\MyDBC.DBC"
nConnectionHandle = SQLCONNECT("SQL_Server")

* --- Get Specific Table Contents Into Cursor ---
cSQLCommand = "SELECT * FROM MySQLTable WHERE <something> "
nRet = SQLEXEC(nConnectionHandle, cSQLCommand, 'Result')

IF nRet = 1
   * --- Result Returned Into Cursor 'Result' ---
   SELECT Result
   < Do Something >
ENDIF

=SQLDISCONNECT(nConnectionHandle)

CLOSE DATABASE

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Jim,

Just to add to the good answers you've already received.

As the others have said, yes you can use DSN / ODBC to access other databases (by which I mean non-Foxpro databases). In fact, that is the normal way of doing so.

Broadly speaking, VFP 9 offers three ways of using ODBC:

1. Remote views. This is a simple approach, and is fine for querying tables and - up to a point - updating them, but it has its limitations.

2. SQL pass-through (SPT). This is a low-level approach, in which you send SQL commands to the database and receive the results in a FoxPro cursor. This is very flexible, as it lets you send any command that the database can undestand.

3. CursorAdapters. This is a more sophisticated approach, which can also used in conjunction with ADO and XML.

My own preference is for SPT, but the other techniques are also worth considering.

To find out more, I suggest you start by reading the Help topics on remote data. If you decide to use SPT, you then need to look at SQLCONNECT(), SQLEXEC(), SQLDISCONNECT() and a few similar functions.

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Blueevo]

Thank you guys, much appreciated.

The system I am looking at uses native VFP tables. This will be changing. I will be looking at still utilising the front end (in VFP9), but the data will be sourced from a different SQL database (using a DSN). Hence the ODBC/SQL question

Thanks

 

[Olaf Doschke]

Only one thing to add: You do not only get "some dataset you can loop through", via remote query technics of VFP you get VFP cursors, so you even can recycle most of the code you already have aside of data access from/to the database.

Bye, Olaf.