Baanboard.com

Go Back   Baanboard.com > Forum > Baan SIGs > Code & Utilities

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 4th June 2005, 17:44
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
general functions for table field manipulations

We have written certain functions which can help one do work on all fields of any table . These functions have helped us extracting certain masters online and generating complete record information in a log based on certain events.

These functions can be encoded in a dll and re-used in any script.
Code:
	domain 	tcmcs.st14 	 cdomn
	long 	coffset,csize,cdept,ctype,cflag,no_key,intlen,reallen,no_col
	string	cdflt
	string flddmn(14),oformat,lechar,ilchar,errmess,iformat,clnm(18)
	long adjust , exprid ,plen , i
	long flcount
	long sort_def(1,4)
	extern string 	recstr(2048)
	domain tcmcs.str9 ptabl,rtfldr, sptr                    |MBD 281204
	extern domain tccuno tmpcuno
	domain tcmcs.st56 tblfl(1) based
| 	domain	tcmcs.st10 tblarr(1) based	
	domain	tcbool newtbl

function initialize.recstr.for.current.record()
{
|Purpose - This functions extracts the current values of a record  of table
|   		(for which data dictionary information is loaded in memory  
|		using function init.table.dd and array tblfl has the appropriate
|		values of the table) into a string tblrec which can be used to
|		write to a file straight away .
|Known Issues - It will create string with the help of separator | . One can have 
|		different separators if one so desires.
|		Date fields will be written as Baan date numbers as against 
|		normal mmddYYYY format.
|Pre-requisites - Array tblfl properly initialized with table dd and a record in the same
|		should be current.
|Output		-	string recstr will hold the complete record buffer of a table.
	long i  , fldec
	string tblrec(3999)
	recstr = ""
	tblrec = ""
	for i = 1 to no_col
		on case lval(tblfl(30,i;11))
		case db.string:
		case db.multibyte:
			tblrec = tblrec & strip$(tblfl(1,i;18)) & "&""|""&" 
			break
		default:
			tblrec = tblrec & "str(" & strip$(tblfl(1,i;18)) & ")&""|""&"
		endcase
	endfor
	tblrec = tblrec(1;len(tblrec)-1)
	fldec = expr.compile(tblrec)
	recstr = s.expr$(fldec)
	expr.free(fldec)
}



function extern long filerec2tablefld(long fp , domain tcmcs.str9 tabl,long recnum)
{
|Inputs 	- fp - file pointer for a file opened with seq.open
|		- tabl - table name of which records are to be read from the file in 
|			tppmmmsss format
|		- recnum - the number of times the function is called . If it is
|		  called 1st time , then it initializes table dd in the array, else it uses 
|	          the definition in the buffer
|output 	- Function returns true when successful in reading the record and transferring the
|		  the same to  table fields. It  returns false in case of end of file and / or 
|		  record not in pre-defined format.
|purpose -  The function reads a record from a file (which is in pipe delimited format)
|	 -  and stores the values from the file to table field .
|Known issues  - dates are baan dates not in mmddyyyy formats
|		- Text numbers may need special handling 
|		- File , opening closing tobe  handled outside function
	string tblrec(3999)
	long curpos , nextpos
	string curvl(500)
	long argnum , i,numelmt,fldec
	domain tcmcs.st17 idxarg(32)
	recstr = ""
	if seq.gets(recstr,2048,fp) < 0 then
|		message("File can not be read from ; probably EOF ")
		return(false)
	endif
	curpos = 0 
	nextpos = 0
	if recnum = 1 then
		init.table.dd(tabl,tblfl)
	endif
	tblrec = ""
	for i = 1 to no_col
		nextpos = pos(recstr(curpos+1),"|")
		if nextpos = 0 then
			if i = no_col then
				nextpos = len(recstr(curpos))
			else
				return(false)
			endif
		endif
		curvl = recstr(curpos+1;nextpos-1)
		on case lval(tblfl(30,i;11))
		case db.string:
		case db.multibyte:
			put.var(pid,strip$(tblfl(1,i;18)),curvl)
			break
		case db.float:
		case db.double:
			put.var(pid,strip$(tblfl(1,i;18)),val(curvl))
			break
		case db.text:
			put.var(pid,strip$(tblfl(1,i;18)),0)
			break
		default:
			put.var(pid,strip$(tblfl(1,i;18)),lval(curvl))
			break
		endcase
		curpos = nextpos+curpos
	endfor
	return(true)

}


