July 2005: Volume 14 Number 1

VAR Programme | Service Pack 3 | SQL Direct | PFXodbc&MS SQL | News, products update

The Value Added Reseller Programme

David Bennett – MD Powerflex Corporation – Australia

The Powerflex Value Added Reseller (VAR) and Maintenance and Support (MSP) programmes are now in full swing and the feedback from our customers has been extremely positive. Most of our customers have found it quite easy to understand and have been quick to take advantage of the benefits.

Our Commitment to our Customers

It should be clearly understood that the VAR and MSP programmes are our commitment to the long term development of our products and first-class support for our valued customers. It is our intention to move to a situation where most, if not all, of our customers are members of these programmes, and to work with programme members in allocating resources to long term support of our products.

The VAR programme provides a number of benefits, including discounted pricing for products, free maintenance and free email support. Currently it also includes free telephone support for urgent problems. See our Web site for details.

We remain committed to supporting all of our products, and we continue to provide support for critical problems, even in versions released a number of years ago. Naturally, our priorities are with our current products. Enhancements and corrections for any reported problems will continue to be released for Version 5 and included in service packs, which are provided free under the MSP programme.

Elsewhere in this issue are details about Service Pack 3 for PFXplus, released in April and provided to all VAR and MSP members who subscribed as at that date. If you haven't received yours yet, perhaps it's time for you to consider joining the programme.

Service Packs are provided specifically for development systems and are tailored to individual features and versions. As a member of the VAR programme you also get the right to apply that Service Pack to any corresponding runtime licences you may have issued to your customers.

New Features and New Products

We continue to work on new features and new products for release later this year and into next year. PFXweb is rapidly approaching the point where it is ready for general release. We have powerful new features to add to the PFXplus runtime.

The VAR and MSP programmes are an integral part of our plans for continuing to develop PFXplus and provide support for our valued customers. If you have not yet done so, I encourage you to join now.

Top of Page

PFXplus Service Pack 3

Gerard Nolan - Powerflex Corporation – Australia

Service Pack 3 for PFXplus Win32 Version 5.0 incorporates many new features, configuration items and corrections. This effectively consigns a lot of minor annoyances reported by PFXplus Developers to the annals of history.

What we Added

As a result of feedback and suggestions from customers and our own ongoing development many new features have been added to PFXplus.

What we Changed

Service Pack 3 contains many enhancements and fixes. These include:

Be Careful

As a result of some of the changes made to the compiler, it is important to note that programs compiled with the new compiler, with either the runtime expression evaluator or the debugger expression evaluator enabled and that contain a large number of symbols will not work correctly with earlier runtimes.

Where to from here

The Service Pack for both developer and end-user runtimes is free to members of the VAR program, and may be freely supplied to all your customers.

After that, it's up to you, the PFXplus Developers.

We are always trying to improve our products and value the feedback from all our customers. So, if you have an idea for a new feature, or something which you'd like to suggest we do differently, or even a problem which can be reliably reproduced, then please send it through to us here at Powerflex.

Service Pack 3 Extended! Since this article was written, we have extended Service Pack 3 to include fixes and enhancements for both, PFXbrowse and PFXodbc. Contact us for more details.

Top of Page

SQL Direct, MS Excel & MS Access

Eva Meisel - Powerflex Corporation - Australia

PFXplus SQL Direct is a wonderful extension of the Powerflex runtime used for accessing third party databases in PFXplus programs. In the previous SQL Direct articles, Volume 12 No 1 and Volume 10 No 2, we demonstrated how to write PFXplus code that uses SQL commands to import third party data to PFX files. Today we will focus on writing PFXplus data to MS Access table and Excel spreadsheet directly from a PFXplus program.

Write From PFX to MS Excel

PFXplus SQL Direct accesses Excel through an ODBC connection. The Excel ODBC driver interprets a workbook as a database and considers each worksheet within the workbook to be a table. The columns and rows of the worksheet are the columns and rows of the table. When accessed through the ODBC, Excel adds a "$" symbol to the name of the worksheet and expects that the first nonblank row contains the labels for worksheet columns.

