Out of handle space error

[bruceeaston]

A client received an fpsql error saying "out of handle space."   They sent us a screen-shot which shows the query overlaid with this error.  The query looks very unusual to me in that there are probably 50-100 or more literals being tested after the "in" operator (they look like specific state-zip-code combos).  Aside from this looking like something that is being done the hard way, do you see any way to prevent this error or how we can explain what it means?  Thanks.

Bruce

Bruce Easton
STN, Inc.   

[ernieb]

What version of fpSql ?
There was a fix in 5.0.15 and 5.6 (something) that I had reported.

[bruceeaston]

What version of fpSql ?
There was a fix in 5.0.15 and 5.6 (something) that I had reported.

Thanks, Ernie - I don not think we have 5.015 or above anywhere he of any filepro product if I am not mistaken, but I am looking.  I am not certain about the client's SQL, but I would imagine that it cannot be as recent as 5.015 filepro.  I am  looking to find 5.015 filepro to at least try to read the release notes.  Should the client just have to download the lastest SQL?

Thanks again,

Bruce

UPDATE - Ernie - I checked all the release notes online (5.0XX thru 5.6) and didn't see anything that looked like it related to this error.

Bruce

[ernieb]

This was the error I was getting - fixed in 5.6.04 and 5.0.15:

=========================
5.6.04 Release Notes
=========================

(All) #941

    fPSQL could crash if the query required 10K of output per record.
    (Default increased to 100K, and it now properly reports error if
    exceeded.)

[kenbrody]

A client received an fpsql error saying "out of handle space."

http://www.fptech.com/Products/Docs/FPFormat/envvars.shtml#PFHAND

The default is 10000 through 5.0, and increased to 100000 in 5.6

[bruceeaston]

http://www.fptech.com/Products/Docs/FPFormat/envvars.shtml#PFHAND

The default is 10000 through 5.0, and increased to 100000 in 5.6

Thanks Ken, Ernie - we will try the env. var.

Bruce

[bruceeaston]

Thanks Ken, Ernie - we will try the env. var.

Bruce

setting PFHAND=100000 eliminated the error - thank you.

Bruce

[bruceeaston]

setting PFHAND=100000 eliminated the error - thank you.

Bruce

This problem has reared its ugly head again and needs a wizard's spell cast upon it.  Initially, as I stated above, upping the PFHAND to 100000 eliminated the error, but since then, the client received the error consistenly on a new sql query where there were 83 items (in this case numbers separated by commas) listed after the "in" in the "where" clause.  I connected and saw that the error about out of handle space came up in a nice neat box in the center of the screen.  If I run  with just 80 items, the error goes away.  Since the inital problem, the PFHAND has been set to 100000.  I bumped it up to 200000, then 400000 and I still get the error which is only three more items than what works.

Marcia and I both tried here with 90+ items, on our sql (5.14) and we can't get it to crash yet even without setting PFHAND - we've tried literals and just numbers.

Any ideas??

Bruce

[bruceeaston]

This problem has reared its ugly head again and needs a wizard's spell cast upon it.  Initially, as I stated above, upping the PFHAND to 100000 eliminated the error, but since then, the client received the error consistenly on a new sql query where there were 83 items (in this case numbers separated by commas) listed after the "in" in the "where" clause.  I connected and saw that the error about out of handle space came up in a nice neat box in the center of the screen.  If I run  with just 80 items, the error goes away.  Since the inital problem, the PFHAND has been set to 100000.  I bumped it up to 200000, then 400000 and I still get the error which is only three more items than what works.

Marcia and I both tried here with 90+ items, on our sql (5.14) and we can't get it to crash yet even without setting PFHAND - we've tried literals and just numbers.

Any ideas??

Bruce

Correction - above I stated we are not getting the error here on 5.14.  We have 4.05 fpsl and do not get the error with 90+ similar items after the "in" operator.  The client's fpsql says 5.14.  Does PFHAND have an upper bound in 5.14?  As I said it does not seem to make sense to me for a difference of a few more items after the "in" operator to cause an error, as it does when PFHAND is set to 100000 or 200000 or 400000.  If I knew the answer to this, I might be able to better advise the client on whether or not upgrading fpsql to 5.15 from 5.14 will make any difference (which I understand requires downloading an updated license and the rest of filepro).

Bruce

[ernieb]

My guess is you'll need the 5.15 to fix it.