function init.table.dd(domain tcmcs.str9 tabl , ref domain tcmcs.st56 tblfl() )
{
|Purpose - This function is required only at the time of doing operations with 
|	all physical fields of a table . And it is desired that such table can be 
|	any table . Combined fields are to be excluded for such purpose.

|Input 	-	tblfl  - array manipulated dynamically to store table field attributes
|		tabl   - Any baan table tppmmmsss format.
|Output	-	Array tblfl will have complete table definition in the array

	rdi.table(tabl(2) ,no_key,no_col,intlen,reallen)
	flcount = 0
	for i = 5 to  no_col 
		rdi.table.column(tabl(2),i,
		clnm,flddmn,coffset,csize,cdept,ctype,cflag,cdflt)
		if  ctype <> db.combined then
			flcount = flcount + 1
			if alloc.mem(tblfl,56,flcount) < 0 then
				mess("tudll00014",1)
				|("Error allocating memory")
				free.mem(tblfl)
			endif
			tblfl(1,flcount) = clnm
			tblfl(19,flcount) = edit$(coffset,string.set$("9",11))
			tblfl(30,flcount) = edit$(ctype,string.set$("9",11))
			tblfl(41,flcount) = flddmn
		endif
	endfor 
	qss.start(sort_def,1,19)
	qss.type(sort_def,1,db.string)
	qss.length(sort_def,1,11)
	qss.way(sort_def,1,qss.up)
	e = qss.sort(tblfl,sort_def)
	no_col = flcount
}
Reply With Quote
  #2  
Old 7th October 2005, 05:44
Kingsto88 Kingsto88 is offline
Senior Member
 
Join Date: Oct 2004
Posts: 208
Kingsto88 is an unknown quantity at this point
Baan: Baan 4, Baan 5 - DB: SQL server, Informix - OS: Windows 2000, HP Unix
use of code

Hi Hitesh,

Could you please explain how to use your functions.

I am looking for ways to migrate the supplier table from Baan4 to Baan5. Can I use these functions and how?

Thanks and regards
Reply With Quote
  #3  
Old 7th October 2005, 16:25
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
usage for migration

Usage of this functions for migrating to 5 may not help bcos the table structures in V would be entriely different. For such purpose complete database / baan export maybe a good thing .Best thing u should refer baan migration guides for such purpose.

These functions can help u if u want to export / import complete record/s of a table with fields in the order defined in the dictionary on certain events such as delete , modify , certain exceptions etc for logging or any other use later on.
Reply With Quote
  #4  
Old 2nd May 2006, 16:40
vishbaan's Avatar
vishbaan vishbaan is offline
Senior Member
 
Join Date: Aug 2001
Location: Dubai
Posts: 207
vishbaan is on a distinguished road
Baan: B40c4, LN - DB: SQL2K - OS: W2K
how to instantiate this call

Hi Hitesh,

we want to export item data when ever there is a change or new record inserted.

your posted script seems to export the data, my problem is how to call this code in the event of an Insert / Modify or Delete in the Item Master.

Can you help pls

Thanks

Vish
__________________
Vishi...Cool
Reply With Quote
  #5  
Old 3rd May 2006, 08:06
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
invoking the dlls

Dear Vish ,

