Forms and Spreadsheet

Note: images to be added when format is approved!

PQLForms and Data Grid Control

SIR Advanced Training New York 2005

David Baxter SIR

Introduction

PQLForms and Spreadsheet are tools for viewing and updating your data. This paper will cover each of these then show how they can be used together and with normal PQL as well.

This course will not go into great detail on the syntax of the forms commands. This information is included with the sample files.

PQLForms

PQLForms is a set of extensions to VisualPQL to create and run sets of linked, interactive dialogs for data entry, retrieval and update. A complete set of dialogs is a single VisualPQL routine known as a Form.

Writing PQLForms

The PQLForms commands define what variables are on each dialog, how they are displayed and edited, how the dialog is to look, and how dialogs are linked together. A PQLForm has built in buttons and associated logic to allow the user to navigate through a set of records and to display, edit and insert data according to the database description. A developer can use all standard VisualPQL commands as necessary and these are executed at appropriate places in the form.

The commands look a bit like old forms with PQL syntax:

FORM

. SCREEN RECORD EMPLOYEE

.   GENERATE

. END SCREEN

END FORM

FORM UPDATE LABELS

. SCREEN RECORD EMPLOYEE

.   FIELD ID

.   FIELD NAME

.   FIELD GENDER

. END SCREEN

END FORM

Running PQLForms

PQLForms are translated into regular PQL. You can get compile errors at the forms translation phase or possibly at the retrieval translation phase. You can save a copy of the regular PQL using the /PQLFILE keyword on the FORM command.

Start form translation

End form translation

Start retrieval translation

Start retrieval execution

Generating PQLForms

A default form can be generated for a database and can be used directly to view, create, or delete records.

WRITE SCHEMA

          FILENAME = "COMP_FORM.pql" /

          PQLFORMS /

From the SIR Menus, choose Database, Write Schema… or Data, Forms…

Forms Painter

The forms painter can be used to change the layout and functionality of the PQLForm program

Embedded PQL

Some clauses take embedded PQL code. These include the SCREEN clauses:

  INITIAL (PQL code)

  READ    (PQL code)

  SELECT  (PQL code)

  WRITE   (PQL code)

And the FIELD clauses:

  EDITIN (PQL commands)

  EDITOUT (PQL commands)

and FBUTTON’s

  ACTION (PQL commands) 

Enter normal PQL with commands separated with “;”. The “;” is converted to new line in the generated PQL commands.

Subroutines

A PQLForm can be re-compiled every time it is used or the compiled version of the form can be saved as an executable member on the procedure file. A PQLForm can also be compiled and saved as a sub-routine and can then be executed as part of another PQLForm or standard retrieval. A PQLForm is run in the same way as any other VisualPQL routine either directly or from a menu.

FORM SUBROUTINE SYSTEM.EMPLOYEE REPLACE

WRITE SCHEMA FILENAME = … / PQLFORMS / SUBS /

Form subroutines can be executed:

FBUTTON ACTION (EXECUTE SUBROUTINE EMPLOYEE) PROMPT "EMPLOYEE SCREEN"

Calling Forms from PQL

RETRIEVAL NOAUTOCASE

EXECUTE SUBROUTINE SYSTEM.EMPLOYEE

Spreadsheet

The Spreadsheet control is used by both the DBMS Spreadsheet Command and the PQL GRID command.

Spreadsheet DBMS Command

SIR SPREADSHEET 

         RECTYPE = EMPLOYEE /UPDATE

Alternative data entry and modification

Control displayed variables and order with /VARIABLE=(list)

GRID PQL Command

The GRID command displays data in a spreadsheet format and can be used to display arrays of data and to accept back changes. The grid is a special dialog with predefined buttons and a grid of data. The columns are array variables; the rows are occurrences in the array. The grid displays very quickly and essentially has no size limitations beyond those imposed by processing very large arrays.

You could use VARGET and VARPUT functions to transfer database data to an array, start the GRID controls and then copy the array data back to the database.

Examples

The included example file RECGRID.PQL creates two subroutines that use the grid control to display all the OCCUP and REVIEW records for a single case. This example lets you modify the data and update the database.

Forms and Spreadsheet

The subroutines above can be executed from PQLForms using an FBUTTON:

FBUTTON ACTION (

    EXECUTE SUBROUTINE SYSTEM.OCCGRID (ID) RETURNING (RC)

   ) PROMPT "Occupation Records"             

The attached GRIDFORM.PQL is a working example of this.

PQL and Forms and Spreadsheet

A PQL Dialog program can call a PQLForm stored as a Subroutine. The DIALOGFRM.PQL shows how this can be done. When this program is run it displays a dialog. Enter an ID or letter in the top section and press Find, Select an Employee to edit their details and press one of the action buttons to change their OCCUP and REVIEW records.

Menus and Dialogs

David Baxter SIR

Introduction

