Home page Home page Home page Home page
Pixel Header R1 C1 Pixel
Pixel Header R2 C1 Pixel
Pixel Header R3 C1 Pixel
By APK | Tuesday 31 January 2023 16:04 | 0 Comments

Recently at a client site, we encountered an error that was a little more baffling than normal.  Under very specific circumstances,    RLIST would bail with no results.  Eventually, we had a duplicatable test sentence which was basically


When we removed the BY clause (CALC_COL_1) our sentence ran to completion.   This put the problem squarely on CALC_COL_1, which is where we started debugging.  

We changed CALC_COL_1 to a simple “@ANS=1” and ran the sentence again.  This worked correctly.  To prove to ourselves that CALC_COL_1 was the problem, we put back the original code and changed our sentence to 


This also worked.   

Now we’re starting to get a little confused.  Modifying CALC_COL_1 solved the problem.  Replacing CALC_COL_2 also solved the problem.  It has to be some sort of interaction with the calculated columns, because there’s no obvious consistency lining up the errors with the sentence structure.

It’s time to break out the profile log and see if something jumps out, and it did.  Something appeared off with EXTERNAL.SORT.  It seems like it executed a few times, and then everything stopped.  We dusted off our copy of PROCMON.EXE 1 and ran the process again.  PROCMON showed that the sort file was being opened, but that it was not being closed.  It also showed a lot of file access  We’re now convinced this has something to do with sorting in general, and not with the specific field being sorted.

After trying well over a dozen different variations on the RLIST sentences, we finally worked out that we needed two things to happen for the error to occur.  The first was that we had to have CALC_COL_1 as the sort column.  The second was that the WITH clauses needed to return enough keys to force the system into using EXTERNAL.SORT instead of a simple in-memory sort.

Normally, when OpenInsight starts behaving very oddly, the culprit is usually a function that’s not clearing set_status() before calling get_status.  When that happens, the get_status() call could be returned an error from 10 programs back.  We reworked the calculated columns to ensure we clear set_status, and feeling very proud of ourselves, sat back and waited to bask in the glory offered to us by our client.  Obviously, the report failed again.

Sitting back, we start wondering if maybe there’s a small part of the RLIST code that’s still thinks it’s Advanced Revelation and it’s actually checking the pseudo STATUS() variable.  So, we reworked the calculated columns to ensure we clear set_status and status(), and feeling very proud of ourselves, sat back and waited to bask in the glory offered to us by our client.  Obviously, the report failed yet again.
Surely there can’t be three ways to set an error condition in OpenInsight?  It turns out that there is.  We suddenly remembered @FILE.ERROR.  So, we rework the calculated columns to ensure we clear set_status and status() and @FILE.ERROR, and feeling very proud of ourselves, sat back and waited to bask in the glory offered to us by our client.  And this time there was much rejoicing and basking.

With the solution in hand, we set out to find out exactly why this happened and where the system was registering the failure.  What we found was the calculated field was doing a lot of file access and would attempt to access records that didn’t exist.  This was what was setting @FILE.ERROR.  But, we hit a two-fer, as they say.  Some of the randomness had to do with the amount of data returned.  If enough sort data was returned the system would call EXTERNAL.SORT to sort the data.  EXTERNAL.SORT writes to temporary OS files, and the OS file commands use @FILE.ERROR to check for errors.  EXTERNAL.SORT would execute an OS operation, check @FILE.ERROR, find the pre-existing error, and abort the process.  But, this would only happen if the very last row processed set @FILE.ERROR.  Otherwise RTP57 would clear any prior @FILE.ERROR settings, and @FILE.ERROR would be null during the EXTERNAL.SORT call. 

1.  PROCMON and other useful tools are available as part of the Sysinternals suite of development tools, which no programmer should be without.



By Sprezz | Thursday 12 January 2023 12:46 | 13 Comments

Those of you who've been in the industry for a while will remember the smugness with which we treated the Y2K issues the rest of the world were experiencing. It didn't affect us, we stored our dates in internal numerical format. I'm sure we all have favourite anecdotes. Mine was being called into the Press Office of a major Government department to certificate their AREV tracking system. I sat down and gave the system a cursory glance. "Ermmm - it doesn't seem to use dates?". "No, it doesn't". "Well, here's your certificate"... turns out they couldn't get the budget for system tweaks but they could for Y2K compliance. I spent the rest of the day implementing their desired changes.