Here is the code to extract the values runtime . It is a session with 3 GL program attached to it without form . It is started automatically when user (updating master) logs in and shuts of automatically when user closes all programs .
Code:
******************************************************************************
|* Tijwx8888  0  VRC B40c c4 cust 
|* Extract masters fin runtime
|* Hitesh
|* 19-04-04 [14:48]
|******************************************************************************
|* Script Type: 0
|******************************************************************************
|* This program is for runtime  extract of masters
|******************************************************************************
	long ret,pno,lpno
	table	ttcmcs001	|Units
	table	ttcmcs002	|Currencies
	table	ttcmcs006	|Unit Sets 
	table	ttcmcs007	|Unit Sets by language
	table	ttcmcs015	|Product types
	table	ttcmcs018	|Product types
	table	ttcmcs022	|Selection Code
	table	ttcmcs023	|Item Groups
	table	ttcmcs024	|Price Groups
	table	tticpr010	|CP comps
	table	tticpr050	|Operation rate codes
	table	tticpr100	|Cost price calculation codes
	table	tticpr150	|Operation rate codes
	table 	ttiitm001	|Items 
	table 	ttiitm004	|Conversion factor
	table	ttiitm013	|Reference Designator
	table	ttibom010	|BOM
	table	ttibom020	|Reference Designator
	table	ttirou002	|Machines
	table	ttirou003	|Tasks
	table	ttirou101	|routing codes by item
	table	ttirou102	|routing codes 
	table	ttdjwx401	|routing codes 
	table 	ttijwx611	|Bom history
	

	table	ttiitm012	|Alternatve code systems
	table  ttccom010
	
	extern 	domain 	tcmcs.st10	tblarr(1) based	
	extern long  tbctr
	
	long attrs(256)
	extern string pname(15)
	domain tcbool fromjwx011 |for jwx1211s001
	domain tcbool diffcopy |whether copy made
	long 	progrun , exitctr,updtctr
	
	string delpath(100),trghost(20)
	
	domain 	tcmcs.str9	maintab
	domain tcbool  mstupd
	extern long 	updstat ,fldord , strtid
	domain 	tcbool cspec
	domain tcsrnb	idxnum,numelmt
	domain tcmcs.st17 idxfld(32),custfld
	domain 	tccuno cuno.f,cuno.t
	domain tcpono prio.f,prio.t
	domain tcdate efdt.f , exdt.f
	domain tilcid stng.f,stng.t
	domain tcsqnc sqno.f,sqno.t
	string	rtfldr(10) , sptr(1) ,clntfldr(10)
	long 	lfp  |lock file pointer
	#pragma used dll otdjwxextrdaemo
	#pragma used dll ottdllfilehand
	#pragma used dll ottdllbw
	|dir.present / file.present / file.cmp
	|proc num  ttdsk 1 other 2 9999 4 8888 8   7777  16
	
