Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
19%
200 - 500 GB
28%
500 - 800 GB
2%
800 - 1200 GB
7%
1200 - 1500 GB
7%
1500 - 2000 GB
12%
> 2000 GB
24%
Total votes: 83

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 6th December 2019, 09:00
martin.f martin.f is offline
Junior Member
 
Join Date: Nov 2019
Location: Germany, Kempten
Posts: 29
martin.f is on a distinguished road
Baan: LN 10.5 - DB: Microsoft SQL - OS: Microsoft Windows 10
Cool Error recursion not possible
Baan: Other/Unknown
C/S: None/Unknown

Hello,
I've written a function which recursivly calculates the weight of an article by looking at the BOM and so on.
However when I run this code, I get an error: Fatal Error : Function 'calculate.weight': recursion not possible; refcount=2.

This is my code:

Code:
function domain tcwght calculate.weight(domain tcitem i.item)
{
|get all BOM positions of item i.item
        select  tibom010.*
        from    tibom010
        where   tibom010.mitm = :i.item
        selectdo
                select  tibom010.*
                from    tibom010
                where   tibom010.mitm = :tibom010.mitm
                selectdo
                        |recursion: article tibom010.mitm has BOM itself
                        weight = weight + calculate.weight(tibom010.mitm)
                selectempty
                        |base case: stop recursion
                        select  tcibd001.wght
                        from    tcibd001
                        selectdo
                                weight = weight + tcibd001.wght
                        selectempty
                                weight = weight + 0
                        endselect
                endselect
        selectempty
        |do nothing
        endselect
                
return (weight)
}
Are recursions not supported in Baan/LN 4GL programming?
Reply With Quote
  #2  
Old 6th December 2019, 09:37
günther günther is offline
Guru
 
Join Date: Jan 2002
Location: Ehingen, Germany
Posts: 595
günther is on a distinguished road
Baan: IVc4 - DB: Informix - OS: HP-UX
Hello Martin.

Recursion is possible, but limited: No arguments to recursive functions.

Please note that the standard BOM explosion functions are handled without recursion. For that, you have to handle a stack on your own.

Regards
Günther
Reply With Quote
Sponsored Links
  #3  
Old 6th December 2019, 09:43
martin.f martin.f is offline
Junior Member
 
Join Date: Nov 2019
Location: Germany, Kempten
Posts: 29
martin.f is on a distinguished road
Baan: LN 10.5 - DB: Microsoft SQL - OS: Microsoft Windows 10
Hello Günther,
thanks for the info.
When doing this without passing arguments and using global variables, I get this error message:
Process 12 - Fatal Error : Number of Function calls exceeds maximum of 500 stack entries


What do you mean with standard BOM explosion functions?
Are there already predefined functions I can use, do you know a possibility how to calculate the weight of an article with nested BOM?

Last edited by martin.f : 6th December 2019 at 09:53.
Reply With Quote
  #4  
Old 6th December 2019, 10:19
tmannais's Avatar
tmannais tmannais is offline
Senior Member
 
Join Date: Jul 2017
Location: Bangkok, Thailand
Posts: 252
tmannais is on a distinguished road
Baan: LN 10.5, 10.6, 10.7 - DB: MSSQL - OS: Windows
Quote:
Originally Posted by martin.f View Post
When doing this without passing arguments and using global variables, I get this error message:
Process 12 - Fatal Error : Number of Function calls exceeds maximum of 500 stack entries
This looks like your function goes into a very long recursion or it just never reaches the base case, hence stack overflow.
Reply With Quote
  #5  
Old 6th December 2019, 11:11
martin.f martin.f is offline
Junior Member
 
Join Date: Nov 2019
Location: Germany, Kempten
Posts: 29
martin.f is on a distinguished road
Baan: LN 10.5 - DB: Microsoft SQL - OS: Microsoft Windows 10
Yes, there was a bug in the code.

Now I have written it like this:

Code:
function domain tcwght calculate.weight()
{
|get all BOM positions of item i.item
        select  tibom010.*
        from    tibom010 
        where   tibom010.mitm = :item.w
        selectdo
                item.w = tibom010.sitm
                select  tibom010.*
                from    tibom010 
                where   tibom010.mitm = :item.w
                selectdo
                        |recursion: article tibom010.mitm has BOM itself
                        |message(str$(item.w))
                        item.w = tibom010.mitm
                        weight = weight + calculate.weight()
                selectempty
                        |base case: stop recursion
                        select  tcibd001.wght
                        from    tcibd001
                        where   tcibd001.item = :item.w
                        selectdo
                                message(str$(weight))
                                weight = weight + tcibd001.wght
                        selectempty
                        endselect
                endselect
        selectempty
        |do nothing
        endselect
                
return (weight)
}

Now I get this error:
Process 15 - Error : SQLState HY010: Function sequence error - Fetch called out of sequence (calculate.weight() in object otxesscpr2210ma.dbg)

When I use the debugger, I see that the recursion is going down to the leaf node.
However, after a return the execution is not jumping back to line which is underlined + bold.
But this is how a recursion should work.

What am I doing wrong?

Thanks!

Last edited by martin.f : 6th December 2019 at 11:46.
Reply With Quote
  #6  
Old 6th December 2019, 16:15
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,409
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Seems to me in 4c4 I had problems with using something like this:
Code:
        select  tibom010.*
        from    tibom010 
        where   tibom010.mitm = :item.w
        selectdo
                item.w = tibom010.sitm
                select  tibom010.*
                from    tibom010 
                where   tibom010.mitm = :item.w
                selectdo
                        |recursion: article tibom010.mitm has BOM itself
                        |message(str$(item.w))
                        item.w = tibom010.mitm

I am wondering if would be possible to use an alias on the second query against tibom010? Not even sure that would solve the issue.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #7  
Old 6th December 2019, 20:32
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,118
NPRao will become famous soon enough
Baan: Baan 4-5,5.2(Reger),LN-6.1,Infor LN-10.x - DB: Oracle,MS-SQL - OS: HPUX, Linux, Windows
Mark,

Quote:
I am wondering if would be possible to use an alias on the second query against tibom010? Not even sure that would solve the issue.
Did you try using - on.main.table()
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
  #8  
Old 6th December 2019, 21:02
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,409
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
I did not know about that command at that time. But Martin should give it a try. In my case I was not doing recursion like this - so an alias was a quick solution. From that point forward whenever I did something like this I started using aliases. And back in the good old days when we did baan we never really got a chance to go back and fix somethings. If it wasn't broke we did not fix. Speaking of which we had 1 program running for a good 10 years with a bug in it - they discovered it this year and I did get to fix it. Whoohoo.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #9  
Old 7th December 2019, 12:15
martin.f martin.f is offline
Junior Member
 
Join Date: Nov 2019
Location: Germany, Kempten
Posts: 29
martin.f is on a distinguished road
Baan: LN 10.5 - DB: Microsoft SQL - OS: Microsoft Windows 10
Thanks guys. I will try the on.main.table() command.
So you mean doing the recursive call like this:
Code:
weight = weight + on.main.table(calculate.weight())
What will do the on.main.table() function? I do not really understand the manual entry for this function.