This training session will show you how to write your own menu and dialog based interface using the VisualPQL commands and functions introduced in SIR200x. We will be concentrating on the logic and theory rather than the details of command syntax. Command syntax will be included as an appendix to these notes.

The SIR2002 dialog painter will be used to demonstrate how easy it is to create a dialog routine.

The Main Window

Definition

The main window program defines an interactive sir session. The program is started and defines the menu and callbacks, and then control is passed to the window. Message blocks are executed on windows events. When the Window block is exited so is the sir session.

Parts of the Main Window

Structure and logic

WINDOW

• Menu definition
• Toolbar definition
• Message block definitions
  • Initial block
  • End initial
  • Command block
  • End command
  • Drop file block
  • End drop file

END WINDOW

Non-gui PQL can appear before the WINDOW block.

Once the first message block is encountered the menu commands can no longer work.

The Menu and Toolbar blocks define the menu system and the initial block is executed. Control is passed to the windows user. Each time they make a selection from the menu or press a toolbar button

MENU definition has one or more MENU / END MENU blocks. Menu blocks can be nested. Top level blocks correspond to the horizontal top menu. Nested menu blocks become pull out submenus.

Menu blocks can contain MENUITEMs – Menu items are the only parts of the menu that trigger messages. Menuitems must have unique numbers associated with them so that the later PQL can tell which menu item has been chosen.

Menu blocks can also contain menu separators.

After the final END MENU you can optionally put the Toolbar definition. Toolbars can contain toolbar buttons (which should correspond to menu items) and separators.

The start of the INITIAL block signifies the end of the menu/toolbar definition.

The Code in the initial block is stored for execution at the point where windows is about to display the menu/toolbar.

The MESSAGE COMMAND id block is stored and executed each time a menu item is selected or a toolbar button pressed. The variable ID is used to receive the unique id of the menu item that has been chosen.

This block is required and must contain an action associated with ID # 0. This special ID should be assigned to the exit action and it is also send when the user clicks the frame’s X or presses Ctrl+F4 (MS Windows) to close.

The DROPFILE filename block is stored and executed when the user drops a file into the sir frame. The filename variable receives the name of the dropped file. This variable needs to be declared as a string long enough to hold full filenames.

The PQL programmer would want to look at the filename’s extension or read the first few bytes to decide what action should be taken with that type of file.

Control Commands & Functions

These commands and functions can be executed anytime the main menu is running (that is not in batch and not before the WINDOW block has started executing. They do not have to be physically in the window program but could be in executed subroutines or other executed code.

Running a window program

Because you can only run one window program in a session, it can be tricky to write a new window program and run it. You need to be able to execute your program before the system window starts.

When the system starts it first check for an EX=member_name parameter on the command line. If this exists then it will run that member (from the system family in the default procfile) first.

After that is run, and if that has not started a window program then SYSPROC.SYSTEM.START is run. First it checks for a file on the sir execution line with an .srm extension and runs that.  If that has not started a window then SYSPROC.SYSTEM.START calls SYSPROC.SYSTEM.MAINMENU and that contains the default system menu.

So there are two ways to run your program – using ex=member or mymenu.srm on the sir command line.

Tips

The new EXECUTE DBMS “command” command is invaluable in a menu system – it allows the current PQL program to suspend while a DBMS command is executed. This command could run another PQL program (which could EXECUTE DBMS…). This command is not allowed within a RETRIEVAL’s CASE block.

Use named constants to hold the unique command ids – it makes the program easier to read and maintain.

Use the Log File option (in settings/Preferences) to record any error messages you might get when you compile your program – if the program fails to compile the SIR session will exit before you have a chance to read the messages.

An easy way to write a main window program is to copy SYSPROC.SYSTEM.MAINMENU to a file and modify it.

Dialogs

Definition

The dialog is defined as a set of controls followed by one or more message blocks. These message blocks define PQL that is called by the dialog (and not by normal PQL control flow).

A dialog is the basic graphical unit that a user sees on a screen. It contains various controls such as labels, buttons, edit fields, etc., which are visual items that make up the dialog and allow the user to interact with the system. Within the program, dialogs have two parts: -

1) The dialog definition that specifies the dialog controls, their positions and order on the dialog. Each control is identified by a unique numeric id, which is used to reference the control during the execution of the dialog. Controls which are not referenced during execution (e.g. labels, lines) can use -1 as the id.

2) The message processing that is executed each time a message event takes place. Events include pressing a dialog button, pressing a character key in a text field, selecting an item in a list, etc. If a message event occurs and there is no message processing routine for that type of event, it is ignored.

Structure and Logic

For a dialog routine to compile it needs to specify the controls, and at least a main message block. The actual handling of messages and manipulation of controls can take place outside the dialog routine source.

PROGRAM

. include external variable block system.dialog

. DIALOG "TEST"

.   POSTYPE 1

.   LABEL     IDSTATIC,   0,        9, 131, "Dialog Structure"