function main()
{	
	if hostname$() = "jewelex1" or hostname$() = "jewelex" then
		rtfldr = "/arch"
		sptr = "/"
	else
		if hostname$() = "JEWDELL" then
			rtfldr = "H:"
			sptr = "\"
		else
			end()
		endif
	endif
	tbctr = 1
	clntfldr = "P:"
	
	|check if the daemon is already running 
	|if yes then extract of (this program)  may not be run
	|to start appropriate session
	
	pno = pstat(parent,pname,attrs)
	while  true
		lpno = pno
		pno = pstat(pno,pname,attrs)
		|to test the pid against pno
		if pname = "tijwx9999m999"  and lpno <> pid then
		endif		
		if pno = 0 then
			break
		endif
	endwhile
	
	|to extract appropriate values from the active sessions
	
	progrun =  31
	exitctr = 0 
	mstupd = false
	pno = parent
	while true
		lpno = pno
		pno = pstat(lpno,pname,attrs)
		strtid = 0 
		cspec = false
		on case pname
		case "tcmcs0101m000":
		case "tcmcs0101s000":
		case "tcmcs0102m000":
		case "tcmcs0102s000":
		case "tcmcs0106m000":
		case "tcmcs0106s000":
		case "tcmcs0115m000":
		case "tcmcs0115s000":
		case "tcmcs0118m000":
		case "tcmcs0118s000":
		case "tcmcs0122m000":
		case "tcmcs0122s000":
		case "tcmcs0123m000":
		case "tcmcs0123s000":
		case "tcmcs0124m000":
		case "tcmcs0124s000":
		case "tcmcs0144m000":
		case "tcmcs0144s000":
		case "tibom1110m000":
		case "tibom1110s000":
		case "tibom0120m000":
		case "tibom0120s000":
		case "ticpr0110m000":
		case "ticpr0110s000":
		case "ticpr0150m000":
		case "ticpr0150s000":
		case "ticpr1101m000":
		case "ticpr1101s000":
		case "ticpr1150m000":
		case "ticpr1150s000":
		case "tiitm0120m000":
		case "tiitm0120s000":
		case "tiitm0112m000": |Client specific
		case "tiitm0112s000": |Client specific
		case "tiitm0113m000": 
		case "tiitm0113s000": 
		case "tirou0101m000":
		case "tirou0101s000":
		case "tirou0102m000":
		case "tirou0102s000":
		case "tirou0103m000":
		case "tirou0103s000":
		case "tirou1101m000":
		case "tirou1101s000":
		case "tirou1102m000":
		case "tirou1102s000":
		case "tirou2110m000":
		case "tirou2110s000":
			get.var(lpno,"g.update.status",updstat)
			on case updstat 
			case modify.set:
			case mark.delete:
			case add.set:
			case dupl.occur:
				if pname = "tibom1110m000" or pname = "tibom1110s000" then
					get.var(lpno,"tibom010.mitm",tijwx611.item)
					get.var(lpno,"logname$",tijwx611.user)
					select tijwx611.*
					from tijwx611 for update
					where tijwx611._index1 = {"tibom010",:tijwx611.item}
					selectdo
						tijwx611.date = date.num()
						tijwx611.time = time.num()
						tijwx611.user = logname$
						db.retry.point()
						db.update(ttijwx611,db.retry)
						commit.transaction()
					selectempty
						tijwx611.tabl = "tibom010"
						tijwx611.date = date.num()
						tijwx611.time = time.num()
						db.retry.point()
						db.insert(ttijwx611,db.retry)
						commit.transaction()
					endselect
				endif

				get.strtid.cspec(pname,strtid, cspec)
				get.var(lpno,"main.table$",maintab)
				get.var(lpno,"attr.id",fldord)
				if fldord >= strtid then
					table.index.info(maintab(2),1,numelmt,idxfld)  |dll
					if numelmt = 0 then
						break
					endif
					update.index.values(pname,lpno,maintab,1,updstat,
					idxfld,numelmt,custfld)
				endif
				mstupd = true
				progrun = bit.and(progrun,2)?progrun:progrun + 2 
				break
			default:
				break
			endcase
			break
		case "tiitm0101s000":
		case "tiitm0101m000":
			get.var(lpno,"g.update.status",updstat)
			on case updstat 
			case modify.set:
|			case add.set:
				get.strtid.cspec(pname,strtid, cspec)       |dll function to get where the cursor should be 
				get.var(lpno,"main.table$",maintab)
				get.var(lpno,"attr.id",fldord)
				if fldord >= strtid and tiitm001.ltcp <> 0 then
					table.index.info(maintab(2),1,numelmt,idxfld)   |dll
					if numelmt = 0 then
						break
					endif
					update.index.values(pname,lpno,maintab,1,updstat,
					idxfld,numelmt,custfld)                         |dll
					mstupd = true
					progrun = bit.and(progrun,2)?progrun:progrun + 2 
				endif
				break
			default:
				break
			endcase
			break
		case "tiitm0202s000":	|Copy item
		case "tiitm0202s100":
			get.var(lpno,"g.update.status",updstat)
			if updstat = cont.process then
				|to check choice  also
					get.var(lpno,"copy.item",tijwx611.item)
					get.var(lpno,"logname$",tijwx611.user)
					select tijwx611.*
					from tijwx611 for update
					where tijwx611._index1 = {"tiitm001",:tijwx611.item}
					selectdo
						tijwx611.date = date.num()
						tijwx611.time = time.num()
						tijwx611.user = logname$
						db.retry.point()
						db.update(ttijwx611,db.retry)
						commit.transaction()
					selectempty
						tijwx611.tabl = "tiitm001"
						tijwx611.date = date.num()
						tijwx611.time = time.num()
						db.retry.point()
						db.insert(ttijwx611,db.retry)
						commit.transaction()
					endselect
				mstupd = true
				progrun = bit.and(progrun,2)?progrun:progrun + 2 
			endif
			break
		case "tiitm0203m000":	|Delete item
		case "tiitm0203s000":
			get.var(lpno,"g.update.status",updstat)
			if updstat = cont.process then
				|to check choice  also to consider other field selections
				mstupd = true
				progrun = bit.and(progrun,2)?progrun:progrun + 2 
			endif
			break
 		case "ottdskmbrowse":
 		case "ottdskbrowser":
			|check uncopied files on server. for style flag
			|variable nooftimeserr , dirs to check
			check.uncopied.files()
			progrun = bit.and(progrun,1)?progrun:progrun + 1 
			break
		case "tijwx9999m999": |Another startup program
			progrun = bit.and(progrun,4)?progrun:progrun + 4 
			break
		case "tijwx7777m777":
			progrun = bit.and(progrun,16)?progrun:progrun + 16
			break
 		case "tijwx8888m888": |Current program
			if mstupd  and not bit.and(progrun,2) then
				if  updtctr >= 3 then
					| if  not  check.folder.lock("masters",         |dll
					| "   JAN", lfp,1,10,true) then
					|This is where actual related  files are created using updated index values in 
					|tdjwx401
					if  not  check.client.lock("masters",
						"   JAN", lfp,1,10,true) then
						|create and set a lock
						alloc.mem(tblarr,10,1)		
						set.mem(tblarr,"")						
						create.export.files("   JAN",tblarr,tbctr)         |dll
						| if  not check.folder.lock("masters",  |dll
						| "   JAN",lfp,2 , 1,true) then	|close file
|						check.client.files()
 						copy.files.2.client()
						if  not check.client.lock("masters",  |dll
						"   JAN",lfp,2 , 1,true) then	|close file					

	
						endif
						mstupd = false
						updtctr = 0
					else 
					endif
				else
					suspend(100)
					updtctr = updtctr + 1
				endif
			else
				if  not bit.and(progrun,3)   then
					exitctr =exitctr + 1
					if exitctr >= 3 then
						progrun = - 1
					endif
				else
					exitctr = 0 
				endif
				if not mstupd then
					suspend(1000)
				endif
			endif
			|create file for export to JAN in this place
			|Update everything in table jwx400 and jwx401 to a file
			| and delete the record
			|endif
			if progrun <   0 then
				break
			endif
			progrun = 0 
			break
		case "init":
		case "ottstpstdlib ":
		case "ottstppollmes":
			break
		default:
|			progrun = bit.and(progrun,2)?progrun:progrun + 2 
			progrun = bit.and(progrun,8)?progrun:progrun + 8 
		endcase
		if progrun < 0 then
			break
		endif
	endwhile 
}                                                                               

