Monthly Archives: October 2021

PDF files and the FILEPREVIEW control

The next release of OpenInsight (version 10.1) includes a couple of updates to the FILEPREVIEW control as a result of using it extensively “out in the field”, and in this post we thought we’d look at these changes and why we made them in case you encounter the same issues yourself.

The Adobe problem

As mentioned in this previous post, the FILEPREVIEW control relies on third-party DLLs to provide “preview handlers” that OpenInsight uses to display the contents of files such as Word or PDF documents. However, what we found is that not all of these handlers are created equal and some can be quite problematic – in our case the Adobe PDF preview handler (supplied with the Adobe PDF Reader) proved to be one of these.

When the handler is loaded by OpenInsight one of the things that must be specified is the context in which it is created – this can be “in-process” (which means it runs in the same address space as OpenInsight) or “out-of-process” (which runs as a separate executable). This is done internally by a set of flags, and when you use the FILENAME property these flags are set to their default values which, until recently, had proved sufficient. However, extensive testing (by Martyn at RevSoft) found that the Adobe PDF preview handler had stopped working, and further investigation revealed that at some point recent versions of this had become sensitive to these context flags, so the first change we made was to provide a new SETFILENAME method, which allows you to set the flags yourself if need be:

The SETFILENAME method

RetVal = Exec_Method( CtrlEntID, "SETFILENAME", FileName, FileExtn, |
                      ContextFlags )
ParameterRequiredDescription
FileNameNoContains the name and path of the file to preview (can be null to remove the preview).
FileExtnNoSpecifies an explicit extension to use, overriding the extension passed in the FileName parameter.
ContextFlagsNoSpecifies a bit-mask of “CLSCTX_” flags used to create the preview handler. Defaults to:

  BitOr( CLSCTX_INPROC_SERVER$, CLSCTX_LOCAL_SERVER$ )

(Equates for these flags can be found in the MSWIN_CLSCTX_EQUATES insert record)

If the returned value is 0 then the operation was successful, otherwise this is an error code reported from Windows and can be passed to the RTI_ErrorText stored procedure to get the details:

E.g.

// Load the PDF in an out-of-process context
$Insert MSWin_ClsCtx_Equates

RetVal = Exec_Method( CtrlEntID, "SETFILENAME", "C:\Temp\Test.PDF", "",
                      CLSCTX_LOCAL_SERVER$ )
If RetVal Then
   // Problem...
   ErrorText = RTI_ErrorText( "WIN", RetVal )
End

Even with this you may still find problems, as the above code was fine for me, but not for Martyn, even though the PDF preview handler worked fine in Windows Explorer itself for both of us! So, we could only conclude that Adobe made sure that the handler worked with the Windows Explorer, but they were less concerned about third party applications (Per-monitor DPI settings are also not supported by the preview handler which is disappointing as well).

The Foxit solution

After some more testing we decided to switch to the Foxit PDF reader which worked as expected, so we would recommend using this for PDF previewing in future if needed.

Removing the FILENAME property at design-time

One other change we made was to remove the FILENAME property from the Form Designer so that it could not be set at design-time due to the following reasons:

  • We had reports that once it had been set it was very difficult to select the control again in the Form Designer, because it basically takes over mouse handling!
  • Document previewing is deemed to more of a run-time operation than a design-time operation.
  • The FILENAME property is deprecated in favor of the SETFILENAME method because the latter provides a more complete API. The FILENAME property is still supported however, and will be going forwards.

Conclusion

So, for v10.1 we have provided a new SETFILENAME method to provide a better interface for file-previewing which gives more feedback and more control, and you should use this in preference to the FILENAME property.

We have also found the Adobe PDF preview handler to be somewhat temperamental in use so would recommend the Foxit preview handler instead if you have problems with the former (Note however, that other preview handlers we use regularly, such as Word, Excel and PowerPoint have all worked well without any issues so far).

RList eXtended – New features for OpenInsight 10.1

RList is the OpenInsight tool for queries and ad hoc reports. OpenInsight 10 implements an extended version of RList via the new RTI_RLISTX stored procedure which offers additional features for selecting and reporting data. However, you do not need to change existing programs to take advantage of this.

This post takes a look at some of the new RList capabilities, along with a full description of the API and some code examples.

OLIST/RUN_REPORT syntax support

LIST statements can use the same syntax as RUN_REPORT or OLIST if the output is going to the screen or printer.

  • With OLIST LIST statements you can use keywords like GRID and PDFFILE. When the target is TARGET_PRINTER$, RList calls OLIST_PRODUCE to render the output.

