>Hi All,
>
>I am trying to figure out how to use the stored procedure. I want to create a SP on the database. And with an SQLEXEC call it and have it return the result set of the query in the SP. I just want to start simple. Just to prove to the Dept Head that they are faster and easy to maintain. Versus keeping all the querys client side. We intend to move to an Oracle database in the new year. ( Finally ) And I'd like to do it right the 1st time.
>
>The test: I want to have the query in the SP. Call it passing 1 numeric parameter. And have a cursor returned with my data.
>
>VFP6 SP5 The database is a standard VFP .dbc
>
>TIA
>Mike
Mike,
Where is the SP ? On VFP database or backend ?
I assume from your post you don't currently have backend, SP is on VFP database and will simulate SQLExec()ing against another VFP table from another dbc (SP querying Testdata!customer as if it were a backend table) :
procedure GetCustomerDetails
lparameters tcCustID, tcRetCursorName
#define TESTDATALOC _samples + 'data\testdata.dbc'
handle = sqlstringconnect( "DSN=Visual FoxPro Database;UID=;PWD=;"+;
"SourceDB="+TESTDATALOC+;
";SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;" )
sqlexec(handle,;
"select * from customer where cust_id=?tcCustID",;
tcRetCursorName)
SQLDISCONNECT(handle)
endproc
PS:No error checking.
Also in VFP8 with new CursorAdapter and XML* it's much more simple to use any remote source.
Cetin