function extern get.strtid.cspec(domain tcmcs.st14 pname , ref long strtid,ref domain tcbool cspec)
{
	cspec = false
	on case pname
	case "tcmcs0101m000":
	case "tcmcs0101s000":
		strtid = 2
		break
	case "tcmcs0102m000":
	case "tcmcs0102s000":
		strtid = 2
		break
	case "tcmcs0106m000":
	case "tcmcs0106s000":
		strtid = 2
		break
	case "tcmcs0115m000":
	case "tcmcs0115s000":
		strtid = 2
		break
	case "tcmcs0118m000":
	case "tcmcs0118s000":
		strtid = 2
		break
	case "tcmcs0122m000":
	case "tcmcs0122s000":
		strtid = 2
		break
	case "tcmcs0123m000":
	case "tcmcs0123s000":
		strtid = 2
		break
	case "tcmcs0124m000":
	case "tcmcs0124s000":
		strtid = 2
		break
	case "tcmcs0144m000":
	case "tcmcs0144s000":
		strtid = 2
		break
	case "tibom1110m000":
	case "tibom1110s000":
		strtid = 8
		break
	case "tibom0120m000":
	case "tibom0120s000":
		strtid = 9
		break
	case "ticpr0110m000":
	case "ticpr0110s000":
		strtid = 2
		break
	case "ticpr0150m000":
	case "ticpr0150s000":
		strtid = 2
		break
	case "ticpr1101m000":
	case "ticpr1101s000":
		strtid = 2
		break
	case "ticpr1150m000":
	case "ticpr1150s000":
		strtid = 5
		break
	case "tiitm0101s000":
	case "tiitm0101m000":
		strtid = 2
		break
	case "tiitm0120m000":
	case "tiitm0120s000":
		strtid = 6
		break
	case "tiitm0112m000": |Client specific
	case "tiitm0112s000": |Client specific
		cspec = true
		strtid = 10
		break
	case "tiitm0113m000": 
	case "tiitm0113s000": 
		strtid = 2
		break
	case "tirou0101m000":
	case "tirou0101s000":
		strtid = 3
		break
	case "tirou0102m000":
	case "tirou0102s000":
		strtid = 3
		break
	case "tirou0103m000":
	case "tirou0103s000":
		strtid = 3
		break
	case "tirou1101m000":
	case "tirou1101s000":
		strtid = 5
		break
	case "tirou1102m000":
	case "tirou1102s000":
		strtid = 5
		break
	case "tirou2110m000":
	case "tirou2110s000":
		strtid = 10
		break
	default:
		strtid = 250
		break
	endcase
}


