Memo field operations lead to "String is too long to fit" error. 2

[DanielEvansJr]

Fox: Visual FoxPro 6 / SP5 & Visual FoxPro 9 / SP2
OS: Windows 7 Professional & Windows 10 Professional

I have a legacy application that imports data from a text file of wage information. Each data line is SSN, Name, Wage. As you can expect, these wage files can get quite large, a few hundred MB sometimes.

The data from the wage file is imported into a memo field of a FoxPro table.

append memo xmemo.xmemo from (m.drive_file)

This works as it should, even with files nearing 400MB.

Then there's some code that verifies and checks the new data in the memo field for things that may need to be stripped out. (linefeeds and carriage returns). The code that does this uses the OCCURS() function.

m.nchr10 = occurs(chr(10), xmemo.xmemo) && count for line feeds
m.nchr13 = occurs(chr(13), xmemo.xmemo) && count for carriage returns

Within the last week, the above two lines have failed with the error "String is too long to fit". Smaller wage files (about 60MB or so), will work fine. But anything larger than that will throw the error. I can even reproduce the error from the command line with

?occurs("x", xmemo.xmemo) && show a count of 'x' characters.

Background:
1. This application has been in production for over 20 years and has taken large files without issue all of that time.
2. I can reproduce the error in Fox 6 and Fox 9.

At this point, I'm stumped. Anyone have any idea what the issue is? Thanks in advance to anyone that can offer some help.

 

[Mike Lewis]

It looks like you have come up against the maximum size of a variable. which is 16,777,184
characters.

You say that it works with files around 60 MB. But the above limit applies to an individual field, not to the whole file. In other words, if a single instance of xmemo.xmemo exceeds that limit, you will see the error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[DanielEvansJr]

But this application has worked for literally a couple of decades and has successfully processed wages files that are nearly 400MB in size. I'm going through the messy code right now but it looks like there are several operations against that memo field and they no longer work with larger files. I'm scratching my head looking for a solution. Other than splitting up the incoming files into smaller chunks. Or writing some utility that strips the files out BEFORE they get imported by the FoxPro app.

 

[Chris Miller]

Not sure whether I/we don't understand you or you don't understan Mike,

but the limit is with several string functions working on text that excceeds 16MB (ca).
DBFs can have up to 2GB, that's not the limit, it's individual texts, i.e. one individual memo being too long will cause that error.

STRTOFILE(SPACE(16777184),'spaces.txt',.f.)
STRTOFILE(SPACE(16777184),'spaces.txt',.t.)

CREATE CURSOR crsText (mText M)
APPEND BLANK
APPEND MEMO mText FROM spaces.txt

? OCCURS(' ',mText)

You can try a few things to see where there is that limit and where not. SPACE() will error, if you go one up, for example. STRTOFILE() is fine with making the file 32MB long, and APPEND MEMO also can set a memo field to 32MB text, in theory a single memo can hold up to 2GB. But finally OCCURS has the same string length limitation as SPACE(), 16MB.

If you never had this problem in 20 years, you never had a single memo going over 32MB of size. You can easily have processed larger files, but not such a large individual memo value.

Chriss

 

[TamarGranor]

Check whether the offending files contain CHR(0) somewhere. That can make things go wonky.

Tamar

 

[Mike Lewis]

Daniel, you say that the code has been working for decades but has now started throwing the error. So the obvioius response is to ask yourself what has changed. Has any of the code been modified? Is there something different in the data? Or the way it is generated? Have you upgraded the operating system?

If you can track down what has changed, you will be half-way to solving the problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I stand by the assumption the point is no earlier memo field had 32MB, but it could matter how long counting line feeds and carriage returns was done by OCCURS, as you can see the OCCURS function has the string limitation, while APPEND or STRTOFILE doesn't. So it's not a strict limit.

You could also "count" lines by using ALINES. That would be limited to 65k lines in earlier VFP versions, though, and VFP9 doesn't exist for 20 years. It's getting close to that age, but if you think of an 80 character per line text, you would get to a maximum of 5 million characters and fail even earlier than with OCCCURS.

Besides this modified text code shows LEN() works with longer strings, but ALINES fails even in VFP9 with "string is too long to fit".

