Records not being returned

[Jamie]

Hello everyone,
I have a strange issue that is occurring with random records inside some of my db's... (Currently we are running Progress 9.1D09.)

My issue is that sometimes when I try to locate a record that I know exists, the record is not returned. But, if I use match and a wildcard instead of '=', it will find the record. And once found, the normal '=' statement will then find the record as it should. (I'm assuming that this is because the pointers to the record are in memory at that point?) Once you close out of procedure editor and reopen it, the record will again not be found...

Now, we also have discovered that putting a 'pause' statement at the end of the procedure will cause the record to be displayed. Also, this problem is not only limited to the editor... this is a problem that was originally discovered with a Crystal Report accessing Progress via ODBC.

Ok, my first thought on the whole thing when I first became aware of it was 'Index Rebuild'. Well, I scheduled some downtime as soon as I could and rebuilt the indexes, but unfortunately after we rebuilt the indexes we are still noticing these issues.

Here are some examples...

If I do a simple:

For each ncr_mstr where ncr_nbr = "005092":
display ncr_mstr with 2 col.
end.

It never returns the record. But if I do a:

For each ncr_mstr where ncr_nbr matches "00509*":
display ncr_mstr with 2 col.
end.

It will find the record while going through the matching criteria. After it finds the record, the first statement above will find it. Also, the following remedies the problem, but not for ODBC connections (obviously...):

For each ncr_mstr where ncr_nbr = "005092":
display ncr_mstr with 2 col.
pause.
end.


I'm really hoping that someone out there can shed a little light on what the problem might be and how to remedy it.

Thanks in advance!

Jamie

 

[Lee Curzon]

Just a quick point - I'm a bit busy at the mo, so have only skimmed your post, sorry if this is irrelevant.

You're not using NO-LOCK in the samples you've provided, so if you're not running with -NL, lock contention may cause a problem.

 

[Lee Curzon]

Oh, and the fact that one where clause (matches) picks up the record, while another doesn't (=) means nothing because you're using a much wider selection criteria in the first instance.

Out of interest what happens with

For each ncr_mstr where integer(ncr_nbr) = 5092?

Just trying to rule out any string formatting issues you may be having.

 

[TomBascom]

I'd look for trailing spaces.

display "[" + ncr_nbr + "]".

Also -- try BEGINS instead of MATCHES (its faster for this case).

 

[Jamie]

Thank you guys for getting back to me on this issue!

I have tried the suggestions that you posted hoping that you guys were pointing out something that I just completely forgot about or overlooked.

I'm sorry that I posted such a simplistic statement. The reason that I did was because I really wanted to show that if something this simple isn't working, then there probably is a bigger issue at hand...

I was pretty confident that this was not a string/formatting issue, as every record is built the same as far as having a length of 6 and filling 0's at the beginning until it meets the length. And also, once the record is found in the table (using the matches or begins), I can use the more precise "005902" string as the criteria and it will find it fine.

Lee, shouldn't record locking not be a factor if I use 'display' instead of 'update'? I could be wrong on that... I tried to convert to an integer and it returns nothing at first. But again, if I do the begins/matches statement, it will find the record and then I can convert to an integer and find it properly.

Tom, thanks for the tip on using 'begins'. I'll keep that in mind. I tried surrounding with brackets and initially, in a fresh Procedure Editor session, it will of course not find the record. Once I do the 'begins' statement, it will find the record that I am looking for and I can have it display: [005902]. The display is as it should be... no leading/trailing chars.

At this point I feel that it is not really a data issue. It seems that since I can find it using the specific string, only after finding it using a more broad set of criteria, that there may be an issue with the db config or a problem with Progress itself.

I'm hoping that someone may have some more ideas on where I might look...

Thank you again for your help!

 

[Lee Curzon]

Lee, shouldn't record locking not be a factor if I use 'display' instead of 'update'? I could be wrong on that...

Hi Jamie,

Will consider your problem again later when I have a mo.

On the point above, the default locking in Progress is share-lock.

If another session has the record exclusive-locked, and you try to read it share-lock, you will be unable to access it, regardless of whether or not you intend to update it.

You should always specify no-lock when moving through a batch of records for display.

However, that shouldn't be an issue in the example you posted, since the loop passed through with no problems, so clearly the record wasn't caught by your WHERE criteria - which suggests (as we have all acknowledged) dodgy characters in your strings as a first line of investigation. ie. my lock suggestion was a red herring.

 

[Lee Curzon]

Incidentally, I have no idea why you should be able to read the record using the same criteria once it has been found using another.

I'm a little rusty with how Progress caches record information - but satisfying the same selection criteria only once in two identical searches (assuming the record has been there all along, and locking is not an issue) is clearly bizarre behaviour.

 

[Lee Curzon]

btw. are all values in that field of a similar length and format?

if so you can try things like

... where not begins('0'),

and

... where length(field) <> length(trim(field))

to try and flag padded records.

 

[Lee Curzon]

btw. are all values in that field of a similar length and format?

ok, you told me that already.:blush:

 

[Lee Curzon]

It seems that since I can find it using the specific string, only after finding it using a more broad set of criteria, that there may be an issue with the db config or a problem with Progress itself.

Progress bug is very unlikely, because I am sure it would have been noticed by other people before now.

 

[Jamie]

Man, Lee, thanks for the quick responses!
I really appreciate all your input! This whole thing is so weird...

 

[Jamie]

Ok, so maybe I should lean towards DB config issue?

 

[Lee Curzon]

I would try rescanning the records first.

eg.

def buffer <table> for <table>. /* make sure the buffer is scoped locally - shouldn't be an issue, but... */

for each <table> no-lock
where length(table.field) <> 6:

display table.key table.field.

end.

and variations including the other suggestions.

Prefixing the field with the table name is a good rule of thumb.

 

[Jamie]

As you suggested, I've gone through looking for anything with a length different than 6. No records were returned... I was really hoping that something would turn up on that one.

 

[Lee Curzon]

ok, one last go for the minute.

assuming you can reproduce this on a development system, dump, delete and reload the table, and if that doesn't clean it, start examining the .d with something like PSPad which allows a hex view.

To be honest, though, I think that's a bit over the top, and no doubt someone will come along soon with a simple solution.

 

[Lee Curzon]

and before you do that (you don't get rid of me that easily), deactivate the indexes for the table, and run your searches again.

I should point out at this point, that we are getting into DBA territory, and others will no doubt have better ideas than this.
(Have you checked all your logs, db and what have you, just in case there is something odd going on?)

I've never had to do either of my last two suggestions before, so they may well be unneccesary.

I am but a humble, ignorant coder.

ttfn

 

[Lee Curzon]

Incidentally, I have no idea why you should be able to read the record using the same criteria once it has been found using another.

I'm a little rusty with how Progress caches record information - but satisfying the same selection criteria only once in two identical searches (assuming the record has been there all along, and locking is not an issue) is clearly bizarre behaviour.

It seems that since I can find it using the specific string, only after finding it using a more broad set of criteria, that there may be an issue with the db config.

I've been reconsidering this, and I think you may be right (corruption/configuration) - there does seem to be something odd going on.

If you have tried all the approaches suggested, then to be honest, I can't think of other options at the minute.

Unless someone else can provide an approach, then I would advise you to raise it with Progress Tech support if possible, and also consider Tom's clever colleagues on the Peg (http://www.peg.com/lists/dba/web/).

If and when you do work it out, please post the solution, even if it turns out to be something trivial that was overlooked, or some silly mistake was made. I'm intrigued, particularly by the strange 'caching' issue.

 

[shanthimarie79]

hey ... i have the same problem..
its not a string problem nor a format problem.
It's the power of "PAUSE".
the pause allowed for the record to be shown.
I dunno why but thats the solution.
the "Hows" I can't quite comprehend myself and maybe it mite hv smthg to do wif da buffer to display to god-knows-what-else in between that....