refresh passive linked tables

[SteveK3003]

I have created an xbasic script (see below) to update several passive linked tables that I use daily. this code works just fine when I run it from the web component in working preview mode. BUT when I try to run it from a web browser, it fails. Can anyone shed any light on this?

script as follows:

function onInitialRender as p (e as p)
dim tableList as C
dim count as N
dim i as N
dim pr as P
dim table_i as C
tableList = <<%txt%
\\Giga-Alpha\A5Webroot\giga_pn.dbf
\\Giga-Alpha\A5Webroot\giga-jobmaster-jcfkl.dbf
\\Giga-Alpha\A5Webroot\giga-job_master-jcfjm.dbf
\\Giga-Alpha\giga_kitlist-jcfkl.dbf%txt%
count = line_count(tableList)
for i = 1 to (count)
table_i = word(tableList,i,crlf())
pr =a5_RefreshPassiveLinkTable(table_i,.f.,.f.)
next i


end function

[pboomwork]

Re: refresh passive linked tables

I have used Passive Link Table extensively on the desktop side. I would find that occasionally they would fail to build due to some undetermined internal Alpaha5 Error. The only solution I could find was retry building the tables several (3) times. In my situation I was building the table versus refreshing them as done in your sample. So i don't know if you am having the same issue. In any case; below is the function that I built that builds Passive Link Tables. If you study it you can see how the building of Passive Link Tables is retried.

'Date Created: 23-Jan-2013 11:07:15 AM
'Last Updated: 04-Jul-2017 07:33:29 AM
'-------------
'zBldPassTbl()
'-------------
'Builds a Passive link table from SQL Query
'Always builds new table does not refresh existing
'Has extra error trapping for rare internal ALPHA5 specific errors
'
'CnnctString 	- The named AlphaDAO connection string
'SQLTable 		- Root value for local DBF file name will be prepended with 'tmp-'
'					If no SQL statement given this is table copied over
'SQLSelect 		- Optional SQL select statement if not given default statement is
'					built using SQLTable name
'Arguments 		- Optional Arguments used in SQL statment (see note below)
'Prefix 		- Prefix to add to table name optional default is tmp-
'RETURNS		- Returns any error as a message, if no error returns null
'
'There are 3 different SQL argument formats in A5. The one partially documented with the
'A5_PassiveLinkTableDefine() function used here which is;
'
'1) Comma delimited list with pipe sperated values in the format;
'		argumentName|type|value
'	Where argument is the argument name in the SQL statement with or without leading : (colon)
'	Type is stand single (uppercase?) letter representing the field type
'	value is an optional value if not provided A5 will prompt the user for the value
'	If provided it must be provided in same format as user would provide.
'   for example; A5 will do SQL formatting on date values
'	If all values are provided A5 will not prompt for values
'    
'2) The format used internally by the sql_query_builder() function
'	and returned by the related but undocumented a5_sql_query_genie()
'	This format allows dialog structure of how the user will be prompted for the values to be defined.
'	Resembles XML in multiline character string
'	This format is compatible with the A5_PassiveLink tableDefine() function.
'
'3) SQL::Arguments	Object format. 
'	Is a pointer variable with one main property .XML which closely resembles format 2
'   This format is not compatable with the A5_PassiveLink tableDefine() function.
	
FUNCTION zBldPassTbl AS C (CnnctStrng as C, SQLTable, SQlSelect as C, Arguments as C = "", Prefix AS C = "tmp-")
	'---------------------
	'// Set Debug Flags --
	'---------------------
	DIM Debug as C = "" 										'use following statement to check for debug flag: if ("m" $ Debug)
	'zDbgSet(type::xbasicmodule.get_current(),Debug)									'set debug values in deployed enviroments
	't - trace retries
	'p - trace names of passive tables that are sucessfully created

	'// Get on with it
	CnctNm = "::Name::"+alltrim(CnnctStrng)						' Get SQL connection to use
	
	if SQLSelect = "" then										'set a default query
		SQLSelect = "select * from " + SQLTable
	end if
	
	LocalTbl = zGetTblName(Prefix + SQLTable +".dbf")			'get path adjusted names for the tables we are building
	LocalDDD = zGetTblName(Prefix + SQLTable +".ddd")
	'LocalRoot = file.filename_parse(LocalTbl,"dpn")
	
	'// Set up variables used
	DIM ErrMess as C											'Any error message returned by zEraseTable() 
	
	Delete DS													'initialize/reinitize Data Set Structure used to retrieve data
	DIM DS as P
	DS.ConnectionString = CnctNm
	DS.SQLSelectStatement = SQlSelect
	DS.Arguments = Arguments

	Dim showProgress as l										'get build function to use system status bar
	Dim allowCancel as l
	showProgress = .t.
	allowCancel = .t.

	'The following code sometimes fails on first run 
	'thier appears to be timing issues in the creation of the *.ddd file
	'These errors cause the a5_passiveLinkTableDefine method to fail
	'It seems to always work on second run 
	'the following error trap enshures we try at least 3 times
	ErrTryCnt = 3
	on error goto ErrTrap

ErrTryAgin:

	if file.exists(LocalTbl) then 								'delete any prexisting table as we always rebuild from scratch
		
		ErrMess = zEraseTable(LocalTbl,"",.F.)					'Erase existing table and delete it's support files
		IF .NOT. isnull(ErrMess)									
			error_generate(ErrMess)
		end if
	end if

	Delete ResultP												'finally run A5 command that builds table	
	DIM ResultP as p
	ResultP = a5_PassiveLinkTableDefine(DS,LocalTbl,showProgress,allowCancel)
	if ResultP.userCancelled = .t. then 
		ErrTryCnt = 0
		error_generate("User Cancelled(2)")
	else if resultP.errorText = "User Cancelled" then
		ErrTryCnt = 0
		error_generate("User Cancelled(3)")
	else if ResultP.HasError = .t. then  
		IF resultP.errorText <> "Success"
			error_generate(REMOVE_BLANK_LINES(resultP.errorText))
		end if
	else
		if ("p" $ Debug)
			zTraceStamp(type::xbasicmodule.get_current())
			trace.WriteLn(*concat_lines("Condition: Passive Link DBF created","File: " + resultP.tablefilename))
		end if
	end if 

	FILE_ADD_TO_DB(LocalTbl)									'no error add built table to database
	zBldPassTbl = ""											'return no error
	return
	
'The following Error Trap allows us to retry the SQL Squery 
'a predetermind number of times after which we log the error
ErrTrap:
	if ErrTryCnt > 0 then
		ErrTryCnt = ErrTryCnt - 1
		IF ("t" $ Debug)
			zTraceStamp(type::xbasicmodule.get_current())
			trace.WriteLn("Condition: Error doing SQL query")
			trace.WriteLn(error_text_get())
			trace.WriteLn("Trying following SQL Query again: ")
			trace.WriteLn(SQlSelect)
		end if
		resume ErrTryAgin
	else
		on error goto 0											'do not retrap with this error trap build error message
		ErrMess =<<%txt%
ERROR running SQL Query:
Script: {error_script_get()}
Line: {error_line_number_get()}
Connect String: {CnnctStrng}
Local DBF to build: {LocalTbl}
{error_text_get()}
%txt%	
																'return error message to calling script
		zBldPassTbl = Evaluate_String(ErrMess)
	end if
end function