What I will also try is solving this by a stack based algorithm (see comment #3):

http://www.baanboard.com/baanboard/s...ad.php?t=64488

Last edited by martin.f : 7th December 2019 at 12:47.
Reply With Quote
  #10  
Old 9th December 2019, 11:18
vahdani's Avatar
vahdani vahdani is offline
Guru
 
Join Date: Aug 2002
Location: Cologne, Germany
Posts: 443
vahdani is on a distinguished road
Baan: all - DB: all - OS: Unix / Win2K
Hallo everyone,
Sometime back I had to find a solution for the same recursion problem. I chose the path of Using dynamic SQL and created a very usefull DLL down below. I offer this as a present to all Baan programmers.

Here an example of how you can use the DLL to solve your weight problem:
Code:
|******************************************************************************
|* tixbo990
|* Test BOM dll
|* H.Vahdani
|* 21.12.12 [22:50]
|******************************************************************************
|* Script Type: 0
|******************************************************************************
extern	domain	tcitem		g.components(1)		based
extern	domain	tcqiv1		g.quantity(1)		based
extern		long		g.componets.count

#pragma used dll otixbodll0010

function main()
{
	domain	tcitem	l.mitm fixed
		long	i
	domain	tcwght	l.total.weight

	l.mitm(10) = "TESTITEM01"


	tixbodll0010.explode.multilevel.bom(l.mitm)

	g.componets.count = g.componets.count

	|Following to get your total weight
	l.total.weight = 0
	for i = 1 to g.componets.count
		l.total.weight = l.total.weight +
			g.quantity(i) * weight.of(g.components(1,i))

	endfor

	l.total.weight = l.total.weight
}

function domain tcwght weight.of(domain tcitem i.item)
{
	..... calculate and return weight of one unit of i.item
}

And now the masterpiece itself:
Code:
|******************************************************************************
|* tixbodll0010
|* Explode multilevel BOM
|* H.Vahdani
|* 21.12.12 [21:16]
|******************************************************************************
|* Script Type: Library
|******************************************************************************
	table	ttibom010

|Internal usage variables
	domain	tcitem		g.mitm.stack(1) 	based
	domain	tcqiv1		g.qana.stack(1)		based
 	domain	tcqiv1		g.excl.stack(1)		based
   		long		g.query.ids(1)		based
		long		g.level
		long		g.stack.size
		long		g.components.array.size
		long		g.item.len

|Results:
extern	domain	tcitem		g.components(1)		based
extern	domain	tcqiv1		g.quantity(1)		based
extern	domain	tcmcs.long	g.exclusivity(1)	based
extern		long		g.componets.count


#define	ALLOC_DELTA	50
#include <bic_dam>
#include "itcmcs2000"	|RETIFNOK,...
	  		                                                                              
function extern long tixbodll0010.explode.multilevel.bom(domain	tcitem	i.mitm)
{
	domain	tcitem	l.mitm	fixed
	domain	tcitem	l.sitm	fixed
	domain	tcqiv1	l.qana, l.mitm.qana, l.sitm.qana
		double	l.unom
	domain	tckitm	l.kitm
		long	l.query.id
		long	l.ret
	domain	tcdate	l.as.of.date
	string		l.query.string(500)
		long	l.convert
		long	l.parent.exclusivity
		long	l.exclusivity

	free.mem(g.mitm.stack)
	free.mem(g.qana.stack)
	free.mem(g.excl.stack)
	free.mem(g.query.ids)
	free.mem(g.components)
	free.mem(g.quantity)
	free.mem(g.exclusivity)
	
	rdi.domain.string("tcitem", g.item.len, l.convert)

	l.as.of.date = utc.num()
	
	l.query.string = 
		"select tibom010.sitm:1, " &
			"tibom010.qana:2, " &
			"tiipd001.unom:3, " &
			"tcibd001.kitm:4 "  &
		"from tibom010, tcibd001, tiipd001 " &
		"where tibom010.mitm = :5 " &
		"and tibom010.indt <= :6 " &
		"and (tibom010.exdt = 0 or tibom010.exdt >= :7) " &
		"and tiipd001.item = tibom010.mitm " &
		"and tcibd001.item = tibom010.sitm "

	g.level = 1
	g.stack.size = 0
	g.componets.count = 0
	g.components.array.size = 0
	l.parent.exclusivity = 1

	RETIFNOK(store.in.stack(i.mitm, 1.0, 1))

	repeat	
		|pop stack values
		l.query.id = g.query.ids(g.level)
		l.mitm = g.mitm.stack(1, g.level)
		l.mitm.qana = g.qana.stack(g.level)
		l.parent.exclusivity = g.excl.stack(g.level)
		if l.query.id = 0 then
			l.query.id = sql.parse(l.query.string)
			g.query.ids(g.level) = l.query.id
			sql.select.bind(l.query.id, 1, l.sitm) 
			sql.select.bind(l.query.id, 2, l.qana) 
			sql.select.bind(l.query.id, 3, l.unom) 
			sql.select.bind(l.query.id, 4, l.kitm) 
			sql.where.bind(l.query.id, 5, l.mitm) 
			sql.where.bind(l.query.id, 6, l.as.of.date) 
			sql.where.bind(l.query.id, 7, l.as.of.date) 
			sql.exec(l.query.id)
		endif
		l.ret = sql.fetch(l.query.id)
		on case l.ret
		case 0:	|Next record is fetched
			l.sitm.qana = l.qana/l.unom * l.mitm.qana
			l.exclusivity = get.exclusivity(l.sitm) * l.parent.exclusivity
			RETIFNOK(register.item.quantity(l.sitm, l.sitm.qana, l.exclusivity))
			if l.kitm = tckitm.manufacture then
				|go one level deeper:
				g.level = g.level + 1
				|push stack values
				l.parent.exclusivity = l.exclusivity
				RETIFNOK(store.in.stack(l.sitm, l.sitm.qana, l.exclusivity)) 
				RETIFNOK(check.for.cycle())
				|search next bom level
			endif
			break
		case eendfile:
		case enorec:
			|Done searching this level.
			|First close sql for this level 
			sql.close(l.query.id)
			g.query.ids(g.level) = 0
			|Go back one level and continue search
			g.level = g.level - 1
			break
		endcase
	until g.level = 0
	
	free.mem(g.mitm.stack)
	free.mem(g.qana.stack)
	free.mem(g.excl.stack)
	free.mem(g.query.ids)

	return(0)
}

function long register.item.quantity(	domain	tcitem	i.item,
					domain	tcqiv1	i.quan,
					long		i.excl)
{
	domain	tcitem	l.item	fixed
		long	i, l.ret
		boolean	l.registered
	
	l.registered = false

	if g.components.array.size >= 1 then
		for i = 1 to g.components.array.size
			l.item	= g.components(1, i)
			if l.item = i.item then
				g.quantity(i) = g.quantity(i) + i.quan
				l.registered = true
				break
			else
				if isspace(l.item) then
					g.components(1, i) = i.item
					g.quantity(i) =  i.quan
					g.exclusivity(i) = i.excl
					g.componets.count = g.componets.count + 1
					l.registered = true
					break
				endif
			endif
		endfor
	endif
	
	if not l.registered then
		|max stack size is reached
		g.components.array.size = g.components.array.size + ALLOC_DELTA
		l.ret = alloc.mem( g.components, g.item.len, g.components.array.size)
		if l.ret < 0 then 
			dal.set.error.message("tcfins0001")
			|Not enough memory.
			return(DALHOOKERROR)
		endif 
		l.ret = alloc.mem( g.quantity, g.components.array.size)
		if l.ret < 0 then 
			dal.set.error.message("tcfins0001")
			|Not enough memory.
			return(DALHOOKERROR)
		endif
		l.ret = alloc.mem( g.exclusivity, g.components.array.size)
		if l.ret < 0 then 
			dal.set.error.message("tcfins0001")
			|Not enough memory.
			return(DALHOOKERROR)
		endif
		g.componets.count = g.componets.count + 1
		g.components(1, g.componets.count) = i.item
		g.quantity(g.componets.count) = i.quan
		g.exclusivity(g.componets.count) = i.excl
	endif		
		
	return(0)
}

function long check.for.cycle()
{
	long	i
	domain	tcitem	l.actual.mitm	fixed

	l.actual.mitm = g.mitm.stack(1, g.level)

	for i = 1 to (g.level-1)
		if g.mitm.stack(1, i) = l.actual.mitm then
			dal.set.error.message("tiboms10000", l.actual.mitm)
			|Loop(s) detected in BOM of %1$s:
			return(DALHOOKERROR)
		endif
	endfor
	
	return(0)
}

function long store.in.stack(	domain	tcitem	i.mitm,
				domain	tcqiv1	i.qana,
				long		i.excl)
{
	long	l.ret

	if g.stack.size < g.level then
		g.stack.size = g.stack.size + ALLOC_DELTA
		l.ret = alloc.mem( g.mitm.stack, g.item.len, g.stack.size)
		if l.ret < 0 then 
			dal.set.error.message("tcfins0001")
			|Not enough memory.
			return(DALHOOKERROR)
		endif
		l.ret = alloc.mem( g.qana.stack, g.stack.size)
		if l.ret < 0 then 
			dal.set.error.message("tcfins0001")
			|Not enough memory.
			return(DALHOOKERROR)
		endif
		l.ret = alloc.mem( g.excl.stack, g.stack.size)
		if l.ret < 0 then 
			dal.set.error.message("tcfins0001")
			|Not enough memory.
			return(DALHOOKERROR)
		endif
		l.ret = alloc.mem( g.query.ids, g.stack.size)
		if l.ret < 0 then 
			dal.set.error.message("tcfins0001")
			|Not enough memory.
			return(DALHOOKERROR)
		endif
	endif
	g.mitm.stack(1, g.level) = i.mitm
	g.qana.stack(g.level) = i.qana
	g.excl.stack(g.level) = i.excl
	return(0)
}

function long get.exclusivity(domain tcitem i.sitm)
{
	|This function returns the number of BOMs in which this
	|Item is used as a component
	long		l.exclusivity
	domain	tcitem	l.mitm fixed
	domain	tcdate	l.as.of.date

	l.as.of.date = utc.num()

	l.exclusivity = 0

	l.mitm = tibom010.mitm	|Save

	select	tibom010.mitm
	from	tibom010
	where	tibom010.sitm = :i.sitm
	and	tibom010.indt <= :l.as.of.date
	and	(tibom010.exdt = 0 or tibom010.exdt >=:l.as.of.date)
	group by tibom010.mitm
	selectdo
		l.exclusivity = l.exclusivity + 1
	endselect
	
	tibom010.mitm = l.mitm	|restore
	
	if l.exclusivity < 1 then
		l.exclusivity = 1
	endif
	
	return(l.exclusivity)
}	
|******************************  end of source  *******************************
__________________
May the force be with you!
Reply With Quote
  #11  
Old 9th December 2019, 11:46
martin.f martin.f is offline
Junior Member
 
Join Date: Nov 2019
Location: Germany, Kempten
Posts: 29
martin.f is on a distinguished road
Baan: LN 10.5 - DB: Microsoft SQL - OS: Microsoft Windows 10
Hello vahdani,

thank you for sharing this code.
I am quite new to LN programming. How can I create a dll on myself?
Where must it be saved in LN?
Reply With Quote
  #12  
Old 9th December 2019, 14:04
vahdani's Avatar
vahdani vahdani is offline
Guru
 
Join Date: Aug 2002
Location: Cologne, Germany
Posts: 443
vahdani is on a distinguished road
Baan: all - DB: all - OS: Unix / Win2K
Hello Martin,

to define Libraries (DLLs) we use the standard tools program "Program Scripts/Libraries" (ttadv25030m000) You can use any module and DLL-name that you prefer.
__________________
May the force be with you!
Reply With Quote
  #13  
Old 13th January 2020, 18:55
avpatil avpatil is offline
Guru
 
Join Date: Feb 2002
Posts: 746
avpatil is on a distinguished road
Baan: IVc3 - DB: SQL2000 - OS: Win 2003
Try this.
Code:
function read.standard.bom()
{
	in.item = stack.item(1,l.count)
	
	 l.count = l.count + 1

	sql.x=sql.parse("select tibom010.sitm, " &
			"tibom010.pono, " &
			"tibom010.indt, " &
			"tibom010.exdt " &
			"from	tibom010 " &
			"where	tibom010._index1 = {:1} " & |  and "  &
			"as prepared set")
	sql.where.bind(sql.x,1,in.item)
	sql.by.level(l.count) = sql.x
	sql.exec(sql.x)
	while	(sql.fetch(sql.x) = 0)
			stack.item(1,l.count) = tibom010.sitm
					
			read.standard.item()

endwhile
}

function read.standard.item()
{
	select	tcibd001.item,tcibd001.cpva,tcibd001.opts,tcibd001.kitm,
		tcibd001.zwth,tcibd001.hand,tcibd001.ctyp,tcibd001.cpln
	from	tcibd001
	where	tcibd001._index1 = {:in.item}
	and     tcibd001._compnr = :bom.comp
	
	as prepared set
	hint no hints
	selectdo
	if sitm.kitm = tckitm.manufacture then
			read.standard.bom()
	
        endif
}

Last edited by mark_h : 13th January 2020 at 19:25. Reason: Add code tags for readability
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 On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +2. The time now is 21:45.


©2001-2018 - Baanboard.com - Baanforums.com