Wiki Home

SQL Pass - Through


Namespace: Wiki
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 just SQL 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 components.
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??
You cannot use OLE DB provider to setup connection in DBC. You cannot use it for views at all.
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 in the _dataquery.vcx class library.
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.
SQL Pass - Through can be done by using an ODBC DataSource, or an OLE DB provider + 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 a DataSource 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.
Ok, can someone show me a connection string that will use the sqlserver ole-db provider? I tried:
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.
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
Ok, that's an ODBC connection string, not OLEDB. JCF
Check samples in VFP_OleDB_Provider -- Alex Feldstein
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
The choice depends a lot on the application. If the application 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 also the possibility that the Connection String for the OLE DB Provider can be stored in a table and passed into the SPT command at run-time. In this case, only the table on the server containing the connection strings would need to be updated.
Contributor: Cindy Winegarden (Please edit if this information is not correct.)
For the record: it is possible to get a value back from a SQL Server Stored Procedure output parameter through SQLExec.
lcVersion = SPACE(200)
SQLExec(lnConn, 'exec gsf_GetVersion [email protected]')
?lcVersion

Be warned - Type safety!: Your receiving parameter must be of a suitable data type because there is no type conversion when the value is received. For example if the output value is an int, it can be received by a numeric variable but not by a char or date, and a logical can only switch between .f. and .t. - MattP

Thanks, this is good for all to know. How would this look if there were input and output parameters, are they positional, seperated by commas? Also, is there a way to get a return value from SPT? Thanks.
In my research there is no way to get the return value of a stored procedure. You have to use output parameters or if you want to pass an error message you have to use the 'raiserror' function in your t-sql procedure.
To get the value of output parameters from VFP you need to use the ODBC escape sequence 'call' command.
nConn = SQLConnect('connectionname')
lcValue1 = ''
lcValue2 = 0
lnResult = SQLExec(nConn, '{call sp_yourproc ([email protected] output, [email protected] output)}')

Contributor Fred Taylor
I've found that you actually CAN get return values from SPT on a stored procedure, though the syntax is a bit odd:
cPassVal = "testing"
nRetVal = 0
nResult = SQLEXEC(nConn,[EXEC sp_yourproc ?cPassVal,[email protected]])

You can't use the "output" keyword, nor can you put the parameters inside parenthesis like a regular call. The "@" seems to be sufficient to tell SQL that it's an output parameter.

FWIW, I couldn't get the escape sequence call command to work no matter what I tried.
You're talking about output parameters rather than actual sp return values; I've found you can get the return values using OBDC call syntax, e.g.:
sqlexec(1,"create procedure testsqr @p1 int as return @p1*@p1")
ret = 0
sqlexec(1,"{[email protected] = call testsqr (15)}")
?ret && prints 225.00

Warning: Any return value doesn't seem to get through if the sp raises an error.
- Matt Peirse
I created a useful component that helps you convert VFP expressions to the grammar expected by the server you're targeting. In fact it is a true Sql Parser. Any comments or sugestions are welcomed.

It is very useful for SQL Pass - Through because you can use familiar VFP SQL grammar and functions. Also, it is very useful if you're targeting two or more server platforms with the same VFP code. The default conversion is made to ODBCGenericSQL syntax, but it can be configured.
The SQLParser can be used as well for defining Remote Views http://www.geocities.com/SiliconValley/Hills/9119/sql_parser.htm -- RemusRusanu
See Also Remote Connection Terms Spt Parameters
Just a warning - VFP's parser will catch any question mark in the SQL string, even if it's within a SQL comment (i.e. on a line beginning with -- or bracketed between /* */) and will error out if there's nothing there that can be a variable. If it can be a variable but you don't have it defined at runtime, you get an ugly inputbox to enter it. Worse, your user gets it.

So watch out. -- Dragan Nedeljkovich
Category VFP Commands Category Code Samples Category Client / Server
( Topic last updated: 2015.11.04 04:54:36 PM )