Sql passthrough

What's the poop about SQL Pass Through in VFP?

SQL Pass - Through (SPT) technology enables you to send SQL statements directly to a server. SQL Pass - Through statements, because they execute on the back-end server, are powerful ways to enhance the performance of your client/server applications.
They differ from Remote Views in that they are based on any native server SQL statement (not justSQL Select as in Remote Views), enabling data definition statements or execution of server stored procedures.
They do share the power of Remote Views in that they can use named parameters that can be evaluated at runtime:
? SQLExec("SELECT * FROM Customer WHERE Customer.CustID=?pCustID")
Note that SQL Pass - Through creates VFP cursors. You cannot pass VFP cursors to COM componentes.
[pic]Interesting, so I assume there is a way to set up a connection in the DBC that uses OLE-DB by using the string connect. How about some sample connect strings that use the SQL Server Ole DB??

[pic]You cannot use OLE DB provider to setup connection in DBC. You cannot use it for views at all.
[pic]
You use the AError(), SQLColumns(), SQLCommit(), SQLConnect(), SQLDisconnect(), SQLGetProp(),SQLExec(), SQLMoreResults(), SQLPrepare(), SQLRollback(), SQLSetProp(), SQLStringConnect(), SQLTables() functions and the CREATE CONNECTION command to use SPT.
Don't let the plethora of functions scare you, a lot can be accomplished with only four of the functions: SQLConnect() or SQLStringConnect(), SQLExec() and SQLDisconnect().
The VFP6 FFC contains a wrapper class _execsp for these functions inthe _dataquery.vcx class library.
[pic]
09/27/2002 Steven Blake - How do you include a local table in a join at the SQL server level, then drop the table once you retrieve the result set? A List Box with the Multi Select Property set true presented me with that problem. I solved the dilemma with SQL - Temporary Table.
[pic]
SQL Pass - Through can be done by using an ODBC DataSource, or an OLE DBprovider + a Connection String.
How about some sample code? Nowhere in the VFP6 docs say that an OLEDB provider can be used with SPT. –JCF

Advantages and Disadvantages of DataSource vs OLE DB Provider:

If the back-end database name changes, the SQL Pass - Through process will need to change also. This could mean that the DataSource on each client machine will need to be changed if aDataSource is used, or that the application will need to be re-built and re-distributed if the Connection String for an OLE DB Provider is used.
If there is not an OLE DB Provider for the database and the Provider for ODBC is being used, the ODBC DataSource will still need to be maintained on the client machine.
[pic]
Ok, can someone show me a connection string that will use the sqlserver ole-dbprovider? I tried:

/* CONEXIÓN SQL CON MOTOR OLEDB*/

nConn = sqlStringConnect([Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=MYSERVER])
This connect string came from creating a new Microsoft data link and then taking the text from the .UDL file.
[pic]I just sucessfully created a DSNLess connection string for a persistent connection in a DBC:DRIVER=SQL Server;SERVER=rsj;UID=sa;PWD=;DATABASE=pubs
[pic]Ok, that's an ODBC connection string, not OLEDB. JCF
[pic]Check samples in VFP_OleDB_Provider -- Alex Feldstein
[pic]That will be pure ADO samples, not SPT. JCF
So the only thing I should need to change is the server name when going from dev to live, right? -- Randy Jean
[pic]
The choice depends a lot on the application. If theapplication resides on a server and there is a schema in place to copy it to the local machine automatically whenever updates are made, the OLE DB Provider + Connection String approach has it's advantages. If the DataSource and SQL database are maintained at the site and the FoxPro application changes very little, it may be preferable for the DataSources on the local machines to be changed.
There is...