Stacked selects

RList accepts a stack of SELECT statements and optionally one output (LIST) statement.  RList will execute SELECT statements until zero records are selected or a LIST statement is executed.  This allows developers to break a complex query into a series of simpler SELECT statements. Each subsequent SELECT statement refines the active select list.

RList to BRW

The TARGET_BRWDESIGN$ option will create a BRW (Banded Report Writer) report from an RList statement and open it in the BRW Designer for the developer to refine it. RList will prompt for the report group name.  The intent is to let you quickly rough out a report or even a set of master/detail reports using RList, then use the BRW Designer to refine the result.

RList to a variable

The TARGET_VARIABLE$ option will return the result of an RList statement into a variable.  For example, you can get a list of keys without using loop/readnext, you can populate an edit table with a SELECT statement, and you can obtain CSV, XML or JSON data by calling RList.  This is similar to the OpenInsight 9 SELECT_INTO stored procedure. The SELECT_INTO syntax is still supported and now calls RTI_RLISTX internally to implement the commands instead.

Cursor support

RList support allows you to specify a cursor number between 0 and 8, or -1 to use the next non-zero cursor. OpenInsight 9 offered cursor support but the RList interface did not. Cursors permit sub-queries that don’t corrupt the main select loop.  Sub-queries can operate on other BFS’s too. For example OpenInsight calculated columns can query SQL tables.

Reduce, Select by, ReadNext, Swap.Cursor, Make.list, Save_Select, Activate_Save_Select all support cursors in OpenInsight 10.

Performance enhancements

RList implements many optimizations in selecting and reporting: 

  • It will use indexes to refine an existing select, whereas previous versions only use indexes on a select without an active select list. 
  • Caching can reduce server IO so RList can now cache rows as they are read if it knows that the same rows will be sorted or reported – previous versions always read the records to select, sort and report.   
  • RESOLVE_SELECT, the program which finalizes a select, is improved. If you specify the number of rows, RESOLVE_SELECT exits when it reaches that number of rows rather than resolving all of the rows before applying the limit.
  • RList calls a new routine, RTI_CHAIN_SELECT, to pre-process selects which has some query optimization built in. For example, it will select on indexed fields before non-indexed, and hard fields before calculated. It performs sorts after the all selects are completed.

RList syntax in OpenInsight 10

Call RList( Statement, Target, TargetName, UserArg, DebugFlag )

Statement parameter

This parameter should contain one or more OpenList (SELECT/LIST) statements, separated by field marks.  RList will process each statement sequentially until it exhausts the list of keys, selects zero rows, or executes a LIST statement.  Note that in OpenInsight 10 RList accepts the same syntax as the classic OLIST or RUN_REPORT procedures as well as that used in TCL and any legacy OpenInsight 9 syntax.   

Target parameter

Target is a code indicating the desired output format.  OpenInsight supplies an insert which enumerates the options for target. See the RLIST_EQUATES insert record for more details.

Target ValueDescription
TARGET_PRINTER$0Sends the output of a LIST statement to the printer.
TARGET_CLIENT$1Sends the output of a LIST statement to the screen.
TARGET_VARIABLE$2Returns the output of a LIST statement to a variable.
TARGET_CALLBACK$3Triggers an RLIST_CALLBACK routine.
TARGET_SAVELIST$4Performs a SAVE_SELECT operation on the result of select statements.
TARGET_ACTIVELIST$5Activates a cursor with the result of select statements.
TARGET_LATENTLIST$6Creates a latent cursor for subsequent ReadNext processing.
TARGET_CHECKSYNTAX$7Checks the statements for valid syntax but does not execute them.
TARGET_CALLBACK_NOFMT$8Triggers an RLIST_CALLBACK routine but with no formatting or truncation applied to the returned values.
TARGET_BRWDESIGN$9Generate a Banded report,  and open it in the designer.
TARGET_BRWRUN$10Generate a Banded report, and execute immediately.

TargetName parameter

This parameter is polymorphic. You supply different values for different combinations of target and SELECT or LIST statements:

TargetSelect StatementLIST statement
TARGET_PRINTER$N/aN/a
TARGET_CLIENT$N/aN/a
TARGET_VARIABLE$RList will return the keys if the output format is “KEYS”.  See the UserArg parameter.RList will return the output into the variable. The format of the output depends on the UserArg parameter.
TARGET_CALLBACK$N/aThe name of an “RList callback” procedure.
TARGET_SAVELIST$The name of the list to save. The string you pass is the list name in the SYSLISTS table.  If you pass a space delimited string, RLIST will write the list to a table other than SYSLISTS using the first word as the name of the table and the second as the id of the list.N/a
TARGET_ACTIVELIST$N/aN/a
TARGET_LATENTLIST$N/aN/a
TARGET_CHECKSYSTAX$N/aN/a
TARGET_CALLBACK_NOFMT$N/aThe name of an “RList callback” procedure.  RList will not enforce column widths on the output. Used by SELECT_INTO in OpenInsight 9.
TARGET_BRWDESIGN$N/aThe name of the report group and report to generate.
TARGET_BRWRUN$N/aN/a

UserArg parameter

Another polymorphic parameter whose format depends on the chosen Target parameter:

TargetSelect StatementLIST statement
TARGET_PRINTER$N/aUserArg<2> = cursor number
TARGET_CLIENT$N/aUserArg<2> = cursor number
TARGET_VARIABLE$UserArg<1> = output format. “KEYS” is the only relevant format. UserArg<2> = cursor numberUserArg<1> = ResultFormat ( see below ) UserArg<2> = cursor number
TARGET_CALLBACK$N/aUserArg<2> = cursor number
TARGET_SAVELIST$UserArg<2> = cursor numberN/a
TARGET_ACTIVELIST$UserArg<2> = cursor numberN/a
TARGET_LATENTLIST$ N/a
TARGET_CHECKSYSTAX$N/aN/a
TARGET_CALLBACK_NOFMT$ N/a UserArg<2> = cursor number
TARGET_BRWDESIGN$N/aThe name of the report group and report to generate.
TARGET_BRWRUN$N/aN/a

ResultFormat

ResultFormat values are applicable when returning the result to a variable using the TARGET_VARIABLE$ Target.

ResultFormatDescription
ADOResult is an OLE record-set containing VARCHAR values.
CSVResult is a set of comma separated values, all values quoted, commas between columns, carriage-return/linefeed characters between rows.
EDTEdit Table format.  Row 1 is column headings, @vm between columns, @fm between rows.  Useful for populating edit tables using RList statements
HTMLResult is an HTML table.
JSONResult is a JSON array of row objects, each column is an object in the row, multi-values are arrays.
KEYSResult is an @fm-delimited list of keys.
MVDefault format. Result is an array – @fm between columns, @rm between rows. Row 1 is the column headers.
TABResult is a set of tab-delimited column values, carriage-return/linefeed characters between rows.
TXTSame as TAB (see above).
XMLResult is an XML collection of rows, each row is an XML collection of columns.

Cursor Number

Cursor numbers specified in the UserArg parameter should be one of the following values:

  • An integer between 0 and 8
  • Null to use for cursor 0 (the default)
  • -1 for next available, which chooses an inactive cursor from 1 to 8

DebugFlag parameter

 N/a.

Code Examples

RList to PDF

RList To PDF

Stacked Queries

Sometimes it’s easier to execute a series of select statements rather than a single complex query. RList accepts a list of selects with an optional closing list statement. RList will execute until completed or no records are selected. If one of the conditions selects nothing then the list statement will not run.

Stacked queries

Query using a cursor

RList now supports cursors like REDUCE and SELECT BY. Indicate the cursor in field two of the UserArg parameter (in other words, UserArg<2>). Use this to execute sub queries. The sub query can return keys from a select or the output from a LIST statement.

Query using alternate cursor

Sub-query without corrupting the main select

Sub-query example

Sub query in calculated column

You can use sub-queries in calculated columns too. A cursor variable of -1 uses the next available cursor. This allows you to nest calculated columns which perform sub-selects. If you always use subqueries rather than direct calls to btree.extract then your logic will function with or without indexes. You can make indexing choices in the performance tuning stage of development rather than when designing the dictionaries.

Calculated column using sub-query

Return results to a variable

A new target type, TARGET_VARIABLE$ ( 2 ), will return the output to a variable. Pass a variable as the third parameter and an output format as the fourth. For SELECT statements, you must specify ‘KEYS’ as the output format. For LISTS statements you supply one of the formats listed above.

Select keys into a variable

Populating an edit table control

The “EDT” output format is convenient for loading data into edit table controls:

Populate an edit table control

Conclusion

RList for OpenInsight 10 offers new features as well as improved performance. Some of the features are available in previous versions but not via the RList interface. We tried to unify the disparate query capabilities (rlist, reduce, btree.extract, select_Into, olist ) under a single interface so you can focus on functionality and we can focus on performance.