WX12 - HFCS: Scheduled Tasks and hDeclareExternal()

Hi,

I'm planning to deploy scheduled tasks on the HFCS server which would trigger a stored procedure. However, in the help it is stated that if you define these schedule entries via the HFCS Control Center, that all files used in the accompanied stored procedure should be declared using hDeclareExternal(). However, there is no example nor on the value that should be entered in the connection parameter of this statement (it is fired off by the server itself, so why should I use a connection param?). Another question is: should I also need to use the hDeclareExternal for stored queries I have on the server and that are used by the same stored procedure? I noticed that everything works fine when debugging straight out of the editor, but that it doesn't work from the server... Any clues out there?

Thanks in advance!

Cheers,

Peter H.

Hi Peter,

I tried myself to use a stored procedure via the HFCS scheduler, and also had no luck. I don't have any words of wisdom for you, only assure you that you aren't the only person having this problem.

Rgds
Paul

Hi Paul,

Thx for your kind words :cheers:.
I've been playing around now for a week with HFCS stored procedures, scheduled tasks, stored queries et all (in WebDev 12) and I can't succeed in getting basic things to work in a stable manner. It all looks pretty unmature to me with numerous crashes if you have your project hooked up to a HFCS SCM (GDS). Also, perfectly written stored procedures using a query would come up all the time with errors saying the stored queries used in the stored procedure aren't recognized requiring to import them over and over again in the analysis in order to recompile the stored procedure successfully. I even removed all server side triggers back to the old style application (and thus client-side) triggers because I just couldn't get the HFCS server side triggers to work in a stable manner: Sometimes they would trigger, sometimes (after a regeneration) they wouldn't. In other words: not very trustworthy and that is the least you expect from server side logic...
I run everything (SCM, WebDev apps, ...) over a hosted HFCS over the internet and all this hassle is currently a real showstopper on my current project. I hope PCSoft gets their act together in this area because here definitetly something is not as good as it should be... And the documentation (yep even the French one) is pretty limited in this area as well. I even went through the latest 7 LST's from PCSoft (to which I'm subscribed) and no... nothing in there as well...

Let me know if you get it solved or if I am just missing a simple thing...

Cheers,

Peter

Hi Paul,

I got it working at last. A few tricks you need to take into account.

SP_EmailFrozenStatusExpiration()
//Local variables
...

//For use in a scheduled environment we need to declare all the files we will use as external resources or that will be used by a stored query used in the code
HDeclareExternal("Affiliate.FIC","Affiliate")
HDeclareExternal("Affiliate_Customer.FIC","Affiliate_Customer")
HDeclareExternal("City.FIC","City")
HDeclareExternal("Region.FIC","Region")
HDeclareExternal("Country.FIC","Country")
HDeclareExternal("Coworker.FIC","Coworker")
HDeclareExternal("CoworkerRole.FIC","CoworkerRole")
HDeclareExternal("Customer.FIC","Customer")
HDeclareExternal("CustomerContact.FIC","CustomerContact")
HDeclareExternal("Language.FIC","Language")
HDeclareExternal("MeetingRequest.FIC","MeetingRequest")
HDeclareExternal("Status.FIC","Status")
HDéclareExterne("StatusHierarchy.FIC","StatusHierarchy")
HDéclareExterne("SystemSetting.FIC","SystemSetting")
HDéclareExterne("LogBook.FIC","LogBook")

WHEN EXCEPTION IN 
	//Write start entry in logbook first
	LogBook.Type="SCHEDULED_TASK"
	LogBook.Originator="SP_EmailFrozenStatusExpiration"
	LogBook.Description="Launched on: "+Today()+" at "+ TimeToString(Now(),"HH:MMS:CC")
	LogBook.CreatedBy=RéseauUtilisateur()
	LogBook.CreatedOn=Today()+Now()
	HAdd(LogBook)

	//Do Logic
	Qry_Affiliate_Customer_SelectExpiredFrozenStatus.pFrozenStatusID = lint_FrozenStatusID
	Qry_Affiliate_Customer_SelectExpiredFrozenStatus.pToday = Today()
	IF HExécuteRequête(Qry_Affiliate_Customer_SelectExpiredFrozenStatus) THEN
		FOR ALL Qry_Affiliate_Customer_SelectExpiredFrozenStatus
			//Get the record
			HReadSeekFirst(Affiliate_Customer,AffiliateCustomerID,Qry_Affiliate_Customer_SelectExpiredFrozenStatus.AffiliateCustomerID)
			IF HFound(Affiliate_Customer) THEN
				HTransactionStart()
				... //etc...
			END
		END
	END
	//Write end entry in logbook
	LogBook.Type="SCHEDULED_TASK"
	LogBook.Originator="SP_EmailFrozenStatusExpiration"
	LogBook.Description="Completed on: "+Today()+" at "+ TimeToString(Now(),"HH:MMS:CC")
	LogBook.CreatedBy=RéseauUtilisateur()
	LogBook.CreatedOn=Today()+Now()
	HAdd(LogBook)
DO
	//Write error entry in logbook
	LogBook.Type="SCHEDULED_TASK"
	LogBook.Originator="SP_EmailFrozenStatusExpiration"
	LogBook.Description="Failed on: "+Today()+" at "+ TimeToString(Now(),"HH:MMS:CC")+CR+CR+"System Error:"+CR+RepeatString("_",30)+CR+ErreurInfo(errComplet)+CR+CR+"Database Error:"+CR+RepeatString("_",30)+CR+HErreurInfo(hErrComplet)
	LogBook.CreatedBy=RéseauUtilisateur()
	LogBook.CreatedOn=Today()+Now()
	HAdd(LogBook)
END

Next I deployed the changes to the HFCS server and used the built-in HFCS control center task scheduler to setup the schedule (e.g. in test environment every minute). And yep, it runs fine... I hope this gets you started as well!

Cheers,

Peter

Great news Peter! Does the HDeclareExternal require any path to the physical file? I notice you only have a file name.

Rgds
Paul

Hi Paul,

You don't need to pass any path (maybe a relative one if you have setup your HFCS database with subdirectories or want to connect to files in e.g. a common database although I'm not sure about that) or connection parameter since the scheduled task is defined against a specific database. Just identical to the example provided.

Cheers,

Peter H.

Thanks Peter. I now have my scheduled stored procedures working.

Rgds
Paul