Maximum Session Open Tables Exceeded

[Charles Hoens]

I thought my code was ok, but suddenly I am getting an error of "Maximum Session Open Tables Exceeded". I have tried closing tables during this rather long iteration, but apparently I haven't found the problem so I am posting this code. Just so you know, this is an invoicing program which iterates through each Consumer, collects certain variables from other tables, then zaps a temp file and puts the results into that zapped table, so that the invoice can be written.

Right now I am going through each record in a form, selecting the records to print and then printing each one. I was attempting to use this script to do this all automatically.

I hope this makes sense:

'Date Created: 11-May-2014 02:58:41 PM
'Last Updated: 15-Jun-2014 08:53:54 PM
'Created By  : Charles Hoens
'Updated By  : Charles Hoens

NEXTT:
dim global jac as c=""
dim global wac as c=""
dim global fac as c=""
dim global sac as n=0
dim global eac as c=""
dim global rac as c=""
dim global cac as n=22
dim global yac as d
dim global pac as c=""
dim global xac as c=""
dim global qac as l=.F.
dim global mult as l=.F.


debug(1)	
s=table.open("consumers")

query.filter="isactive=.T."
qry3=s.query_create()
s.fetch_first()
while .not. s.fetch_eof()
jac=s.conid
wac=s.PROVId
pac=s.consumerid
fac=word(s.lastfirst,2)+" "+word(s.lastfirst,1)
u=table.open("providers")
query.filter="provid=var->wac"
qry=u.query_create()
dim global hac as c=u.pname
qry.drop()

if wac="P0001"
	cac=22
end if
if wac="P0002"
	cac=22
end if
if wac="P0003"
	cac=10
end if
if wac="P0004"
	cac=10
end if
if wac="P0005"
	cac=10
end if
if wac="P0006"
	cac=22
end if
if wac="P0007"
	cac=10
end if


u=table.open("waivercodes")
u.fetch_first()
while .not. u.fetch_eof()
sac=0
yat=u.wcode
'debug(1)
r=table.open("temp_report.dbf")
r.zap(.T.)

t=table.open("timehours")
query.filter = "conid = '" + jac + "'.and.isbilled='No'.and.wcode= '" + yat + "'"
query.order = "edate"
qry=t.query_create()

opt=a5_get_records_in_query("timehours",query.filter)
if opt=0
	goto NEXTITER
end if


t.fetch_last()
yac=t.edate
if (t.empno="E100000070" .and. t.conid="C0039")
	'qac=.T.
	wac="P0003"
	cac=10
	'qac=.F.
end if

t.fetch_first()
while .not. t.fetch_eof()
	sac=sac+1
	t.change_begin()
	t.charge=.T.
	t.change_end()
	r.enter_begin()
	r.empno = t.empno
	r.lastfirst=t.lastfirst
	r.edate=t.edate
	r.sttime1=t.sttime1
	r.sttime2=t.sttime2
	r.edate2=t.edate2
	r.endtime1=t.endtime1
	r.endtime2=t.endtime2
	r.tothours=t.tothours
	if t.servicename="Day Program".or.t.servicename="Transport"
		r.tothours2=1
	else
		r.tothours2=t.tothours2
	end if
	r.consumer=word(t.consumer,2)+" "+word(t.consumer,1)
	r.conid=t.conid
	r.servicename=t.servicename
	r.serviceamt=t.serviceamt
	r.waivercode=t.waivercode
	r.totamount=t.totamount
	r.nummiles=t.nummiles
	r.print=t.print
	r.sent=t.sent
	r.provider=var->hac
	r.outcome=t.outcome
	r.service=t.service
	r.ispaid=t.ispaid		
	r.isbilled=t.isbilled
	r.wcode=t.wcode
	r.enter_end()
	t.fetch_next()
end while

t.fetch_last()
eac=cmonth(t.edate)
rac=cyear(t.edate)
xac=t.wcode
t.close()

while sac < cac
	r.enter_begin()
	r.conid="."
	r.enter_end()
	sac=sac+1
end while
qry.drop()
r.close()

query.filter = ""
query.order = ""

dim global invno as c=""
e=table.open("Ident")
invno=alltrim(e.invoiceno)
e.close()


SELECT

	
CASE wac="P0001"
'prompt_result = ui_get_print_or_preview("Print")
'If prompt_result = "Print" then
	report.print("ES_Invoice2@c:\alpha5\foc\temp_report.ddd")
'Else if prompt_result = "Preview" then
'	report.preview("ES_Invoice2@c:\alpha5\foc\temp_report.ddd")	
'End if

CASE wac="P0002"
'prompt_result = ui_get_print_or_preview("Print")
'If prompt_result = "Print" then
	report.print("DCF_Voucher1@c:\alpha5\foc\temp_report.ddd")
'Else if prompt_result = "Preview" then 
'	report.preview("DCF_Voucher1@c:\alpha5\foc\temp_report.ddd")
'End if

CASE wac="P0003"
do_script("DoverCalc")
'prompt_result = ui_get_print_or_preview("Print")
'If prompt_result = "Print" then
	report.print("Dover_Voucher2@c:\alpha5\foc\temp_report2.ddd")
'Else if prompt_result = "Preview" then 
'	report.preview("Dover_Voucher2@c:\alpha5\foc\temp_report2.ddd")
'End if

CASE wac="P0004"
'prompt_result = ui_get_print_or_preview("Print")
'If prompt_result = "Print" then
	report.print("Parsipp_Voucher1@c:\alpha5\foc\temp_report.ddd")
