January/February 2000: Volume 10 Number 1

PFXplus 5.0 | SQL and ODBC | Active radio buttons | FlexTable Powerflex for Delphi | Fonts with WM_SETFONT

Experience the Power of PFXplus Version 5.0

You can expect nothing less than the best from Powerflex Corporation’s latest and greatest achievement – PFXplus version 5.0 ! This major new release contains many exciting new features that make life even easier for PFXplus developers.

Read on for a list of some of the dazzling new features, including:

Below is a basic example of the type of reports that are possible in PFXplus with the new WINOPT option and Print Preview!

WINOPT Demo

Other features include new sample programs as well as improved Table Lookups and lots more.

PFXplus version 5.0 is right on track for its scheduled release at the end of March. For those who can’t wait to get their hands on it, you can get in early by filling out the ORDER FORM and faxing it to Powerflex Corporation on +61 3 9888 5451, or you can contact your local dealer.

Top of Page

The Power of SQL and ODBC!

David Bennett – Powerflex Corporation – Australia

Over the past year Powerflex Corporation has announced a number of new products and options to support SQL. Some of the terminology and some of the functions overlap, making this a potentially confusing area for our customers. This article aims to help customers to understand what we have to offer and how it will help them to deliver better solutions.

SQL stands for Structured Query Language. Invented by IBM in the early 1970s, SQL is both an interactive query language and a database programming language for manipulating data stored in a relational database. It includes statements for retrieving data (SELECT), modifying data (INSERT, DELETE, UPDATE), managing tables (CREATE, DROP) and for general programming (SET, CALL).

SQL Allows Complex Queries

The power of SQL comes from its ability to express arbitrarily complex queries without needing predefined access paths. SQL statements can join multiple tables, filter out just the needed data, choose a subset of columns and apply operations such as grouping and totalling, all without programming. The power is awesome!

Following concerted efforts by a number of parties, large parts of SQL are now standardised and work consistently from one vendor to another. However the programming interface, the means by which SQL commands in a program are submitted to a database and results retrieved, was not.

Microsoft’s Multi-Vendor SQL

Microsoft introduced ODBC (Open Database Connectivity) to provide exactly this capability to Windows users. ODBC defines an API (Application Programming Interface) and an SQL grammar, through which data stored from many different vendors can be accessed.

ODBC is now widely used by programming languages and end-user tools as a standardised way to retrieve or manipulate data in the Windows environment.

Since ODBC 3.5 now fully implements the CLI (Call-Level Interface) specifications from X/Open, and uses ANSI/ISO standard SQL, it represents a truly global standard.

Powerflex Corporation has three main products that use SQL and ODBC. They are PFXodbc, PFXplus SQL Direct, and PFXplus Mark 5 Support.

Fully-Fledged ODBC 3.5 Driver

PFXodbc is a fully-fledged ODBC 3.5 driver providing other applications with access to data stored in PFXplus database files. It works by installing an ODBC driver which contains the same powerful file access routines as in the PFXplus runtime and PFX C-lib. The ODBC driver is then invoked by the ODBC driver manager in the Control Panel to configure a data source (DSN) which allows access to any set of PFXplus data files. Now any application that uses ODBC to access data is able to select this DSN and access the data in the PFXplus database files.

PFXodbc supports a wide range of SQL operations, including SELECT, UPDATE, INSERT and DELETE. It can also be used indirectly, by programming using ADO, DAO, RDO and similar techniques. It does not support locking or transaction control for multi-user updating.

PFXodbc is intended for developers who need to allow non-PFXplus applications to access PFXplus data, or who wish to use the power of SQL to manipulate PFXplus data. It is also suitable for end-users who wish to retrieve data using ODBC-enabled query and reporting tools.

For example, end-users with Microsoft Access can browse individual PFXplus database files as relational database tables and make changes to the data using the built in Access table editor. Likewise they can retrieve data into other Microsoft applications using MS Query, a standard part of Microsoft Word and Excel, or Seagate Crystal Reports.

Developers can write applications using standard Windows languages such as Visual Basic and Delphi to retrieve or modify the contents of PFXplus data files using the power of the SQL language. They can also write Web pages using ASP (Active Server Pages), or add links into other applications, allowing data to be imported and exported to PFXplus applications in real time.

PFXodbc is a stand-alone product, and can be used even with a Dataflex application.