*produces about 37MB long text with 455000 lines of each 80 spaces followed by CRLF.
STRTOFILE(REPLICATE(SPACE(80)+CHR(13)+CHR(10),65000),'lines.txt',.f.)
STRTOFILE(REPLICATE(SPACE(80)+CHR(13)+CHR(10),65000),'lines.txt',.t.)
STRTOFILE(REPLICATE(SPACE(80)+CHR(13)+CHR(10),65000),'lines.txt',.t.)
STRTOFILE(REPLICATE(SPACE(80)+CHR(13)+CHR(10),65000),'lines.txt',.t.)
STRTOFILE(REPLICATE(SPACE(80)+CHR(13)+CHR(10),65000),'lines.txt',.t.)
STRTOFILE(REPLICATE(SPACE(80)+CHR(13)+CHR(10),65000),'lines.txt',.t.)
STRTOFILE(REPLICATE(SPACE(80)+CHR(13)+CHR(10),65000),'lines.txt',.t.)

CREATE CURSOR crsText (mText M)
APPEND BLANK
APPEND MEMO mText FROM lines.txt

? LEN(mText)
? ALINES(laLines,mText)

As I thought, LEN doesn't fail, but ALINES can't cut the long text.

Looking a bit deeper I see LEFT() can't cope with long strings, but SUBSTR can, so instead of LEFT(str,x) you can use SUBSTR(str,1,x), or you could chunk a long text this way:

*...
? LEN(mText)
cHalf1 = substr(mText,1,20000000)
cHalf2 = Substr(mText,20000001)
? ALINES(laLines,cHalf1) 
? ALINES(laLines,cHalf2)
?Len(cHalf1)
?Len(cHalf2)

You get something that doesn't sum to 455000 CRLF, but that's due to SUBSTR returning 16777184 bytes long strings, even when you ask for 2000000.

The overall advice from system capacities mentioning the 16777184 bytes string limit is to not process longer strings, even with functions that won't throw errors you can get unexpected results.

Chriss

 

[DanielEvansJr]

First....thanks to everyone that's replied so far. I'm sorry if this seems confusing. It's confusing to me as well.

Second, I was wrong about the working file size. The memo field can accept data up to 2GB. But if you use a string operation against any memo field holding more than about 16MB worth of data, you will get the 'String is too long to fit' error. This is the part that's new and confusing. It's CLEARLY in the documentation as such, but like I said...this has worked without error for a very long time with very large files.

As for why it's not working:

1. There HAS BEEN very recent updates to my Win 7 and Win 10 PC's that I use to develop. The timing does coincide with when this stopped working correctly.

2. The error occurs regardless of the contents of the memo field (thank you, Tamar).

3. No changes have been made to the import code. I'm sure of this, as I'm the only FoxPro Dev here. I'm not the original Dev for the app, but it's been under my umbrella and mine alone for about 6 years and I've never needed to work with the 'import' module.

4. The data isn't funky. It's just alpha and numeric text. We deal with wages and they come to us in files that are formatted like below. The Fox app takes the entire file of data, brings it into the memo field, checks it for anything that needs to be stripped out, then processes the file line by line.

E2021000000000        9999999999       99      12212021119999
S999999999LASTNAME   FIRSTNAME   X47   00000000000000  12212021119999
S999999999LASTNAME   FIRSTNAME   X47   00000000000000  12212021119999
S999999999LASTNAME   FIRSTNAME   X47   00000000000000  12212021119999
S999999999LASTNAME   FIRSTNAME   X47   00000000000000  12212021119999
S999999999LASTNAME   FIRSTNAME   X47   00000000000000  12212021119999
S999999999LASTNAME   FIRSTNAME   X47   00000000000000  12212021119999
S999999999LASTNAME   FIRSTNAME   X47   00000000000000  12212021119999
S999999999LASTNAME   FIRSTNAME   X47   00000000000000  12212021119999
S999999999LASTNAME   FIRSTNAME   X47   00000000000000  12212021119999
F99999999

At this point, I've spent way too much time trying to figure it out. I’m going to chalk it up the Windows updates or just…magic. My solution, at least until the app can be truly replaced, is to take any large files (15MB+) from my Users, split them up into 15-16MB chunks, and then let them get processed individually. As you can imagine, this takes a chunk of my time, but this is wage data and it's time sensitive.

Thanks again, to anyone that spent any brain power on this.