'Else if prompt_result = "Preview" then 
'	report.preview("Parsipp_Voucher1@c:\alpha5\foc\temp_report.ddd")
'End if

CASE wac="P0006"
'prompt_result = ui_get_print_or_preview("Print")
'If prompt_result = "Print" then
	report.print("CPEP_Voucher1@c:\alpha5\foc\temp_report.ddd")
'Else if prompt_result = "Preview" then 
'	report.preview("CPEP_Voucher1@c:\alpha5\foc\temp_report.ddd")
'End if

CASE wac="P0005"
'prompt_result = ui_get_print_or_preview("Print")
'If prompt_result = "Print" then
	report.print("Coord_Voucher1@c:\alpha5\foc\temp_report.ddd")
'Else if prompt_result = "Preview" then 
'	report.preview("Coord_Voucher1@c:\alpha5\foc\temp_report.ddd")
'End if

CASE wac="P0007"
'prompt_result = ui_get_print_or_preview("Print")
'If prompt_result = "Print" then
	report.print("Family_Voucher1@c:\alpha5\foc\temp_report.ddd")
'Else if prompt_result = "Preview" then 
'	report.preview("Family_Voucher1@c:\alpha5\foc\temp_report.ddd")
'End if

END SELECT


		e=table.open("ident")
		e.change_begin()
		e.invoiceno=alltrim(str(val(invno)+1))
		e.change_end()
		e.close()
	
		
		a_tbl = table.open("timehours")
		query.order = "recno()"
		query.filter = "charge=.T."
		query.options = "I"
		a_tbl.query_create()
		update.fields = 2
		update.field1 = "ISBILLED"
		update.expr1 = "\"YES\""
		update.field2 = "INVNO"
		update.expr2 = "var->invno"
		a_tbl.update()
		a_tbl.close()
		
	
a_tbl = table.open("timehours")

DIM a5_operation_order as C
a5_operation_order = ""
query.order = a5_operation_order
DIM a5_operation_filter as C
a5_operation_filter = ".T."
query.filter = a5_operation_filter
query.options = "I"
query.description = "Temporary Query"
i_indx = a_tbl.query_create("N")

update.fields = 1
update.field1 = "CHARGE"
update.expr1 = ".F."

a_tbl.update()
a_tbl.close()


NEXTITER:

u.fetch_next()
sac=0
end while

s.fetch_next()
end while

[Stan Mathews]

Re: Maximum Session Open Tables Exceeded

In every script where you issue a command like

s=table.open("consumers")
u=table.open("providers")
a_tbl = table.open("timehours")

there should be a corresponding

s.close()
u.close()
a_tbl.close()

et cetera.

[Charles Hoens]

Re: Maximum Session Open Tables Exceeded

Thanks Stan. I've gone over this script again and again, but can't find the error. I thought I closed them all, so that each iteration should start fresh, but I don't know.

Can you spot the trouble?

[Charles Hoens]

Re: Maximum Session Open Tables Exceeded

OK. Well I did a little bit more investigating and found that these lines of code were what was creating the problem.

opt=a5_get_records_in_query("timehours",query.filter)
if opt=0
	goto NEXTITER
end if

When I commented this out, the script ran as expected. Not sure why though.

[Stan Mathews]

Re: Maximum Session Open Tables Exceeded

When I tried to copy the script from the screen I apparently missed capturing some of it and didn't see the close() commands. For that large a script it might be better to save to a text file and attach.

[GGandhi]

Re: Maximum Session Open Tables Exceeded

if one uses the notepad or notepad++ then search for corresponding close statement they can detect the missing statements easily than just reading the file.

[Tom Cone Jr]

Re: Maximum Session Open Tables Exceeded

I think its dangerous to re-use pointer names.

In this case at the top of the script the pointer "u" gets used twice in quick succession, but the first instance is never closed, so far as I can tell.

there are other instances of re-use scattered throughout.

[Gene FitzPatrick]

Re: Maximum Session Open Tables Exceeded

I can not find s.close() in your code. Could that be the issue?

Gene FitzPatrick

[Tom Cone Jr]

Re: Maximum Session Open Tables Exceeded

If later sections of the script depend upon previous sections running to completion first, its prudent to place the previous sections in function blocks to assure that they run to completion before code execution drops through to later sections.

[Charles Hoens]

Re: Maximum Session Open Tables Exceeded

Thanks for all the suggestions! (And I have made the corrections.)

However, the offending code was: a5_get_records_in_query("timehours",query.filter) which, when ran several times with the loop, was the thing that produced the open tables error.

So how can I get the count of the records in a table without using this code? I need that for the loop to work properly.

Thanks.

[Tim Kiebert]

Re: Maximum Session Open Tables Exceeded

t=table.open("timehours")
query.filter = "conid = '" + jac + "'.and.isbilled='No'.and.wcode= '" + yat + "'"
query.order = "edate"
qry=t.query_create()

opt=a5_get_records_in_query("timehours",query.filter)

If I am understanding the flow correctly at that point in the script you have just used the same filter to run a query and assigned that query to a pointer called 'qry'

You should be able to use

opt = qry.records_get()

[Charles Hoens]

Re: Maximum Session Open Tables Exceeded

Thanks Tim (and all!) What I ended up using was kind of simple and I was sorry I didn't think of it before:

t.fetch_first()
if t.fetch_eof()
   goto Nextspot
end if

The point was though that the a5_records_in_query() function somehow opens and keeps open multiple sessions, which is not good for a loop of this nature.

Thanks.