PFXplus SQL Direct

PFXplus SQL Direct is complementary to PFXodbc, as it allows PFXplus applications to get access to ODBC data. PFXplus applications using this product can connect to and execute queries on any ODBC data source, and retrieve results for further processing by the application.

SQL Direct works by providing two additional capabilities in the PFXplus language. First, it adds SQLEXEC, which allows a program to execute any single SQL statement. SQLEXEC is used for connecting to a database and for executing statements such as INSERT, DELETE, UPDATE, CREATE and DROP, which do not return a result set.

The other capability is provided by SQLOPEN, which executes a query, and SQLFETCH, which processes the returned result set. The commonest use is to execute a SELECT statement and then process the rows of data returned. The data can be processed directly, or stored into an in-memory table for further analysis.

PFXplus SQL Direct is primarily for experienced PFXplus application developers who wish to have their applications exchange data with other applications. It is supplied as a PFXplus runtime replacement for PFLN.

PFXplus SQL Mark 5 Support

PFXplus SQL Mark 5 Support allows a PFXplus application to have its entire database set up on a relational database management system (RDBMS). The application will require little or no coding change, as all the existing database statements such as OPEN, FIND, SAVE and DELETE work as normal.

A Mark 5 database application takes advantage of powerful features of the database manager such as transaction logging and rollback, guaranteed integrity, security and scalability. It allows the application to be integrated seamlessly with other applications using the same database manager.

Each PFXplus database file corresponds to a database table, and each field corresponds to a column in the table. Any reporting or query tools or applications which can access the underlying database can access the PFXplus tables, just like any others.

The first step in converting an application to Mark 5 is to install the chosen RDBMS (such as Microsoft SQL Server) on client and server. Next, the developer creates an empty PFXplus SQL database using a program called PFMKSQLT. Then the database is converted from its current format (which may be any of Mark 1 to 4) into Mark 5 using the conversion program PFDATSQL. Both these programs are provided as PFXplus source code and may be customised if needed. With the configuration items set up correctly, the Mark 5 application will now run.

PFXplus SQL Mark 5 Support is primarily designed for developers with larger customers who have a definite need to store their data on a particular database system, and who have the skills required to set it up and manage it. Hardware requirements tend to be higher, particularly at the server end, but performance on high-end equipment is outstanding.

Convert Applications to Mark 5 in Three Steps

Developers unfamiliar with the relational database world face a significant learning curve, but the rewards are substantial. This product is supplied as an add-on upgrade to PFXplus, with additional documentation and conversion tools, and includes PFXplus SQL Direct. Currently, the DBMS can be Microsoft SQL Server, IBM DB2, Sybase or Oracle.

The developer will need to purchase the PFXplus SQL Developer Kit, which includes a five-user SQL licence for testing purposes. The end-user will require a runtime licence for SQL Direct, or for Mark 5 support, for the appropriate number of users.

These three powerful products allow PFXplus developers to take full advantage of the power of SQL and ODBC. Contact Powerflex or your dealer for details.

Top of Page

Active Radio Buttons

David Roberts – Powerflex Corporation – Australia

Often it is useful to perform an action at the click of a button. But not necessarily an OK button – perhaps a radio button or even a check box. Here we explain how to change the default behaviour of a radio button to execute an action the instant it is clicked. The same technique can be applied to check boxes.

Class wRadio is defined in WINOBJ.PFI as:

CLASS WRADIO OF WCONTROL;
{WINCLASS="button",;
style=BS_AUTORADIOBUTTON}
END_CLASS

Subclass wRadioBtn of class wRadio contains the appropriate code to alter the default behaviour of the radio button (see below). The radio buttons are defined in the Object Definition Hierarchy as wRadioBtn objects.

Procedure $START is called before the radio button object is displayed. The integer variable code is set in the radio button object definition (see the object definition hierarchy below). In this case code is set to FK_USER3.

When a radio button is clicked, the event EVI_NOTIFY is triggered. In the subclass definition below, EVI_NOTIFY pokes the termchar value (or current key mapping) of code, as returned by the function Get$FlexKey.

When a termchar is poked, it has the effect of calling the associated keyproc (in this case keyproc KEY.USER3).

The code in the keyproc is then executed and the desired action is performed.

Sample Code for Active Radio Button

