loCursor = NEWOBJECT("cRemoteCursor") loCursor.CreateCursor("curSelected") loCursor.SendData("curSelected") TEXT TO lcSQL TEXTMERGE NOSHOW SELECT * FROM mAccounts WHERE iID IN (SELECT iID FROM curSelected) ENDTEXT SQLPT(lcSQL, "mAccountsLK") loCursor.DropCursor("curSelected") loCursor = NULLClass (SQLPT() is a wrapper for SQLEXEC() the last .T. parameter will not raise an error if found):
*** 27/10/2007 class which duplicates the given VFP cursor onto the *** backend DEFINE CLASS cRemoteCursor AS LINE FUNCTION CreateCursor(tcCurName) LOCAL lnFields, lcSQL, lnI LOCAL ARRAY laFields[1] *** get the vfp cursor's field details lnFields = AFIELDS(laFields, tcCurName) *** drop the table if already existing THIS.DropCursor(tcCurName) *** create the table lcSQL = "" lcSQL = lcSQL + [CREATE TABLE ] + tcCurName + [ (] + CHR(13) + CHR(10) FOR lnI = 1 TO lnFields lcSQL = lcSQL + [ ] + ALLTRIM(laFields[lnI, 1]) DO CASE CASE laFields[lnI, 2] = 'C' && Character lcSQL = lcSQL + [ CHAR(] + ALLTRIM(STR(laFields[lnI, 3])) + [)] + IIF(laFields[lnI, 5], [], [ NOT NULL]) CASE INLIST(laFields[lnI, 2], 'Y', 'B') && Currency, Double lcSQL = lcSQL + [ DOUBLE PRECISION] + IIF(laFields[lnI, 5], [], [ NOT NULL]) CASE laFields[lnI, 2] = 'D' && Date lcSQL = lcSQL + [ DATE] + IIF(laFields[lnI, 5], [], [ NOT NULL]) CASE laFields[lnI, 2] = 'T' && DateTime lcSQL = lcSQL + [ TIMESTAMP] + IIF(laFields[lnI, 5], [], [ NOT NULL]) CASE laFields[lnI, 2] = 'F' && Float lcSQL = lcSQL + [ FLOAT] + IIF(laFields[lnI, 5], [], [ NOT NULL]) CASE INLIST(laFields[lnI, 2], 'G', 'Q', 'W') && General, Varbinary, Blob lcSQL = lcSQL + [ BLOB] + IIF(laFields[lnI, 5], [], [ NOT NULL]) CASE laFields[lnI, 2] = 'I' && Integer lcSQL = lcSQL + [ INTEGER] + IIF(laFields[lnI, 5], [], [ NOT NULL]) CASE laFields[lnI, 2] = 'L' && Logical lcSQL = lcSQL + [ SMALLINT] + IIF(laFields[lnI, 5], [], [ NOT NULL]) CASE laFields[lnI, 2] = 'M' && Memo lcSQL = lcSQL + [ BLOB SUB_TYPE TEXT] + IIF(laFields[lnI, 5], [], [ NOT NULL]) CASE laFields[lnI, 2] = 'N' && Numeric lcSQL = lcSQL + [ NUMERIC(] + ALLTRIM(STR(laFields[lnI, 3])) + [, ] + ALLTRIM(STR(laFields[lnI, 4])) + [)] + IIF(laFields[lnI, 5], [], [ NOT NULL]) CASE laFields[lnI, 2] = 'V' && Varchar and Varchar (Binary) lcSQL = lcSQL + [ VARCHAR(] + ALLTRIM(STR(laFields[lnI, 3])) + [)] + IIF(laFields[lnI, 5], [], [ NOT NULL]) ENDCASE lcSQL = lcSQL + [,] + CHR(13) + CHR(10) ENDFOR lcSQL = LEFT(lcSQL, LEN(lcSQL) - 3) + [)] SQLPT(lcSQL, tcCurName) ENDFUNC FUNCTION DropCursor(tcCurName) LOCAL lcSQL lcSQL = "" lcSQL = lcSQL + [DROP TABLE ] + tcCurName SQLPT(lcSQL, tcCurName, .T.) ENDFUNC FUNCTION SendData(tcCurName) LOCAL loErr AS EXCEPTION *** drop the temp view if exists TRY DROP VIEW Temp CATCH TO loErr DO CASE CASE loErr.ErrorNo = 1562 *** Ignore: Cannot find object Temp in the database. OTHERWISE THROW ENDCASE ENDTRY *** create the temp view CREATE SQL VIEW Temp ; REMOTE CONNECTION (goApp.cProjectCode + '_Data_FB') AS ; SELECT * ; FROM &tcCurName DBSETPROP("Temp", "VIEW", "SendUpdates", .T.) *** append data into the view USE Temp IN 0 SELECT Temp APPEND FROM (DBF(tcCurName)) TABLEUPDATE(.T., .T.) *** drop the view after use TRY DROP VIEW Temp CATCH TO loErr DO CASE CASE loErr.ErrorNo = 1562 *** Ignore: Cannot find object Temp in the database. OTHERWISE THROW ENDCASE ENDTRY ENDFUNC ENDDEFINE