Illegal characters causing problems and can't be found

[Gary Mugford 12]

Down the rabbit hatch again. I'm hoping the smart minds here will continue to try and help me. I am battling to complete a conversion of a BDE project, but it still has to run in the meantime. Written in D7. Been going for close to 30 years dating back to the venerable DOS Paradox 1.11i. Since Microsoft's Fall 2017 Update to Windows 10, our data integrity has been under daily attack. Running a rebuilder session once a day is a good day, seven times in a day is ... all too often. MOSTLY, the problems have been Blob Modified and Index out of Date errors. In both cases, I believe the timing between Win10, SOME Win7 workstations, BDE and the server are just off. Guessing the locking protocols are broken badly. But we persevere. Today, a NEW problem. 

 

ONE user, running the app in a Win7 VirtualBox on Win10 (one of my workarounds to try and minimize the BDE issues), got an error that clearly says: "Invalid characters. Field: PartNum" I wondered if it might be a picture mask issue, but no, this was an existing part and once into the system, part numbers are inviolate and can't be edited at all. So, I presume our persistent integrity issues were at play and somehow a low-ascii character had been inserted/appended during a rebuild session. Indeed, in rebuilding logs, I have seen a reference to up to four records needing repairing for low-ascii.  So I built a small utility to check for these characters. And found nothing. The error message, very reproducible, and my blank results are ... not mutually compatible. So, I must be doing something wrong in my search for illusive control characters. The array is 0 to 31.  Here's the code:

procedure TFrmMain.FormCreate(Sender: TObject);
begin
  t.databaseName := 'GM';
  t.TableName := 'PARTNUMBERS.DB';
  t.active := true;
  TotalRex := t.RecordCount;
  for Kounter := low(IllChar) to high(IllChar) do IllChar[Kounter] := chr(Kounter);
end;

procedure TFrmMain.BtnStartClick(Sender: TObject);
var
  mNote: string;
begin
  CurrRex := 0;
  with T do begin
    // disableControls;
    first;
    while not eof do begin
    inc(CurrRex);
      g.Position := Trunc(100*CurrRex/TotalRex);
      Application.ProcessMessages;
      ThePN := fieldByName(PN).asString;
      mNote := '';
      for Kounter := low(IllChar) to high(IllChar) do begin
        if pos(IllChar[Kounter],ThePN) > 0 then begin
           if mNote = '' then mNote := PN + ' ... ';
           mNote := mNote + ' ' + IntToStr(Kounter);
           end;
        end;
      if mNote <> '' then m.Lines.Add(mNote);
      next;
      end;
    // enableControls;
    end;
  ShowMessage('Finished');
end;

Is it possible that the D7 POS command won't find control characters in strings? Or a sub-set of the control characters that includes the ACTUAL problem characters? Did I goof up the memo lines adding note creation? I slowed the thing down by using a gauge and commenting out the disableControls/enableControls.

 

Ideas appreciated. Thanks in advance, GM

[Sue King 5]

I wonder if fieldByName(PN).AsString is not giving you all the characters stored for the field as it translates it to a string, because of the invalid character.

 

Can you access the field as an ansichar array somehow to do the test ?

 

Sue

[Gary Mugford 12]

