Wiki Home

Remote View Of SP


Namespace: WIN_COM_API
One day I was trying to explain to someone that VFP did not really care what the query syntax was for a remote view. It got me thinking, "How far can I take this? How about calling an SP?" Turns out an SP is just fine, as long as it returns a cursor. Maddness you say? Turns out 2 people have already found a use for this and are using it in production.

* rvsp.prg

Create Database rvsp

CREATE CONNECTION WNL_Con ;
	CONNSTRING "DRIVER={SQL Server};Network=DBMSSOCN;SERVER=msde.personnelware.com;UID=WnlUser;PWD=wnlpw;DATABASE=WNL"

Create sql View v_Configure ;
	remote connection WNL_Con ;
	as exec sp_configure ?lcConfigName

lcConfigName = 'locks'
Use v_Configure
Browse
Use

Return


Yes, we were one of those that found a great use of this tecnique in a new app we were working on. We had the need to have a "round robin" distribution of new records for different users to work on. When a user clicks the button "Next Doc" we needed to insure that they retrieve the next record in the "stack" but that another user does not get the same record at the same time. The store procedure udpates the date/time stamp as it's retrieving to push the record to the bottom of the "stack" before another user can retrieve it at the same time. -- Randy Jean

Here is the stored proc:
CREATE PROCEDURE sp_GetBasicFormData
    @QueueID uniqueidentifier, @FormID uniqueidentifier  as

set nocount on
declare @FormDataID UniqueIdentifier

update basicformdata
set reviewdate = getdate(),
@FormDataID = basicformdata.basicformdataid
from (SELECT TOP 1 * FROM basicformdata where currentqueueid = @QueueID and formtypeid = @FormID ORDER BY reviewdate ASC) as t1
where basicformdata.basicformdataid = t1.basicformdataid

SELECT BasicFormData.*, Documents.Document_Path FROM basicformdata
  LEFT JOIN Documents 
   ON BasicFormData.BasicFormDataID = Documents.BasicFormDataID
WHERE BasicFormData.basicformdataid = @FormDataID
GO