#use KEYS.PFI
#use WINOBJ.PFI
//[]--------------------------------------------[]
//[] Sub Class wRadioBtn
//[]--------------------------------------------[]
// Keypokes the designated Function key
// instantly when a Radio Button is clicked
Class wRadioBtn of wRadio
public:
integer code
PROCEDURE $START if code <= 0 code = FK_RETURN END_PROCEDURE
FUNCTION Get$FlexKey integer k returns integer string s 210 s = get_env("tKeyMap") result=ascii(mid(s,1,4*k+2*0+1)) ; +256*ascii(mid(s,1,4*k+2*0+2)) END_FUNCTION
event EVI_NOTIFY //integer arg1 integer arg2 keypoke Get$FlexKey(code) $CURRENT_EVENT=0 // otherwise pass through End_Event
End_Class
//[]--------------------------------------------[] //[] Object Definition Hierarchy
//[]--------------------------------------------[] CCA_FormBegin screen1 "Enquiry Screen" ; "STANDARD" "STANDARD" "STANDARD" 500 330 CCA_FormSubformBegin 1 screen1 wRadioBtn {wndw=s1_rbPrintDEF,code=FK_USER3} wRadioBtn {wndw=s1_rbPrintFD, code=FK_USER3} CCA_FormSubFormEnd CCA_FormEnd
//[]--------------------------------------------[] //[] K E Y P R O C S //[]--------------------------------------------[] keyproc key.user3 Switch s1_rbPrintDEF when 1 | s1_Description = Fname + ".DEF" when 2 | s1_Description = Fname + ".FD" endSwitch return Start

Top of Page

FlexTable Powerflex for Delphi

Wally Ripper – Razar Pty Ltd – Australia

FlexTable is a new product that expands on the PFX C-lib DLL to provide Powerflex database access from within Borland’s Delphi. FlexTable offers a seamless integration of Powerflex databases into this exciting new development platform. Now you can add the flexibility of the Delphi development environment to your existing Powerflex systems and combine the strengths of each.

FlexTable and Delphi

Delphi is a Windows RAD (Rapid Application Development) tool that is especially well suited to building Internet, database, and distributed applications. Delphi is an object-orientated development platform with a Pascal-style syntax like Powerflex, and uses modular design to simplify project development. One of the strengths of Delphi is that its open architecture allows custom components written by third party groups to be incorporated into your programs. This can help reduce development time by allowing incorporation of existing proven products when special needs arise. If a custom functionality is needed then the odds are that just such a component has been written (the range of products available includes internet protocol tools, report writers, barcode readers, fax components and more).

To provide open access for database developers, the database programming tools are based around a generic database wrapper (TDataSet). By encapsulating a database in a custom descendant of TDataSet, any database can be used in the visual development environment. FlexTable is a TDataSet descendant that has been written to provide a wrapper for the PFX C-lib Windows DLL.

FlexTable performs all data access through the PFX C-lib DLL so that data integrity is maintained. Your data tables are still accessible by your existing systems, therefore FlexTable and your existing systems can run side by side. To ensure safe multi-user operations, table locking by FlexTable is performed through PFX C-lib.

An Overview of FlexTable

FlexTable is a descendant of the Delphi core database access object TDataSet. This is a generic wrapper that provides the basic database operations. TDataSet provides local record buffering, simple navigation and add, search and filter capabilities. In addition, FlexTable has implemented some of the more complex dataset functions so as to maximise the power of the component.

Passing records between the Delphi environment and the PFX C-lib DLL is the core function of FlexTable. When a record is requested, FlexTable passes that request to the PFX C-lib DLL. If the record is found, then it is passed to the FlexTable record buffer. As there are some differences between the internal data formats of Powerflex tables and the Delphi platform, data conversion may be required. When changes are made to a record, or a new record is to be inserted, then FlexTable will locally verify the data, perform any data conversions needed, request a table lock and send the record to the PFX C-lib DLL for saving. All data updates using FlexTable are 'write through', so that updates are written to file as soon as the alterations are committed.

FlexTable has searching facilities based on both the power of Powerflex index-based searches and the more general searching capabilities required of TDataSet components. FlexTable can execute index-based search requests on any defined index but extends this functionality by also performing non index-based or partially index-based based searches for any given record.