function extern table.index.info(domain tcmcs.str9 tabl, domain tcsrnb indx , 
			ref domain tcsrnb numelmt, ref domain tcmcs.st17 idxfld())
{
	numelmt = 0 
	set.mem(idxfld ,"")
	if  rdi.column.combined(strip$(tabl)&"._index" & str$(indx),idxfld) < 0  then 
		numelmt = 0
		return
	endif
	while not isspace(idxfld(1,numelmt+1))
		numelmt = numelmt + 1
	endwhile
}

function extern update.index.values(domain tcmcs.st14 pname, long pno,
		domain tcmcs.str9 tabl, domain tcsrnb indx, long updstat,
		ref domain tcmcs.st17 idxfld(),domain tcsrnb numelmt,
		ref domain tcmcs.st17 cusfld)
{
	string 	 idxstr(200)
	long i
	string impstr(500)
	long implong
	double impdbl
	impstr = ""
	idxstr = ""
	for i =1 to numelmt
		if len(strip$(shiftl$(idxstr))) >= 200 then
			message("Index values can not be accomodated in single container")
		endif
		rdi.column(idxfld(1,i),
		flddmn,coffset,csize,cdept,ctype,cflag,cdflt)
		if flddmn = "tccuno" then
			cusfld = idxfld(1,i) 
			get.var(pno,idxfld(1,i),impstr)
			if impstr <> "   JAN" then
				|later on add cuno in function argument
				return
			endif 
		endif
		on case ctype
		case db.string:
		case db.multibyte:
			get.var(pno,idxfld(1,i),impstr)
			if cusfld = idxfld(1,i) then
				idxstr = idxstr & strip$(idxfld(1,i)) &
				 ":=""   JAN"","
			else
				idxstr = idxstr & strip$(idxfld(1,i)) & ":=""" & impstr & ""","
			endif
			break
		case db.long:
		case db.integer:
		case db.byte:
			get.var(pno,idxfld(1,i),implong)
			idxstr = idxstr & strip$(idxfld(1,i)) & ":=" & str$(implong) & ","
			break
		case db.double:
		case db.float:
			get.var(pno,idxfld(1,i),impdbl)
			idxstr = idxstr & strip$(idxfld(1,i)) & ":=" & str$(impdbl) & ","
			break
		case db.date:
			get.var(pno,idxfld(1,i),implong)
			idxstr = idxstr & strip$(idxfld(1,i)) & ":=" & str$(implong) & ","
			break
		case db.bitset:
		case db.enum:
			get.var(pno,idxfld(1,i),implong)
			idxstr = idxstr & strip$(idxfld(1,i)) & ":=" & str$(implong) & ","
			break
		endcase
	endfor
	if len(idxstr) > 0 then
		idxstr = idxstr(1;len(idxstr)-1)
	endif

	tdjwx400.tabl = tabl
	tdjwx400.user = logname$
	tdjwx400.sess = pname
	db.retry.point()
	db.insert(ttdjwx400,db.retry,db.skip.dupl )
	commit.transaction()
	
	tdjwx401.tabl = tabl
	tdjwx401.user = logname$
	tdjwx401.sess = pname
	on case updstat 
	case modify.set:
		tdjwx401.updm  = tcqms.mode.update
		break
	case mark.delete:
		tdjwx401.updm  = tcqms.mode.delete
		break
	case add.set:
	case dupl.occur:
		tdjwx401.updm  = tcqms.mode.add
		break
	default:
	endcase
	tdjwx401.idxv = idxstr
	db.retry.point()
	db.insert(ttdjwx401,db.retry,db.skip.dupl )
	commit.transaction()

}

Certain dll functions (for which simialr source is given may not exist now .
Also note following.

1. We are not doing item masters because of dynamic stock data / cumulative stock data which we do not require in export . We use the extracted index values to write specific exchange schema exporting only specific data. Item copy /delete tracking is taken care off in this program.

2. Also need to take care off undo / escape of the commands . This is taken already care off by writing of the primary key values in a temp table and then check the same at the time of creating file whether they really exist / are deleted .

Last edited by Hitesh Shah : 3rd November 2007 at 14:11.
Reply With Quote
Sponsored Links
  #6  
Old 3rd May 2006, 16:12
vishbaan's Avatar
vishbaan vishbaan is offline
Senior Member
 
Join Date: Aug 2001
Location: Dubai
Posts: 207
vishbaan is on a distinguished road
Baan: B40c4, LN - DB: SQL2K - OS: W2K
audit trail

Dear Hitesh,

First of all, its a very good thought and effort by you, congratulations friend.

My requirement was to sense the changes done to Item Master and extract them out to another host system every half an hour.

- I was trying triggers from the underlying RDBMS SQL 2000 ; it didnt work,
may be bcos baan has implicit commits.
- I was trying baan audit, but then to decode baan audit file and update
another host table regularly seemed a trouble.
- Now checking of parallel table options(copy of items in another table) so
that always compare and take-out the differences; But this way, when no.
of items grow more(100,000+), the performance is too slow.

Your script is interesting,
as I understand, you seem to sense from the sessions for the changes done to the tables; can u pls clarify my following questions:

1) if changes are done to tables using GTM or using an exchange scheme , is it possible to sense the items that are changed.

2) anyother means is possible to achieve the above.

Thanks and hv a nice day.

Vish
__________________
Vishi...Cool
Reply With Quote
  #7  
Old 4th May 2006, 06:25
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
audit trail

For us , it's sufficient bcos our users do not have access to GTM or exchange schema . It's working nice . We are supplementing it with day end item master exchange for static data using extracted key values.

For GTM and exchange update , I think audit trail is the best thing u can use . I have an impression , enabling audit is a performance hit on the application program . This also u can probably address by choosing only static fields in the audit . It should not be difficult to decode audit files .

Though I am not much familiar with SQL triggers as such , it should also work . Maybe some SQL server expert can help u on this.
Reply With Quote
  #8  
Old 4th May 2006, 21:10
lbencic's Avatar
lbencic lbencic is offline
Guru
 
Join Date: Dec 2001
Location: Lisle, IL, USA
Posts: 1,148
lbencic will become famous soon enough
Baan: 2.2d - LN - DB: most - OS: most
Have you thought to use Table Audit? It writes changes only automatically. This can be used in multi-company exchanges (Baan V+), but even in Baan IV it will write the changes. You need special functions to read this audit then.

I see Hitesh has suggested. It can be a performance hit on transaction tables. For the item master, USUALLY not changed as often as a transaction table, it is done without TOO much of a hit, and can always be turned off if it affects you too much.
Reply With Quote
  #9  
Old 5th May 2006, 06:00
Hitesh Shah's Avatar
Hitesh Shah Hitesh Shah is offline
Guru
 
Join Date: Nov 2001
Location: Mumbai,India
Posts: 1,855
Hitesh Shah is on a distinguished road
Baan: triton,Baan IVc4 , ERP Ln - DB: Oracle/Bisam/SQL 2000/SQL 2005 - OS: Sun Solaris/Windows 2003
audit managemnt

I had considered this possibility .But ruled it out in favour of the above mentioned custom program for following reasons .

1. Audit causes performance overhead on application programs updating the audit enabled tables .e.g item master is updated by many programs for inventories and other dynamic data.
2. Again there is need to write programs to decode the audit files and audit DD and possibly table DD for this purpose .

I wonder why SQL server triggers also should not work for this matter. I did not understand implicit commits Vish talked about .
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Forcing input on a string field jcook331 Tools Development 4 16th November 2004 21:16


All times are GMT +2. The time now is 01:03.


©2001-2017 - Baanboard.com - Baanforums.com