Anyway, this week wiped the smile off of our collective faces (if you'd ignored the advice in KB 42 thirty or so years ago - which to be honest you could be forgiven for) with the advent of our very own variant - we'll call it the 20100 bug, although it's not a bug, it's an unfortunate feature.

Users began reporting that date searches were failing for values after the 10th of January 2023. At first we couldn't see an obvious reason. We built a database containing date values going back to the previous century with five rows per date and indexed it. We wrote a test program and ran it

RUN ZZ_POP 10/01/2023

RUN ZZ_POP 11/01/2023

RUN ZZ_POP 12/02/2023

But then a throwaway remark from Martyn about pivot dates being discussed in the MV community led to a realisation. What if we used internal dates?

RUN ZZ_POP 20099

RUN ZZ_POP 20100


What we've fallen foul of is discussed in the KB reference earlier. Basically BTREE.EXTRACT takes what it is given and tries to ICONV it to use for the look up. If it can't ICONV it (20099 isn't a viable date) it uses the value passed. If it CAN ICONV it, is uses the ICONVed value. And guess what? 20100 ICONVs to the 2nd of January 2000 as you can see above.

Of course, this won't be the first time this has happened in the wild - for example looking for the 7th of December 1998 using 11299 would have returned the 1st of December 1999 and so on.

Note that the same issue will be experienced when using internal date formats with RLIST statements.

The solution? When calling BTREE.EXTRACT use EXTERNAL values as it will try and ICONV the data before using it.


We've been asked to share the code we used to locate btree.extracts in client systems to enable a manual inspection to determine possible failure points. This is a rough and dirty hack which met our requirements. Feel free to tailor to your own requirements.

0001  Subroutine zz_find_Btree_Extract( void )
0002  /*
0003     Author      AMcA
0004     Date        Jan 2022
0005     Purpose     Quick hack to help identify system usage of btree.extract
0006                 Provided as is with no warranty
0007  */
0009     Gosub initialise
0010     Gosub process
0012  Return
0014  initialise:
0017     columnsToCheck = ",,8"
0019     loopCtr        = Dcount(filesToCheck, ",")
0020     resultString   = ""
0022  Return
0024  process:
0026     For loopPtr    = 1 To loopCtr
0027        file        = Field( filesToCheck, ",", loopPtr )
0028        column      = Field( columnsToCheck, ",", loopPtr )
0030        resultString := file : \0D0A\
0032        if file = "SYSTABLES" Then
0033           starting = "%"
0034           Gosub processSysTables
0035        End Else
0036           starting = "@"
0037           Gosub processRest
0038        End
0040        resultString := \0D0A0D0A\
0042     Next
0044     Oswrite resultString On "ZZ_BE.TXT"
0045     Call Set_Property("CLIPBOARD", "TEXT", resultString )
0047  Return
0049  processRest:
0050     Open file To v Then
0051        Gosub processV
0052     End Else
0053        Call FSMsg()
0054     End
0055  Return
0057  processV:
0059     Select v
0060     eof = 0
0061     Loop
0062        Readnext id Else eof = 1
0063     Until eof Do
0064        If id[1, 1] = starting else
0065           Read row From v, id Then
0066              ptr = 1
0067              If column Then
0068                 saveRow = row
0069                 row = row< column >
0070                 Convert @Vm To @Fm In row
0071              end
0072              lenRow = Len(row)
0073              lineNo = 0
0074              If lenRow > 0 then
0075                 Loop
0076                    nextline = row[ptr, @Fm]
0077                    ptr = Col2() + 1
0078                    lineno += 1
0079                    there = IndexC( nextLine, "btree.extract(", 1)
0080                    If there Then
0081                       variable = Trim( nextLine[ there + 14, ","] )
0082                       If variable[1, 1] = "'" Or variable[1, 1] = '"' Then
0083                          // passing literals - check the line
0084                          resultString := file : \09\ : id : \09\ : lineNo : \09\ : nextLine : \0D0A\
0085                       End else
0086                          // ok we're now going to work back through the code until we find = for our var
0087                          tempLineNo = lineNo
0088                          Loop
0089                             tempLineNo -= 1
0090                             line = row< tempLineNo >
0091                             If Trimf( line )[1, 1] = "*" Then
0092                                line = ""
0093                             End
0094                             Convert " " To "" In line
0095                             line  = " " : line
0096                          Until IndexC( line, " " : variable : "=", 1)
0097                          Until tempLineNo = 0
0098                          Repeat
0099                          If tempLineNo = 0 then
0100                             resultString := file : \09\ : id : \09\ : lineNo : \09\ : "Not found " : row< LineNo > : \0D0A\
0101                          End else
0102                             resultString := file: \09\ : id : \09\ : lineNo : \09\ : row< tempLineNo > : \0D0A\
0103                          end
0104                       end
0105                    End
0106                 While ptr < lenRow
0108                 Repeat
0109              End
0110           End Else
0111           end
0112           Call send_info( file : " " : id )
0113        end
0114     repeat
0115  return
0117  processSystables:
0119     Open file To v Then
0120        Select v
0121        saveV = v
0122        eof = 0
0123        dictCtr = 0 
0125        Loop
0126           Readnext id Else eof = 1
0127        Until eof Do
0128           If ID[1, 5] = "DICT." Then
0129              Open id To v Then
0131                 file = id
0133                 Call push.Select( v1, v2, v3, v4 )
0134                 Gosub processV
0135                 Call pop.Select( v1, v2, v3, v4 )
0136                 eof = 0
0138              End Else
0139              *   Call FSMsg()
0140              End
0141              v = saveV
0142           end
0143        Repeat
0144     End Else
0145        Call FSMsg()
0146     End
0148  return
By Sprezz | Wednesday 4 January 2023 10:16 | 0 Comments

Tabbed interfaces provide a much loved way of fitting lots of information onto a single entry form. The introduction of multi page forms and the improved integration of page controlling made this a much easier exercise - each tab could be a page of the form and we could simply move between pages on a tab click. In fact a quick event was added to make this even easier.

But what has not been possible until now, without a deal of smoke and mirrors, is to have one or more tab controls embedded within a page on a single or multi-page form and have them only appear on a specific page. The only way to accomplish this would be to have a set of controls per tab, all within the tab boundaries, and make them visible or invisible depending on the currently clicked tab. This makes form maintenance incredibly tedious and code overly complex.

Before OI10 there was no real concept of parent/child relationships in OI - other than that forms and group boxes owned all of the controls within their boundaries. This led to many issues, not least with group boxes and tab controls. With the advent of OI10 this has changed. OI now allows the implementation of, and respects, parent/child relationships.

The biggest example of this is the introduction of a "Panel" container type.

We are going to use the "Panel" (not the "Simple Panel") to enable tabs within a form. But first, what's the difference between a "Panel" and a Simple Panel?

A Panel is simply a container that can be placed anywhere on the screen so that when controls are pasted into the Panel they become children of the Panel, not of the Window. So move the Panel and the controls owned by the Panel move with it. Don't be too put out by the "Simple" part of the description, pretty much the only difference between a Panel and a Simple Panel has to do with paging. So looking at Simple Panel/Panel  properties in the Form Designer we see this :

and comparing the two sets of properties we see that the only properties unique to Panels are

    Virtual Size

and the only property unique to Simple Panels is the DummyCaption property.

So, to implement a tab within a form page, we can now combine the Tab Control, the Panel Control and one simple Quick Event to achieve the desired results.

Let's start with a simple one page entry screen.

Select "Panel" from the Containers and position it within the tab control - using Right-Click and drag to size it appropriately. (See Addendum for other keystroke combinations). There are no hard and fast rules about this but I tend to leave an 8 pixel border from the tab control to make it easier to edit in the form designer.

(As an aside, if you right-click on the Window you can un-anchor it to allow moving it wherever you want.)

Tell the Panel that it has three pages and label the tab accordingly (after setting the tab to three values) :

Select the Panel and add in the controls for the first page.

Now select the Panel and use the spin control to move to page 2 and add in the controls for the second page. Note that the spin control applies to whichever control is both currently selected and supports paging - namely Windows and Panels. 

And finally the third page.

Finally we need to add a Quick Event on the Tab click. This needs to get the VALUE Property of the Tab control, and set the CURRENTPAGE Property of the Panel.

and Bob, as they say, is your uncle...

Actually a quick revisit - internal discussion at Sprezz Towers brought up the fact that the article missed a fairly big point (because the author ass-u-med it was obvious which it obviously isn't :)). Panels can host panels that can host panels - as far as you are practically likely to go. So in the screen below we see multiple panels in action.

Addendum - Mouse Use In Form Designer

When in Draw Mode:
Left Click
1) Left-click to drop a default-size control on a the form
2) Left-click and drag to draw a control on the form
3) Ctrl-left-click to drop a default size control on a container control
4) Ctrl-left-click and drag to draw a control on a container control.
Right Click
1) Right-click and drag to draw a control on the form
2) Right-click and drag to draw a control on a container control.
When in Normal Mode:

Left Click
1) Left-click on the form to unselect all controls
1) Left-click on an unselected control to select it
2) Left-click and drag around/across a group of unselected controls on the form to select them
3) Left-click and drag on an unselected or selected control to move it
4) Left-click and drag around a group of selected controls to move them
5) Ctrl-left-click on a selected container control and drag around/across unselected child controls to select them
6) Ctrl-left-click on a selected container control and drag around/across unselected child controls to select them
7) Shift-left-click to add an unselected control to a group of selected controls with the same parent
8) Shift-left-click on a group-selected control to remove it from a group of selected controls

Right Click

1) Right-click on the form to display it's context menu
2) Right-click on an unselected control to select it and display it's context menu
3) Right-click on a selected control to display it's context menu
4) Right-click on a group of selected controls to display the group-selection context menu
5) Right-click and drag on the form to draw the most recently added type.
6) Right-click and drag on an selected container control to draw the most recently added type and add it as a child.
7) Right-click and drag on an unselected container to draw the most recently added type and add it as a child.
8) Shift-right-click on the form to drop the the most recently added type with a default size. 
9) Shift-right-click on an selected container control to drop the the most recently added type with a default size and add it as a child.
10) Shift-right-click on an unselected container control to drop the the most recently added type with a default size and add it as a child.

Pixel Footer R1 C1 Pixel