FlexTable enhances the PFX C-lib DLL by providing the ability to examine only a subset of the data. This can be by either restricting the range of values allowed on the currently selected index (the most efficient form of restriction), or by using filters to customise the restrictions independent of any indexes.

FlexTable does not enforce referential integrity between tables, but does provide the ability to link tables into master-detail relationships. These master-detail relationships can be set on an indexed field in the detail table, which is then linked to a field in the master table. FlexTable also supports the ability to provide lookup operations on other tables.

It is possible to add your own custom behaviour to FlexTable by writing custom events. These events are surfaced around most of the common operations and can contain your own custom code to verify data, notify other components of changes, or perform some special processing of data.

Because most data access operations using FlexTable simply access the required record from the PFX C-lib DLL and convert the data if necessary, FlexTable adds only a small overhead to the operations of the Powerflex DLL. This avoids the performance detriments associated with other forms of access such as ODBC.

Extensibility and Flexibility

FlexTable can help you write more flexible programs by leveraging on the existing base of custom tools available for Delphi developers. By using FlexTable, you can instantly get access to third-party database manipulation tools. This might provide (for example) custom report generation, charts, or faxes. These extensions can co-exist with your existing systems.

RAD Environment

FlexTable can also assist in project prototyping by using the Delphi RAD environment to enable rapid development of screen mockups that can be compiled to a portable executable file. Here is an example:

Example: Database of Insurance Companies

Have Your Cake and Eat It Too

With FlexTable you can combine the award-winning Windows visual development platform of Delphi with Powerflex databases and blend the results with the proven strength of your existing Powerflex systems. FlexTable is currently being used successfully both in desktop applications and in high-traffic Internet applications.

FlexTable Features

Delphi support for TDataSet components

A fully working demonstration copy can be downloaded from www.flexfiles.com.

Top of Page

Fonts with WM_SETFONT

Christine Charalambous – Powerflex Corporation – Australia

There are a number of ways in which you can set the font in your Windows GUI mode applications.

One common method is to set the sFontName and sFontSize configuration items in the PFX.INI file, which sets the font name and size for the entire dialog. An alternative method is to set the fontName and fontSize properties of a FORM object in the definition hierarchy, which does the same.

Not so well known however, is the WM_SETFONT method. The major difference between WM_SETFONT and the above methods is that WM_SETFONT offers the ability to set the font properties for individual objects on a dialog, whereas the others can only be applied to the entire dialog.

WM_SETFONT is a Windows constant defined in the WINDEFS.PFI include file, and is easily used simply by passing it, the object handle and the font to the API call, winSendMessage.

Once the font of a particular object has been set using WM_SETFONT, it often becomes necessary to set the control height and/or width in the PFXplus Dialog Resource Editor to accommodate the new font size, especially where is it larger than the default.

Changing the font of a static is a bit different as there is no "static" object as such, so handles on statics need to be accessed in a different way to other controls. You will notice in the sample code below that two different methods have been used.

One of the methods utilised is to call the Windows function GetDlgItem. This function is passed the handle of the dialog on which the static resides, as well as the control ID of the static, as set by the resource compiler, MKRES. GetDlgItem returns a handle on the static control specified by the control ID.

The other method is to format a control as a static and pass the control object handle to winSendMessage.

Demo program output

The above is the output from a simple demonstration program, from which the block of code below was extracted. This program is available for download from this Web site.

Sample Code for Setting Fonts with WM_SETFONT

// Get handle using GetDlgItem
hStaticHandle= ;
winGetDlgItem(oScreen1\handle(),1500)
sts=SetHeadingFont(hStaticHandle)
sts=SetStaticFont(s1_Static2\handle())
// Control formatted as a Static FUNCTION SetHeadingFont integer hStaticHandle ; RETURNS indicator integer ijunk iStaticFont= WinCreateFont(20,; // Height 5,; // Width 0,; // Escapement 0,; // Orientation 600,;// Weight 1,; // Italic 0,; // Underline 1,; // StrikeOut 0,; // CharSet 1,; // Out Precision 2,; // Clip Precision 1,; // Quality 34,; // Pitch & Family string("Courier"+character(0))) // Facename ijunk = WinSendMessage(hStaticHandle, ; WM_SETFONT, iStaticFont, True) RepaintWindow hStaticHandle result = true END_FUNCTION

Top of Page