Passing temp-tables by reference

Posted by Nigel Allen on 14-Apr-2014 21:34

Greetings

As I understand it, passing temp-tables by-reference is only valid if you are passing them to a local procedure.

I'm currently trying to find the cause of some 3244 errors. They don't happen for a while and then start to build up until the entire system has to be restarted. The errors all occur in the same temp-table when an external appserver calls a routine in an internal appserver.

The system looks like this:

<dot net app><external appserver><external .p><internal appserver><internal .p>

Does anyone know if declaring a passed temp-table by reference can do any harm or is it simply ignored and passed by-value?

Any spin off effect that anyone is aware of?

Regards

Nigel.

Posted by Mike Fechner on 14-Apr-2014 23:39

When a temp-table is used as a BY-REFERNCE parameter in an AppServer call it’s becoming a BY-VALUE. As BY-REFERENCE is only valid in a session. The biggest pitfall in those situations may be that the called procedure has to DELETE OBJECT the temp-table handle before returning. Otherwise it leaves the dynamic temp-table in memory for very long.

 

Not sure, what your 3244 error is – I don’t learn Progress Error Message numbers by heart ;-)

All Replies

Posted by Mike Fechner on 14-Apr-2014 23:39

When a temp-table is used as a BY-REFERNCE parameter in an AppServer call it’s becoming a BY-VALUE. As BY-REFERENCE is only valid in a session. The biggest pitfall in those situations may be that the called procedure has to DELETE OBJECT the temp-table handle before returning. Otherwise it leaves the dynamic temp-table in memory for very long.

 

Not sure, what your 3244 error is – I don’t learn Progress Error Message numbers by heart ;-)

Posted by Nigel Allen on 14-Apr-2014 23:57

Hey Mike

Thanks for the input.

+++++++++++++++++++++++++++++++++++++

Unable to create schema information for temp-table <table>. (3244)

Both errors indicate the maximum number of 32767 temp-tables in the session is being exceeded.

The root cause for these errors is the same: There are too many temp-tables active, usually because the ABL code is leaking temp-tables somewhere.

++++++++++++++++++++++++++++++++++++++

I suspect you are correct. Back to the search.

Mit freundlichen Grüßen aus Brisbane

Nigel.

Posted by Mike Fechner on 14-Apr-2014 23:59

“The root cause for these errors is the same: There are too many temp-tables active, usually because the ABL code is leaking temp-tables somewhere.”

Looks like you’ll have to start the day hunting for missing DELETE OBJECT statements L

Posted by jmls on 15-Apr-2014 00:09

this article may help : knowledgebase.progress.com/.../P133306

good luck !

Posted by Nigel Allen on 15-Apr-2014 00:28

Hey Julian

Would have been very helpful but in this case all the temp-tables are being statically defined - committed to memory for future use though - thanks.

Given the static nature of the tables, I thought that the built in garbage collection would have dealt with the problem. Only other possibility I can see immediately is that there is a trx being held open or some nasty recursion in play.

I'll keep looking - appreciate the input.

N/

Posted by jmls on 15-Apr-2014 00:30

hmmm. In that case I would look for persistent procedures that are not being deleted, If there are some of these, and they create / use temp-tables it may be the cause

Posted by Nigel Allen on 15-Apr-2014 17:06

Alas, there is only one persistent procedure in the entire system and it doesn't touch the particular temp table that is causing the problem. Unfortunately the entire system is permeated with the problem child which doesn't make tracking it down any easier.

It has to be a leak as the system (and thus the AppServers) were restarted last night and this morning it's gone.

Mother told me there'd be days like this......

Posted by Frank Meulblok on 16-Apr-2014 08:31

"in this case all the temp-tables are being statically defined"

Does that also apply to the temp-table parameter definitions ? Meaning: You're not using table-handle or dataset-handle parameters ?

Otherwise, may want to read this article as well: knowledgebase.progress.com/.../P112397

on implicitly created temp-tables.

Note, the article on the client logging Julian linked to should catch those implicit temp-tables as well.

Posted by Nigel Allen on 16-Apr-2014 17:48

Thanks for the input Frank - unfortunately the answer is yes, there is no "table-handle" or "dataset-handle" in the entire sub-system.

Rgds

N/

This thread is closed