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.

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
}

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

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.

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

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 .

******************************************************************************
|* 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 .

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

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.

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.

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 .