Hi Nick,

I hadn't thought about the absence of the COMPILE code at run time. Sorry :-(

But maybe all is not lost. I have had a play at inserting the source and object code directly and the results are below. I have tried to break it for the last hour but it seems robust. It probably needs a user to break it.

I have tested the code with two other instances of VFP accessing the database at the same time, but this was all on the same PC. Funny things can happen on networks.

I get a couple of errors when I compile, It's looking for the procedures. For some reason EXTERNAL doesn't work, so I ignored them.

If this doesn't do it for some reason, you could update an 'offline' version of the database and copy the DBC, DCT and DCX to the 'live' setup. This would require no users to have the database open, whilst you do the copy.

HTH

Alan

SET DEFAULT TO c:\shepdev\learnsql\storedprocs

CRLF = CHR(13)+CHR(10)

**********************************************
* Test should give errors when proc is missing
**********************************************

* Start with no procedures
=DelAllStoredProc("MyDB.DBC")
OPEN DATABASE MyDB SHARED
DO TestProc1
DO TestProc2
CLOSE DATABASES

* Create some source  and object code
lcString = 'MESSAGEBOX("HELLO")'

* Store the procedure and test
=AddStoredProc("MyDB.DBC", "TestProc1", lcString)
OPEN DATABASE MyDB SHARED
DO TestProc1
DO TestProc2
CLOSE DATABASES

* Do another
lcString = 'MESSAGEBOX("GOODBYE")'

* Store the procedure and test
=AddStoredProc("MyDB.DBC", "TestProc2", lcString)
OPEN DATABASE MyDB SHARED
DO TestProc1
DO TestProc2
CLOSE DATABASES

* Delete one of the procedures
=DelStoredProc("MyDB.DBC", "TestProc1")
OPEN DATABASE MyDB SHARED
DO TestProc1
DO TestProc2
CLOSE DATABASES


*******************************
* The functions and procedures
*******************************

* Add a stored procedure
FUNCTION AddStoredProc
LPARAMETERS DBName, ProcName, SourceCode
   * Store the source code
   USE (DBName) SHARED
   LOCATE FOR ObjectName = "StoredProceduresSource"
   REPLACE Code WITH Code + "PROCEDURE "+ProcName + CRLF + SourceCode + CRLF + "ENDPROC" + CRLF
   * Update the object code
   DO StoreObjectCode
ENDFUNC

* Delete a stored procedure
FUNCTION DelStoredProc
LPARAMETERS DBName, ProcName
   * Delete from the source code
   USE (DBName) SHARED
   LOCATE FOR ObjectName = "StoredProceduresSource"
   lcProc = STREXTRACT(code, 'PROCEDURE '+ProcName, 'ENDPROC'+CRLF,1,1)
   REPLACE code WITH STUFF(code,AT(lcProc,code)-10-LEN(ProcName),LEN(lcProc)+19+LEN(ProcName),'')
   * Update the object code
   DO StoreObjectCode
ENDFUNC

* Create and store the object code
PROCEDURE StoreObjectCode
   * The source code has been updated at this point. Create the object code
   STRTOFILE(code,"temp.prg")
   COMPILE temp.prg
   * Store the object code
   LOCATE FOR ObjectName = "StoredProceduresObject"
   REPLACE Code WITH FILETOSTR("temp.fxp")
   FLUSH
   * Tidy
   DELETE FILE temp.prg
   DELETE FILE temp.fxp
   USE
ENDPROC

* Delete all stored procedures
FUNCTION DelAllStoredProc
LPARAMETERS DBName
   USE (DBName) SHARED
   LOCATE FOR ObjectName = "StoredProceduresSource"
   REPLACE code WITH ''
   * Update the object code
   DO StoreObjectCode
ENDFUNC