Harry, while I have in the past advised people of ways to accomplish this, I suspect many felt it too complex so I decided that I'd post a solution myself.
Basically my solution is to create a unique file/table reference based on the columns you want to select so that the data being returned only consisted of the fields you wanted.
There is a fairly easy way to accomplish this using some of the build-in functionality within PxPlus.
The Scenario:Lets assume you want to have you application be able to run with native files or SQL tables interchangeably and you have a file called
"PRODUCTS" which has a variety of columns including a primary key of the
PRODCD$, a
NAME$ column and a
PRICE column. In order for most of your code to be compatible when running on a SQL based table, you have a PREFIX FILE which defines type of database, database name, table name, etc. so that in your code you only ever have to issue an OPEN to
"PRODUCTS"Now you want to create a PxPlus SELECT that will work on either type of file but to improve database access speed you only want the system to pull the PRODCD$, NAME$, and PRICE columns down from the server.
The Solution:To make this simple to use from your code, create a dummy system library link file called
*fields* which points to temporary memory file but invokes a device driver (
*dev/fields) to actually return the data you want.
So here is the device driver which you need to save in *dev/fields:
! Field Field filter driver *dev/fields
!
file_chn=lfo
read data from fin(lfo,"FILENAME"),sep=";" to *,filename$,fields$,keyno
if filename$="" \
then end
!
close (lfo)
!
open input (file_chn,iol=*)filename$
!
file_type$=mid(fib(file_chn),19,1)
if file_type$="2" or file_type$="K" \
then goto Keyed_file
!
if pos(file_type$="OoQa")=0 \
then msgbox "File "+filename$+" not a valid type for Filter";
close (lfo);
exit 10
!
! Database file
!
p$=stp(pth(lfo),1,";")+";**;"+stp(opt(lfo),1,";")+";"
close (lfo)
!
nopts=pos(";"=p$,1,0)
dim o$[1:nopts]
read data from p$,sep=";" to o${all}
!
path$=""
opt$=""
!
for opt=1 to nopts
o1$=stp(lcs(o$[opt]),2) ! Strip leading/trailing spaces and make lcs
if o1$="**" \
then path$=mid(opt$,2);
opt$="";
continue
!
if mid(o1$,1,4)="rec=" \
then continue ! Throw away original rec= if present
!
if mid(o1$,1,4)="key=" \
then msgbox "key ... '"+o1$+"'";
if (keyno--)<>0 \
then continue
!
opt$+=";"+o$[opt]
next
!
opt$+=";rec="+sub(fields$,"$","")
!
open input (lfo,iol=*,opt=opt$(2))path$
end
!
Keyed_file:
setdev (lfo) program pgn
setdev (lfo)iol=cpl("IOLIST "+fields$)
!
if keyno<>0 \
then extract (lfo,key="",kno=keyno,dom=*next)* ! Position file
end
!
Post_read:
local _ACCESS_MODE,_KEY$,_INDEX,_VALUE$,_FLAG,_KNO
enter _ACCESS_MODE,_KEY$,_INDEX,_VALUE$,_FLAG,_KNO
!
local iol=iol(lfa:*) ! Preserver all values
read data from _value$ to iol=iol(lfa:*)
return rec(iol(lfa))
Now can run "*UCL" to define the link file in the system library called *fields* which points to *memory* as a file with the device driver above.
'Link' files utility
Name of 'Link' file?*fields*
File '*fields*' does not exist. Create?
Yes No Quit
'*fields*' links to...
Name of file/device:*memory*
What type of link is '*fields*'
File Printer Device Quit
--- Select the driver fields from the list provided ---
Okay now you can use the logical file name "*fields*;filename;field1$,field2$,...; keyno" in an OPEN or SELECT statement and get just the fields you specify.
Where:
- filename
Is the name of the file or database table defined in your prefix file - field1$, field2$
Is a list of the fields you want returned - keyno
Is the optional key number you want to use. For Database files the fields used in the key MUST be set in the list of fields being selected.
So for the example mentioned above your select would be:
SELECT * FROM "*fields*;PRODUCTS;PRODCD$,NAME$,PRICE;0"
...
NEXT RECORDHope this gives you some ideas as to how to accomplish what you wanted.
BTW: If you use something like this just make sure you copy/create the *fields* link file and device driver to your systems library whenever getting new software releases.