.   BUTTON    IDPRESS ,   0,       98,  40, 0, "Press Me"

.   BUTTON    IDCLOSE ,  17,       98,  40, 0, "Close"

.   MESSAGE ALL M_ID, M_ARG1, M_ARG2

.     execute subroutine system.messages

.     if (m_id eq 0) exit message

.   END MESSAGE

. END DIALOG

END PROGRAM

 

Controls

The dialog controls and decorations are LABELS, BUTTONS, EDIT, LIST, CHOICE, CHECK, RADIO, TEXT, LINE and IMAGE.

Each control has an associated id. The id must be unique if the control is to be referenced in the dialog program – labels and lines do not need to have unique numbers unless you plan to HIDE them – they can all be given an id of –1.

The controls will have row and column coordinates on the dialog and may have height, width and other parameters depending on the control type. 

POSTYPE

POSTYPE 1 means the row and height references are in dialog units. Postype 0 means the rows and heights are based on the maximum height of a single line control plus a small gap. Postype does not effect column and width specifications.

Message Processing

There are two standard message processing blocks.

The INITIAL message processing routine is executed before the dialog appears on the screen and the MESSAGE ALL routine that is executed for all other standard messages. (There are some special messages HELP, FOCUS & TIMER not passed to this routine.)

Within a MESSAGE ALL processing routine there are three numeric variables passed by the generating message. If created by the dialog painter these have the following names and values:

M_ID is set to the id of the control that generated the message.
M_ARG1, is set to the position in a list or choice if appropriate.
M_ARG2, is set to 0 or 1 where 0 means a single mouse click and 1 means a double mouse click.

The EXIT MESSAGE command deletes the dialog from the screen and performs any commands that follow the END DIALOG command. When the end of the program is reached, control is returned to any higher level dialog or to the menu system.

If the user exits the dialog using the windowing system (e.g. by clicking on the X button in Windows), a button message is generated with an id of 0 (zero) so ensure that MESSAGE BUTTON 0 exits the dialog.

MESSAGE FOCUS id

Executed when a text control (id) looses focus – that is you finish entering text – you would use this block to do any checking on the full text rather than as each character is entered – which is the normal time you get messages from text controls.

MESSAGE HELP id

When a HELP message block appears in a dialog program then a context sensitive help button appears on the dialog’s title bar. Pressing the ? button followed by the control will cause the HELP block to be executed. This can be triggered by pressing F1 when the control has focus.

MESSAGE TIMER

This message processing block receives messages that are automatically generated. This could be used to refresh the display of some image or animation.

Use the ENABLE TIMER n command to start automatic generation of timer messages every n tenths of a second. Use the DISABLE TIMER command to stop generation of timer messages.

Commands and Functions

There are various commands and functions for working with dialogs and dialog controls.

The PQL commands and functions communicate with the windows system and use the control id number to indicate which control is to be used. This means that the original dialog routine does not have to be the routine that is using the functions. The functions apply to the topmost dialog.

For example in the DBMS Command dialog if you run:

PROGRAM

. FOR I = 4,10

.   SET ITEM I "Hello World"

. END FOR

END PROGRAM

Then the command will affect the DBMS Command dialog.

The above text was copy and pasted.   More to follow if this format is approved. 

SIR and ODBC

Introduction
Setting up ODBC sources
ODBC import
ODBC pql
ODBC members
Using sirweb.cgi
ODBC and the sirweb.cgi
ODBC and the PQLServer
Conclusion

---

Introduction
For years the only easy way to exchange data between SIR and other systems was through files.
SIR procedures could be used to write files for specific systems i.e. SPSS,SAS dbase etc.

• SIR PQL can be written to create scripts and files to build databases in other systems such as Oracle and MySql
• SIR batch data input could be used to import data into SIR from other systems provided they could write fixed column data.
• SIR PQL can be written to read other data formats.
• With the advent of ODBC life becomes a lot easier for all ODBC enabled systems.
• Another system that has become very popular is the web. Getting data to and from that is now very easy.
• Cross platform access is now possible for these technologies as well.
• ODBC Open DataBase Connectivity
• This session will show you how to set up ODBC sources and use them from SIR.
• It will show the simplest way to go about this with PQL members available from SUG website.
• It will show how to access SIR data via ODBC from other packages.
• It will show how to exchange data using the web.

Setting up ODBC sources

• ODBC sources are set up using Data Sources from the Administrative Tools Menu
• Sources can be User, available to that user only or System, available to all users both are configured in exactly the same way but from different tabs
• There is another type of DSN labelled File DSN this is an alternative way of storing the ODBC parameters. User and System store the details in the registry, File stores them as the name implies in a file, it can point to the registry for parameters if desired.

• I shall show the steps in setting up  some System DSNs
• I have previously set them all up as User DSNs for use in the demonstrations

• Choose the source and click Finish.
• You will then get a configuration screen which will vary according to the source.
• The next screens will show various different types