Remote Connection Terms
Connection String - a string made up of name=value pairs, separated by semi-colons.
DSN (Data Source Name) - Names a collection of parameters that are used to connect via odbc. Created using Windows control panel, ODBC Data Sources. Basically just stores a Connection String.
DBC connection - similar to a DSN, but stored in a VFP dbc. It can either use a Connection String or reference a DSN (which is itself storing a Connection String). If it references a DSN, it can override parameters in the DSN and/or add additional ones. Created with the VFP Create Connection command. MSFT would have done better to have named this 'connection definition' to avoid ambiguity between what is called the connection in the DBC and the actual physical runtime connection (see below).
VFP Commands and Functions:
SqlConnect( dsn ) - connects by referencing a DSN.
SqlStringConnect( ConnectionString ) - connects using a Connection String
CREATE CONNECTION [ConnectionName] [DATASOURCE cDataSourceName] [CONNSTRING cConnectionString]
CREATE SQL VIEW ViewName REMOTE CONNECTION DSN - creates a view that will use the parameters in a DSN.
CREATE SQL VIEW ViewName REMOTE CONNECTION DbcConnection - creates a view that will use the parameters in a DBC Connection. If you have a DSN and a dbc Connection with the same name, which one get used?
CURSORGETPROP("ConnectHandle", "ViewName" ) - gets the connection handle used by a view. This handle can then be used for SPT. There is currently no way to go the other way (open a view using a connection handle created by Sql Connect.)
SPT - SQL Pass - Through
Remote data connections 101
It may seem like there are many ways of connecting VFP to a remote data source. There aren't as many as it seems. However, there are many places you can store the parameters needed to make a connection.
First, the actual ways to connect: ODBC, Ole DB, some custom code that is your own problem.
To connect using ODBC, something needs to specify the name of the driver and a few parameters like server, protocol, database, user, etc. There are many places to store these parameters: a Connection String can be stored just like you would store any other string (dbf, registry, prg...), a DSN (user and system are stored in the registry?, file is stored in a text file). The VFP dbc has a place for connections, and that can either be a string or a reference to a DSN. Regardless of which place the parameters are stored, they all use the same odbc driver, so the advantages are all related to storing the parameters, not capabilities of the connection (like performance.)
Just to further complicate things, a Connection String can reference a DSN ("DSN=MyDsn;"). So I think you could have a remote view that uses a DbcConnection that is a Connection String that refereneces a DSN. There might be a good reason for doing it that way, but I am sure it will just confuse everyone who tries to follow it.
I don't know much about Ole DB, but from what I gather it is about the same as ODBC.
Alot of what makes this confusing is that two 'things' are called connections...
1. Connection - is an item in the DBC that contains information on how to connect to a remote data source.
2. Connection - a physical connection to a remote data source accessed via a connection/statement handle.
I wish VFP has called the 'thing' in the dbc a conndef (connection definition) or something like that, cause it is not really a connection it is just a place to store the information used to create a connection.
Another confustion is that alot of folks think you HAVE to use a DSN to use remote views. That is false, you can put a connection string into your dbc connection definition and still have DSN less connections.
Contributors Carl Karsten Bob Archer Del Lee
( Topic last updated: 2004.12.30 07:19:10 PM )