And here is the remote view:

	CREATE SQL VIEW WORKQUEUEINFOVIEW REMOTE CONNECTION BHRCCONNECT AS ;
		EXEC sp_GetBasicFormData ?vp_currentqueueid, ;
				?vp_formtypeid
		
	* View Properties
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'UpdateType',1)
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'WhereType',3)
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'FetchMemo',.T.)
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'SendUpdates',.T.)
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'UseMemoSize',255)
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'FetchSize',-1)
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'MaxRecords',-1)
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'Tables',[dbo.BasicFormData])
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'Comment',[])
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'BatchUpdateCount',1)
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'ShareConnection',.T.)
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'Prepared',.F.)
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'CompareMemo',.T.)
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'FetchAsNeeded',.F.)
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'RuleExpression',[])
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'RuleText',[])
	DBSetProp("WORKQUEUEINFOVIEW", 'View', 'ParameterList',[VP_CURRENTQUEUEID,'C';VP_FORMTYPEID,'C'])
	
	* View Field Properties
	DBSetProp("WORKQUEUEINFOVIEW.basicformdataid",'Field', 'KeyField',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.basicformdataid",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.basicformdataid",'Field', 'UpdateName',[dbo.BasicFormData.Basicformdataid])
	DBSetProp("WORKQUEUEINFOVIEW.basicformdataid",'Field', 'DataType',[C(36) NOCPTRANS])
	DBSetProp("WORKQUEUEINFOVIEW.basicformdataid",'Field', 'Caption',[])
	DBSetProp("WORKQUEUEINFOVIEW.basicformdataid",'Field', 'DefaultValue',[GUID(36)])
	
	DBSetProp("WORKQUEUEINFOVIEW.batch_id",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.batch_id",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.batch_id",'Field', 'UpdateName',[dbo.BasicFormData.Batch_ID])
	DBSetProp("WORKQUEUEINFOVIEW.batch_id",'Field', 'DataType',[I])
	
	DBSetProp("WORKQUEUEINFOVIEW.batchdate",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.batchdate",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.batchdate",'Field', 'UpdateName',[dbo.BasicFormData.BatchDate])
	DBSetProp("WORKQUEUEINFOVIEW.batchdate",'Field', 'DataType',[C(20)])
	
	DBSetProp("WORKQUEUEINFOVIEW.batchtime",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.batchtime",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.batchtime",'Field', 'UpdateName',[dbo.BasicFormData.BatchTime])
	DBSetProp("WORKQUEUEINFOVIEW.batchtime",'Field', 'DataType',[C(20)])
	
	DBSetProp("WORKQUEUEINFOVIEW.csid",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.csid",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.csid",'Field', 'UpdateName',[dbo.BasicFormData.CSID])
	DBSetProp("WORKQUEUEINFOVIEW.csid",'Field', 'DataType',[C(20)])
	
	DBSetProp("WORKQUEUEINFOVIEW.receiveddate",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.receiveddate",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.receiveddate",'Field', 'UpdateName',[dbo.BasicFormData.ReceivedDate])
	DBSetProp("WORKQUEUEINFOVIEW.receiveddate",'Field', 'DataType',[C(20)])
	
	DBSetProp("WORKQUEUEINFOVIEW.receivedtime",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.receivedtime",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.receivedtime",'Field', 'UpdateName',[dbo.BasicFormData.ReceivedTime])
	DBSetProp("WORKQUEUEINFOVIEW.receivedtime",'Field', 'DataType',[C(20)])
	
	DBSetProp("WORKQUEUEINFOVIEW.formtypeid",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.formtypeid",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.formtypeid",'Field', 'UpdateName',[dbo.BasicFormData.FormTypeID])
	DBSetProp("WORKQUEUEINFOVIEW.formtypeid",'Field', 'DataType',[C(36)])
	
	DBSetProp("WORKQUEUEINFOVIEW.currentqueueid",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.currentqueueid",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.currentqueueid",'Field', 'UpdateName',[dbo.BasicFormData.CurrentQueueID])
	DBSetProp("WORKQUEUEINFOVIEW.currentqueueid",'Field', 'DataType',[C(36)])
	
	DBSetProp("WORKQUEUEINFOVIEW.lastqueueid",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.lastqueueid",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.lastqueueid",'Field', 'UpdateName',[dbo.BasicFormData.LastQueueID])
	DBSetProp("WORKQUEUEINFOVIEW.lastqueueid",'Field', 'DataType',[C(36)])
	
	DBSetProp("WORKQUEUEINFOVIEW.statusid",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.statusid",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.statusid",'Field', 'UpdateName',[dbo.BasicFormData.StatusID])
	DBSetProp("WORKQUEUEINFOVIEW.statusid",'Field', 'DataType',[C(36)])
	
	DBSetProp("WORKQUEUEINFOVIEW.ssn",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.ssn",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.ssn",'Field', 'UpdateName',[dbo.BasicFormData.SSN])
	DBSetProp("WORKQUEUEINFOVIEW.ssn",'Field', 'DataType',[C(11)])
	
	DBSetProp("WORKQUEUEINFOVIEW.lastname",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.lastname",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.lastname",'Field', 'UpdateName',[dbo.BasicFormData.LastName])
	DBSetProp("WORKQUEUEINFOVIEW.lastname",'Field', 'DataType',[C(40)])
	
	DBSetProp("WORKQUEUEINFOVIEW.firstname",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.firstname",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.firstname",'Field', 'UpdateName',[dbo.BasicFormData.FirstName])
	DBSetProp("WORKQUEUEINFOVIEW.firstname",'Field', 'DataType',[C(40)])
	
	DBSetProp("WORKQUEUEINFOVIEW.company",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.company",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.company",'Field', 'UpdateName',[dbo.BasicFormData.Company])
	DBSetProp("WORKQUEUEINFOVIEW.company",'Field', 'DataType',[C(40)])
	
	DBSetProp("WORKQUEUEINFOVIEW.phone",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.phone",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.phone",'Field', 'UpdateName',[dbo.BasicFormData.Phone])
	DBSetProp("WORKQUEUEINFOVIEW.phone",'Field', 'DataType',[C(13)])
	
	DBSetProp("WORKQUEUEINFOVIEW.email",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.email",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.email",'Field', 'UpdateName',[dbo.BasicFormData.EMail])
	DBSetProp("WORKQUEUEINFOVIEW.email",'Field', 'DataType',[M])
	
	DBSetProp("WORKQUEUEINFOVIEW.notes1",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.notes1",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.notes1",'Field', 'UpdateName',[dbo.BasicFormData.notes1])
	DBSetProp("WORKQUEUEINFOVIEW.notes1",'Field', 'DataType',[M])
	
	DBSetProp("WORKQUEUEINFOVIEW.notes2",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.notes2",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.notes2",'Field', 'UpdateName',[dbo.BasicFormData.notes2])
	DBSetProp("WORKQUEUEINFOVIEW.notes2",'Field', 'DataType',[M])
	
	DBSetProp("WORKQUEUEINFOVIEW.reviewdate",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.reviewdate",'Field', 'Updatable',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.reviewdate",'Field', 'UpdateName',[dbo.BasicFormData.ReviewDate])
	DBSetProp("WORKQUEUEINFOVIEW.reviewdate",'Field', 'DataType',[T])
	
	DBSetProp("WORKQUEUEINFOVIEW.reviewby",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.reviewby",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.reviewby",'Field', 'UpdateName',[dbo.BasicFormData.ReviewBy])
	DBSetProp("WORKQUEUEINFOVIEW.reviewby",'Field', 'DataType',[C(40)])
	
	DBSetProp("WORKQUEUEINFOVIEW.assigned",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.assigned",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.assigned",'Field', 'UpdateName',[dbo.BasicFormData.Assigned])
	DBSetProp("WORKQUEUEINFOVIEW.assigned",'Field', 'DataType',[L])
	
	DBSetProp("WORKQUEUEINFOVIEW.document_path",'Field', 'KeyField',.F.)
	DBSetProp("WORKQUEUEINFOVIEW.document_path",'Field', 'Updatable',.T.)
	DBSetProp("WORKQUEUEINFOVIEW.document_path",'Field', 'UpdateName',[dbo.Documents.Document_path])
	DBSetProp("WORKQUEUEINFOVIEW.document_path",'Field', 'DataType',[M])

Contributors Carl Karsten, Randy Jean
( Topic last updated: 2006.01.25 02:16:42 PM )