Testing requires the PFCN compiler and the SQL Direct runtimes PFLNSD or PFLTSD.

Let's create an Excel file composers.xls that contains Sheet 2 to be updated with data from sample data file composer.dat.

In the ODBC Administrator setup an odbc data source connecting to composers.xls via the Microsoft Excel driver. Clear the Read-Only check box in Select Workbook.
In the following PFXplus code, the first SQLEXEC creates a table with one column named 'surname_here'. The next SQLEXEC statement loads the data into the Excel file using the standard SQL statement INSERT INTO. String data VALUES are enclosed in quotation marks.

// For readability some lines have been broken
string nam str 100
SQLEXEC "@connect pfxcomposers,,"
str='CREATE table [Sheet2$] (surname_here Text)'
SQLEXEC str
open "composer" as composer
file_mode composer read_only
clear composer
find GT composer by index.1
while found
nam=composer.surname
str=('INSERT INTO [Sheet2$] (surname_here)') + ;
" VALUES ('" + trim(nam) + "')"
SQLEXEC str
str="
FIND GT composer by index.1
loop
SQLEXEC "@disconnect"

Open sheet2 in composers.xls and run the above code.

Inserts result in an append to the table. According to the Microsoft web site, the Excel ODBC driver does not support DELETE, UPDATE or ALTER SQL statements. If you wish to use any of these, consider using MS Access. It will provide a more robust database than the Excel spreadsheet.

Write From PFX to MS Access

In the ODBC Administrator create a DSN for an existing MS Access database that will receive the PFXplus data.

Once this is done, it is time to compile and run the program.