Sue,

 

  Thanks for your response. I've been a fieldByName guy forever. So, I've never thought much about how it retrieves (and potentially changes) the string from the A15 field in the Paradox database. (And yes PN is a constant equal to 'PartNum.' I DO know that the stated problem child has an unusual PartNumber. I remember eons ago putting in a mask for just alphanumerics and a dash. But that changed 'bout five years ago when a client more or less demanded this + part number scheme because they were building out a temporary set of items to fulfill some legal judgement against them ... not having something or other to meet some local code of some sort. It was meant to go away. But they decided after the fact NOT to let it go away. Ergo, a change to the mask to allow the plus sign ended up being permanent. I checked that. So, I'm more or less, of the opinion that it's something evil sneaking into the data. I DID have a problem with one place where the minions were managing to get carriage return characters into a list, rather than just filling in the blank with one long comma-separated list where that was what should have been entering.  The miracles of copy and paste) So I had to add a Strip(DeptNums,crLF) into my posting check. I really only found it exporting to XLS where some deptNums were double height in the spreadsheet. Otherwise it was colourless and odourless. 

 

  The other place where evidence rears it's head is in Rebuilder, which is a customized version of RK Solutions' Pdxrbld. Here's a slightly redacted example for one table in a recent rebuild:

   Table EXAMPLE        - #records: 90802
   Index EXAMPLE.PX     - Index version does not match table version
   Index EXAMPLE.XG0    - Secondary Index onF1 is out of date
   Index EXAMPLE.XG1    - Secondary Index onF2 is out of date
   Index EXAMPLE.XG2    - Secondary Index onF3 is out of date
   Index EXAMPLE.XG3    - Secondary Index onF4 is out of date
   Table EXAMPLE.DB     - Alpha Field SVException in record 68422 contains low ascii chars
   Table EXAMPLE.DB     - Alpha Field SVException in record 69147 contains low ascii chars
   Table EXAMPLE.DB     - Alpha Field SVException in record 70147 contains low ascii chars
   Table EXAMPLE.DB     - Forward block pointer in block 2893 is out of range
   Table EXAMPLE        - Errors fixed (#records: 90803)
   Table EXAMPLE.DB     - successfuly packed

   As you can see, there are three records that trigger a low ascii chars warning. However, by the time I'm alerted to this, the rebuild has changed the database and I'm stuck for a reproducible error to work against. I DO have our Rebuilder zip up the raw data, but it goes to a dated name that gets overwritten all day if there are multiple rebuilds. EVERY TIME I ask for them to look before doing it again, I get blank looks. We hire the cheapest, not the best. (Including, probably, the programmer). 

 

  When I saw this example, I did change my code to test for all string fields in each record. Came up clean as a toothpick in ready-to-eat cake. A bit frustrating, but c'est la vie. I have to get this all transferred into NexusDB and this problem goes away. Maybe I shouldn't be wasting my time and everybody else's time when it's an issue that sort of fixes itself when something else breaks and Rebuilder gets run. Sure is curious though.

 

  Thanks for your DOCUMENTATION code you sent me. I didn't QUITE get it all straight, but I figured out an alternative once I figured out I could get scripts out of the SERVER's SQL context menu where as I had been looking into the SQL found in MANAGER. It was quite the DUH moment. But taking a bit of your code, that creation code and my own stuff, I have something that equates to my needed documentor from BDE. Kicked it all over to the Atomic Scribbler novel writing software and made it into a Documentation Writer too. So, thanks again for help here AND in the past.

[ByteJuggler 45]

It's not going to be Pos().

 

I'm slightly wondering what's the content of "IllChar?"  Unless that's all characters below 32 you may be missing characters due to them being not in your array, possibly causing trouble. 

 

Really, instead of having an array of "Ill characters" and stepping through this repeatedly calling Pos(), I'd probably just step through every character in "ThePN" and check for Char(ThePN) < #32 as all characters below point 32 is non-printable. (This is also slightly more efficient, but that is not any major concern here so I digress.)

 

(Aside, my sympathies.  I bled a lot with Paradox and the BDE back in the day.  Still have some remnants of the BDE here and there, soon to be all gone... )

Edited December 14, 2018 by ByteJuggler

[Gary Mugford 12]

In the FormCreate procedure, I fill the IllChar array with all the control characters from zero to 31. It is in the line 

for Kounter := low(IllChar) to high(IllChar) do IllChar[Kounter] := chr(Kounter);

In the global vars, I had declared IllChar as an array [0..31] of string, so I THINK I got them all. Then in the compare area, I loop through the array and check with the POS() function. Now, it's eminently possible that in my stuffing line, what I assume is completely filling it fails for a particular code. I haven't EVER used this concept before, since I've always depended on Woll2Woll's masks to keep stuff out that I want out. And I never checked after the fact. And I truly wonder if these data corruptions are causing the insertion of the characters after the fact. In fact, it's the ONLY explanation I have. They are happening. I've seen the EurekaLog errors and I've seen the Rebuilder logs with the notations. But they are like ghosts. When I GET a chance to go looking for them. They're gone. 

 

That said, you're suggestion of parsing each PN character by character and doing Char(ThePN) < #32 is worth trying. Approaching any problem that doesn't produce results from a different direction is always worth trying. Thanks for the suggestion. I'll report back here with the results.