// For readability some lines have been broken
Open "composer" as composer
file_mode composer read_only
SQLEXEC "@connect comps,pfxuser,userpassword"
PROCEDURE CreateTableComposers
SQLEXEC ('CREATE table composers
(composer_no INT PRIMARY KEY, surname CHAR(20) NULL) ')
END_PROCEDURE
PROCEDURE LoadDataComposers
string nam str 100
integer i
i=0
Clear composer
Find GT composer by index.1
while found
i=i+1
nam=composer.surname
str=('INSERT INTO composers VALUES ') +
"(" + string(i) + ",'" + nam + "')"
SQLEXEC str
str=""
FIND GT composer by index.1
loop
END_PROCEDURE
CreateTableComposers
LoadDataComposers

Composers appear in the MS Access table.

Easy Transfer

It is this easy to transfer data between Powerflex and Microsoft applications using PFXplus SQL Direct runtimes. If you can create an ODBC data source for the Microsoft application your program needs to communicate with, PFXplus SQL Direct will do the job!

Top of Page

PFXodbc and MS SQL Server

Stephen Bennett - Computing Directions Australia

When our customers buy our product, they always want data converted from their previous system into ours. My role is to carry out that conversion by analysing their data and writing SQL scripts to insert it into the 200 or so tables that make up our product. Sometimes it's a straightforward process, especially if we've done many conversions from that system before.

Before we can start analysing the data, however, we have to somehow get it into SQL server. On one occasion, the CD of data to be converted arrived containing about 340 files with extensions TAG, DEF, TAB, K1, K2 and so on. In another directory were various files making up the old system. Although I wasn't familiar with Powerflex, there were some Powerflex scripts on the CD, which led me to contact Powerflex for assistance.

We purchased the PFXodbc driver and once it was installed I set up the data source (DSN) according to the directions provided with the product.

When setting up the DSN, I selected to process all files on the data path (Figure 1). Also, from this screen, I made note of the log filename as this is the best place for finding out what errors and warnings are detected when creating the DSN.

Figure 1

When this process completed I checked the above mentioned data.log and found that the DSN was set up without errors. There were a few warnings but these could be ignored.

Once I had created the DSN, I considered the following 3 methods of migrating the Powerflex data into SQL Server. Microsoft's Data Transformation Services (DTS), Delphi's Data Pump or write my own program.

Both DTS and Data Pump work well with PFXodbc but to keep the conversion in line with our application I opted to write a small program in Borland Delphi. It reads the PFXplus data via the PFXodbc driver, then writes it out to comma separated values files (csv). This was useful as we already have another tool to import from .csv files into SQL server.

Below is an extract of the code I wrote. It shows the connection string used to connect to the PFXodbc DSN. The entire program can be downloaded here .

procedure TForm1.btnGoClick(Sender:TObject);
var i,col,row: integer;
F: TextFile;
FName: String;
tempstr: String;
begin
lbxProblems.Items.Clear;
conMain.Close;
conMain.ConnectionString :=
'Provider=MSDASQL.1;Password="";' +
'Persist Security Info=True;User I' +
'D=pfxuser;Data Source='+edtDSN.Text+';Mode=Read';
conMain.Open;
lbxTables.Clear
conMain.GetTableNames(lbxTables.Items);
RecordCount.Clear;

Conclusion

At the end of the day I found the PFXodbc driver solved my problem very effectively and without it life would have been much more difficult if not near impossible for this particular data conversion.

I can recommend the product and the support from Powerflex for the odd problem that I encountered was superb it made the experience of using the driver stress free.

Top of Page

Powerflex News and Product Update

Since the last issue of POWERlines we have introduced a new procedure for distributing Service Packs. Developers eligible for Service Packs now receive a file by email which automatically installs the Service Packs for their relevant products. Feedback for this new procedure has been very positive.

GUI Conversion

From the number of enquiries we have been receiving it seems there are still many old Dataflex (and indeed Powerflex) character mode applications out there just waiting to be upgraded to Windows GUI. If you are a developer with such an application then we have the easiest solution for you.

PFXplus provides the simplest upgrade path from character mode to GUI for both Dataflex and Powerflex applications. Let us show you how easy it is by contacting us now.

PFXcrystal Beta

Beta testing of PFXcrystal version 5.0 with support for Crystal Reports versions 9, 10 and 11 is currently in progress. If you would like to be part of this programme and have an existing PFXcrystal version 5.0 licence then contact us for more details.

PFXplus or the Web

The latest beta version of PFXplus for the Web has just been released. Significant enhancements have been made in this release following feedback from Powerflex developers involved in the beta testing programme. As we move into the final stages of beta testing this exciting and innovative product, we encourage more PFXplus developers to become beta testers and experience its power for themselves. Contact us to find out how to become a beta tester.

Contributors Welcome

Don't just sit on that great piece of code or that new idea. Share it with other Powerflex Developers and write an article for POWERlines. We are always interested to hear about the things our developers are doing. Contact us if you have something you want to tell everyone about!

Top of Page

Current Versions of Powerflex Release Software

PFXplus Developer's Kit 32-bit for Windows/Btrieve/SQL incl SP2 5.0
PFXplus Runtimes 32-bit for Windows/Btrieve/SQL incl SP2 5.0
Service Pack 3 for PFXplus Developer/Runtime/PFXodbc/PFXbrowse 32-bit for Windows/Btrieve/SQL 5.0
PFXplus for UnixWare incl SP2 5.0
PFXplus for Open Unix 5.0
PFXplus for AIX incl. SP2 5.0
PFXplus for SCO Unix 5.0
PFXplus for Linux SP1 5.0
PFX C-lib 32 bit for Windows/SQL incl SP2 5.0
PFX C-lib for Linux 4.41
PFX C-lib for SCO Unix 4.41
PFXsort for 32 bit Windows/SQL incl SP2 5.0
PFXsort for UnixWare 5.0
PFXsort for SCO Unix 4.41
PFXsort for Linux 4.41
PFXbrowse 32-bit Windows/Btrieve/SQL Developer version incl SP2 5.0
PFXbrowse 32-bit Windows/Btrieve/SQL End-user version incl SP2 5.0
PFXbrowse Developer for Linux 4.41
PFXbrowse Developer for SCO Unix 2.10
PFXodbc 32-bit for Windows incl SP2 5.0
PFXcrystal 32-bit for Windows SP2 5.0
PFXplus HTML Help 5.0

Top of Page